Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Feel free to edit the subject or post to improve clarity.
Thanks.

share|improve this question

migrated from stackoverflow.com Aug 24 '13 at 21:50

This question came from our site for professional and enthusiast programmers.

1  
You are looking for log shipping replication: postgresql.org/docs/current/static/warm-standby.html –  a_horse_with_no_name Aug 24 '13 at 21:53

2 Answers 2

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.

share|improve this answer
    
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? –  Lo Sauer Aug 29 '13 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. –  Craig Ringer Aug 29 '13 at 23:15

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.

Full disclosure: I work at Delphix

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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