# How to restore a PostgreSQL database on a new server > Move one PostgreSQL database to a new server with its roles, extensions and grants: rehearse the restore, redo it at the cutover, check every table. Source: https://rack4.io/tutorials/restore-postgresql-database · Updated: 2026-11-07 Difficulty: intermediate · Time: about 45 minutes · Tested on: Ubuntu 24.04 LTS Prerequisites: - Two servers running Ubuntu 24.04 with PostgreSQL installed - Root or a user with sudo on both - SSH from the old server to the new one To restore a PostgreSQL database on a new server, dump it with `pg_dump -Fc`, create the roles it needs on the new server **before** you restore, create an empty database owned by the right role, and load the dump with `pg_restore --exit-on-error`. A one-database dump has the tables, data, functions, views, extensions and table grants, but not the roles (users), their passwords, or who may connect to the database. Those live in the cluster, not in the database, and a restore without them stops at the first `ALTER ... OWNER TO`. This tutorial moves one application database: a rehearsal first, with the old server still live, then a final restore at the cutover that replaces the rehearsal copy, then checks that every table matches. For the rest of a server move (files, DNS, HTTPS), follow [Move to a larger server](https://rack4.io/help/move-to-a-larger-server). To check a backup rather than move a database, see [Test a backup restore](https://rack4.io/help/test-a-backup-restore). What we tested: on 7 November 2026, every command here ran against PostgreSQL 16.15 → 16.15 and 16.15 → 17.11, in throwaway Docker containers (the PostgreSQL project's Debian builds, with the target's `pg_hba.conf` changed to require SCRAM passwords over TCP, as Ubuntu's does). The test database had a non-default owner, a read-only group role granted to a login role, the `citext` and `pgcrypto` extensions, a view, a PL/pgSQL function and default privileges. We ran the rehearsal, then inserted, updated and deleted rows and added a table on the old server, wrote a test row on the new one, ran the final restore over the populated copy and compared every table. On a real server you run them through `sudo -u postgres` and `systemctl`, as written below. What we didn't test is listed at the end. The examples use these names. Replace them with yours: | What | In this tutorial | | --- | --- | | Old server, still live | `203.0.113.10` | | New server | `203.0.113.50` | | The database | `shopdb` | | The role that owns it (the app logs in as this role) | `appowner` | | A group role with read access, and a login role in it | `app_readonly` and `reporter` | | An older login role, for a reporting job | `legacyjob` | ## Before you start: versions and what a dump leaves out **Versions.** Restore onto the same major version or a newer one. Ubuntu 24.04 ships PostgreSQL 16; check both sides: ```bash sudo -u postgres psql -c "SELECT version();" ``` A 16 dump restores on 16 and on 17 (we tested both). The reverse doesn't work: a dump made by `pg_dump` 17 fails on PostgreSQL 16 before it restores anything, with `pg_restore: error: unsupported version (1.16) in file header`. To go to an older major version you need a plain SQL dump and hand fixes, which this tutorial doesn't cover. **What `pg_dump` of one database includes, and what it doesn't:** | In the dump | Not in the dump: you bring it across | | --- | --- | | Tables, data, sequences and their current values | Roles and their passwords | | Views, functions, triggers, indexes, constraints | Membership of one role in another (`GRANT app_readonly TO reporter`) | | `CREATE EXTENSION` for each extension | The extension's files, which come from a package | | Owners of each object, table grants, default privileges | Who may connect (`GRANT CONNECT ON DATABASE`), and `ALTER DATABASE ... SET` settings | **Stop nothing yet.** The rehearsal runs while the app keeps working on the old server. You stop writes only for the final restore. ## Dump the database On the old server, dump into the `postgres` user's home folder, so the `postgres` user can write and later read the file: ```bash sudo -u postgres pg_dump -Fc -f /var/lib/postgresql/shopdb.dump shopdb ``` `-Fc` is the custom format: compressed, restored with `pg_restore`, and it lets `pg_restore` load several tables at once with `-j`. `pg_dump` takes a consistent snapshot, so the app can keep writing while it runs. Check the file reads back and see which version made it: ```bash sudo -u postgres pg_restore -l /var/lib/postgresql/shopdb.dump | head -12 ``` ```text ; ; Archive created at 2026-11-07 16:34:36 UTC ; dbname: shopdb ; TOC Entries: 31 ; Compression: gzip ; Dump Version: 1.15-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 16.15 (Debian 16.15-1.pgdg13+2) ; Dumped by pg_dump version: 16.15 (Debian 16.15-1.pgdg13+2) ``` ## List the roles and extensions it needs **Roles that own or are granted something in the dump.** Ask the dump itself: ```bash sudo -u postgres pg_restore -f - /var/lib/postgresql/shopdb.dump | grep -E "OWNER TO|^GRANT|^REVOKE|^ALTER DEFAULT" | sort | uniq -c ``` ```text 1 ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly; 1 ALTER FUNCTION public.place_order(p_email public.citext, p_total numeric) OWNER TO appowner; 1 ALTER TABLE public.customers OWNER TO appowner; 1 ALTER TABLE public.orders OWNER TO appowner; 1 ALTER VIEW public.order_totals OWNER TO appowner; 1 GRANT SELECT ON TABLE public.customers TO app_readonly; 1 GRANT SELECT ON TABLE public.orders TO app_readonly; 1 GRANT SELECT ON TABLE public.order_totals TO app_readonly; 1 GRANT SELECT ON TABLE public.order_totals TO legacyjob; ``` That gives `appowner`, `app_readonly` and `legacyjob`. It can't show roles that only log in and get their rights through another role: `reporter` reads through its membership of `app_readonly` and appears nowhere in the dump. Look at who may connect to the database too: ```bash sudo -u postgres psql -c '\l shopdb' ``` ```text Name | Owner | Encoding | ... | Access privileges --------+----------+----------+-----+----------------------- shopdb | appowner | UTF8 | ... | =T/appowner + | | | | appowner=CTc/appowner+ | | | | reporter=c/appowner + | | | | legacyjob=c/appowner ``` Each `name=c` is a role with `CONNECT`. Here the default `CONNECT` for everyone has been revoked (`=T` has no `c`), and `reporter` and `legacyjob` were given it. Add every login your apps, cron jobs and people use; the list for this example is `appowner`, `app_readonly`, `reporter` and `legacyjob`. **Extensions.** List them on the old server: ```bash sudo -u postgres psql -d shopdb -c '\dx' ``` ```text Name | Version | Schema | Description ----------+---------+------------+-------------------------------------------------- citext | 1.6 | public | data type for case-insensitive character strings pgcrypto | 1.3 | public | cryptographic functions plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language ``` The dump creates them, but the new server needs their files. `citext`, `pgcrypto` and the other extensions that ship with PostgreSQL itself are part of Ubuntu's `postgresql-16` package. Others come in their own package and must be installed on the new server first, for example `postgresql-16-postgis-3` for PostGIS. Without the files, the restore stops with `ERROR: extension "citext" is not available`. Because you restore as `postgres`, a superuser, the dump can create any extension whose files are there. ## Copy the roles `pg_dumpall --roles-only` writes every role in the cluster, with password hashes. On the old server: ```bash sudo -u postgres pg_dumpall --roles-only -f /var/lib/postgresql/roles-all.sql ``` Keep only the roles on your list, and the memberships: ```bash sudo -u postgres bash -c 'grep -E "^(CREATE|ALTER) ROLE (appowner|app_readonly|reporter|legacyjob)[ ;]|^GRANT " /var/lib/postgresql/roles-all.sql > /var/lib/postgresql/roles.sql' sudo -u postgres cat /var/lib/postgresql/roles.sql ``` ```text CREATE ROLE app_readonly; ALTER ROLE app_readonly WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS; CREATE ROLE appowner; ALTER ROLE appowner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:...'; CREATE ROLE legacyjob; ALTER ROLE legacyjob WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5f873...'; CREATE ROLE reporter; ALTER ROLE reporter WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:...'; GRANT app_readonly TO reporter WITH INHERIT TRUE GRANTED BY postgres; ``` Read it before you use it. The filter drops `postgres` (which already exists on the new server and would stop the script) and every role you didn't name, but the `^GRANT` part keeps every membership line: delete any that mention a role you didn't copy. Remove `roles-all.sql` afterwards (`sudo rm /var/lib/postgresql/roles-all.sql`); it holds every password hash on the server. **Passwords.** The hashes copy as they are, so each role keeps its password. Check for old `md5` hashes on the old server: ```bash sudo -u postgres psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'" ``` ```text legacyjob ``` Ubuntu 24.04's `pg_hba.conf` asks for SCRAM-SHA-256 over TCP, and an `md5` hash can't answer it. We'll set a new password for those roles on the new server below. ## Send the files to the new server From the old server (as root, with an SSH key the new server accepts; [Move to a larger server](https://rack4.io/help/move-to-a-larger-server) sets one up): ```bash rsync -a /var/lib/postgresql/shopdb.dump /var/lib/postgresql/roles.sql root@203.0.113.50:/var/lib/postgresql/ ``` On the new server, give them to `postgres` and compare the checksum with `sha256sum /var/lib/postgresql/shopdb.dump` on the old one: ```bash chown postgres:postgres /var/lib/postgresql/shopdb.dump /var/lib/postgresql/roles.sql sha256sum /var/lib/postgresql/shopdb.dump ``` ## Rehearse the restore Everything in this section runs on the new server. Create the roles, then an empty database owned by the app's role, then restore: ```bash sudo -u postgres psql -v ON_ERROR_STOP=1 -f /var/lib/postgresql/roles.sql sudo -u postgres createdb --owner=appowner shopdb sudo -u postgres pg_restore --exit-on-error -j 4 -d shopdb /var/lib/postgresql/shopdb.dump echo $? ``` - **The roles exist first,** so `pg_restore` gives every table, view and function its original owner and grants. You don't need `--no-owner`, which would make `postgres` the owner of everything and leave the app unable to run its own migrations. - **`--owner=appowner`** makes the database itself belong to the app's role, as it did on the old server. - **`--exit-on-error`** stops at the first failure. Without it, `pg_restore` carries on past errors and prints a count at the end, which is easy to miss when a table is missing. - **`-j 4`** restores four tables or indexes at a time. Use up to the number of CPU cores. It needs a custom-format file (`-Fc`), not a pipe. - **`echo $?`** prints `0` when the restore finished with no errors. Then put back what the dump doesn't carry. Who may connect, if the old server limited it: ```bash sudo -u postgres psql -c "REVOKE CONNECT ON DATABASE shopdb FROM PUBLIC" -c "GRANT CONNECT ON DATABASE shopdb TO reporter, legacyjob" ``` Settings made with `ALTER DATABASE shopdb SET ...`: run `sudo -u postgres psql -c '\drds'` on the old server, which lists them, and repeat each on the new one. And a SCRAM password for each role that had an `md5` hash. Use its real password, so whatever logs in with it doesn't need changing: ```bash sudo -u postgres psql -c "ALTER ROLE legacyjob PASSWORD 'the-same-password-it-had'" ``` Now log in the way the app does, over TCP with a password: ```bash psql -h 127.0.0.1 -U reporter -d shopdb -c 'SELECT count(*) FROM order_totals' ``` Point a copy of the app at the new database and click through it, as in [testing before anyone else sees it](https://rack4.io/help/move-to-a-larger-server). Anything written here is replaced at the final restore. ## Check the data Counting a couple of tables catches a restore that stopped early. To catch a table that came across wrong, compare every table's row count and a checksum of its contents on both servers. Save this as `/var/lib/postgresql/verify.sql` on both: ```sql SET TimeZone = 'UTC'; SELECT format('%I.%I', table_schema, table_name) AS table, (xpath('/row/n/text()', query_to_xml(format( 'SELECT count(*) AS n FROM %I.%I', table_schema, table_name), false, true, '')))[1]::text AS rows, (xpath('/row/h/text()', query_to_xml(format( 'SELECT md5(coalesce(string_agg(t::text, E''\n'' ORDER BY t::text), '''')) AS h FROM %I.%I t', table_schema, table_name), false, true, '')))[1]::text AS md5 FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1; ``` It prints one line per table: name, exact row count and an MD5 of every row. `SET TimeZone` makes timestamps print the same way on both servers. Run it on the old server and copy the result across: ```bash sudo -u postgres psql -qAt -d shopdb -f /var/lib/postgresql/verify.sql > /var/lib/postgresql/verify-old.txt ``` On the new server, run it again and compare: ```bash sudo -u postgres psql -qAt -d shopdb -f /var/lib/postgresql/verify.sql > /var/lib/postgresql/verify-new.txt diff /var/lib/postgresql/verify-old.txt /var/lib/postgresql/verify-new.txt && echo "no differences" ``` After the rehearsal, differences are expected: the old server has kept taking writes. After the final restore, before the app starts, it should print `no differences`. Here is what a difference looks like, from our test, after a test order was placed on the new server: ```text 2,3c2,3 < public.customers|5002|c2b54b9df3120876c5c26f074bf6d5d9 < public.orders|20007|209660d453defea8622edbfd8dc0e1ba --- > public.customers|5003|2d8d39a8dd42833256dcefe4468f80a1 > public.orders|20008|f60d857f378681029d90379301c0dcc2 ``` The query reads each table whole, and builds each checksum in memory: fine for tables up to a few hundred megabytes, too slow or too big beyond that (one value in PostgreSQL can't pass 1 GB). For big tables, keep the counts and checksum a recent slice instead, for example the rows with `id` above a recent value, in the same `string_agg` form. ## Do the final restore Plan a few minutes of downtime: from when the old app stops to when the new one starts. 1. **Stop the app on the new server** (for example `sudo systemctl stop myapp`, with your own service and worker names), so nothing writes to the rehearsal copy while you replace it. 2. **Stop writes on the old server.** Stop the app, its workers and its cron jobs the same way. Then, as a guard for anything you missed, make new sessions read-only and close the ones still open: ```bash sudo -u postgres psql -c "ALTER DATABASE shopdb SET default_transaction_read_only = on" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'shopdb' AND pid <> pg_backend_pid()" ``` Anything that reconnects and tries to write now fails with `ERROR: cannot execute INSERT in a read-only transaction`, and `pg_dump` works as usual. A program can switch it off for its own session, so it's a safety net, not a lock. The setting isn't part of the dump, so it doesn't follow the data to the new server. 3. **Dump again and send it**, with the same `pg_dump` and `rsync` commands as before. Compare the checksums. 4. **On the new server, drop the rehearsal database and restore into a fresh one.** The roles are already there: ```bash sudo -u postgres dropdb --force shopdb sudo -u postgres createdb --owner=appowner shopdb sudo -u postgres pg_restore --exit-on-error -j 4 -d shopdb /var/lib/postgresql/shopdb.dump echo $? ``` Without `--force`, `dropdb` refuses while anything is connected: `ERROR: database "shopdb" is being accessed by other users`. `--force` closes those sessions; make sure they are yours (step 1), not an app you forgot. 5. **Repeat the database-level steps**: the `REVOKE`/`GRANT CONNECT` lines and any `ALTER DATABASE ... SET` settings. They went with the dropped database. The roles and their passwords stay, so you don't repeat those. 6. **Compare** with `verify.sql` on both servers as above. Expect `no differences`. 7. **Start the app** on the new server, check it can write (place a test order, sign up a test user), and switch DNS as in [Move to a larger server](https://rack4.io/help/move-to-a-larger-server). **Why drop and recreate, rather than `pg_restore --clean`?** `--clean --if-exists` drops and recreates only the objects that are in the dump. In our test it ran without errors over the populated rehearsal copy, and a table we had created on the new server after the rehearsal was still there afterwards. A fresh database leaves nothing from the rehearsal behind. ## Keep the old database, and writes after the dump Leave the old server's database exactly as it is for a few days, read-only, along with the final dump file. It is your way back and your reference if a number looks wrong. Anything written to the old database after the final dump isn't on the new server. That is why writes stop first, and why the read-only setting is there: it catches a worker or job you forgot to stop, as long as it doesn't switch the setting off itself. Once the new server is live, it takes writes the old one never sees. If you go back to the old server after that, those writes stay on the new server, and nothing copies them back for you: compare the newest rows on both (for example `SELECT max(id) FROM orders;`) and move them across by hand or from the app. To make the old database writable again: ```bash sudo -u postgres psql -c "ALTER DATABASE shopdb RESET default_transaction_read_only" ``` ## Troubleshooting **`pg_restore: error: could not execute query: ERROR: role "appowner" does not exist`.** The roles weren't created before the restore. Drop the half-restored database (`dropdb shopdb`), run `roles.sql`, create it again and restore. **`ERROR: extension "citext" is not available` with `HINT: The extension must first be installed on the system where PostgreSQL is running.`** Install the package that provides that extension for your PostgreSQL version on the new server, drop the database and restore again. **`FATAL: password authentication failed for user "legacyjob"` on the new server only.** The server log (`journalctl -u postgresql@16-main`) says `DETAIL: User "legacyjob" does not have a valid SCRAM secret.` The role came across with an `md5` hash. Set its password again with `ALTER ROLE ... PASSWORD`, as above. **`pg_restore: error: unsupported version (1.16) in file header`.** The dump came from a newer `pg_dump` than the `pg_restore` reading it. Restore onto the same major version or a newer one. **`ERROR: database "shopdb" is being accessed by other users`.** Something is connected to the database you're dropping. `sudo -u postgres psql -c "SELECT pid, usename, application_name, client_addr FROM pg_stat_activity WHERE datname = 'shopdb'"` shows what; stop it, or use `dropdb --force` once you're sure. ## What wasn't tested - Ubuntu's own packages and `systemctl`: the test used the PostgreSQL project's Debian builds in containers, run directly rather than through `sudo` and systemd. - Restoring onto PostgreSQL 18, or from a version older than 16. - Extensions that need their own package (PostGIS, TimescaleDB, pgvector), and the `rsync` copy between two servers (the test copied the files between containers and compared checksums). - Large databases: the test database was about 25,000 rows, so the timing tells you nothing about yours. Rehearse to find out how long your restore takes. - Going back to the old server after the new one has taken writes. ## Next steps - Back up the new server from day one: [restic to S3-compatible storage](https://rack4.io/tutorials/restic-backup-s3) or [borgmatic to a second server](https://rack4.io/tutorials/borgmatic-backup-setup), and [test that backup](https://rack4.io/help/test-a-backup-restore) now and then. - Moving MariaDB too? [Restore a MariaDB database on a new server](https://rack4.io/tutorials/restore-mariadb-database) follows the same plan. - The PostgreSQL documentation on [SQL dumps](https://www.postgresql.org/docs/current/backup-dump.html) and [pg_restore](https://www.postgresql.org/docs/current/app-pgrestore.html) covers every option used here.