OT: 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?
Better Database Migrations in Postgres
21–30 of 89 posts
Re: Better Database Migrations in Postgres
#22What's everyone favorite library for doing Postgres migrations using node? I'm using knex.js and still doing migrations mostly by hand.
I use TypeORM -- supports migrations, though not to the same power as whats discussed in the OP.
Re: Better Database Migrations in Postgres
#23OT: 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?
I have taken to using DBeaver. The overall experience is really nice.
I don't like that you have to toggle your "Active database" rather than just opening a new window with a new connection to a different database. If that annoys me much more then I plan to try SquirrelSQL and then retry Postage.
I never understood the hate that pgAdmin3 received, I liked it a lot. V4 though is a mess.
Re: Better Database Migrations in Postgres
#24What's everyone favorite library for doing Postgres migrations using node? I'm using knex.js and still doing migrations mostly by hand.
We have written our own migration workflow on top of pg-promise because all the alternatives didn't work out for us. We don't use an ORM because of several reasons and we favor SQL over SQL-abstractions. We use Bookshelf/knex in a quite large project and we had some trouble with the latter so that we decided to go with "raw" pg-promise.
So what we basically have is a script that runs migration SQL files in order within a transaction and then uses COPY to seed the database from a folder of CSV files. Triggered by a shell script which itself can be run via yarn/npm.
Re: Better Database Migrations in Postgres
#25I 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 it would be very difficult (and manually intensive) to baseline everything again and get the database back to a working state. Even though modifying the schema was clearly laid out as "not supported", it would still be something we'd have to fix, because ultimately they (and their account reps, etc) still need the product to work.
We used DBGhost, and then had custom pre- and post-deployment scripts to do certain types of changes, such as renaming a column (`if exists old_column { add new column; copy old to new; drop old_column; }`), or adding expensive indexes. One of the best parts is all it stores in source control is all the CREATE scripts for database objects (and the custom pre/post deployment scripts). Pull requests would let you trivially see a new column or index being added.
Compared to the pain of creating and maintaining up/down scripts and the long-term problem where deploying a new instance takes thousands of migration steps (or risking the inital CREATE scripts not matching what happens after all migrations), doing a schema sync was significantly simpler in nearly every respect.
I've been looking for something similar for Postgres and MySQL/MariaDB without any luck, and it really surprises me there's not more interest in doing migrations this way.
Re: Better Database Migrations in Postgres
#26OT: 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?
I have taken to using DBeaver. The overall experience is really nice. I don't like that you have to toggle your "Active database" rather than just opening a new window with a new connection to a different database. If that annoys me much more then I plan to try SquirrelSQL and then retry Postage. I never understood the hate that pgAdmin3 received, I liked it a lot. V4 though is a mess.
DBeaver is really excellent for so many things.
I use it to organize my scripts which I need regularly. I can also put them on a network/shared drive so that others can access it.
I don't know if pgAdmin allows it, but in DBeaver, I can switch between Grid and Text view to copy paste data into email in a nicely tabulated format, besides of course, being able to export a result set out into CSV etc.
Re: Better Database Migrations in Postgres
#27OT: 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?
Although i stick to bash tools as pg_ctl and co, one of my colleagues uses Postico [https://eggerapps.at/postico/] and it seems to do the job pretty well.
And yes they made such a mess with this UI.. looks like they tried to rebuild an Os inside themselves, with that painful windows management, notification popups etc.. crazy shit happens
Re: Better Database Migrations in Postgres
#28Earlier quoted context omitted.
I like the whole sqitch approach much better than that provided by most migrations (if I'm in migration land, I use https://flywaydb.org/ ). From the version control perspective you get the sprawl of changes across files like you do with migrations. I find that, for tables, if I write anonymous "DO" blocks in a single script to manage both the initial creation of the table and any later deltas, I get a very satisfact…
Another options is liquibase[0]. I use the diff[1] tool to create a changeset and then convert it to sql before applying it to my db. liquibase keeps a log and lock table in your db so you can always review the changeset you applied to the db at a later time. [0] http://www.liquibase.org/ [1] http://www.liquibase.org/documentation/diff.html
Liquibase is definitifely a good recommendation. We use it to update Oracle, postgreSQL, MariaDB as well as exporting the current schema as a hsqldb-script. Some migrations are only executed for some of our customers.
Re: Better Database Migrations in Postgres
#29OT: 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?
I have taken to using DBeaver. The overall experience is really nice. I don't like that you have to toggle your "Active database" rather than just opening a new window with a new connection to a different database. If that annoys me much more then I plan to try SquirrelSQL and then retry Postage. I never understood the hate that pgAdmin3 received, I liked it a lot. V4 though is a mess.
Yeah, I liked pgadmin3 but don't like pgadmin4. I like a GUI for traversing and understanding table structure, without having to do \d table. I use cli for everything else.
Will give dbeaver a try.
Re: Better Database Migrations in Postgres
#30I 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…
Sql Server with Management Studio can do something like it with the dacpak format. It calculates differences and what needs to be done.