How to restore a PostgreSQL database on a new server

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. To check a backup rather than move a database, see 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:

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:

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:

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:

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:

sudo -u postgres pg_restore -l /var/lib/postgresql/shopdb.dump | head -12

;
; 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:

sudo -u postgres pg_restore -f - /var/lib/postgresql/shopdb.dump | grep -E "OWNER TO|^GRANT|^REVOKE|^ALTER DEFAULT" | sort | uniq -c

      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:

sudo -u postgres psql -c '\l shopdb'

  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:

sudo -u postgres psql -d shopdb -c '\dx'

   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:

sudo -u postgres pg_dumpall --roles-only -f /var/lib/postgresql/roles-all.sql

Keep only the roles on your list, and the memberships:

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

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:

sudo -u postgres psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'"

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 sets one up):

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:

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:

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:

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:

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:

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. 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:

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:

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:

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:

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.

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:

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 or borgmatic to a second server, and test that backup now and then.
• Moving MariaDB too? Restore a MariaDB database on a new server follows the same plan.
• The PostgreSQL documentation on SQL dumps and pg_restore covers every option used here.