This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
postgresql [2016/10/17 18:10] mstraub [Change owner on all tables] |
postgresql [2018/07/24 16:47] mstraub [Incremental Backups on Localhost] |
||
---|---|---|---|
Line 126: | Line 126: | ||
-- list all users | -- list all users | ||
\du | \du | ||
+ | |||
-- list all schemas | -- list all schemas | ||
\dn | \dn | ||
- | -- list all tables (and views) in the current database / show details of table | + | |
+ | -- list tables | ||
\d | \d | ||
\d+ -- (also show comments) | \d+ -- (also show comments) | ||
- | \d tablename | + | \d tablename -- show details of a table |
- | -- list all tables of a certain schema | + | |
- | \dt schemaname.* | + | -- list all tables / tables of a certain schema |
+ | \dt *.* -- list all tables in DB | ||
+ | \dt schemaname.* -- list all tables in a certain schema | ||
+ | |||
+ | -- list all views | ||
+ | \dv *.* -- list all views in DB | ||
-- list all databases on the host | -- list all databases on the host | ||
\l | \l | ||
+ | |||
-- connect to a database | -- connect to a database | ||
\c dbname | \c dbname | ||
+ | |||
-- execute a command on the Linux shell (as-is) | -- execute a command on the Linux shell (as-is) | ||
\! command | \! command | ||
+ | |||
-- quit | -- quit | ||
\q | \q | ||
Line 579: | Line 590: | ||
====== 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 610: | Line 632: | ||
zcat YYYY-MM-DD_db_backup.dmp.gz | pg_restore -Fc -d target_db | zcat YYYY-MM-DD_db_backup.dmp.gz | pg_restore -Fc -d target_db | ||
</code> | </code> | ||
+ | |||
+ | Or, when creating the backup via cronjob in ''/etc/cron.d'': | ||
+ | <code> | ||
+ | 0 5 * * * postgres pg_dump -Fc dbname | gzip > /path/to/db_backup_`date +\%Y-\%m-\%dT\%H_\%M`.dmp.gz | ||
+ | # ┬ ┬ ┬ ┬ ┬ ┬ ┬ | ||
+ | # │ │ │ │ | │ └ command | ||
+ | # │ │ │ │ | └───── user executing the command | ||
+ | # │ │ │ │ └───────── day of week (0 - 7) (0 to 7 are Sunday to Sunday, or use names) | ||
+ | # │ │ │ └───────────── month (1 - 12) | ||
+ | # │ │ └───────────────── day of month (1 - 31) | ||
+ | # │ └───────────────────── hour (0 - 23) | ||
+ | # └───────────────────────── min (0 - 59) | ||
+ | </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 ====== | ||