pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsetting up postgres replication FATAL: database system identifier differs between the primary and standby
4+ messages / 3 participants
[nested] [flat]
* setting up postgres replication FATAL: database system identifier differs between the primary and standby
@ 2022-02-12 23:37 Noah <noah-list@enabled.com>
2022-02-13 00:35 ` Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby Ian Lawrence Barwick <barwick@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Noah @ 2022-02-12 23:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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-p...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby
2022-02-12 23:37 setting up postgres replication FATAL: database system identifier differs between the primary and standby Noah <noah-list@enabled.com>
@ 2022-02-13 00:35 ` Ian Lawrence Barwick <barwick@gmail.com>
2022-02-13 01:03 ` Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ian Lawrence Barwick @ 2022-02-13 00:35 UTC (permalink / raw)
To: Noah <noah-list@enabled.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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-p...
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby
2022-02-12 23:37 setting up postgres replication FATAL: database system identifier differs between the primary and standby Noah <noah-list@enabled.com>
2022-02-13 00:35 ` Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby Ian Lawrence Barwick <barwick@gmail.com>
@ 2022-02-13 01:03 ` Ron <ronljohnsonjr@gmail.com>
2022-02-13 02:02 ` Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby Noah <noah-list@enabled.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ron @ 2022-02-13 01:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby
2022-02-12 23:37 setting up postgres replication FATAL: database system identifier differs between the primary and standby Noah <noah-list@enabled.com>
2022-02-13 00:35 ` Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby Ian Lawrence Barwick <barwick@gmail.com>
2022-02-13 01:03 ` Re: setting up postgres replication FATAL: database system identifier differs between the primary and standby Ron <ronljohnsonjr@gmail.com>
@ 2022-02-13 02:02 ` Noah <noah-list@enabled.com>
0 siblings, 0 replies; 4+ messages in thread
From: Noah @ 2022-02-13 02:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-02-13 02:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-12 23:37 setting up postgres replication FATAL: database system identifier differs between the primary and standby Noah <noah-list@enabled.com>
2022-02-13 00:35 ` Ian Lawrence Barwick <barwick@gmail.com>
2022-02-13 01:03 ` Ron <ronljohnsonjr@gmail.com>
2022-02-13 02:02 ` Noah <noah-list@enabled.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox