User Tools

Site Tools


postgresql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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 ======
  
postgresql.txt ยท Last modified: 2020/07/15 09:42 by mstraub