... | @@ -4,13 +4,11 @@ |
... | @@ -4,13 +4,11 @@ |
|
fast, although possibly less well-known and popular than it's eternal
|
|
fast, although possibly less well-known and popular than it's eternal
|
|
rival in the free software world, MySQL.
|
|
rival in the free software world, MySQL.
|
|
|
|
|
|
Tutorial
|
|
# Tutorial
|
|
========
|
|
|
|
|
|
|
|
Those are quick reminders on easy things to do in a cluster.
|
|
Those are quick reminders on easy things to do in a cluster.
|
|
|
|
|
|
Connecting
|
|
## Connecting
|
|
----------
|
|
|
|
|
|
|
|
Our PostgreSQL setup is fairly standard so connecting to the database
|
|
Our PostgreSQL setup is fairly standard so connecting to the database
|
|
is like any other Debian machine:
|
|
is like any other Debian machine:
|
... | @@ -20,11 +18,9 @@ is like any other Debian machine: |
... | @@ -20,11 +18,9 @@ is like any other Debian machine: |
|
This drops you in a [psql shell](https://www.postgresql.org/docs/11/app-psql.html) where you can issue SQL queries
|
|
This drops you in a [psql shell](https://www.postgresql.org/docs/11/app-psql.html) where you can issue SQL queries
|
|
and so on.
|
|
and so on.
|
|
|
|
|
|
Howto
|
|
# How-to
|
|
=====
|
|
|
|
|
|
|
|
Deployment
|
|
## Deployment
|
|
----------
|
|
|
|
|
|
|
|
A PostgreSQL server should be deployed with puppet, using the
|
|
A PostgreSQL server should be deployed with puppet, using the
|
|
`postgresql` module. Make sure you also include the
|
|
`postgresql` module. Make sure you also include the
|
... | @@ -38,8 +34,7 @@ Puppet things. |
... | @@ -38,8 +34,7 @@ Puppet things. |
|
TODO: turn this in a Tutorial by making it more straightforward to
|
|
TODO: turn this in a Tutorial by making it more straightforward to
|
|
deploy a cluster, for example just by including a single class.
|
|
deploy a cluster, for example just by including a single class.
|
|
|
|
|
|
Find what is taking up space
|
|
## Find what is taking up space
|
|
----------------------------
|
|
|
|
|
|
|
|
This will report size information for all tables, in both raw bytes and "pretty" form:
|
|
This will report size information for all tables, in both raw bytes and "pretty" form:
|
|
|
|
|
... | @@ -77,8 +72,7 @@ Same with databases: |
... | @@ -77,8 +72,7 @@ Same with databases: |
|
|
|
|
|
Source: [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Disk_Usage). See also [the upstream manual](https://www.postgresql.org/docs/11/disk-usage.html).
|
|
Source: [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Disk_Usage). See also [the upstream manual](https://www.postgresql.org/docs/11/disk-usage.html).
|
|
|
|
|
|
Running a full backup
|
|
## Running a full backup
|
|
---------------------
|
|
|
|
|
|
|
|
Backups are normally automatically ran on the backup server (currently
|
|
Backups are normally automatically ran on the backup server (currently
|
|
`bungei`) but you can also run a backup by hand. This will run a
|
|
`bungei`) but you can also run a backup by hand. This will run a
|
... | @@ -88,8 +82,7 @@ backup of `meronense`, for example: |
... | @@ -88,8 +82,7 @@ backup of `meronense`, for example: |
|
|
|
|
|
See the [reference](#Reference) section for details.
|
|
See the [reference](#Reference) section for details.
|
|
|
|
|
|
Picking a restore procedures
|
|
## Picking a restore procedures
|
|
----------------------------
|
|
|
|
|
|
|
|
We have two ways of restoring PostgreSQL backups, **indirect** and
|
|
We have two ways of restoring PostgreSQL backups, **indirect** and
|
|
**direct**.
|
|
**direct**.
|
... | @@ -111,8 +104,7 @@ to deploy and diagnose, as it has more moving parts. |
... | @@ -111,8 +104,7 @@ to deploy and diagnose, as it has more moving parts. |
|
Both procedures are adaptations of the [official recovery
|
|
Both procedures are adaptations of the [official recovery
|
|
procedure](https://www.postgresql.org/docs/11/continuous-archiving.html#BACKUP-PITR-RECOVERY), which can be refered to for more information.
|
|
procedure](https://www.postgresql.org/docs/11/continuous-archiving.html#BACKUP-PITR-RECOVERY), which can be refered to for more information.
|
|
|
|
|
|
Indirect restore procedures
|
|
## Indirect restore procedures
|
|
---------------------------
|
|
|
|
|
|
|
|
Reminder: this procedure **copies** the backup files from the backup
|
|
Reminder: this procedure **copies** the backup files from the backup
|
|
server to the database server and restores from those.
|
|
server to the database server and restores from those.
|
... | @@ -226,8 +218,7 @@ could be many causes for this, but the ones I stumbled upon were: |
... | @@ -226,8 +218,7 @@ could be many causes for this, but the ones I stumbled upon were: |
|
* wrong path or pattern for `restore_command` (double-check the path
|
|
* wrong path or pattern for `restore_command` (double-check the path
|
|
and make sure to include the right prefix, e.g. `main.WAL`)
|
|
and make sure to include the right prefix, e.g. `main.WAL`)
|
|
|
|
|
|
Direct restore procedure
|
|
## Direct restore procedure
|
|
------------------------
|
|
|
|
|
|
|
|
Reminder: this procedure **streams** the files from the backup server
|
|
Reminder: this procedure **streams** the files from the backup server
|
|
to the database server and restore directly. See above for more
|
|
to the database server and restore directly. See above for more
|
... | @@ -422,8 +413,7 @@ If you get this kind of errors, it's because you forgot to restore the |
... | @@ -422,8 +413,7 @@ If you get this kind of errors, it's because you forgot to restore the |
|
See also the "Direct restore procedure" troubleshooting section, which
|
|
See also the "Direct restore procedure" troubleshooting section, which
|
|
also applies here.
|
|
also applies here.
|
|
|
|
|
|
Monitoring warnings
|
|
## Monitoring warnings
|
|
-------------------
|
|
|
|
|
|
|
|
TODO: there's some more information about backup handling in the
|
|
TODO: there's some more information about backup handling in the
|
|
[Debian DSA documentation](https://dsa.debian.org/howto/postgres-backup/).
|
|
[Debian DSA documentation](https://dsa.debian.org/howto/postgres-backup/).
|
... | @@ -470,11 +460,9 @@ happened between 6:26 and 13:57 on that day. |
... | @@ -470,11 +460,9 @@ happened between 6:26 and 13:57 on that day. |
|
be performed. See [Running a full
|
|
be performed. See [Running a full
|
|
backup](#Running_a_full_backup) above.
|
|
backup](#Running_a_full_backup) above.
|
|
|
|
|
|
Reference
|
|
# Reference
|
|
=========
|
|
|
|
|
|
|
|
Backup design
|
|
## Design
|
|
-------------
|
|
|
|
|
|
|
|
We use upstream's [Continuous Archiving and Point-in-Time Recovery
|
|
We use upstream's [Continuous Archiving and Point-in-Time Recovery
|
|
(PITR)](https://www.postgresql.org/docs/11/continuous-archiving.html)
|
|
(PITR)](https://www.postgresql.org/docs/11/continuous-archiving.html)
|
... | @@ -529,8 +517,7 @@ Backups are checked for freshness in Nagios using the |
... | @@ -529,8 +517,7 @@ Backups are checked for freshness in Nagios using the |
|
cluster. The Nagios plugin also takes care of expiring backups when
|
|
cluster. The Nagios plugin also takes care of expiring backups when
|
|
they are healthy.
|
|
they are healthy.
|
|
|
|
|
|
Other documentation
|
|
## Other documentation
|
|
-------------------
|
|
|
|
|
|
|
|
See also:
|
|
See also:
|
|
|
|
|
... | | ... | |