How to copy a PostgreSQL database to another server

Copying a database from one PostgreSQL server to another is a backup and a restore back to back. You'll need it to refresh a staging database from production, to move from one host to another (say Heroku to Neon), or to pull a production copy onto your laptop for debugging.

The two-command version

Dump from the source in custom format, then restore into the target:

$bin = 'C:\Program Files\PostgreSQL\18\bin'

# 1. Dump the source
& "$bin\pg_dump.exe" -h prod.example.com -U app -F c -f "$env:TEMP\shop.dump" shop

# 2. Create the target database and restore into it
& "$bin\createdb.exe" -h localhost -U postgres shop_copy
& "$bin\pg_restore.exe" -h localhost -U postgres -d shop_copy --no-owner --no-privileges "$env:TEMP\shop.dump"

• --no-owner makes the restoring user own everything instead of trying to recreate the source's roles, which usually don't exist on the target.
• --no-privileges skips GRANTs for those same missing roles. Leave both off only when the target has the same roles as the source.
• Use client tools at least as new as the source server (why). The target can be newer than the source, which makes this a clean way to upgrade major versions.

Add $env:PGSSLMODE = 'require' before a command that talks to a hosted database.

Copying over an existing database

To refresh a target that already has the tables (staging, typically), either drop and recreate it:

& "$bin\dropdb.exe" -h staging.example.com -U postgres --if-exists shop_staging
& "$bin\createdb.exe" -h staging.example.com -U postgres shop_staging

or let pg_restore remove what it's about to recreate:

& "$bin\pg_restore.exe" -h staging.example.com -U postgres -d shop_staging --clean --if-exists --no-owner --no-privileges "$env:TEMP\shop.dump"

--clean --if-exists drops each object in the dump before recreating it. Objects that exist only on the target (a table you created on staging) stay. To end up with an exact copy, drop and recreate the database.

Big databases: parallel dump and restore

With the directory format, both sides can use several connections:

& "$bin\pg_dump.exe" -h prod.example.com -U app -F d -j 4 -f "$env:TEMP\shop_dir" shop
& "$bin\pg_restore.exe" -h localhost -U postgres -d shop_copy -j 4 --no-owner --no-privileges "$env:TEMP\shop_dir"

-j 4 uses four parallel jobs. Don't go beyond the CPU cores of the server being read.

Piping without a file

On Linux people often pipe pg_dump | psql. On Windows PowerShell 5.1, pipes between native programs pass text and re-encode it, which corrupts custom-format output. Stick to the file, as above. It's also a backup if the restore fails halfway.

Copying only some tables

& "$bin\pg_dump.exe" -h prod.example.com -U app -F c -t public.customers -t public.orders -f "$env:TEMP\two-tables.dump" shop

Or dump everything and restore selectively with pg_restore -t orders. Excluding large log tables from a staging refresh is common: --exclude-table-data=public.audit_log keeps the table but not its rows.

Things that don't come along

• Roles and their passwords: create them on the target first, or use --no-owner --no-privileges.
• Extensions must be available on the target server. Hosted providers support different lists.
• Sequences come along with their current values, so new rows don't collide.

How PostgresBackup does it

PostgresBackup's Clone does exactly this from a dialog. Pick the source server and database, pick the target (it creates the database if it doesn't exist), and click Clone. It runs pg_dump and pg_restore for you, restores with several parallel jobs (one per processor core, up to 4), shows progress table by table, and checks that every table arrived. Before it starts, it checks that the drive holding its temporary dump file has room, and you can put that file on another drive. It asks before overwriting anything, and it refuses to clone a database onto itself. Cloning is free, with or without a license.