public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Ekaterina Amez Gonzalez <[email protected]>
Cc: [email protected]
Subject: Re: Streaming replication problem with collation
Date: Fri, 20 Dec 2024 10:31:26 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAC5zuTqssvYehaG3maYNeWYmvURN+h5xG7KiOoSeYtQKhCLV_g@mail.gmail.com>
References: <CAC5zuTqssvYehaG3maYNeWYmvURN+h5xG7KiOoSeYtQKhCLV_g@mail.gmail.com>

Ekaterina Amez Gonzalez <[email protected]> writes:
> I tried what was suggested: reindexing and running "refresh collation"
> alter after that and everything seems to work ok so this looks like an easy
> wat to migrate from one server to another. Plus I feel more comfortable
> using streaming replication than logical replication, and also I find it
> more useful when you need to replicate the whole cluster.
> So my question is: is there anything I'm missing here, some kind of problem
> that could hit my face after moving to the new server?

That will almost certainly blow up in your face.  Physical replication
assumes that the source and replica databases are to be kept bitwise
identical.  What you've described is already not bitwise identical
because (a) the collation versions recorded for the indexes are
different and (b) reindexing would have rebuilt the indexes, so that
there's no reason to expect that all the index entries are in the same
physical spots as before.  Moreover, the entire point of all this
worry about collation versions is that (c) the logical ordering of
the indexes might now be different.  So enabling physical replication
at this point would surely make a mess of the replica's indexes.

You'll have to use logical replication for this.

			regards, tom lane

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Streaming replication problem with collation
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox