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 in order to have pgBackRest configured on the instance.
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):
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:
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:
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 dump_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