Streaming replication problem with collation
Hi List,
I'm making some tests in order to prepare a db migration. We have version
9.6 over CentOS 7 and we're going to migrate to version 15 over Rocky Linux
9. Of course there is a no downtime requirement or I wouldn't be here
asking.
I was previously aware of the problem with different glibc version between
systems before I started my tests, but I tried it anyway. I first upgraded
CentOS version to 15 and then made a streaming replication to the other
server (Rocky). After that I encountered the next warning when connecting
to new hot standby database:
WARNING: database my_db has a collation version mismatch
DETAIL: The database was created using collation version 2.17, but the
operating system provides version 2.34.
HINT: Rebuild all objects in this database that use the default collation
and run ALTER DATABASE my_db REFRESH COLLATION VERSION, or build PostgreSQL
with the right library version.
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?
Thanks in advance,
Ekaterina
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