I've got a Postgres 9.2 database (production) which is almost 90 GB, I need to restore production database to development postgres server every day, what I think is to schedule a backup an restore script as cron job on linux, but the problem is it won't work in long term, as the database is growing too fast, so base backup and restore every night would take longer and longer as the size of database is increasing.

Is there a better way to refresh the development database on daily basis? for example using log shipping or something which only update the development database with delta changes.

PS: for some security reason, I can't use replication during business hours.

  • 1
    Are you taking backups regardless of the dev box? If so (you should), you might want to use two dev boxes, swapping them daily. So you always have one up even during the time of the other being restored from the fresher backup. FYI, our biggest DB (8+ TB, measured on disk) needs around 4-5 hours to take a backup from (using pg_dump). – dezso Sep 2 '16 at 6:53

If you're ok with the development db being read only you could use replication using wal shipping. Instead of continuously shipping, do it on a daily basis.

See: 25.2. Log-Shipping Standby Servers

If you need it in write mode it makes things a bit more complex as you'll need to keep the prod copy in read and clone it. If your storage subsystem support snapshots than it's trivial.

Btw, 90gb should clone pretty fast - how long does it currently take? What disks are you using?

  • Thanks for reply, development db can be read only, we are using VM server with spped of 15K. – Sam Sep 5 '16 at 2:42
  • Then, log-shipping should be good for you. Note that you should probably ship the logs anyway (for backup proposes ) just ship them to dev as well. – cohenjo Sep 5 '16 at 6:40

90 gb, sounds like the db design is not 3rd normal form relational, or has a lot of blobs which might be better off as files, if you've got a growing db problem like this your bank account must be also growing, maybe invest in some design optimisations.

To mitigate short term you should try dumps by table, one at a time, spread out cron jobs by table by the time necessary.

  • 5
    This is a wild guess, isn't it? We have some properly normalized DBs around of several TB in size. – dezso Sep 2 '16 at 6:49

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.