I was hoping this was about updating your version of Postgres. Whenever I do that my database doesn't work - this works with MySql.
Better Database Migrations in Postgres
61–70 of 89 posts
Re: Better Database Migrations in Postgres
#62Please correct me if I'm wrong, but aren't migrations a solved problem in databases that support triggers? * create a new schema in a table.new * install update triggers on table.old to write the same content into table.new * backfill table.new from table.old * swap table.new and table.old
and now say you have a 1TB table still want to go this route ?
Re: Better Database Migrations in Postgres
#63I really just want a deterministic (or sync-based) migration tool. The only two I'm aware of are Innovartis DBGhost and RedGate SQL Compare (though RedGate requires a license everywhere it runs, whereas DBGhost only requires a license to compile the package). This stems from my work years ago with databases in on-premise products. Customers would modify the database schema, causing migration "up" scripts to fail, and…
We used thisbat a previous job. I loved it. https://github.com/dbsteward/dbsteward
You define you're schema and it figures out what tonsonbased onbyour current schema.
Re: Better Database Migrations in Postgres
#64> Gradually backfill the default value Might I add a little warning from experience. The "gradually" part is important to get right, since in postgres updates actually create new tuples and leave dead tuples behind, if you update too much at once two things might happen: you increase disk usage fast (worth considering how much room you have to work and adjust before if needed) and increase the amount of dead tuples f…
Actually, if you don't change any keys I believe it can update in place under certain circumstances. I'll have to find a link.
Re: Better Database Migrations in Postgres
#65OT: Postgres is an incredible piece of open source software, but the official admin UI pgAdmin is in a state of complete chaos. Any suggestions for an OSS replacement?
Not OSS, but JetBrain's DataGrip is pretty great. Easily worth the money.
Re: Better Database Migrations in Postgres
#66Please correct me if I'm wrong, but aren't migrations a solved problem in databases that support triggers? * create a new schema in a table.new * install update triggers on table.old to write the same content into table.new * backfill table.new from table.old * swap table.new and table.old
and now say you have a 1TB table still want to go this route ?
Yes, the bigger the table the safer this route is for online migrations.
Re: Better Database Migrations in Postgres
#67OT: Postgres is an incredible piece of open source software, but the official admin UI pgAdmin is in a state of complete chaos. Any suggestions for an OSS replacement?
Not OSS nor free, but I've always been happy with Navicat: https://www.navicat.com/en/products/navicat-for-postgresql
Re: Better Database Migrations in Postgres
#68> Gradually backfill the default value Might I add a little warning from experience. The "gradually" part is important to get right, since in postgres updates actually create new tuples and leave dead tuples behind, if you update too much at once two things might happen: you increase disk usage fast (worth considering how much room you have to work and adjust before if needed) and increase the amount of dead tuples f…
Actually, if you don't change any keys I believe it can update in place under certain circumstances. I'll have to find a link.
You mean heap-only-tuples: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...
Re: Better Database Migrations in Postgres
#69Sqitch[0] is probably one of the best migration tools I've used. It expresses dependencies explicitly and doesn't rely on ordering. It will run change verification scripts for you and roll everything back if it doesn't check out. Plus you can tag your history and bundle everything up into a release for you. It feels a lot like using Git for managing database state.
Re: Better Database Migrations in Postgres
#70Earlier quoted context omitted.
Actually, if you don't change any keys I believe it can update in place under certain circumstances. I'll have to find a link.
You mean heap-only-tuples: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...
Yes, I was just about to post https://github.com/postgres/postgres/blob/master/src/backend... I misremembered it a little in my original post.
(I'm not able to open your link for some reason :-\)