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
Next revision Both sides next revision
postgresql [2018/07/24 14:26]
mstraub [Incremental Backups on Localhost]
postgresql [2018/07/24 16:47]
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
 +
 +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