Thread: [GENERAL] DATA Integrity & Recovery

On Wed, 12 Jul 2017, chris faber wrote:

> I would appreciate the communities help in the following:
>
> 1. Determine if data from the incremental backups can be restored or
> recovered.
> 2. Determine if data can be recovered from individual files backed up from
> main Postgres data directory.

Chris,

   I am very far from being a postgres admin expert, but my understanding
from years ago is that general system backups (and I use dirvish every day
for incremental backups of all partitions) are different from
postgres-specific backups using pg_dump and pg_dumpall. I never looked into
the differences or why they mattered, but each week I run pg_dumpall and
save the .sql file in a database_backup/ directory.

   This does not answer your specific questions but does offer a strategy for
future operations.

HTH,

Rich

chris faber wrote:
> Postgres 9.2
> 
> We have a POSTGRES database that we have been backing up via Incremental backups.

You are talking of a physical base backup and WAL archives, right?

> We had an incident where we had to recover from backup. Our software vendor has completed
> a restore and we have lost 10 days of data. There is no explanation as to the reason we
> have sustained this loss.

Then press your software wendor for a reason.
They did the restore, so they should know.

> I would appreciate the communities help in the following:
> 
> 1. Determine if data from the incremental backups can be restored or recovered.

If properly done, you can recover to any point of time after the backup with
a base backup and WAL archives.

> 2. Determine if data can be recovered from individual files backed up from main Postgres
> data directory.

That is more tricky.  There is no straightforward way to extract such
information, particularly if the commit log is missing.
If that file is from a base backup, there is the additional difficulty
that the file could be in an inconsistent state.

You would have to hire a specialist for such work.


It sounds like you should consider letting somebody more reliable than
your software vendor manage your database backups.

Yours,
Laurenz Albe