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 [2017/05/23 10:57]
mstraub [PSQL meta-commands]
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 590: Line 594:
  
 ====== Backups ====== ====== Backups ======
 +
 +There are many options available on how to back up your databases. Postgres already contains some tools itself:
 +  * ''​pg_dump'':​ per-database dumping of db contents
 +  * ''​pg_basebackup'':​ binary backup of whole postgres clusters (per db backup is not possible)
 +
 +However, especially for larger databases (hundreds of GB) more powerful third-party tools featuring parallelism ​ and incremental backups are [[http://​bonesmoses.org/​2017/​02/​03/​pg-phriday-getting-back-up|recommended]]:​
 +  * [[https://​pgbackrest.org|pgBackRest]]
 +  * [[https://​www.pgbarman.org|pgBarman]]
 +
 +
 +===== pg_dump =====
  
 It makes sense to use the postgres user It makes sense to use the postgres user
Line 634: Line 649:
 # └───────────────────────── min (0 - 59) # └───────────────────────── min (0 - 59)
 </​code>​ </​code>​
 +
 +===== pg_basebackup =====
 +
 +see e.g. [[https://​blog.sleeplessbeastie.eu/​2016/​02/​15/​how-to-perform-base-postgresql-backup-using-pg_basebackup-utility|this tutorial]]
 +
 +===== pgBackRest =====
 +
 +A typical use of pgBackRest would be to install a backup server that backs up several DB servers. For now we only use a simpler approach: pgBackRest backing up on localhost. For more see the [[https://​pgbackrest.org/​user-guide.html|excellent user guide]] or [[https://​pgstef.github.io/​2018/​01/​04/​introduction_to_pgbackrest.html|this good introduction blog post]].
 +
 +Install from postgres repo:
 +
 +<code bash>
 +sudo add-apt-repository "deb http://​apt.postgresql.org/​pub/​repos/​apt/​ xenial-pgdg main"
 +wget --quiet -O - https://​www.postgresql.org/​media/​keys/​ACCC4CF8.asc | sudo apt-key add -
 +sudo apt update
 +sudo apt install pgbackrest
 +</​code>​
 +
 +==== Incremental Backups on Localhost ====
 +
 +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/​pgbackrest.conf>​
 +[global]
 +repo1-path=/​var/​lib/​pgbackrest
 +repo1-retention-full=5
 +repo1-retention-diff=1
 +
 +[STANZANAME]
 +pg1-path=/​var/​lib/​postgresql/​9.5/​CLUSTERNAME
 +</​file>​
 +
 +Then adjust the postgres config to allow for proper backups
 +
 +<file - /​etc/​postgresql/​9.5/​main/​postgresql.conf>​
 +listen_addresses = '​*'​
 +
 +# WRITE AHEAD LOG
 +wal_level = hot_standby ​
 +archive_mode = on 
 +archive_command = '​pgbackrest --stanza=STANZANAME archive-push %p'
 +
 +# REPLICATION
 +max_wal_senders = 3
 +
 +# ERROR REPORTING AND LOGGING
 +log_line_prefix = ''​
 +</​file>​
 +
 +Reload the cluster (or the complete postgres server) to activate changes
 +
 +  sudo systemctl restart postgresql@9.5-CLUSTERNAME
 +  ​
 +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 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.1495529836.txt.gz · Last modified: 2017/05/23 10:57 by mstraub