Can we do PITR of one specific PostgreSQL database

All we need is an easy explanation of the problem, so here it is.

I am new to PostgreSQL, so please ignore if my question sounds stupid.

Imagine we have three databases in PostgreSQL and someone mistakenly ran a TRUNCATE command an hour ago on one of the databases. Now we want to do a PITR for that database alone, since there is an issue with only one database.

Is it possible in PostgreSQL to do PITR for one specific database OR it can happen only at instance level?

I read the documentation for Continuous Archiving and Point-in-Time Recovery (PITR). It looks like it can only be done at instance level (I guess we say instance as "Database Cluster" in PostgreSQL world, I come from SQL Server world).

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

If PITR is not possible at DB level, then how Prod DBAs are handling this situation?

Also one last thing, I have a test server with multiple DBs and if PITR is possible at DB level, then please share me the steps so that I can test it myself.

I am mainly interested in supported versions of Postgres. My test server is PostgreSQL 9.6.16.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Restore your backup on another server. Perform a point-in-time recovery. Export the data / tables you’re interested in and import them into your production database.

This is generally how it’s done. You don’t want to lose all the other data but cherry-pick the data you need from a restore on another server.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply