7

Virtual machine software like VirtualBox allow one to make incremental VM clones. That is data, once "touched" (opened writable), will be copied and stored in the incremental cache of the new clone.

I am searching for the same option for a Postgres DBMS.

How can I setup an incremental database copy, where entries are read from the original database, and touched/modified rows from the local copy?

If not on the DBMS level, how can I emulate such behavior at the file-system/storage level using a separate DBMS instance?

Background: The idea is to utilize the powerful database-server yet without incurring much resource overhead for a staged/developer database-copy.

1

2 Answers 2

6

There isn't really anything like this.

You can use pg_basebackup or pg_dump to copy the original DB, but there's no copy-on-write facility. The built-in replication requires a shared-nothing copy and the replicas are read-only.

You can use 3rd party replication options like Londiste or Slony-I to replicate the state of the master to the dev machines, but these systems are a bit fragile, don't deal with DDL replication, and can have a significant impact the master server's performance.

Future PostgreSQL versions will hopefully have log-streaming logical replication which would be closer to what you want. It exists as a patch to the source code now, but at time of writing (Pg 9.3 release) it's in alpha stage development where it's validated for a couple of uses but not for general use.

I'd probably go a layer or two down. I'd use file-system copy-on-write snapshots, or even copy-on-write virtual machine images, to achieve what you want.

2
  • Thanks. I also found this answer of yours useful: stackoverflow.com/questions/17069163/… . What do you think of triggers to mirror all CUD operations onto another database or copied-tables? Aug 29, 2013 at 17:34
  • 1
    @LoSauer That's exactly what Londiste and Slony-I do. It's harder than it looks, don't try to write it yourself. See wiki.postgresql.org/wiki/… for options. Non-trivial load is added to the master, the replicas need to be always-on, and DDL changes are a pain. Aug 29, 2013 at 23:15
3

Delphix virtualizes Postgres as well as Oracle and SQL Server. Delphix is software that runs as a VM. Delphix connects to a source database, collects what is called a "timeflow." A timeflow is is collection of data from the source database allowing Delphix to spin up clones on a target machine anywhere with in a time window covered by timeflow. Timeflow is by default two weeks. Data older than the two weeks that is not being used is purged. Delphix looks like a NAS to the target databases, the clones. New clones just point to existing datablocks on Delphix so new clones take up no new space and can be created in minutes. The clones can be refreshed from the source in minutes, branch to create data control which is to data what source control is to code, and can be rolled back to allow rerunning destructive QA tests over and over.

You can download a free version of Delphix here http://datavirtualizer.com/delphix-express-installation/

Full disclosure: I work at Delphix

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.