Thread: [GENERAL] WAL replication wrong collate

Hello,

i try to replicate my database. what i have done?

- create a cluster on slave (UTF8, en_US.utf8 collate/c_type)
- stop cluster and cleanup datadir
- do basebackup from master
- start db-cluster

Master has utf8, en_us.uft8 collate/c_type
Now my db on slave has UTF8, c.utf8 collate/c_type.
Whats wrong here?

master and slave had set the same locales.
I dont unterstand that i can create a database in en_us.utf8 and then
when i did the basebackup it's change to c locale.
I cant find any option for pg_basebackup to set locale/collate.
I use this howto https://wiki.postgresql.org/wiki/Streaming_Replication
and had install an other cluster in the past with successful.

How can help please?

Best regards,
basti

i have fixed. pg_update has create a wrong cluster


On 14.08.2017 20:52, basti wrote:
> Hello,
>
> i try to replicate my database. what i have done?
>
> - create a cluster on slave (UTF8, en_US.utf8 collate/c_type)
> - stop cluster and cleanup datadir
> - do basebackup from master
> - start db-cluster
>
> Master has utf8, en_us.uft8 collate/c_type
> Now my db on slave has UTF8, c.utf8 collate/c_type.
> Whats wrong here?
>
> master and slave had set the same locales.
> I dont unterstand that i can create a database in en_us.utf8 and then
> when i did the basebackup it's change to c locale.
> I cant find any option for pg_basebackup to set locale/collate.
> I use this howto
> https://wiki.postgresql.org/wiki/Streaming_Replication and had install
> an other cluster in the past with successful.
>
> How can help please?
>
> Best regards,
> basti
>
>
>

On Tue, Aug 15, 2017 at 3:52 AM, basti <mailinglist@unix-solution.de> wrote:
> master and slave had set the same locales.
> I dont unterstand that i can create a database in en_us.utf8 and then when i
> did the basebackup it's change to c locale.
> I cant find any option for pg_basebackup to set locale/collate.
> I use this howto https://wiki.postgresql.org/wiki/Streaming_Replication and
> had install an other cluster in the past with successful.
>
> How can help please?

Are you sure that you are connecting to the right standby server? The
creation of a database is WAL-logged in roughly two phases:
- Log the new pg_database record.
- Copy the directory of the template database to the new database.
So collations are conserved at replay.
--
Michael

On Tue, Aug 15, 2017 at 4:45 AM, basti <basti@unix-solution.de> wrote:
> i have fixed. pg_update has create a wrong cluster

Let's be sure that we are not talking about a bug here, because you
are giving no details so it is hard to know if what you are seeing is
caused by an incorrect operation, or if that's an actual bug. So,
could you answer the following questions?
1) Are you referring to pg_upgrade instead of pg_update?
2) How did you do the upgrade of the standby server?
3) What are the original version of the server and the new version?
4) What is the exact order of the operations you did that made the
state you saw happen?
--
Michael