PostgreSQL

09 February 2025 12:56

that can be managed by the application (or so say the developers) but it's good to have it in mind

PostgreSQL

09 February 2025 12:55

You can put a bouncer in front of your db, and move the clients transparently to the new db once migrated

PostgreSQL

09 February 2025 12:54

Or rather, do all the small ones in a single chunk

PostgreSQL

09 February 2025 12:53

I would just dump every db and reload it on the latest version and that's it

PostgreSQL

09 February 2025 12:53

So, you're looking at what, 10TB+ to upgrade with logical replication? Sounds like a lot of fun. Not.

PostgreSQL

09 February 2025 12:51

I understand correctly that I can easily update version 14 to 16 or 17 using pg_dumpall?

PostgreSQL

09 February 2025 12:45

Do they actually need 1000 db's in 1 server? I have seen similar setup in the wild with around 3700 DBs in one server but it was due to historical reason or system architect forgot there are schemas.

PostgreSQL

09 February 2025 12:42

https://www.depesz.com/2023/06/04/a-tale-about-incomplete-upgrade-from-postgresql-12-to-14/

Depesz article about logical replication pain. There are couple of more issues you get with very large databases like sync snapshot too large or replication worker terminating due to lack of host throughput capacity.

PostgreSQL

09 February 2025 12:39

With 1000 DBs, the schema dump & restore will easily take hours, yeah.

PostgreSQL

09 February 2025 12:35

the problem is that downtime has to be (and has been so far) under a few minutes, but the process is quite stressing

PostgreSQL

09 February 2025 12:32

yes, I have this PITA feeling ... I just wanted to confirm it 😊 and if maybe there was a cream to calm the pain

PostgreSQL

09 February 2025 11:18

Hello, ... I'm handling postgresql major version upgrades by setting up a new server and logically replicate the data with pglogical.
I have 3 servers with around 1000 dbs in each server now.
I generate in bash the commands to stablish and check the replication (a long list of psql -f commands).
It's become quite cognitive intense to keep it under control for me.
Is there any tool to manage this volume of logical replication that gives me a better feeling of management and control of the process?
Thanks!

PostgreSQL

08 February 2025 19:08

The best guide to action turned out to be official documentation ;)

PostgreSQL

07 February 2025 18:56

https://www.postgresql.org/docs/9.2/release.html

Check from here, you can choose the version at the top of the document

PostgreSQL

07 February 2025 18:51

Since you're jumping a theroretical of 12 versions ahead, i would suggest a full dump & restore with pg_dumpall, since i'm not sure pg_upgrade will happily move you from 9.2 to 17

PostgreSQL

09 February 2025 12:55

After you've done migrating all the databases, remove the bouncer and put the new server in place of the old one

PostgreSQL

09 February 2025 12:54

And then do the >10gb ones individually

PostgreSQL

09 February 2025 12:54

I would like to do that, but seems more automatable to use logical replication

PostgreSQL

09 February 2025 12:53

from a few MBs to less than 1GB, except 3 or 4 of about 10GB

PostgreSQL

09 February 2025 12:51

Maybe you find a way to patch pg_upgrade in a way that it accepts a new DB with the schema already applied? That'd probably even be accepted as a feature; I heard horror stories of similarly huge schemas delaying pg_upgrade to _days_...

PostgreSQL

09 February 2025 12:51

Could it be perhaps one db per customer?

PostgreSQL

09 February 2025 12:44

yes that's my fear. Thanks for the link

PostgreSQL

09 February 2025 12:41

Now, the application allows to point to individual dbs within one server or the other, so it hasn't to be done all 1000 dbs at once and it should be possible to be done in smaller batches, but doing it all at once is always in the mind of the managers

PostgreSQL

09 February 2025 12:38

Downtime should depend on size of pg catalog (number of objects) so hard to guess without testing. pg18 onwards there will be no need to analyze as stats can be exported so that will lower downtime.

PostgreSQL

09 February 2025 12:33

Logical replication is very fragile and other factors affecting it's stability are size and rate of data changes. Just pg upgrade with link method and some downtime might be better option.

PostgreSQL

09 February 2025 12:25

Managing thousands of publications & subscriptions will be a royal PITA no matter what. I doubt anybody ever implemented a tool with this lot in mind... 🤷

PostgreSQL

08 February 2025 19:11

No problem, I asked a similar question
/channel/pg_sql/92043

PostgreSQL

08 February 2025 17:18

I need Postgres training videos any one have

PostgreSQL

07 February 2025 18:54

What all main changes from 9.2.24 to 9.6.*latest verion

PostgreSQL

07 February 2025 18:50

Depending on the complexity of the database (stored procedures, queries you run) you might have some incompatibilities. Surely you'll need to read the release notes carefully: https://www.postgresql.org/docs/9.6/release.html