PostgreSQL, I wish [LWN.net]

PostgreSQL, I wish

Posted Aug 21, 2012 1:27 UTC (Tue) by Cyberax (✭ supporter ✭, #52523)
In reply to: PostgreSQL, I wish by khim
Parent article: MariaDB: Disappearing test cases or did another part of MySQL just become closed source?

>PostgreSQL requires dump/restore cycle. Not acceptable.
Not true. PostgreSQL now has live migration feature - you can migrate between _major_ _releases_ live, without interrupting service at all.

(Log in to post comments)

PostgreSQL, I wish

Posted Aug 21, 2012 9:46 UTC (Tue) by andresfreund (subscriber, #69562) [Link]

Which features are you referring to? Neither Hot-Standby+Streaming Rep, not pg_upgrade really give you that.
You can stitch something together if you add something like londiste or slony to the mix, but thats rather complicated to get right.

Getting really seamless migration is still very, very hard.

PostgreSQL, I wish

Posted Aug 21, 2012 14:46 UTC (Tue) by Cyberax (✭ supporter ✭, #52523) [Link]

You need to set up streaming replication (yes, it works across Postgres versions) slave, upgrade it (in-place upgrades are fast), let it catch up with the master and then switch over to it. The switchover procedure is the most complicated step here.

PostgreSQL, I wish

Posted Aug 21, 2012 15:27 UTC (Tue) by andresfreund (subscriber, #69562) [Link]

> You need to set up streaming replication (yes, it works across Postgres versions) slave
Unfortunately no. Streaming Rep doesn't work across versions. Streaming rep requires that the wal version number/magic, the catalog version number and several other identifying factors are the same between both ends.

SR/HS work by replaying the wal from the primary on the standby. Unfortunately its format changed in at least each of the last 5 releases. Even 9.3 - with 9.2 being in beta atm - already has significant changes to the format.

> upgrade it (in-place upgrades are fast), let it catch up with the master
And unfortunately, while pg_upgrade upgrades are way much faster than the traditional pg_dump/pg_restore, they aren't that fast because they require the planner statistics to be rebuilt. A whole database analyze can take a bit on a larger database.

Don't get me wrong: I *really* like postgres. I really like SR/HS. I even like that pg_upgrade exists although the way it works isn't that elegant from my POV.
Unfortunately that doesn't make those features appear :(.