User Tools

Site Tools



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
postgresql [2018/07/24 14:26]
mstraub [Incremental Backups on Localhost]
postgresql [2020/07/15 09:42] (current)
Line 44: Line 44:
 # set a password # set a password
 \password \password
 +# make an existing user superuser (or remove superuser rights)
 +sudo -u postgres psql -c "ALTER USER theuser WITH SUPERUSER;"​
 +sudo -u postgres psql -c "ALTER USER theuser WITH NOSUPERUSER;"​
 # create a restricted user with password (long and short version) # create a restricted user with password (long and short version)
Line 58: Line 62:
 ==== 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 665: Line 669:
 ==== 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]
 </​file>​ </​file>​
Line 696: Line 702:
   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
 +**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.1532435205.txt.gz · Last modified: 2018/07/24 14:26 by mstraub