Web stack / Stage 8 of 9 · step 6 of 7

Restore a database from a dump

~25 min

Not running Starting usually ~20 s

Situation

This machine runs the shop database of an online store in PostgreSQL. After a new version was released, an order disappeared from it and some orders got different amounts.

Every night the database is copied with pg_dump into /var/backups/shop. The team wants a copy of the database as it was before the failure, next to it, as a separate shop_restore database, to bring the data back from. shop itself stays untouched for now: the team uses it to work out what the release did. A colleague has already started restoring a copy into shop_restore, but did not finish and did not write down how far they got.

The release log is /var/log/shop/deploy.log. Run administrative commands with sudo; sudo -u postgres psql connects you as the PostgreSQL superuser. There is no internet on the machine.

Task

shop_restore holds the orders and settings from the last copy taken before the failure.
The tables in shop_restore are built as in that copy: columns, keys, constraints.
A new order in shop_restore would get the same number it would have got at the time of the copy.
The shop_restore database and everything in it belong to the shop role, as in shop.
The shop database and the copy files in /var/backups/shop stay as they are.

Lab goals

0 of 5

shop_restore holds the orders and settings from the last copy taken before the failure — not checked yet
The tables in shop_restore are built as in that copy — columns, keys, constraints — not checked yet
A new order in shop_restore would get the same number as at the time of the copy — not checked yet
The shop_restore database and everything in it belong to the shop role — not checked yet
The shop database and the copy files in /var/backups/shop are unchanged — not checked yet

Hints

0 of 3 open · they open one at a time

Hint 1

Show the solution after 3 failed checks

Services and systemd · picked for this lab

PostgreSQL — access, pg_hba.conf, dump and restore

Cheat sheet

$_ — insert into the terminal

sudo systemctl start postgresql the service is off in the labs
pg_lsclusters version, port, status
sudo -u postgres psql superuser console
psql -h 127.0.0.1 -U appuser -d appdb TCP login with a password
sudo -u postgres psql -c 'TABLE pg_hba_file_rules' access rules
sudo ss -tlnp | grep 5432 which address it listens on
sudo -u postgres pg_dump -Fc -f /tmp/appdb.dump appdb
sudo -u postgres pg_restore -l /tmp/appdb.dump table of contents

Two files in /etc/postgresql/16/main/: postgresql.conf holds server parameters, pg_hba.conf says who may log in and how. What the server actually read: SHOW hba_file;, SHOW listen_addresses;.

pg_hba.conf

pg_hba.conf holds access rules, one per line. Ubuntu's defaults:

text

# TYPE  DATABASE  USER      ADDRESS        METHOD
local   all       postgres                 peer
local   all       all                      peer
host    all       all       127.0.0.1/32   scram-sha-256
host    all       all       ::1/128        scram-sha-256

Field	Meaning
TYPE	`local` — over the socket, `host` — over TCP
DATABASE	a database name or `all`
USER	a role or `all`
ADDRESS	the client address with a mask; empty for `local`
METHOD	how to check: `peer`, `scram-sha-256`, `reject`

peer admits a Linux user only to the role with the same name, which is why the superuser logs in as sudo -u postgres psql. scram-sha-256 is a password. reject refuses without checking.

Lines are read top to bottom and the first matching line wins; the rest are not looked at. A reject line above an allowing one blocks access even with the right password. Put a new rule for a specific database and role above the general ones.

sudo systemctl reload postgresql or SELECT pg_reload_conf(); applies the change. The pg_hba_file_rules view shows what the server read: line number, fields and an error column. If the file has an error, the server rejects it and keeps working with the old rules, and the log says pg_hba.conf was not reloaded.

Address and port

By default listen_addresses = 'localhost' — only 127.0.0.1 and ::1, port 5432. To accept connections from the network you need three things: the address in listen_addresses ('*' means all), a host line for the client address in pg_hba.conf, and the port open in the firewall. listen_addresses changes only with sudo systemctl restart postgresql: after reload the parameter is marked pending_restart and the server listens as before.

Your own setting is best kept in a separate file: postgresql.conf reads the conf.d directory next to it (include_dir = 'conf.d'), and a package upgrade leaves such a file alone:

bash

echo "listen_addresses = '127.0.0.1'" \
  | sudo tee /etc/postgresql/16/main/conf.d/listen.conf
sudo systemctl restart postgresql
sudo ss -tlnp | grep 5432

conf.d is read after the include_dir line at the end of postgresql.conf, and the last value wins. With listen_addresses = '*' in postgresql.conf and '127.0.0.1' in conf.d/listen.conf the server listens on 127.0.0.1. pg_settings shows where the value came from and whether it waits for a restart:

text

student@ubuntu:~$ sudo -u postgres psql -Atc "SELECT setting,
> sourcefile, sourceline, pending_restart FROM pg_settings
> WHERE name='listen_addresses'"
127.0.0.1|/etc/postgresql/16/main/conf.d/listen.conf|1|f

After editing the file and a reload, the value is unchanged, pending_restart is t, and /var/log/postgresql/postgresql-16-main.log says:

text

LOG:  parameter "listen_addresses" cannot be changed without restarting
  the server
LOG:  configuration file "/etc/postgresql/16/main/conf.d/listen.conf"
  contains errors; unaffected changes were applied

pg_file_settings shows every line of every file and what happened to it (applied, error — for example, setting could not be applied).

A role and a database for an application

bash

sudo -u postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'apppass'"
sudo -u postgres createdb -O appuser appdb
psql -h 127.0.0.1 -U appuser -d appdb -c 'SELECT current_user'

A new password — ALTER ROLE appuser PASSWORD '…';; list roles with \du in psql, databases with \l.

A password without typing: ~/.pgpass

bash

