There are many ways to move a PostgreSQL database between servers, but for a production-grade setup,where roles already exist and you simply want to migrate one database cleanly, the safest path is to create the target database first and restore into it.
This approach keeps ownerships and permissions consistent, avoids overwriting roles, and works perfectly between PostgreSQL servers of the same major version (in this case, version 15).
Preparing the Destination
Start by making sure the role (user) already exists on the destination. In this example, we’ll migrate a database named myapp that belongs to the role myapp_user.
Check if the role exists.
|
0 1 2 |
sudo -u postgres psql -c "SELECT 1 FROM pg_roles WHERE rolname='myapp_user';" |
If not found, create it.
|
0 1 2 |
sudo -u postgres psql -c "CREATE ROLE myapp_user WITH LOGIN PASSWORD 'strongpass';" |
We won’t import global roles from the source, because I already manage users manually.
Step 1, Create a Backup on the Source Server
On the source host, run:
|
0 1 2 |
pg_dump -U postgres -h localhost -Fc -f /tmp/myapp.dump myapp |
This command creates a custom-format dump file that can be restored flexibly and in parallel.
Check the file.
|
0 1 2 |
ls -lh /tmp/myapp.dump |
Step 2, Copy the Dump to the Destination
Send the dump file securely to the new server.
|
0 1 2 |
scp /tmp/myapp.dump user@pg-dst:/tmp/ |
Or, if you prefer to pull it.
|
0 1 2 |
scp user@pg-src:/tmp/myapp.dump /tmp/ |
Step 3, Create the Target Database
Now on the destination server:
|
0 1 2 |
sudo -u postgres createdb -O myapp_user myapp |
This prepares an empty database owned by the correct role.
Confirm.
|
0 1 2 |
sudo -u postgres psql -c "\l myapp" |
Step 4, Restore the Dump
Restore the data into the new database.
|
0 1 2 |
sudo -u postgres pg_restore -d myapp /tmp/myapp.dump |
If you only want to import schema and data (ignoring ownership or ACL info from the dump), use.
|
0 1 2 |
sudo -u postgres pg_restore --no-owner --no-acl -d myapp /tmp/myapp.dump |
Large databases can be restored in parallel.
|
0 1 2 |
sudo -u postgres pg_restore -j 4 --no-owner --no-acl -d myapp /tmp/myapp.dump |
Step 5, Verify the Result
List tables.
|
0 1 2 |
sudo -u postgres psql -d myapp -c "\dt" |
Log in as the application user.
|
0 1 2 |
psql -h localhost -U myapp_user -d myapp |
Inside psql.
|
0 1 2 3 4 |
SELECT current_user; SELECT count(*) FROM information_schema.tables WHERE table_schema='public'; \q |
If that works, the migration is complete.
Optional, Direct Pipe Transfer (No Dump File)
If the servers are connected by SSH and you prefer streaming,
|
0 1 2 3 |
sudo -u postgres createdb -O myapp_user myapp ssh postgres@pg-src "pg_dump -U postgres -Fc myapp" | sudo -u postgres pg_restore --no-owner --no-acl -d myapp |
This performs the same transfer in one step without creating a dump file on disk.
Summary
| Step | Action | Command |
|---|---|---|
| 1 | Dump database on source | pg_dump -Fc -f /tmp/myapp.dump myapp |
| 2 | Copy to destination | scp /tmp/myapp.dump user@pg-dst:/tmp/ |
| 3 | Create DB on destination | createdb -O myapp_user myapp |
| 4 | Restore | pg_restore -d myapp /tmp/myapp.dump |
| 5 | Verify | psql -d myapp -c "\dt" |
Closing Notes
When both PostgreSQL instances are on version 15, this method is safe, idempotent, and easy to automate.
Creating the database first and assigning it to the same role keeps your access structure intact, ideal for teams managing multiple environments with predefined roles.
It’s the same principle you’d apply in any infrastructure move, build the container first, then pour the data in.