PostgreSQL Backups with pg_back

1. Introduction

In the world of PostgreSQL database management, ensuring robust backup strategies is mandatory. This week I discovered on postgresql.org latest news one useful tool that streamlines this process: pg_back.

pg_back serves as a dedicated dumping tool designed for PostgreSQL. Its primary objective is to facilitate the comprehensive dumping of either all or specific databases, along with global entities, in a user-specified format. This distinction is crucial, as a conventional call to pg_dumpall only exports databases in the plain SQL format.

Under the hood, pg_back make use of pg_dumpall to handle the dumping of roles and tablespaces definitions. Additionally, it utilizes pg_dump to create separate files, each containing the dump of an entire database or the selected databases, all in a customized format. This approach ensures flexibility and precision in the backup process. It also purges old dumps and has many different features, let’s discover that !

Before starting, just know that for this blog I’m running PostgreSQL 16beta1 installed from source on the OS openSUSE 15.4. The use of the beta version of Postgres caused me issues when I wanted to restore my dumps (pg_restore: error: unsupported version (1.15) in file header), so don’t be surprised if you noticed that I updated to PostgreSQL 16.1 during the blog.

2. Installation

We are going to install pg_back from source, for this, you need go version 1.20 at least.

11:23:15 postgres@localhost:/home/postgres/ [pgdummy] go install github.com/orgrim/pg_back@latest

To make pg_back globally accessible, add its path to your PATH:

postgres@localhost:/home/postgres/ [pgdummy] vi ~/.bashrc
export PATH=$PATH:/home/postgres/go/bin
postgres@localhost:/home/postgres/ [pgdummy] source ~/.bashrc
postgres@localhost:/home/postgres/ [pgdummy] pg_back --version
pg_back version 2.2.0

3. Creating Databases and Tables

Just to have some content to dump later, create databases and a table with some data.

postgres=# create database test1;
CREATE DATABASE
postgres=# create database test2;
CREATE DATABASE
postgres=# \c test1 postgres
psql (16beta1 dbi services build, server 14.9)
You are now connected to database "test1" as user "postgres".

test1=# INSERT INTO t1 (id)
SELECT generate_series(1, 1001000);
INSERT 0 1001000

4. Setting up Backup Repository

By default, the dumps from pg_back will go to /var/backups/postgresql.
You can use the -b parameter to give the path where to save the dump files.

postgres@localhost:/home/postgres/ [PG16] mkdir /u99/pgdata/16beta1/backup

5. Initial Backup

Perform the first test dump without extra parameters. You need to use the necessary PostgreSQL connection parameters.

pg_back -U postgres -p 5432 -d test1 -b /u99/pgdata/16beta1/backup/

11:53:30 postgres@localhost:/home/postgres/ [PG16] pg_back -U postgres -p 5432 -d test1 -b /u99/pgdata/16beta1/backup/
2024/01/18 11:54:22 INFO: dumping globals
2024/01/18 11:54:23 INFO: dumping instance configuration
2024/01/18 11:54:23 INFO: dumping database postgres
2024/01/18 11:54:23 INFO: dump of postgres to /u99/pgdata/16beta1/backup/postgres_2024-01-18T11:54:23+01:00.dump done
2024/01/18 11:54:23 INFO: dumping database test1
2024/01/18 11:54:23 INFO: dump of test1 to /u99/pgdata/16beta1/backup/test1_2024-01-18T11:54:23+01:00.dump done
2024/01/18 11:54:23 INFO: dumping database test2
2024/01/18 11:54:23 INFO: dump of test2 to /u99/pgdata/16beta1/backup/test2_2024-01-18T11:54:23+01:00.dump done
2024/01/18 11:54:23 INFO: waiting for postprocessing to complete
2024/01/18 11:54:23 INFO: purging old dumps

Control the dump files creation

