From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ekaterina Amez Gonzalez <registrosekaterina@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Streaming replication problem with collation
In-reply-to: <CAC5zuTqssvYehaG3maYNeWYmvURN+h5xG7KiOoSeYtQKhCLV_g@mail.gmail.com>
References: <CAC5zuTqssvYehaG3maYNeWYmvURN+h5xG7KiOoSeYtQKhCLV_g@mail.gmail.com>
Comments: In-reply-to Ekaterina Amez Gonzalez <registrosekaterina@gmail.com>
	message dated "Fri, 20 Dec 2024 11:06:29 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <675018.1734708686.1@sss.pgh.pa.us>
Date: Fri, 20 Dec 2024 10:31:26 -0500
Message-ID: <675019.1734708686@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/675019.1734708686%40sss.pgh.pa.us>
Precedence: bulk

Ekaterina Amez Gonzalez <registrosekaterina@gmail.com> 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