0

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.

Improve this question
1
  • 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).
    – András Váczi
    Commented Sep 2, 2016 at 6:53

2 Answers 2

Reset to default
1

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?

Improve this answer
2
  • Thanks for reply, development db can be read only, we are using VM server with spped of 15K.
    – Sam
    Commented Sep 5, 2016 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
    Commented Sep 5, 2016 at 6:40
-3

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.

Improve this answer
1
  • 5
    This is a wild guess, isn't it? We have some properly normalized DBs around of several TB in size.
    – András Váczi
    Commented Sep 2, 2016 at 6:49

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.