pg_dump and pg_restore are the standard tools for copying a PostgreSQL database from one server to another, and the safest way to move between major versions. In this tutorial you will migrate a database called myapp from a source server to a new target server on Ubuntu 24.04: copy the roles, take a parallel dump, restore it in parallel, and check that the data matches before switching the application over.
Prerequisites
To follow this guide you need:
- A source PostgreSQL server and a target PostgreSQL server (for example a new CubePath VPS running Ubuntu 24.04) with PostgreSQL already installed. The target can run the same or a newer major version.
- A machine to run the tools from. It can be the target server itself, which is the simplest option.
- A PostgreSQL superuser (usually
postgres) on both servers, reachable over the network from the machine running the tools. On the source,pg_hba.confmust allow that connection. - Free disk space on the machine that stores the dump. A compressed dump is usually 20 to 50 percent of the on-disk database size, but check with the query in Step 3.
In the commands below, replace source_host and target_host with the addresses of your servers and myapp with your database name.
Step 1 - Installing matching client tools
pg_dump can read from older servers, but its output is only guaranteed to load into a server of the same or newer version than pg_dump itself. Use the client tools that match the target major version.
Ubuntu 24.04 ships PostgreSQL 16. If your target runs a newer version, add the official PostgreSQL (PGDG) repository first:
sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
The script adds the repository with its signing key and asks for confirmation. Then install the client package for your target version, for example 17:
sudo apt install -y postgresql-client-17
Check the version that runs by default:
pg_dump --version
pg_dump (PostgreSQL) 17.6 (Ubuntu 17.6-1.pgdg24.04+1)
If several versions are installed, Ubuntu's wrapper picks the newest. You can also call a specific one with its full path, such as /usr/lib/postgresql/17/bin/pg_dump.
Step 2 - Storing credentials in .pgpass
Parallel dumps and restores open several connections, and each one would prompt for a password. A ~/.pgpass file avoids that without putting passwords on the command line. Create it:
nano ~/.pgpass
source_host:5432:*:postgres:your_source_password
target_host:5432:*:postgres:your_target_password
The format is host:port:database:user:password and * matches any database. The file is ignored unless only your user can read it:
chmod 600 ~/.pgpass
Test both connections:
psql -h source_host -U postgres -d myapp -c 'SELECT version();'
psql -h target_host -U postgres -d postgres -c 'SELECT version();'
Both commands should print the server version without asking for a password.
Step 3 - Inspecting the source database
Before dumping, collect three things: the size, the extensions in use and the roles that own objects.
psql -h source_host -U postgres -d myapp -c "SELECT pg_size_pretty(pg_database_size('myapp'));"
psql -h source_host -U postgres -d myapp -c '\dx'
pg_size_pretty
----------------
18 GB
(1 row)
Every extension listed by \dx (for example postgis or pg_trgm) must be installable on the target. Extensions from postgresql-contrib are included in the server package on Ubuntu; third-party ones such as PostGIS need their own package, like postgresql-17-postgis-3, installed on the target before you restore.
Step 4 - Migrating roles and other global objects
pg_dump copies one database but not roles, because roles belong to the whole server. Without them, the restore fails with role "appuser" does not exist and ownership is lost. Dump the global objects with pg_dumpall:
mkdir -p ~/migration
pg_dumpall -h source_host -U postgres --globals-only -f ~/migration/globals.sql
Open the file and remove anything that should not move, such as the line that alters the postgres role or roles used only on the old server:
nano ~/migration/globals.sql
Load it on the target:
psql -h target_host -U postgres -d postgres -f ~/migration/globals.sql
Errors like role "postgres" already exists are expected and harmless. Confirm the application roles are there:
psql -h target_host -U postgres -d postgres -c '\du'
NoteSome managed PostgreSQL services do not let you read password hashes. In that case add
--no-role-passwordstopg_dumpalland set the passwords on the target withALTER ROLE ... PASSWORD.
Step 5 - Dumping the database in parallel
pg_dump supports four output formats. For migrations, use the custom or directory format, because both allow parallel and selective restores:
| Format | Flag | Parallel dump | Parallel restore | Restore with |
|---|---|---|---|---|
| Plain SQL | -Fp | No | No | psql |
| Custom | -Fc | No | Yes | pg_restore |
| Directory | -Fd | Yes | Yes | pg_restore |
| Tar | -Ft | No | No | pg_restore |
Stop writes to the source database first, or at least schedule a maintenance window: anything written after the dump starts is not included. Then take a directory-format dump with 4 parallel jobs:
pg_dump -h source_host -U postgres -d myapp -Fd -j 4 -f ~/migration/myapp.dir
-j opens one connection per job plus one, and each job dumps a different table, so it helps most when the database has several large tables. Keep it at or below the number of CPU cores on the source. All jobs share a single snapshot, so the dump is consistent.
Check the result:
du -sh ~/migration/myapp.dir
pg_restore --list ~/migration/myapp.dir | head -n 15
2.9G /home/your_user/migration/myapp.dir
;
; Archive created at 2026-09-25 10:12:03 UTC
; dbname: myapp
; TOC Entries: 214
; Compression: gzip
; Dump Version: 1.16-0
; Format: DIRECTORY
...
The table of contents lists every schema, table, index, constraint and sequence value in the dump.
Step 6 - Restoring on the target server
Create an empty database on the target owned by the application role. Using template0 guarantees it contains nothing that could collide with the dump:
createdb -h target_host -U postgres -T template0 -O appuser myapp
Restore with parallel jobs. On the restore side, -j parallelizes data loading and, more importantly, index and constraint creation, which is usually the slowest part:
pg_restore -h target_host -U postgres -d myapp -j 4 --exit-on-error ~/migration/myapp.dir
--exit-on-error stops at the first problem instead of printing hundreds of follow-up errors. If the command finishes without output, the restore succeeded.
pg_restore does not update the planner statistics, so queries can be slow until they are rebuilt. Generate them now:
vacuumdb -h target_host -U postgres -d myapp --analyze-in-stages
Restoring only part of a dump
A custom or directory dump can be restored selectively. To restore a single table (definition and data):
pg_restore -h target_host -U postgres -d myapp -t orders ~/migration/myapp.dir
For finer control, export the table of contents, comment out the entries you do not want with a leading ;, and restore with that list:
pg_restore --list ~/migration/myapp.dir > ~/migration/toc.list
nano ~/migration/toc.list
pg_restore -h target_host -U postgres -d myapp -L ~/migration/toc.list ~/migration/myapp.dir
Skipping the intermediate file
If the machine has no space for the dump, stream it straight into the target. Parallel jobs are not available this way:
pg_dump -h source_host -U postgres -d myapp -Fc | pg_restore -h target_host -U postgres -d myapp
Step 7 - Verifying the migrated data
pg_stat_user_tables.n_live_tup is only an estimate, so compare exact row counts. This query counts every table in the database; run it on both servers and save the output:
nano ~/migration/rowcounts.sql
SELECT table_schema, table_name,
(xpath('/row/c/text()',
query_to_xml(format('SELECT count(*) AS c FROM %I.%I', table_schema, table_name),
false, true, '')))[1]::text::bigint AS row_count
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;
psql -h source_host -U postgres -d myapp -At -f ~/migration/rowcounts.sql > ~/migration/source.txt
psql -h target_host -U postgres -d myapp -At -f ~/migration/rowcounts.sql > ~/migration/target.txt
diff ~/migration/source.txt ~/migration/target.txt && echo "Row counts match"
Row counts match
Counting large tables takes time, so run this while writes to the source are stopped. Also confirm that the extensions and the owner of the tables are as expected:
psql -h target_host -U postgres -d myapp -c '\dx' -c '\dt'
Finally, point a staging copy of the application at the target, or run your test suite against it, before the real cutover.
Step 8 - Switching the application over
With the data verified:
- Keep the source read-only or stopped so no new writes land there.
- Change the application's connection string (host, and credentials if they changed) to the target server.
- Restart the application and watch its logs and the PostgreSQL log on the target (
sudo journalctl -u postgresql@17-main -fon Ubuntu, adjusting the version).
Keep the source server and the dump for a few days as a rollback path.
Troubleshooting
pg_dump: error: aborting because of server version mismatch. The server is newer than your pg_dump. Install the client package for that version or newer, as shown in Step 1.
role "..." does not exist during restore. The globals from Step 4 were not loaded. Load them, or restore with --no-owner --no-privileges so every object is owned by the user running pg_restore, then fix ownership and grants yourself.
could not open extension control file or extension "..." is not available. The extension package is missing on the target server. Install it (matching the server's major version) and restore again into a fresh database.
The restore is very slow. Increase -j if the target has spare cores, and make sure the target is not short on RAM. Index builds use maintenance_work_mem; raising it in postgresql.conf for the duration of the restore speeds them up.
Conclusion
You moved a PostgreSQL database to a new server with its roles, using parallel dump and restore, and verified exact row counts before the cutover. The same procedure works for major version upgrades, since the newer pg_dump handles the conversion. For databases too large to take offline even briefly, look at logical replication (CREATE PUBLICATION and CREATE SUBSCRIPTION) to keep the target in sync until cutover, and schedule regular pg_dump backups of the new server.