Backup and restore postgres databases using cli tools

As usual PostgreSQL documentation is very well designed and you can find info of all the tools one can use to backup and restore databases as well as a lot of examples.

This post is more of a quick reminder of the most common script I use on a daily basis. You won't find an exhaustive list of all available options.

How to backup a database

pg_dump dbname -h localhost -U username -n schema_name -O -v -b -Fc > /path/to/backup/file.backup

-h or --host where is running the postgres server
-U or --username to specify the user
-n or --schema to specify the schema. You can select multiple schemas by duplicating the -n option. If the schema depends on objects in other schemas those will not be backup. Blob as well are not backup unless you add the --blobs switch
-b or --blobs backup blob data
-O or --no-owner to discard original ownership of the objects in the backup file
-v or --verbose verbose mode
-Fc or format=c format custom. Use this mode when you need a compressed file. This format can be restored using pg_restore. If format is not specified it will be a plain SQL script by default.

Or:

pg_dump --file "/path/to/backup/file.backup"
    --host=localhost
    --username "userName"
    --no-password
    --verbose
    --format=c
    --blobs
    --no-owner
    --schema bre_anren_2020_2021
    dbname

Restore using pg_restore

To restore a backup generated with the -Fc format option:

pg_restore --host=localhost -U username -d dbname -Fc -v < /path/to/backup/file.backup

--host where is running the postgres server
-U or --username to specify the user
-d name of the database

Note on using it with docker

Most of the time I'm using these tools on a Docker container. You simply need to execute it with:

docker exec -i container_name pg_dump dbname > /path/to/backup/file.backup

/path/to/backup/file.backup will be in the container so one way to download it would be to volume the directory with your host. Or use: docker cp <containerName>:/path/to/backup/file.backup /host/path/

Restore a backup without running the refresh of the materialized view

The solution consists in extracting from your backup the list the table of contents with the -l or --list option. Altering this list of contents to remove the refresh of the views. And then launching the backup using the table of contents as parameter with the -L or --list-file option.

# backup in custom format
pg_dump database -Fc backup.dump

# generate the table of contents and remove MATERIALIZED VIEW DATA
pg_restore -l backup.dump | sed '/MATERIALIZED VIEW DATA/d' > refresh.lst

# restore using the table of contents as parameter
pg_restore -L refresh.lst -d database backup.dump

# generate the table of contents with only the materialized view data
pg_restore -l backup.dump | grep 'MATERIALIZED VIEW DATA' > refresh.lst

# restore the backup executing only the refresh of the materialized views
pg_restore -L refresh.lst -d database backup.dump

If you need to restore it on a docker container the list file must be on the container. You need to copy it on the container before running the pg_restore command.

# generate the table of contents and remove MATERIALIZED VIEW DATA
# using your host `pg_restore`
pg_restore -l backup.dump | sed '/MATERIALIZED VIEW DATA/d' > refresh.lst

# copy the table of contents file on the container
docker cp refresh.lst containerName:/tmp

# restore the backup executing only the refresh of the materialized views
# on your running container `containerName`
docker exec -i containerName pg_restore -L /tmp/refresh.lst -U username -d database -Fc -v /host/path/backup.dump

Source: https://gist.github.com/kmatt/14c8e6ae2cff9e4faa0e

Disconnect all client before doing a restore

If you need to restore a database on a database that is actively used you'll need to disconnect all clients before doing so:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'dbname'
AND pid <> pg_backend_pid();