I have a production server with static (no deletes/updates) PostgreSQL database, serving a huge amount of read queries.
Once a week or two I have to update my database with new data. Right now the process it pretty complicated, so I'm using a separate server to build new database from the scratch.
So, at some point I have live production server with outdated data and up to date database on some secondary server.
First I've tried backup/restore approach, but it resulted in 5 minutes downtime, which is not acceptable.
Then I've tried adding
--single-transaction flag to
pg_restore call, but it resulted in 2 minuted downtime, because all requests to PostgreSQL hanged for a couple of minutes after transaction was committed.
The best solution I came up with so far is to copy new database to production server under some new name and then switch my application to it.
But I wonder if there's any better solution, which will allow me to ship new data to production server without restarting my application.