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.
Best Answer
There isn't really anything like this.
You can use
pg_basebackuporpg_dumpto 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.