11:54:23 postgres@localhost:/home/postgres/ [PG16] cd /u99/pgdata/16beta1/backup/
11:54:45 postgres@localhost:/u99/pgdata/16beta1/backup/ [PG16] ll
total 656
-rw------- 1 postgres postgres   4586 Jan 18 11:54 hba_file_2024-01-18T11:54:23+01:00.out
-rw------- 1 postgres postgres   1679 Jan 18 11:54 ident_file_2024-01-18T11:54:23+01:00.out
-rw------- 1 postgres postgres    372 Jan 18 11:54 pg_globals_2024-01-18T11:54:22+01:00.sql
-rw------- 1 postgres postgres    453 Jan 18 11:54 pg_settings_2024-01-18T11:54:23+01:00.out
-rw------- 1 postgres postgres   1486 Jan 18 11:54 postgres_2024-01-18T11:54:23+01:00.dump
-rw------- 1 postgres postgres 641324 Jan 18 11:54 test1_2024-01-18T11:54:23+01:00.dump
-rw------- 1 postgres postgres   1259 Jan 18 11:54 test2_2024-01-18T11:54:23+01:00.dump

6. Advanced Usage of pg_back Parameters

6.1 Exclude Database

Exclude a specific database from the backup with option -D

11:57:07 postgres@localhost:/u99/pgdata/16beta1/ [PG16] mkdir backup_exclude_db
11:57:18 postgres@localhost:/u99/pgdata/16beta1/ [PG16] pg_back -U postgres -p 5432 -d test1 -D test2 -b /u99/pgdata/16beta1/backup_exclude_db/

Notice that the dump for the database test2 is missing in ./backup_exclude_db

6.2 Compress Backup

Compress the backup to save space with option -Z

12:06:20 postgres@localhost:/u99/pgdata/16beta1/ [PG16] mkdir backup_compressed
12:06:44 postgres@localhost:/u99/pgdata/16beta1/ [PG16] pg_back -U postgres -p 5432 -d test1 -Z 4 -b /u99/pgdata/16beta1/backup_compressed/

Check the difference of size between the backup

12:06:52 postgres@localhost:/u99/pgdata/16beta1/ [PG16] du -sh *
656K    backup
524K    backup_compressed
712K    backup_exclude_db

7. pg_back config file

You can manage the behavior of pg_back thanks to a config file. You can find a template of this file on the github of the project https://github.com/orgrim/pg_back/blob/master/pg_back.conf
Inside the template, you will find all possible options to use pg_back. We fill the PostgreSQL connection parameters and adjust the purge one. Create and edit the pg_back.conf file:

vi /u99/pgdata/16beta1/pg_back.conf
# PostgreSQL connection options. This are the usual libpq
# variables. dbname is the database used to dump globals, acl,
# configuration and pause replication. password is better set in
# ~/.pgpass
host =
port = 5432
user = postgres
dbname = db1
backup_directory = /u99/pgdata/16beta1/backup_config_file/{dbname}
# Purge dumps older than this number of days.
purge_older_than = 6m

Run a backup with the config file

14:36:44 postgres@localhost:/u99/pgdata/16beta1/ [PG16] mkdir backup_config_file
14:41:13 postgres@localhost:/u99/pgdata/16beta1/ [PG16] pg_back -c pg_back.conf

8. Restoring Database Dump

Lastly, we need to see how to restore the dumps from pg_back. First, for our tests, drop the database to delete the data and recreate the database before restoring.

postgres=# drop database test1;
DROP DATABASE
postgres=# create database test1;
CREATE DATABASE

postgres=# \c test1 postgres
psql (16beta1 dbi services build, server 14.9)
You are now connected to database "test1" as user "postgres".
test1=# \dt
Did not find any relations.

Restore the database test1

16:35:06 postgres@localhost:/u99/pgdata/16beta1/ [PG16] pg_restore -U postgres -p 5432 -v --exit-on-error 2> restore_errors.log -d test1 backup_config_file/test1/test1_2024-01-18T16:32:47+01:00.dump

Verify that our table t1 and its data have been restored

postgres=# \c test1
psql (16.1 dbi services build, server 14.9)
You are now connected to database "test1" as user "postgres".
test1=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

test1=# select count(*) from t1;
  count
---------
 1001000
(1 row)

9. Conclusion

In the world of database management, the importance of robust backup strategies cannot be overstated. As we explored in this blog, the use of pg_back proves to be a valuable addition to the PostgreSQL toolkit, simplifying the backup process with its dedicated dumping capabilities.
In conclusion, pg_back stands as a valuable asset for PostgreSQL administrators, offering a versatile and easy approach to database backup management. As you integrate this tool into your PostgreSQL environment, you gain simplified control and flexibility.