Обсуждение: setting up postgres replication FATAL: database system identifier differs between the primary and standby

Ahoy there,

I am attempting to set up postgres12 with replication between two servers.

I followed these instructions.


https://www.digitalocean.com/community/tutorials/how-to-set-up-physical-streaming-replication-with-postgresql-12-on-ubuntu-20-04

At step 4 - I restarted the replica database with command "sudo 
systemctl restart postgresql@12-main"

I see these messages in the /var/log/postgresql/postgresql-12-main.log file

--- snip ---

2022-02-12 17:39:51.345 UTC [237364] LOG:  entering standby mode
2022-02-12 17:39:51.356 UTC [237365] FATAL:  database system identifier 
differs between the primary and standby
2022-02-12 17:39:51.356 UTC [237365] DETAIL:  The primary's identifier 
is 7048326921585830819, the standby's identifier is 7063458841707763655.
2022-02-12 17:39:51.367 UTC [237369] FATAL:  database system identifier 
differs between the primary and standby
2022-02-12 17:39:51.367 UTC [237369] DETAIL:  The primary's identifier 
is 7048326921585830819, the standby's identifier is 7063458841707763655.
2022-02-12 17:39:56.380 UTC [237371] FATAL:  database system identifier 
differs between the primary and standby
2022-02-12 17:39:56.380 UTC [237371] DETAIL:  The primary's identifier 
is 7048326921585830819, the standby's identifier is 7063458841707763655.

--- snip ---


Is there an easy way to solve this problem?


Cheers

2022年2月13日(日) 8:37 Noah <noah-list@enabled.com>:
>
> Ahoy there,
>
> I am attempting to set up postgres12 with replication between two servers.
>
> I followed these instructions.
>
>
https://www.digitalocean.com/community/tutorials/how-to-set-up-physical-streaming-replication-with-postgresql-12-on-ubuntu-20-04
>
> At step 4 - I restarted the replica database with command "sudo
> systemctl restart postgresql@12-main"
>
> I see these messages in the /var/log/postgresql/postgresql-12-main.log file
>
> --- snip ---
>
> 2022-02-12 17:39:51.345 UTC [237364] LOG:  entering standby mode
> 2022-02-12 17:39:51.356 UTC [237365] FATAL:  database system identifier
> differs between the primary and standby
> 2022-02-12 17:39:51.356 UTC [237365] DETAIL:  The primary's identifier
> is 7048326921585830819, the standby's identifier is 7063458841707763655.
> 2022-02-12 17:39:51.367 UTC [237369] FATAL:  database system identifier
> differs between the primary and standby
> 2022-02-12 17:39:51.367 UTC [237369] DETAIL:  The primary's identifier
> is 7048326921585830819, the standby's identifier is 7063458841707763655.
> 2022-02-12 17:39:56.380 UTC [237371] FATAL:  database system identifier
> differs between the primary and standby
> 2022-02-12 17:39:56.380 UTC [237371] DETAIL:  The primary's identifier
> is 7048326921585830819, the standby's identifier is 7063458841707763655.
>
> --- snip ---
>
>
> Is there an easy way to solve this problem?

You appear to be starting a database which is not a copy of the primary node it
is trying to connect to. It's impossible to tell from the provided information
how that happened. Possibly you have more than one instance on the standby
(replica) and are somehow starting the wrong one. If not you'll need to make
a backup of the primary and make sure that's what is being started.

Regards

Ian Barwick

--
EnterpriseDB: https://www.enterprisedb.com

On 2/12/22 6:35 PM, Ian Lawrence Barwick wrote:
[snip]

> You appear to be starting a database which is not a copy of the primary node it
binary copy
> is trying to connect to. It's impossible to tell from the provided information
> how that happened. Possibly you have more than one instance on the standby
> (replica) and are somehow starting the wrong one. If not you'll need to make
> a backup of the primary and make sure that's what is being started.

Binary copy using pg_basebackup (or something like pgbackrest).

-- 
Angular momentum makes the world go 'round.

Hi,

On 2/12/22 5:03 PM, Ron wrote:
> On 2/12/22 6:35 PM, Ian Lawrence Barwick wrote:
> [snip]
> 
>> You appear to be starting a database which is not a copy of the 
>> primary node it
> binary copy
>> is trying to connect to. It's impossible to tell from the provided 
>> information
>> how that happened. Possibly you have more than one instance on the 
>> standby
>> (replica) and are somehow starting the wrong one. If not you'll need 
>> to make
>> a backup of the primary and make sure that's what is being started.
> 
> Binary copy using pg_basebackup (or something like pgbackrest).
> 

Binary copy finally worked (the second time).  Thanks for your assistance.

<host># rm -r /var/lib/postgresql/12/main
<host># sudo -u postgres mkdir /var/lib/postgresql/12/main
<host># sudo -u postgres chmod 700 /var/lib/postgresql/12/main
<host># sudo -u postgres pg_basebackup -h <ip> -p 5432 -U replication -D 
/var/lib/postgresql/12/main/ -Fp -Xs -R
Password:
<host># sudo systemctl restart postgresql@12-main
<host>#

---- logs show ----
2022-02-13 01:58:32.445 UTC [264092] LOG:  redo starts at 0/DA000028
2022-02-13 01:58:32.448 UTC [264092] LOG:  consistent recovery state 
reached at 0/DA000100
2022-02-13 01:58:32.448 UTC [264091] LOG:  database system is ready to 
accept read only connections
2022-02-13 01:58:32.464 UTC [264096] LOG:  started streaming WAL from 
primary at 0/DB000000 on timeline 1
---- snip ----


Cheers