I need some idea how I can deploy a financial application in a production environment using PostgreSQL with zero down time.

I have two servers in Master-Slave design. Currently I am giving downtime while deploying applications and executing database script.

During deployment, I don't want to turn off my database because of transactions. If I turn off one node for changes and second for real transactions, now both database nodes have some changes and there will be sync issue. I am looking for a solution where I can deploy DB changes and real time transactions too.

For zero downtime, do we need any configuration or any third party software or PostgreSQL utility that will handle this scenario, so that in production realtime environment we don't lose any transactions, and I also can update database script simultaneously?

"Zero downtime" means "no loss of transactions" and "no interruption of services" both. I understand it's challenging, and also possible like Google never shows any downtime. But still it's not clear how to do it.

  • PostgreSQL supports transactional DDL so in theory this is possible, but it depends on how your application locks the tables. Feb 12 '14 at 12:47
  • @Colin: Could you please elaborate more what i need to check here.
    – ajkush
    Feb 12 '14 at 13:18
  • 1
    Is zero down time necessary during all failures and during any upgrades? That is going to be pretty difficult, as even failing over you could potentially lose a few in-flight transactions. A queuing process in front of the db might be necessary to allow you to replay anything that gets dropped during failover. Also, some settings (eg. changing shared_buffers) will require bouncing the db, so unless you can pause incoming connections you are going to need a way to queue up your data.
    – bma
    Feb 18 '14 at 14:04
  • Did you achieve zero down time with postgresql database deployment? IF so please share your idea. I'm also looking for such solution. Thanks Darshan Shah Jan 1 at 6:37

Answer originally left as a question comment.

For zero downtime during tables refactoring, we create new tables along with old ones, and switch in small steps.

From Developing Low-Maintenance Databases by Alex Kuznetsov on Simple Talk.

Instead of making all the changes at once, we make multiple incremental changes, as follows:

  • Make sure that all the modules accessing the table are well covered with automated tests.
  • Create new tables. Alter all procedures that modify the old table, so that they modify both old and new tables.
  • Migrate existing data into new structure. Do it in smallish batches, so that it does not seriously impact the overall performance on the server.
  • Verify that the migration of data succeeded.
  • Redirect some of the selecting procedures from the old table to the new ones. Use automated tests to make sure that the changed modules are still correct. Make sure their performance is acceptable. Deploy the altered procedures.
  • Repeat the previous step until all the reports use the new tables.
  • Change the procedures that modify the tickets, so that they only access the new tables.
  • Archive the old table and remove it from the system.

It is true that this approach has many steps and is not very simple; yet we love it – it is far better than its alternatives. Each step incurs very little risk, and if needed, we can roll it back in a split second. We can set the migration aside and switch to another task, or enjoy our weekend, at any time – the system keeps working in any of these intermediate states for as much as needed. Our users do not experience any disruptions.

See the original article for full details.

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.