2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
user3792812
  • 155
  • 1
  • 4
  • 13

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks, one more query, as you said: _There is only one WAL stream for the complete database cluster..._. I could not understood it completely. I have multiple databases and each of them is generating wal_archive which goes to different direcotries, does it mean all of them are same streams or did I misunderstood something very basic incorrectly. – user3792812 Apr 09 '19 at 11:54
  • You may have multiple *clusters* that write into different WAL archive directories. All of the WAL for all databases in one cluster will certainly be archived to the same directory. – Laurenz Albe Apr 09 '19 at 12:08