--- title: "How to Migrate PostgreSQL with pg_dump and pg_restore" description: "Move a PostgreSQL database to a new server with pg_dump and pg_restore: migrate roles, run parallel dumps and restores, and verify row counts before cutover." category: "Database Operations" language: en source: https://cubepath.com/docs/database-operations/database-migration-pg-dump-pg-restore updated: 2026-09-25 --- # How to Migrate PostgreSQL with pg_dump and pg_restore `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.conf` must 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: ```bash 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: ```bash sudo apt install -y postgresql-client-17 ``` Check the version that runs by default: ```bash pg_dump --version ``` ```output 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: ```bash nano ~/.pgpass ``` ```text 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: ```bash chmod 600 ~/.pgpass ``` Test both connections: ```bash 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. ```bash 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' ``` ```output 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`: ```bash 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: ```bash nano ~/migration/globals.sql ``` Load it on the target: ```bash 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: ```bash psql -h target_host -U postgres -d postgres -c '\du' ``` > Note: Some managed PostgreSQL services do not let you read password hashes. In that case add `--no-role-passwords` to `pg_dumpall` and set the passwords on the target with `ALTER 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: ```bash 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: ```bash du -sh ~/migration/myapp.dir pg_restore --list ~/migration/myapp.dir | head -n 15 ``` ```output 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: ```bash 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: ```bash 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: ```bash 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): ```bash 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: ```bash 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: ```bash 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: ```bash nano ~/migration/rowcounts.sql ``` ```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; ``` ```bash 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" ``` ```output 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: ```bash 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: 1. Keep the source read-only or stopped so no new writes land there. 2. Change the application's connection string (host, and credentials if they changed) to the target server. 3. Restart the application and watch its logs and the PostgreSQL log on the target (`sudo journalctl -u postgresql@17-main -f` on 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.