Learn how to export and import a PostgreSQL database on both Windows and Mac. This guide provides a clear, side-by-side comparison of the commands needed, using PowerShell for Windows and Bash for Mac.

PowerShell

Export database

pg_dump -U postgres -d codingbowl_existing_db > codingbowl_existing_db_backup.sql

Create new database

createdb -U postgres codingbowl_new_db

Import database

Get-Content -Path codingbowl_existing_db_backup.sql | psql -U postgres -d codingbowl_new_db

Bash

Export database

pg_dump -U postgres -d codingbowl_existing_db > codingbowl_existing_db_backup.sql

Create new database

createdb -U postgres codingbowl_new_db

Import database

psql -U postgres -d codingbowl_new_db < codingbowl_existing_db_backup.sql

Additional Information

If your PostgreSQL server is running in Docker or on a different host/port, you'll need to specify the host and port in your commands. Also, be aware of character encoding issues, which are common when moving databases between different systems.

Installation

If you don't have PostgreSQL installed, you'll need to install it first. On macOS, you can use Homebrew:

brew install postgresql

Specifying Host and Port

When running your server on a non-default host or port, add the -h (host) and -p (port) flags to your commands. This is crucial for connecting to a server running in Docker, for example.

createdb -h localhost -p 5432 -U postgres codingbowl_new_db

psql -h localhost -p 5432 -U postgres -d codingbowl_new_db < codingbowl_existing_db_backup.sql

Converting Character Encoding

PostgreSQL requires UTF-8 encoding. If your database backup file has a different encoding, such as UTF-16LE, you must convert it. The iconv command is an easy way to do this.

iconv -f UTF-16LE -t UTF-8 codingbowl_existing_db_backup.sql > codingbowl_existing_db_backup_utf8.sql