I make full backups of my PG databases each day. I store these pg_dumped archives/blobs in a dir on the same computer, as well as on offline disks, up to a year before they are culled.

I'm constantly worried that my "live" data is being either deleted or modified by accident or through malice. It would be extremely useful to be able to do something like:

pg_compare.exe "path to pg_dump of my database X" "Database X" --deletes --updates

And then it would return a JSON array of any records found in the dump but not in the live database, as well as any changes in existing data found between the backup and the live database. (It would not care about new INSERTed rows, of course.)

Basically (translated to human form):

1. The row in table "invoices" with "id" 4825 has changed its numeric "amount" column cell from "49" to "499".
2. The row in table "bookkeeping" with "id" 13459 is no longer present.
3. ...

At the very least, this would help against honest mistakes and severely help against my paranoia and fear. As I'm working in pgAdmin 4, for example, I'm always worried that maybe I had selected a row out of sight or something as I press that "trash can" icon and then the "accept changes" icon. I don't want to lose data.

It's important that this can be done in an automated fashion, constantly behind the scenes (or maybe once a day if it's resource-demanding). It must not involve manual creating of databases and a bunch of complex custom SQL queries etc. And please, no extensions! I hate extensions.

Is there such an ultra-useful feature built into PG?

  • No, there is not such a feature.
    – mustaccio
    Jan 16 '21 at 15:59
  • What you need is an auditing solution. You should have this built-in to the database rather than comparing dumps to do this. Jan 21 '21 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.