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?