Is it possible in PostgreSQL to have some DBs under PITR stragtegy while other DBs are not?

Question

I have a PostgreSQL server that contains a number of databases. The number varies over time between 20 to 30.

Some of these databases contain real production information for what we want to have the best protection possible. So I activated PITR (wal log backup) for them.

But some others are just containing immutable information or information that can be reconstructed with not so much cost. For these ones we don't want to have backups at all or something like one pg_dump a day must be enough.

The issue I have is that after activating PITR Postgres is saving the logs for all the databases. And some of the second category DB's have really big modification rate, leading to huge space taken by logs.

I will like to have PITR active for only some tablespaces (for example). This will allow me to place the DBs in the right tablespace for having the level of protection that we need.

But I have been unable to find a way to achieve that.

Any experience on that setup?

Thanks!

Answer 1

Postgresql WAL and recovery process are instance-wide. Strictly speaking, the ability to make a PITR is only a small consequence of the need to ensure ACID durability purposes. Even without configuring a WAL archive, postgresql will track all changes in that instance through WAL. If you do not want to write some changes to WAL, you will not be able to make not only PITR, but also can not start after, for example, a short-term power outage.

You could split the current cluster into two independent ones. Let's say set up a second one running on a different port.

---

You may probably prefer to get some benefits of using unlogged tables. Changes to unlogged tables are not logged to WAL. But be aware that postgresql will clear all data in such tables on any unexpected restart. Without complex conditions: if we do not start from a "clean shutdown" state, we truncate all unlogged tables.