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 [2018/07/24 14:26] mstraub [Incremental Backups on Localhost] |
postgresql [2018/07/24 15:44] mstraub [Incremental Backups on Localhost] |
||
---|---|---|---|
Line 665: | Line 665: | ||
==== Incremental Backups on Localhost ==== | ==== Incremental Backups on Localhost ==== | ||
- | In addition to the global section configure a so-called "stanza" for each cluster you want to back up. The default repo-path should already be there (and owned by user postgres). | + | 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.conf> | + | <file - /etc/pgbackrest/pgbackrest.conf> |
[global] | [global] | ||
- | repo-path=/var/lib/pgbackrest | + | repo1-path=/var/lib/pgbackrest |
+ | repo1-retention-full=5 | ||
+ | repo1-retention-diff=1 | ||
[STANZANAME] | [STANZANAME] | ||
- | db-path=/var/lib/postgresql/9.5/CLUSTERNAME | + | pg1-path=/var/lib/postgresql/9.5/CLUSTERNAME |
</file> | </file> | ||
Line 696: | Line 698: | ||
sudo systemctl restart postgresql@9.5-CLUSTERNAME | sudo systemctl restart postgresql@9.5-CLUSTERNAME | ||
| | ||
- | Create the pgbackrest stanza | + | 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 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 | ||
+ | |||
+ | |||
+ | |||
+ | Log files can be found in ''/var/log/pgbackrest''. | ||
====== Performance Tuning ====== | ====== Performance Tuning ====== | ||