This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
postgresql [2017/05/23 10:57] mstraub [PSQL meta-commands] |
postgresql [2018/07/24 16:47] mstraub [Incremental Backups on Localhost] |
||
---|---|---|---|
Line 590: | Line 590: | ||
====== Backups ====== | ====== Backups ====== | ||
+ | |||
+ | There are many options available on how to back up your databases. Postgres already contains some tools itself: | ||
+ | * ''pg_dump'': per-database dumping of db contents | ||
+ | * ''pg_basebackup'': binary backup of whole postgres clusters (per db backup is not possible) | ||
+ | |||
+ | However, especially for larger databases (hundreds of GB) more powerful third-party tools featuring parallelism and incremental backups are [[http://bonesmoses.org/2017/02/03/pg-phriday-getting-back-up|recommended]]: | ||
+ | * [[https://pgbackrest.org|pgBackRest]] | ||
+ | * [[https://www.pgbarman.org|pgBarman]] | ||
+ | |||
+ | |||
+ | ===== pg_dump ===== | ||
It makes sense to use the postgres user | It makes sense to use the postgres user | ||
Line 634: | Line 645: | ||
# └───────────────────────── min (0 - 59) | # └───────────────────────── min (0 - 59) | ||
</code> | </code> | ||
+ | |||
+ | ===== pg_basebackup ===== | ||
+ | |||
+ | see e.g. [[https://blog.sleeplessbeastie.eu/2016/02/15/how-to-perform-base-postgresql-backup-using-pg_basebackup-utility|this tutorial]] | ||
+ | |||
+ | ===== pgBackRest ===== | ||
+ | |||
+ | A typical use of pgBackRest would be to install a backup server that backs up several DB servers. For now we only use a simpler approach: pgBackRest backing up on localhost. For more see the [[https://pgbackrest.org/user-guide.html|excellent user guide]] or [[https://pgstef.github.io/2018/01/04/introduction_to_pgbackrest.html|this good introduction blog post]]. | ||
+ | |||
+ | Install from postgres repo: | ||
+ | |||
+ | <code bash> | ||
+ | sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" | ||
+ | wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - | ||
+ | sudo apt update | ||
+ | sudo apt install pgbackrest | ||
+ | </code> | ||
+ | |||
+ | ==== Incremental Backups on Localhost ==== | ||
+ | |||
+ | In addition to the global section, where e.g. retention is configured, you must add a so-called "stanza" for each cluster you want to back up. Make sure the repo1-path exists and is owned by user postgres. | ||
+ | |||
+ | <file - /etc/pgbackrest/pgbackrest.conf> | ||
+ | [global] | ||
+ | repo1-path=/var/lib/pgbackrest | ||
+ | repo1-retention-full=5 | ||
+ | repo1-retention-diff=1 | ||
+ | |||
+ | [STANZANAME] | ||
+ | pg1-path=/var/lib/postgresql/9.5/CLUSTERNAME | ||
+ | </file> | ||
+ | |||
+ | Then adjust the postgres config to allow for proper backups | ||
+ | |||
+ | <file - /etc/postgresql/9.5/main/postgresql.conf> | ||
+ | listen_addresses = '*' | ||
+ | |||
+ | # WRITE AHEAD LOG | ||
+ | wal_level = hot_standby | ||
+ | archive_mode = on | ||
+ | archive_command = 'pgbackrest --stanza=STANZANAME archive-push %p' | ||
+ | |||
+ | # REPLICATION | ||
+ | max_wal_senders = 3 | ||
+ | |||
+ | # ERROR REPORTING AND LOGGING | ||
+ | log_line_prefix = '' | ||
+ | </file> | ||
+ | |||
+ | Reload the cluster (or the complete postgres server) to activate changes | ||
+ | |||
+ | sudo systemctl restart postgresql@9.5-CLUSTERNAME | ||
+ | | ||
+ | Create and test the pgbackrest stanza | ||
+ | |||
+ | sudo -u postgres pgbackrest --stanza=STANZANAME --log-level-console=info stanza-create | ||
+ | sudo -u postgres pgbackrest --stanza=STANZANAME --log-level-console=info check | ||
+ | |||
+ | Make backups manually (e.g. from a cronjob) | ||
+ | |||
+ | sudo -u postgres pgbackrest --stanza=STANZANAME --type=full --log-level-console=detail backup | ||
+ | sudo -u postgres pgbackrest --stanza=STANZANAME --type=incr --log-level-console=detail backup | ||
+ | |||
+ | Make **regular backups via cronjob** | ||
+ | |||
+ | <file - /etc/cron.d/pgbackrest> | ||
+ | #minute hour day-of-month month day-of-week user command | ||
+ | 0 4 * * 0 postgres pgbackrest --type=full --stanza=STANZANAME backup | ||
+ | 0 4 * * 1-6 postgres pgbackrest --type=diff --stanza=STANZANAME backup | ||
+ | </file> | ||
+ | |||
+ | **Restore latest backup.** The cluster must be stopped and the cluster directory must exist and be empty. | ||
+ | |||
+ | sudo systemctl stop postgresql@9.5-CLUSTERNAME | ||
+ | sudo mv /var/lib/postgresql/9.5/CLUSTERNAME /opt/backups/CLUSTERNAME_backup | ||
+ | sudo -u postgres mkdir /var/lib/postgresql/9.5/CLUSTERNAME | ||
+ | sudo chmod 0700 /var/lib/postgresql/9.5/CLUSTERNAME | ||
+ | sudo -u postgres pgbackrest --stanza=STANZANAME --log-level-console=detail restore | ||
+ | sudo systemctl start postgresql@9.5-CLUSTERNAME | ||
+ | |||
+ | To **restore a specific backup** use ''%%--set%%'' with the name of a folder in ''/var/lib/pgbackrest/backup/STANZANAME'', e.g.: | ||
+ | |||
+ | sudo -u postgres pgbackrest --set=20180724-144511F --stanza=STANZANAME --log-level-console=detail restore | ||
+ | |||
+ | You can also copy the contents of ''/var/lib/pgbackrest'' to a different device and restore it there. | ||
+ | |||
+ | Log files can be found in ''/var/log/pgbackrest''. | ||
====== Performance Tuning ====== | ====== Performance Tuning ====== | ||