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
postgresql [2018/07/24 14:50]
mstraub [Incremental Backups on Localhost]
postgresql [2020/07/15 09:42] (current)
mstraub
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 703: Line 707:
   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.1532436624.txt.gz ยท Last modified: 2018/07/24 14:50 by mstraub