Postgresql – Postgres database zero down time production deployment

Architecturedeploymenthigh-availabilitypostgresql

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.

Best Answer

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.

Related Question