WittCode💻

Backing Up a Postgres Database with pg_dump and pg_restore

By

Learn how to backup and restore a Postgres database using pg_dump and pg_restore. We will also go over some useful configuration options and psql commands.

Table of Contents 📖

What is a Backup?

A Postgres database backup is a copy of a database that can be restored at a later time. This includes the tables, constraints, data, and other information about the database. Backups are essential for preventing data loss against hardware failures, human error, etc. Postgres comes with utility commands to make backing up a database much easier: pg_dump and pg_restore.

pg_dump

The pg_dump utility command extracts the contents of a Postgres database into a script file or another type of archive file. The outputted file is a snapshot of the database at the time pg_dump was ran. To demonstrate, consider a postgres database called my_db containing the following tables and data.

CREATE TABLE subscriber (
  subscriber_id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

INSERT INTO subscriber (name, email) VALUES
('wittcode', 'a@a.com'),
('Sabin', 'b@b.com'),
('Greg', 'c@c.com'),
('Spencer', 'd@d.com');

CREATE TABLE cheese (
  cheese_id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

INSERT INTO cheese (name) VALUES
('cheddar'),
('brie'),
('feta'),
('mozzarella'),
('parmesan');

Lets backup this database using pg_dump.

pg_dump -U WittCepter -d my_db -f ./my-backup.sql
  • -U - The username to connect to Postgres as. Here the username is WittCepter (my chrome extension, go check it out on the chrome webstore!).
  • -d - The name of the database to connect to.
  • -f - Where to send the output to. Note that by default pg_dump will output the data as a plain-text SQL script file.

If we observe the contents of this file, we can see the SQL statements that will be used to reconstruct the database.

--
-- PostgreSQL database dump
--

-- Dumped from database version 16.2
-- Dumped by pg_dump version 16.2

SET statement_timeout = 0;
SET lock_timeout = 0;
...

--
-- Name: cheese; Type: TABLE; Schema: public; Owner: WittCepter
--

CREATE TABLE public.cheese (
    cheese_id integer NOT NULL,
    name character varying(255) NOT NULL
);


ALTER TABLE public.cheese OWNER TO "WittCepter";

...

--
-- Name: subscriber; Type: TABLE; Schema: public; Owner: WittCepter
--

CREATE TABLE public.subscriber (
    subscriber_id integer NOT NULL,
    name character varying(255) NOT NULL,
    email character varying(255) NOT NULL
);


ALTER TABLE public.subscriber OWNER TO "WittCepter";

...

--
-- Data for Name: cheese; Type: TABLE DATA; Schema: public; Owner: WittCepter
--

COPY public.cheese (cheese_id, name) FROM stdin;
1	cheddar
2	brie
3	feta
4	mozzarella
5	parmesan
.


--
-- Data for Name: subscriber; Type: TABLE DATA; Schema: public; Owner: WittCepter
--

COPY public.subscriber (subscriber_id, name, email) FROM stdin;
1	wittcode	a@a.com
2	Sabin	b@b.com
3	Greg	c@c.com
4	Spencer	d@d.com
.

...

--
-- PostgreSQL database dump complete
--

We can of course configure the pg_dump command further. For example, we can tell it to only back up our cheese table by using the -t flag.

pg_dump -U WittCepter -d my_db -t cheese -f ./cheese-backup.sql

The -t flag accepts a pattern to match against the table names. If we check the contents of the cheese-backup.sql file, we can see that only the cheese table was backed up.

--
-- PostgreSQL database dump
--

-- Dumped from database version 16.2
-- Dumped by pg_dump version 16.2

...

--
-- Name: cheese; Type: TABLE; Schema: public; Owner: WittCepter
--

CREATE TABLE public.cheese (
    cheese_id integer NOT NULL,
    name character varying(255) NOT NULL
);

ALTER TABLE public.cheese OWNER TO "WittCepter";

--
-- Data for Name: cheese; Type: TABLE DATA; Schema: public; Owner: WittCepter
--

COPY public.cheese (cheese_id, name) FROM stdin;
1	cheddar
2	brie
3	feta
4	mozzarella
5	parmesan
.

...

--
-- PostgreSQL database dump complete
--

pg_dump Format

It should be noted that pg_restore is meant to be used with dump files, not plain-text SQL files, which is the default output type for pg_dump. We can still restore a database with an SQL file, but through using psql. If we want to use pg_restore then we should change the output format using the -F flag.

pg_dump -U WittCepter -d my_db -F tar -f ./my-backup.tar

The -F flag specifies the format of the archive. The default format is plain-text SQL. Another option is to output a tar file. The tar-format is suitable for backup and restore with pg_restore.

pg_restore

After creating a backup of a Postgres database with pg_dump, we can restore it with pg_restore. The pg_restore utility command allows us to restore a Postgres database from an archive file. To demonstrate, lets use our tar-format backup to restore the database. First however, lets add a couple rows to our cheese table.

psql -U WittCepter -d my_db
INSERT INTO cheese (name) VALUES ('gorgonzola'), ('gouda');
SELECT * FROM cheese;
 cheese_id |    name    
-----------+------------
         1 | cheddar
         2 | brie
         3 | feta
         4 | mozzarella
         5 | parmesan
         6 | gorgonzola
         7 | gouda
(7 rows)
\q

Now that we have two extra rows, lets restore the database to what it was before with pg_restore.

pg_restore -U WittCepter -c -C -d postgres -v my-backup.tar
  • -U - The username to connect to the database as.
  • -c - Before restoring the database, drop all objects that will be restored. Useful for overwriting an existing database. Short for --clean.
  • -C - Create the database before restoring into it. If -c (--clean) is specified too then drop and recreate the target database before connecting to it. Short for --create.
  • -d - Database to issue the initial DROP DATABASE and CREATE DATABASE commands (because -C is used).
  • -v - Make the command verbose.
  • Supply the tar-format archive file to perform the restore.

We can see the verbose output when running the command, including dropping of the database, creating the database, etc.

pg_restore: connecting to database for restore
pg_restore: dropping DATABASE my_db
pg_restore: creating DATABASE "my_db"
pg_restore: connecting to new database "my_db"
pg_restore: creating TABLE "public.cheese"
pg_restore: creating SEQUENCE "public.cheese_cheese_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.cheese_cheese_id_seq"
pg_restore: creating TABLE "public.subscriber"
pg_restore: creating SEQUENCE "public.subscriber_subscriber_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.subscriber_subscriber_id_seq"
pg_restore: creating DEFAULT "public.cheese cheese_id"
pg_restore: creating DEFAULT "public.subscriber subscriber_id"
pg_restore: processing data for table "public.cheese"
pg_restore: processing data for table "public.subscriber"
pg_restore: executing SEQUENCE SET cheese_cheese_id_seq
pg_restore: executing SEQUENCE SET subscriber_subscriber_id_seq
pg_restore: creating CONSTRAINT "public.cheese cheese_pkey"
pg_restore: creating CONSTRAINT "public.subscriber subscriber_email_key"
pg_restore: creating CONSTRAINT "public.subscriber subscriber_pkey"

Now if we query the database, we can see that it has been restored to its previous snapshot.

psql -U WittCepter -d my_db
SELECT * FROM cheese;
cheese_id |    name    
-----------+------------
         1 | cheddar
         2 | brie
         3 | feta
         4 | mozzarella
         5 | parmesan
(5 rows)
Backing Up a Postgres Database with pg_dump and pg_restore