PostgreSQL Backup and Recovery

One of the most important principles of working with databases is that you always need a way to recover the database structures and data in case of an emergency. Some examples of an emergency could be that a table was loaded with bad data or a database structure was modified in a way that broke it. The best way to protect yourself is to frequently back up your databases. Ideally, you want the backups to be done in a scheduled job/program, so that you can recover to the point of time that you need. This blog post will show you how to backup and recover manually.

SQL Dump

Not that one

A SQL Dump is a file with SQL commands that will recreate the database in the same state as it was at the time of the dump.

The syntax of creating a dump:

pg_dump dbname > dumpfile

This will create a dumpfile in the folder that you are currently in.

An example with a timestamp (so you know when the backup was made):

pg_dump idea > idea-backup-$(date +%Y-%m-%d-%H.%M.%S).sql

Creates:

idea-backup-2019-08-31-12.13.50.sql

Snippets of how the dump looks:

CREATE TABLE public."Contents" (
id integer NOT NULL,
idea_id integer,
post character varying(255),
audio character varying(255),
"createdAt" timestamp with time zone NOT NULL,
"updatedAt" timestamp with time zone NOT NULL,
html character varying(255)
);
CREATE TABLE public."Ideas" (
id integer NOT NULL,
subject character varying(255),
category character varying(255),
"createdAt" timestamp with time zone NOT NULL,
"updatedAt" timestamp with time zone NOT NULL
);

COPY public."Contents" (id, idea_id, post, audio, "createdAt", "updatedAt", html) FROM stdin;
28  10  Hello ddd !Paragraph 2  aaa 2019-08-22 11:25:16.63-04   2019-08-22 11:25:16.63-04   <p>Hello <b>ddd</b> !</p><p>Paragraph 2</p>
29  11  Hello dd !Paragraph 2   aaa 2019-08-22 11:45:17.628-04  2019-08-22 11:45:17.628-04  <p>Hello <b>dd</b> !</p><p>Paragraph 2</p>
30  12  Hellooooooo&nbsp;!  bop 2019-08-23 11:36:47.964-04  2019-08-23 11:36:47.964-04  <p>Hellooooooo !</p>

COPY public."Ideas" (id, subject, category, "createdAt", "updatedAt") FROM stdin;
10  first song  music   2019-08-22 11:02:02.847-04  2019-08-22 11:02:02.847-04
11  second song music   2019-08-22 11:41:05.768-04  2019-08-22 11:41:05.768-04
12  third song  music   2019-08-23 11:36:32.717-04  2019-08-23 11:36:32.717-04
13  fourth song music   2019-08-24 10:32:15.107-04  2019-08-24 10:32:15.107-04
\.

As you can see, the SQL commands for creating the tables and inserting the data are generated from the pg_dump command.

Recovery

Not that one

The syntax for recovering a database with a dumpfile:

psql dbname < dumpfile

An example:

psql idea < idea-backup-2019-08-31-12.13.50.sql

You might get this error message after running this:

SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "SequelizeMeta" already exists
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 3
COPY 4
ERROR:  duplicate key value violates unique constraint "SequelizeMeta_pkey"
DETAIL:  Key (name)=(20190803175505-create-idea.js) already exists.
CONTEXT:  COPY SequelizeMeta, line 1
setval
--------
30
(1 row)
setval
--------
13
(1 row)
ALTER TABLE
ALTER TABLE
ERROR:  multiple primary keys for table "SequelizeMeta" are not allowed

Don’t worry about this. It is just metadata about the database that already exists on the SequelizeMeta table. It won’t break your database and its data.

You can check your data with a SQL query in PSQL to see that all your data is there. Your database is now fortified for whatever terrible things might happen to it. Make sure to do this often!