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?

Improve this question
3
  • 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).
    – András Váczi
    Commented Sep 14, 2015 at 15:20
  • Is your concern the amount of time it takes to do the sync, or the amount of traffic it generates?
    – jjanes
    Commented Sep 14, 2015 at 17:19
  • The amount of time it takes to sync
    – user1044281
    Commented Sep 15, 2015 at 14:55

1 Answer 1

Reset to default
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.

Improve this answer

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.