2

My database is quite large 50GB+ what I've always had to do when I want to sync my development database with production is download a backup of production, drop my development database then restore the production database on my development machine.

Is there any way of doing a (Sync/merge) where only new data is added?

  • 2
    I am not aware there is any out-of-the-box solution that gets you only the new data. You might be able to implement one, for example by using londiste. With it, you can transfer all changes to the dev DB - but there might be problems if this DB has incompatible data changes (from the development application, for example). – dezso Sep 14 '15 at 15:20
  • Is your concern the amount of time it takes to do the sync, or the amount of traffic it generates? – jjanes Sep 14 '15 at 17:19
  • The amount of time it takes to sync – user1044281 Sep 15 '15 at 14:55
1

What I do is keep two copies of production on my test machine. At 50GB, that should be pretty doable. When I want a fresh copy of production, all I have to do is:

pg_ctl stop -D $WHERE/dev -mi
rm $WHERE/dev/* -r
mv $WHERE/pre_stage/* $WHERE/dev
cp $WHERE/recovery.sample $WHERE/dev/recovery.conf
pg_ctl start -D $WHERE/dev
tar xf /mnt/something/latest/base.tgz -C $WHERE/pre_stage &

It takes anywhere from a few seconds to a few minutes, depending on how much WAL log has been generated since the last base backup was taken. The last step of replenishing the pre_stage takes longer, but it doesn't matter since I don't have to sit around and wait for it to finish.

Of course this requires you to be using log archiving on the production machine. I would be doing that anyway, though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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