Show HN: Safe Data Changes in PostgreSQL

Re:Show HN: Safe Data Changes in PostgreSQL by pawelduda on Thursday March 09, 2023 @01:54PM (#35085066)

I see this doesn't yet support something I've wanted for some time:

What's the simplest way to snapshot a database, perform an operation in app, snapshot it again and get an overview of how many rows were affected in each table? Like an overview of diff. Optionally list changed pkey ids. I tried googling but couldn't find anything like this, so I was thinking of making a DYI solution but wasn't that desperate yet.

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by ciminelli on Thursday March 09, 2023 @02:43PM (#35085650)

We were looking at tooling for snapshotting production data for testing purposes, that use case is interesting to have the diff view based on changes happening from application actions. Would you use it for testing changes or more for debugging production issues?

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by pawelduda on Thursday March 09, 2023 @03:36PM (#35086318)

Well, my main use case would be to speed up project onboarding where I can play around with the app from user perspective and checking how my actions impact database... Could help cutting through a lot of frontend/backend layers...

If you have multiple DB snapshots and WAL enabled, theoretically you could inspect the log to see what happened inbetween. There's pg_xlogdump for that but I think it will output very raw data...

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by ciminelli on Friday March 10, 2023 @12:47PM (#35098073)

How would you want to actually look at the database diff? Just a summary view of rows that were changed given a certain time period?

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by PaulMest on Thursday March 09, 2023 @02:50PM (#35085725)

I'd guess that https://neon.tech/ could help with this. Neon is branded as "Serverless Postgres". They have APIs to create branches of your database.

So you could effectively:

1) Create a snapshot of your production DB -> DB_2A

2) Then create a snapshot of that snapshot -> DB_2B

3) Now you have two copies of the exact same database. Run your query/workload/migration on DB_2B.

4) Run some metadata queries against DB_2A and DB_2B and compare the results.

5) If your metadata queries are inline with expectations, delete the snapshots. If not, leave them around for a bit for manual inspection.

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by pawelduda on Thursday March 09, 2023 @03:42PM (#35086385)

Interesting. Will check it out in detail. From a quick glance tho I'm afraid that vendor-specific cloud is a no-go because I would rather not upload any client's data in there, even if anonymized. Besides, it's an overkill to convince anyone to switch to a different vendor for sake of diffing DB alone :)

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by krembo on Thursday March 09, 2023 @04:29PM (#35086989)

Why not use aws rds snapshots to create a new replica in no-time?

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by klysm on Thursday March 09, 2023 @11:09PM (#35090261)

I wonder if you could utilize templates to snapshot the DB.

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by pawelduda on Friday March 10, 2023 @09:11AM (#35094469)

This is what tools like `dslr` do and it's very fast. My problem wasn't backing up/restoring though but rather diffing data in these 2 databases :)

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by klysm on Tuesday March 14, 2023 @01:22AM (#35147723)

It seems like it should be doable to write something that generically diffs tables.

[ Parent | on HN ]

Re:Show HN: Safe Data Changes in PostgreSQL by pbronez on Friday March 10, 2023 @05:39PM (#35102258)

Dolthub does that https://www.dolthub.com/

[ Parent | on HN ]