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.
Best Answer
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?