This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
postgresql [2018/07/24 14:50] mstraub [Incremental Backups on Localhost] |
postgresql [2018/08/27 13:41] mantis |
||
---|---|---|---|
Line 58: | Line 58: | ||
==== Setting a password for user postgres ==== | ==== Setting a password for user postgres ==== | ||
<code sql> | <code sql> | ||
- | alter user postgres with password 'password'; | + | \password postgres |
</code> | </code> | ||
Line 703: | Line 703: | ||
sudo -u postgres pgbackrest --stanza=STANZANAME --log-level-console=info check | 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 ====== | ||