0

I'm developing a system which, relevant to this question, consists of PostgreSQL database tables.

When I first created the SQL code to CREATE TABLE, I did think it through. However, over the years, I've thought of improvements, fixed bugs, and implemented new functionality. Each time I do this, on the "live" table containing my data, I then make sure to make the appropriate change to the code file, so that (at least in theory) I could run it on a different computer or fresh PostgreSQL installation and get an identical "structure".

However, as we all are painfully aware of, it's impossible to change the column order. So already that is a change between my live table and the code. There's also the possibility that I simply forget to change it after fiddling with some problem for hours.

Either way, it's not a good feeling that my table creation code and my live tables are "slightly different", if only in the column order and some of the identifiers for constraints which I never refer to in my code, and thus "don't matter". But it's not a nice feeling.

I can't just nuke the tables and re-run my code to recreate the structure, because then I lose all the data in them!

Is there any reasonable way for me to "sync" my code and the live table structure while retaining the data, in a safe and problem-free manner?

2
  • 2
    For one: the column order in a table has absolutely no meaning. If you have a problem with that, it's a bug in your code that you need to fix. – a_horse_with_no_name Feb 19 at 14:39
  • All these problems are addressed by using tools known as "schema migration tools". In a nutshell: never run DDL "ad-hoc" in any environment. Create a script that does the change. Store it with a system that makes it clear from which version to which version this will migrate the data model. Tools like Liquibase, Sqitch or Flyway were created exactly for that. – a_horse_with_no_name Feb 19 at 14:41
0

There are times where someone wants to write code and not specify each individual column. If I write a script to split out one customers data to another database, not specifying the column names means that I don't need to change the script every time I add a new column. As long as the column order is constant. If there is one table that you want to change the column order, you can drop the constraints and indexes, rename the table, create the table again without the indexes and constraints, insert the data by specifying column names and create the indexes and constraints. It is a lot of work and requires downtime. But it would allow you to change the column order. Once you do that, you should update the tables using a tool like Flyway or ant where you can run code against each database as a release to make sure that the changes happen in the same way.

1
  • "There are times where someone wants to write code and not specify each individual column." Again, column order in a relational model is totally irrelevant. No application should depend on the ordering of columns, and if one does, then it's a problem that you need to fix. – Albert Godfrind Feb 20 at 18:37
0

What you are basically looking for is a reverse engineering tool for Postgress. For the database products I worked with, the maker provided a reverse engineering tool that could extract the metadata stored in the database itself and transform that back into a database create script, written in SQL. I don't know Postgress, but I expect there to be such a tool in the product package. If not, there are third party products you can buy that will do this. A quick Google search on Postgress reverse engineering showed dozens of hits. A good Postgress DBA should be able to recommend one. Some of those third party tools can also make a huge diagram that depicts the database as well.

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.