postgresqlpostgresql-10pitr

PostgreSQL: point-in-time recovery for individual database and not whole cluster


As per standard Postgres documentation

As with the plain file-system-backup technique, this method can only support restoration of an entire database cluster, not a subset.

From this, I understood that it is not possible to setup PITR for individual databases in a cluster (a.k.a. a database instance holding multiple databases). If my understanding is incorrect, probably the next part of the question is not relevant, but if not, here it is:

I still do not get the problem in setting this up theoretically as each database is generating its own WAL archive.

The problem here is: I am in need of setting up multiple Postgres clusters and somehow I have only 2 RHEL 7.6 machines to handle this. I am trying to reduce the number of clusters on these 2 machines to only 2. I am planning to create multiple database rather than multiple instances to handle customer applications. But that means that I have to sacrifice PITS, as PITR only can be performed on the instance/cluster level and not on the database level (as per the official documentation).

Could someone please help clarifying my misunderstanding.


Solution

  • You are correct, you can only do PITR on a PostgreSQL database cluster, not on an individual database.

    There is only one WAL stream for the complete database cluster; WAL is not split up per database.

    Don't hesitate to run several PostgreSQL clusters on a single machine if that is advantageous for you.

    There is little overhead in running a second database cluster. The biggest resource that is hogged by a cluster is shared buffers, but you want that to be only a fraction of the available RAM anyway. Most of the memory should be left to the filesystem cache that is shared by all PostgreSQL clusters.