Minimal downtime major PostgreSQL version upgrades with pg_easy

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#11

Post by shayonj » Tue, Jun 20, 2023, 4:34 PM UTC

srslack wrote: post #7

I did this the "hard way" a year ago. This blog post came in handy: https://jstaf.github.io/posts/pglogical/ pglogical is magic. Keeping an eye on this project for the future, looks great.

Love pglogical. I was thinking, it'd be nice to orchestrate this setup using pglogical too. Would attempt it if there is interest. Especially with bi-directional replication setup.

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#12

Post by shayonj » Tue, Jun 20, 2023, 4:35 PM UTC

pelagicAustral wrote: post #8

Much appreciated. I was actually looking into a few options for this exact same thing. I will give this shot right away.

Thanks for giving it a try. Please don't hesitate to open bugs/feature requests.

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#13

Post by pqdbr » Tue, Jun 20, 2023, 4:56 PM UTC

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)?

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#14

Post by jrochkind1 » Tue, Jun 20, 2023, 4:58 PM UTC

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?

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#15

Post by jrochkind1 » Tue, Jun 20, 2023, 5:00 PM UTC

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 easy answer would be on some non-production copy of the Rails app, point at the new db in database.yml, and simply run `rails db:create; rails db:schema:load`, or some variation rails command like `rails db:prepare`.

Rather than this ad-hoc thing, you could also try to create a new rails "environment" for this use, I guess.

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#16

Post by jxcl » Tue, Jun 20, 2023, 5:01 PM UTC

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)?

Yeah I think the documentation is a bit ambiguous here. Postgres "schemas" have a very specific meaning, different from what most people think of when they hear the word. It's more like a table namespace.

https://www.postgresql.org/docs/current/ddl-schemas.html

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#17

Post by shayonj » Tue, Jun 20, 2023, 5:02 PM UTC

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)?

That’s a great question: internally what we have done is when preparing the new empty database, we also run a “bundle exec rake db:schema:load” against it. Depending on your setup, it can be hard to do.

I am exploring some options like loading schema from a schema.sql file or something along the lines of what pg_dump does for schema export.

Would love to hear any ideas.

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#18

Post by jensenbox » Tue, Jun 20, 2023, 5:03 PM UTC

I know I am going to get fairly polarized responses on this one...

Too bad it is written in Ruby.

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#19

Post by shayonj » Tue, Jun 20, 2023, 5:04 PM UTC

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?

Great question: so you can watch the stats command. The command returns a JSON every second which has a switchover_completed_at field. It is updated the as soon as the switchover is complete.

UPDATE: Some more reading material here: https://github.com/shayonj/pg_easy_replicate#switchover-stra...

Re: Minimal downtime major PostgreSQL version upgrades with pg_easy_replicate

#20

Post by sgt » Tue, Jun 20, 2023, 5:05 PM UTC

srslack wrote: post #7

I did this the "hard way" a year ago. This blog post came in handy: https://jstaf.github.io/posts/pglogical/ pglogical is magic. Keeping an eye on this project for the future, looks great.

> Binary replication only works with DBs of the same major version. If you want to replicate to a different version, well… you can’t.

Is this true? I thought they fixed that at some point after PG11.