PostgreSQL backups (ocp13, ocp24)

[ghost]

Linode provides file backups which is fine for site files however this is not ideal for Postgres.
During the backup Postgres will still have data loaded into memory so we cannot ensure the quality of a backup using this approach.

We should use pgBackRest to store backups in AWS S3, as we have done on other servers
https://ocdsdeploy.readthedocs.io/en/latest/develop/update/postgres.html#set-up-backups

pgBackRest has the added benefit that it is offsite and recoverable without Linode.

---

Other non-development servers with postgres but without pgbackrest:

• Credere (ocp24)
• Registry (ocp13) (see also Registry: Read-only backup server #289)
  • Only the data_registry and spoonbill_web databases. (kingfisher_process, pelican_backend and pelican_frontend have transient data only.)

[RobHooper]

Credere production backups setup:
a86c0d5

[jpmckinney]

Just curious how the value max_wal_senders = 4 is decided?

[RobHooper]

@jpmckinney , max_wal_senders limits the number of concurrent connections streaming WAL data, usually this is to configure database replication but it is also used for our backups.
Theoretically we should only need 1 concurrent connection but most sources recommend 3. 3 allows admins to add replication easily in the future if needed, it also leaves room if a connections hang.

I set 4 for Credere as an educated estimate being more limited than the default value (10) but happy to reduce.

[jpmckinney]

Cool, was just wondering as kingfisher-main was set to 5, but it seems like the rule is at least 3, but less than 10.