Postgresql – Migrate schema updates onto existing schema

postgresql

So we have three development environments from a programming point of view.
Each environment has its own postgres database.

Let's say a table gets an extra column in our development environment, we need to update the acceptance environment as well when the code is merged back. Other changes can be column renames etc.

This is needed because we are still developing the app quite heavily, but it is still important that the production environment can keep up on a database level as well.

So the question is: how can I propagate schema updates to a different database without loss of data.

Best Answer

I suggest that you use a tool such as Liquibase or Flyway which is designed to manage such schema changes in a database and keep the configurations consistent and known.

http://www.liquibase.org/

https://flywaydb.org/

There are other tools available and they usually have plugins to maven, Puppet & Chef etc...it'll come down to a google search and personal preference.

Related Solutions

Postgresql – How to optimization database for heavy I/O from updates (software and hardware)

If doing a hardware upgrade, is the number of disks key to performance?

Yes, as a hard disc - even SAS - has a head that takes time to move.

Want a HUGH upgrade?

Kill the SAS discs, go to SATA. Plug in SATA SSD - enterprise level, like the Samsung 843T.

Result? You can do around 60.000 (that is 60 thousand) IOPS per drive.

This is the reason SSD are killers in DB space and so much cheaper than any SAS drive. Phyiscal spinning disc just can not keep up with the IOPS capabilities of discs.

Your SAS discs were a mediocre choise to start with (too large to get a lot of IOPS) For a higher use database (more smaller discs would mean a lot more IOPS), but at the end SSD are the game changer here.

Regarding software / kernel. Any decent database will do a lot of IOPS and flush the buffers. THe log file needs to be WRITTEN for basic ACID conditions to be guaranteed. The only kernel tunes you could do would invalidate your transactional integrity - partially you CAN get away with that. The Raid controller in write back mode will do that - confirm the write as flushed even if it is not - but it can do so because the BBU is assumed to safe the day when power fails. Anything you do higher up in the kernel - better know you can live with the negative side effects.

At the end, databases need IOPS, and you may be surprised to see how tiny your setup is compared to some others here. I have seen databaes with 100+ discs just to get the IOPS they needed. But really, today, you buy SSD and go for size on them - they are so superior in IOPS capabilities, it makes no sense to fight this game with SAS drives.

And yes, your IOPS numbers do not look bad for the hardware. Within what I would expect.

PostgreSQL – How to Use Column with Hash Based on Other Columns as Unique Index

You could use a UNIQUE INDEX instead of the UNIQUE CONSTRAINT using the function coalesce to treat null as a regular value for your uniqueness:

CREATE UNIQUE INDEX ON offer (
       product_id,
       coalesce(price_old,-1),
       coalesce(price,-1),
       coalesce(price_alt,''),
       valid_from,
       valid_to);

This will enforce your uniqueness as you described.

For details between a Unique constraint and a unique index,see: https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index

Best Answer

Related Solutions

Postgresql – How to optimization database for heavy I/O from updates (software and hardware)

PostgreSQL – How to Use Column with Hash Based on Other Columns as Unique Index

Related Question