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?