Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Better Database Migrations in Postgres

craigkerstiens.com

Post reply on HN
Print view View original

81–89 of 89 posts

Re: Better Database Migrations in Postgres

#81

Post by jimktrains2 »

jimktrains2 wrote: post #77
Mouse47 wrote: post #75

Earlier quoted context omitted.

Can it handle stored procs / UDFs? Was unable to find this in the docs...

Yes. I know I've done it for triggers, let me see if I can find that project.

https://github.com/dbsteward/dbsteward/blob/master/xml/somea... is an example with a function and trigger.

Re: Better Database Migrations in Postgres

#82

Post by redler »

dt_rsp wrote: post #23
danmaz74 wrote: post #21

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?

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.

I've used Aqua Data Studio for ages. Very solid, many features. But DBeaver looks interesting (especially considering the cost of ADS).

Re: Better Database Migrations in Postgres

#83

Post by notyourday »

keithlfrost wrote: post #71
vog wrote: post #36

Earlier quoted context omitted.

(We should coin a term for this. I propose: "idempotent database updates".) I'm also a strong proponent of idempotent database updates, and prefer those over classic migrations wherever possible. Some experience from PostgreSQL (with several years of experience in various applications): While this approach works pretty well for idempotent changes such as "add column if not exists", it is more tricky when data content…

One caveat regarding "Always put each migration into a database transaction": I've found that for very large database tables on a live system, it becomes impractical to e.g. create a new index inside a transaction, because the entire table would need to be locked for the duration of the operation.

That's because this entire approach is broken: if one cannot afford to bring database down to do alter table migration, one should always go a lazy migration either via application stack using write-to-new only, read from new on miss, read from old, followed by a backfill or using triggers.

Re: Better Database Migrations in Postgres

#84

Post by zmoazeni »

notyourday wrote: post #60

Please 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

No. https://githubengineering.com/gh-ost-github-s-online-migrati... has an excellent section on why triggers fail (at least in the MySQL world. I imagine the locking story is similar in postgresql).

I don't work for GitHub but we had the same problems using pt-online-schema-change independently of their issues (lots of locking contention affecting the app negatively). We're finally moving to gh-ost for our large/risky migrations and so far it's amazing.

Re: Better Database Migrations in Postgres

#85

Post by benmanns »

tclancy wrote: post #78
icebraining wrote: post #44

Earlier quoted context omitted.

What do you mean?

I think they're referring to the last item in the list at https://github.com/ankane/strong_migrations#dangerous-operat... which was linked from the article.

It looks like that can affect `SELECT DISTINCT` queries. From https://github.com/ankane/strong_migrations#adding-a-json-co...

Re: Better Database Migrations in Postgres

#87

Post by ryanmt »

danmaz74 wrote: post #21

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?

Not sure if this fits your needs, but I've really liked pgweb. https://github.com/sosedoff/pgweb GitHub - sosedoff/pgweb: Cross-platform client for PostgreSQL ...

Re: Better Database Migrations in Postgres

#88

Post by garyclarke27 »

danmaz74 wrote: post #21

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?

All of the GUI tools I've tried such as Postico, pgadmin etc have very poor flaky crash prone query editors. So I use Sublime Text, it has Postgres specific SQL syntax highlighter, powerful search and replace including Regex. Blazingly fast, Rock solid, Runs any query, can even handle result sets of millions of rows that would make most editors crash. Postico is pretty, but last time I tried it, it didn't even show the line number of the code causing an error, so was completely useless to me.
Post reply on HN