Hacker News Reader

Comment by netcraft | Hacker News Reader

There are two things I would love to have in postgres -

1. The ability to set a point in time to roll back to, revert everything back to that point. Would be useful in lots of development scenarios

2. The ability to set a point in time and then allow me to make changes however - direct sql, using an attached application, whatever - and then get the DDL/DDM necessary to take it from the snapshot to the current state. I would love to be able to take a seed database, hook it up to an app and use the UI to set up scenarios that I could then use in my unit tests. This isn't bad in isolated cases, but when setting up a scenario takes coordination of tens of tables, this would be much easier. I could imagine it being useful in a lot of other scenarios as well.

I'm not sure if you could do #2 with something on top of logical replication - ive started to look at that in the past but I would really like something that outputs SQL.

View on HN

About #1: I don't think you're going to see it again: https://www.postgresql.org/docs/6.3/c0503.htm

The closest is this, but maybe not helpful in your scenario: https://news.ycombinator.com/item?id=26748096

I think a practical alternative is to CREATE DATABASE .. TEMPLATE foo. Maybe it could be boosted up by supporting reflink copies many filesystems nowadays support. In fact reflink in context of PostgreSQL has been discussed earlier, I wonder whatever happened to it: https://www.postgresql-archive.org/file-cloning-in-pg-upgrad...

Full disclosure way up front: I'm one of the developers working on https://spawn.cc at Redgate and the two scenarios you describe are some examples of exactly what we've built Spawn for.

Not trying to sell you anything (since we are still in beta anyway!) but I'd encourage you to try it out and see if it does what you're looking for. We're in the early stages so any and all feedback is really helpful!

It's a hosted service where you can immediately spin up copies of "data images" (a snapshot of schema and data in a database instance) instantly regardless of the size. As an example, we created a copy of the 400GB public stack overflow DB for development in ~15s.

You can also snapshot the state of the DB at any time and move around those save points whenever you like. Creating a "data image" for later copies is also possible based off the current state of the database, so you can curate data through your web UI, save the state, and then make that dataset available for multiple copies across your dev team.

We've got a couple of repos to show it in action:

- https://github.com/red-gate/spawn-demo

- https://github.com/red-gate/flyway-spawn-demo/actions

As well as a youtube video: https://www.youtube.com/watch?v=sxHtca85CoE

Your first point is already there ;) it‘s called savepoints and works exactly like nested transactions.

Unfortunately savepoints only work for a non-transactional single connection test. E.g. you can only set a savepoint within a transaction. If the code under test, like an integration test, uses transactions itself, or multiple connections, savepoints won't help you. I would love to be able to globally revert all committed changes to a point in time.

  SUPER SAVEPOINT A;
  <NO HOLDS BARRED CODE USING MULTIPLE CONNECTIONS, TRANSACTIONS, NORMAL SAVEPOINTS ETC>
  SUPER RESTORE A;

It could be implemented by playing the WAL logs backwards, I imagine.

Alternatively, just making CREATE DATABASE WITH TEMPLATE fast would be nice. Even on tmpfs and no fsync this operation takes about 300 ms on my (fast) workstation. Then you could consider the template database a savepoint in itself.

Reply on news.ycombinator.com