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

Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

github.com

Post reply on HN
Print view View original

51–60 of 63 posts

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#51

Post by somat »

pqdbr wrote: post #13

Let's say I have a Rails app and I want to do a major postgres version upgrade using this tool. I see one of the requirements is that 'Both databases should have the same schema'. How should I manually load the schema to the new database (running on port 5433 for instance)?

The postgres documentation for logical replication suggests using "pg_dump --schema-only" as a starting point.

I am sure the pg_easy_replicate tool brings something to the table(probably replication lifecycle management) but reading the docs, the bare postgres replication looks just as easy if not easier than the easy replicate tool. I am going to try it on my toy db and see how it goes.

https://www.postgresql.org/docs/13/logical-replication-restr...

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#52

Post by nijave »

take-five wrote: post #22

Nice UX! I checked the code and noticed some things that you might want to address in the future (I've done major version upgrades via logical replication myself several times using a script similar to this). The "default" way of bringing the target DB in sync with the source one (CREATE PUBLICATION on the source then CREATE SUBSCRIPTION on the target) only works for moderately sized databases. Even on ~50GB I notice…

Check out pgcopydb. It uses COPY under the hood in parallel and is a lot faster than pg_dump/pg_restore.

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#53

Post by nijave »

CoolCold wrote: post #38
shayonj wrote: post #5

Earlier quoted context omitted.

Thanks for taking a look! Thats right, it was a typo. Mean't zero data loss and minimal downtime. There are some strategies in the readme like using weighted based DNS failover to reduce the downtime even less without requiring application deployment.

Simple LB like Haproxy works fine for this and similar cases. In very nutshell, Haproxy executes checks every n seconds (in your case it can be readonly mode checks ) and disables that upstream. We use it over Mysql/Pg+patroni/Redis and works fine - we made tests for low load though, just up to 5k qps fot PG case and up to 80k rps for Redis case.

This can be done with pgbouncer and you can suspend the database so pgbouncer "holds" connections. If the application has high enough timeouts, it will look like it's just taking longer than normal to connect.

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#54

Post by nijave »

somat wrote: post #51
pqdbr wrote: post #13

Let's say I have a Rails app and I want to do a major postgres version upgrade using this tool. I see one of the requirements is that 'Both databases should have the same schema'. How should I manually load the schema to the new database (running on port 5433 for instance)?

The postgres documentation for logical replication suggests using "pg_dump --schema-only" as a starting point. I am sure the pg_easy_replicate tool brings something to the table(probably replication lifecycle management) but reading the docs, the bare postgres replication looks just as easy if not easier than the easy replicate tool. I am going to try it on my toy db and see how it goes. https://www.postgresql.org/do…

This looks like a script that basically just runs all the necessary SQL for you. It looks pretty similar to an internal Python tool I came up with to do the same thing (basically just scripting out setting up logical rep and waiting for it to catch up, then executing switchover commands)

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#55

Post by nijave »

nylonstrung wrote: post #39

Would it make sense to use this for backup/redundancy as well? If not, what would better approaches be

For backup there's tools like barman and pg_backrest. If Postgres is virtualized, it's potentially faster to take disk spanshots & use PITR.

For redundancy (cluster management where you have hot standbys) there's tools like Patroni or Stolon

I don't have experience with Stolon but Patroni can help orchestrate backup tools for you (you still need to pick the tool and do some config)

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#56

Post by nijave »

oulu2006 wrote: post #45
gfosco wrote: post #6

Have done minimal downtime major version upgrades using standard replication, switching between two "master" servers. In the five minute range.

Have you done this postgresql/RDS? Because when I last tried it, the replica has to be the same major version (WAL streaming requires it) and then upgrading it to the latest version can take 10+ hours. Definitely nowhere near 5 minutes downtime.

You'd want logical replication instead of physical replication. Logical rep doesn't stream the WAL as-is, it streams a representation of the statement that can be replayed on the destination.

It looks like this uses pglogical plugin but there's also a built in decoder I think called "pgoutput"

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#57

Post by nijave »

jrochkind1 wrote: post #14

I guess this is pretty similar to what heroku does for pg updates? If using this tool for upgrades with minimal downtime, if I understand right, you need to orchestrate your db callers to switch over to the new primary at the right point, when it's ready? Tips for getting that to happen at just the right point to minimize downtime?

Connect through a single pgbouncer instance and use pgbouncer to orchestrate the switchover by hot reloading/updating the config

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#58

Post by shayonj »

nijave wrote: post #54
somat wrote: post #51

Earlier quoted context omitted.

The postgres documentation for logical replication suggests using "pg_dump --schema-only" as a starting point. I am sure the pg_easy_replicate tool brings something to the table(probably replication lifecycle management) but reading the docs, the bare postgres replication looks just as easy if not easier than the easy replicate tool. I am going to try it on my toy db and see how it goes. https://www.postgresql.org/do…

This looks like a script that basically just runs all the necessary SQL for you. It looks pretty similar to an internal Python tool I came up with to do the same thing (basically just scripting out setting up logical rep and waiting for it to catch up, then executing switchover commands)

Thats right! Its mostly a CLI orchestration tool (mentioned in the readme), with some logging, stats and safety checks so that user/engineer can be mostly hands off during the process. Perhaps, even automate the entire process by wiring up different pg_easy_replicate commands together.

We do that with Tines, where we can be fully hands off (zero touch ops) and are able to kick off upgrade with switchover for tens of DBs (one at a time for now :D).

Post reply on HN