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.

  • Does 'new data' mean INSERTs only? If not, some sort of logical replication may help you - it won't make the process much simpler, though. – dezso Nov 25 '15 at 10:18
  • Does this complex procedure ("update my database with new data") have only inserts? – ypercubeᵀᴹ Nov 25 '15 at 10:21
  • No, it's not only inserts. Otherwise, there wouldn't be any problem pushing it to production database. – Leonid Beschastny Nov 25 '15 at 10:25
  • The canonical solution is 1.start transaction 2. DML. 3.commit transaction, while the readers keep reading. If you know already why that wouldn't work in your case, please add it to your question. – Daniel Vérité Dec 1 '15 at 16:20
  • @DanielVérité yes, it should work. For some reason I thought that pg_restore does it by default. But now I realize that I just missed --single-transaction flag. – Leonid Beschastny Dec 1 '15 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.