0

We have a PostgreSQL cluster running on one master (db1) and two async hot-standby servers (db2 and db3) replicating from it. All the servers are working as a backend for a web service and all the read/write transactions connect to db1 and all read-only transactions are executed on hot-standby servers if possible. The clients are smart enough to fallback to talking to master if hot-standby servers are too delayed or down. All the servers have identical hardware so it doesn't matter which one is the master server.

We're planning to upgrade the PostgreSQL to newer major version and want to do that with minimal downtime.

We're currently using binary/physical streaming replication but that replication mode only supports running all servers on the same major version.

I know that logical streaming replication can be used to synchronize data between different major versios so to upgrade the whole system to more recent release, I've following idea:

  • Start publishing changes on db1 over logical replication: create a new logical replication slot called db1_to_db3 for this purpose.
  • Take db3 down, upgrade the PostgreSQL to next major versio we want to use.
  • Clear the whole database and manually copy the database schema from db1 to db3.
  • Configure db3 as hot-standby and subscribe to slow db1_to_db3.
  • Wait for db3 to eventually synchronize.
  • Then do the same thing for db2 except make it synchronize from db3.
  • Then promote db3 as the new master. Only do it now that we have at least two servers (db3 and db2) with the full database to avoid lots of downtime in case of server hardware failure.
  • Finally upgrade the PostgreSQL to the next major version on db1 and add it as a hot-standby replica for db3.

However, I have following open questions:

  1. Does creating the logical replication slot on db1 guarantee that db3 can be syncronized from scratch?
  2. Does publishing the logical slot increase WAL log disk usage? If so, how much (e.g. +100%?)
  3. How to switch back from logical replication to binary replication in the future? I know that we don't want to use logical replication in long run because it doesn't handle DDL changes (schema changes).
  4. Is it possible to lose transactions with logical replication? How to verify the databases actually have the same data?

In case it makes a difference, we're planning to upgrade from version 12.x to 14.x.

Improve this question
5
  • Alternative way to do this with very little downtime is to use pg_upgrade --link. However, that doesn't have recovery plan in case pg_uprade failed for any reason: dba.stackexchange.com/a/301517/29183
    – Mikko Rantalainen
    Commented Jun 12, 2023 at 8:46
  • 1
    You cannot switch between physical and logical replication. You have to start from scratch each time.
    – Laurenz Albe
    Commented Jun 12, 2023 at 14:06
  • @LaurenzAlbe with "start from scratch" you mean that the initial sync cannot be accelerated but you have to rebuild the hot-standby from scratch when you switch between physical and logical replication, right? Publishing both physical and logical replication streams from master in parallel should be possible as far as I know.
    – Mikko Rantalainen
    Commented Jun 13, 2023 at 12:50
  • 1
    Yes, you can have both at the same time. Perhaps I didn't understand your complicated description, but I had the impression that you want to turn a streaming replication standby into a logical standby (which is possible), upgrade that way and turn the logical standby into a physical standby (which is not possible). Ignore my comment if I got you wrong.
    – Laurenz Albe
    Commented Jun 13, 2023 at 13:04
  • Your comment answered my question 3. I have to convert standby servers one-by-one from logical replication to physical replication because I have to clear the database on the slave, then redo everything from pg_basebackup. That's okay but simply takes more time than I was hoping for.
    – Mikko Rantalainen
    Commented Jun 13, 2023 at 13:17

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.