Backup

Instances

The pglift instance command line entry point exposes backup and restore commands to respectively perform instance-level backup and restoration using selected PITR tool, currently pgBackRest.

For this to work the pgBackRest satellite component needs to be enabled in site settings. Please refer to pgBackRest site configuration section.

Once enabled, at instance creation, providing a name for the stanza is mandatory. Reading the Stanza Option section in pgBackRest documentation will help you choose the right name.

$ pglift instance create main --pgbackrest-stanza=myapp

Backup

Assuming we have a main instance running:

$ pglift instance status main
PostgreSQL: running

The instance backup command can be used as follows:

$ pglift instance backup main
INFO     backing up instance with pgBackRest

The type of backup (full, incremental or differential) can be specified through --type [full|incr|diff] option. By default, an incremental backup would be performed, unless no prior backup exists in which case pgBackRest will switch to a full backup.

The backups command can be used to list available backups:

$ pglift instance backups main
                                                 Available backups for instance 14/main
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ label                             ┃ size    ┃ repo_size ┃ date_start                ┃ date_stop                 ┃ type ┃ databases              ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━┩
│ 20220518-102816F_20220518-103636I │ 49.4MiB │ 5.1MiB    │ 2022-05-18 10:36:36+02:00 │ 2022-05-18 10:36:39+02:00 │ incr │ myapp, postgres        │
│ 20220518-102816F_20220518-103527I │ 73.0MiB │ 7.0MiB    │ 2022-05-18 10:35:27+02:00 │ 2022-05-18 10:35:31+02:00 │ incr │ bench, myapp, postgres │
│ 20220518-102816F                  │ 49.4MiB │ 5.1MiB    │ 2022-05-18 10:28:16+02:00 │ 2022-05-18 10:28:21+02:00 │ full │ bench, postgres        │
└───────────────────────────────────┴─────────┴───────────┴───────────────────────────┴───────────────────────────┴──────┴────────────────────────┘

Restore

To restore the PostgreSQL instance, use instance restore command (the instance must not be running):

$ pglift instance stop main
$ pglift instance restore main
INFO     restoring instance with pgBackRest

With no option, the restore action will use the latest backup and replay all available WAL.

With --label option, the restore action does not replay WAL and the instance is restored at its state targeted by specified label.

$ pglift instance restore main --label 20220518-102816F_20220518-103527I
INFO     restoring instance with pgBackRest
$ pglift database list
┏━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ name      ┃ owner    ┃ encoding ┃ collation ┃ ctype ┃ acls                    ┃ size    ┃ description             ┃ tablespace       ┃
┡━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ bench     │ postgres │ UTF8     │ C         │ C     │                         │ 23.6MiB │                         │ name: pg_default │
│           │          │          │           │       │                         │         │                         │ location:        │
│           │          │          │           │       │                         │         │                         │ size: 72.6MiB    │
│ myapp     │ postgres │ UTF8     │ C         │ C     │                         │ 23.6MiB │                         │ name: pg_default │
│           │          │          │           │       │                         │         │                         │ location:        │
│           │          │          │           │       │                         │         │                         │ size: 72.6MiB    │
│ postgres  │ postgres │ UTF8     │ C         │ C     │                         │ 8.6MiB  │ default administrative  │ name: pg_default │
│           │          │          │           │       │                         │         │ connection database     │ location:        │
│           │          │          │           │       │                         │         │                         │ size: 72.6MiB    │
│ template1 │ postgres │ UTF8     │ C         │ C     │ =c/postgres,            │ 8.4MiB  │ default template for    │ name: pg_default │
│           │          │          │           │       │ postgres=CTc/postgres   │         │ new databases           │ location:        │
│           │          │          │           │       │                         │         │                         │ size: 72.6MiB    │
└───────────┴──────────┴──────────┴───────────┴───────┴─────────────────────────┴─────────┴─────────────────────────┴──────────────────┘

Note

Often when performing instance restore, it can be useful to examine pgBackRest command output. This can be achieved by setting the log-level to DEBUG in pglift command (e.g. pglift -L debug instance restore).

Databases logical backup (dump)

Create a dump

pglift also provides a convenient way to backup a database for an instance.

Assuming we have a main instance running with a myapp database.

The database dump command can be used as follows:

$ pglift database -i main dump myapp
INFO     backing up database 'myapp' on instance 14/main

By default, this command runs the pg_dump utility and creates a dump in the dumps_directory (with a name including the current date and time). Option -o/--output DIRECTORY may alternatively be used to specify another output directory.

The dumps_directory and dump_commands can be adjusted in the site settings (see Settings):

/etc/pglift/settings.yaml
 postgresql:
     dumps_directory: dumps/{version}-{name}
     dump_commands:
     -
         - "{bindir}/pg_dump"
         - -Fc
         - -f
         - "{path}/{dbname}_{date}.dump"
         - -d
         - "{conninfo}"

Here’s an example of a command that simply dumps the database in SQL format:

/etc/pglift/settings.yaml
 postgresql:
     dump_commands:
     -
         - "{bindir}/pg_dump"
         - -f
         - "{path}/{dbname}_{date}.sql"
         - -d
         - "{conninfo}"

When using pg_dump, some important information is not dumped (for example roles or tablespaces definitions). If this is a limitation, pg_back can be used instead:

/etc/pglift/settings.yaml
 postgresql:
     dump_commands:
     -
         - /path/to/pg_back
         - -B
         - "{bindir}"
         - -b
         - "{path}"
         - -d
         - "{conninfo}"
         - "{dbname}"

List dumps

Database dumps can be listed with the following command:

$ pglift database -i main dumps myapp
                        Dumps for myapp
┏━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ id               ┃ dbname ┃ date                      ┃
┡━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ myapp_65e9c2e982 │ myapp  │ 2024-03-12 14:39:55+01:00 │
│ myapp_fcfc27161e │ myapp  │ 2024-03-12 14:51:34+01:00 │
└──────────────────┴────────┴───────────────────────────┘

Warning

Only dumps created in the default output directory are listed.

Note

This command may not work as expected if dumps_commands has been adjusted in the site settings. The dumps will be listed only if their file name follows the {dbname}_{date}.dump pattern.

It will fortunately be the case if pg_back is used (with its default “custom” output format).

Restore a dump

The database restore command can be used as follows:

$ pglift database -i main drop myapp
$ pglift database -i main restore myapp_65e9c2e982
INFO     restoring dump for 'myapp' on instance 16/main

Or the dump can be restored on a new database:

$ pglift database -i main create mynewdb
$ pglift database -i main restore myapp_65e9c2e982 mynewdb
INFO     restoring a database backup on instance 16/main

Scheduled backups

At instance creation, when systemd is used as a scheduler, a timer for periodic backup is installed:

$ systemctl --user list-timers
NEXT                         LEFT     LAST                         PASSED       UNIT                            ACTIVATES
Thu 2021-09-16 00:00:00 CEST 12h left Wed 2021-09-15 08:15:58 CEST 3h 23min ago postgresql-backup@13-main.timer postgresql-backup@13-main.service

1 timers listed.
$ systemctl --user cat postgresql-backup@13-main.service
[Unit]
Description=Backup %i PostgreSQL database instance
After=postgresql@%i.service

[Service]
Type=oneshot

ExecStart=/usr/bin/python3 -m pglift instance backup %I