echo '127.0.0.1:5432:appdb:appuser:apppass' > ~/.pgpass
chmod 600 ~/.pgpass
psql -h 127.0.0.1 -U appuser -d appdb

A line is host:port:database:role:password; * matches any value. The mode must be 600: otherwise psql says password file … has group or world access and skips the file. The PGPASSWORD variable takes precedence over the file.

Dump formats

pg_dump takes a logical dump — the commands and data the database is rebuilt from. Roles are not included: pg_dumpall --globals-only saves them.

Format	Command	Restored with
plain SQL	`pg_dump -f appdb.sql appdb`	`psql -f`
custom	`pg_dump -Fc -f appdb.dump appdb`	`pg_restore`
directory	`pg_dump -Fd -j 4 -f appdb.dir appdb`	`pg_restore -j 4`

Plain SQL is human-readable: CREATE TABLE, COPY with the data, setval for counters. At the start and end there are \restrict and \unrestrict lines with a random key: they protect psql from foreign commands in the data, this is expected. The custom format is compressed, starts with the bytes PGDMP and lets you list its contents and restore part of it:

bash

sudo -u postgres pg_restore -l /tmp/appdb.dump

text

216; 1259 16387 TABLE public orders appuser
215; 1259 16386 SEQUENCE public orders_id_seq appuser
3396; 0 16387 TABLE DATA public orders appuser
3404; 0 0 SEQUENCE SET public orders_id_seq appuser

Above the contents is the archive header: Archive created at 2026-10-20 10:05:53 UTC, dbname: appdb and the server versions. It tells which copy this is and where it came from. pg_restore -f - -t orders /tmp/appdb.dump prints one table's data without restoring — the table definition and the COPY rows.

Restore into a new database

bash

sudo -u postgres createdb -O appuser appdb_copy
sudo -u postgres pg_restore -d appdb_copy /tmp/appdb.dump
sudo -u postgres psql -d appdb_copy -c 'SELECT count(*) FROM orders'

A new empty database has no conflicts: exit code 0. The dump restores the id sequence with setval('public.orders_id_seq', 3, true) — the next insert gets id 4.

Owners. createdb -O appuser sets the owner of the database itself. Run as postgres, pg_restore takes table owners from the archive — with ALTER TABLE … OWNER TO appuser lines, so that role must exist. With --no-owner the restoring role becomes the owner, and with --no-owner --role=r — role r.

pg_restore option	What it does
`-l`	only the archive's table of contents
`-L list`	restore the TOC lines listed in a file
`-f -`	print SQL instead of writing to a database
`--exit-on-error`	stop at the first error
`--single-transaction`	all or nothing: roll back on error
`--clean --if-exists`	drop objects before creating them
`--create -d postgres`	create the database named in the dump
`--no-owner --role=r`	make role r the owner of objects

Compare a copy with the source

sql

SELECT count(*) FROM orders;
SELECT md5(string_agg(t::text, ',' ORDER BY id)) FROM orders t;
SELECT last_value FROM orders_id_seq;

The same row count, selection checksum and counter value in the source and the copy mean the data matches. \dt shows the table owners.

Connection refused

connection to server at "127.0.0.1", port 5432 failed: Connection refused and Is the server running on that host and accepting TCP/IP connections?. The service is not running (pg_lsclusters, systemctl status postgresql) or does not listen on that address: sudo ss -tlnp | grep 5432, SHOW listen_addresses;. Over the socket the same looks like No such file or directory and Is the server running locally….

no pg_hba.conf entry

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "appuser", database "appdb", no encryption — no line matched this address, database and role. The message comes twice: psql tries with SSL first, then without. Add a line host appdb appuser <address>/32 scram-sha-256, reload and check pg_hba_file_rules.

pg_hba.conf rejects connection for host … — a reject line matched; it sits above the allowing one.

password authentication failed

A rule matched, the password did not. For a role that does not exist the server says the same — check \du. ALTER ROLE … PASSWORD sets the password; remember that PGPASSWORD overrides ~/.pgpass. fe_sendauth: no password supplied — a password was asked for and there was nowhere to take it from.

Peer authentication failed

psql -U appuser without -h goes through the socket, where the rule is peer: the Linux user must have the role's name. Connect with -h 127.0.0.1 and a password, or use sudo -u postgres psql. role "student" does not exist — psql without -U takes the role from the Linux user name.

A pg_hba.conf change has no effect

The file was not re-read, or was re-read with an error — then the old rules apply, and pg_reload_conf() still returns t. The error shows in SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;, and the log says pg_hba.conf was not reloaded.

pg_restore: relation "…" already exists

A restore into a database that already has the objects: an error for each, then pg_restore: warning: errors ignored on restore: 4, exit code 1, and part of the data may have landed on top. Restore into a new empty database. --single-transaction rolls everything back at the first error. --create -d postgres while a database of that name exists gives database "appdb" already exists.

input file appears to be a text format dump

pg_restore got plain SQL — restore it with psql -f. The reverse error: psql -f on a custom dump says The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client….

trust in pg_hba.conf

trust admits anyone matching the line without a password. host all all 0.0.0.0/0 trust opens every database to anyone who can reach the port.

pg_restore --clean into a live database

--clean drops objects before creating them. In a live database that replaces current data with old data from the dump. Restore into a separate database and compare first.

Restore a database from a dump

Situation

Task

Signing in is needed to start the machine

A role and a database for an application

A password without typing: ~/.pgpass

Dump formats

Restore into a new database

Compare a copy with the source

Connection refused

no pg_hba.conf entry

password authentication failed

Peer authentication failed

A pg_hba.conf change has no effect

pg_restore: relation "…" already exists

input file appears to be a text format dump

trust in pg_hba.conf

pg_restore --clean into a live database