1

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.

Improve this question
0

1 Answer 1

Reset to default
6

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.

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.