How to Export and Import a PostgreSQL Database (PowerShell and Bash)

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`