Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1nJ4Dl-0005D9-CR
	for pgsql-admin@arkaria.postgresql.org; Sun, 13 Feb 2022 02:02:37 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1nJ4Dk-00057E-9K
	for pgsql-admin@arkaria.postgresql.org; Sun, 13 Feb 2022 02:02:36 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <noah-list@enabled.com>)
	id 1nJ4Dj-000574-Uk
	for pgsql-admin@lists.postgresql.org; Sun, 13 Feb 2022 02:02:35 +0000
Received: from www-virt.enabled.com ([45.56.68.196])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <noah-list@enabled.com>)
	id 1nJ4Dh-0001XL-AT
	for pgsql-admin@lists.postgresql.org; Sun, 13 Feb 2022 02:02:35 +0000
Received: from localhost (localhost [127.0.0.1])
	by www-virt.enabled.com (Postfix) with ESMTP id 3F56A2E8F2
	for <pgsql-admin@lists.postgresql.org>; Sun, 13 Feb 2022 02:02:30 +0000 (UTC)
Received: from www-virt.enabled.com ([127.0.0.1])
	by localhost (localhost [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id x5kbKv3pP8ZR for <pgsql-admin@lists.postgresql.org>;
	Sun, 13 Feb 2022 02:02:30 +0000 (UTC)
Received: from [192.168.1.101] (157-131-171-72.fiber.dynamic.sonic.net
 [157.131.171.72])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(No client certificate requested)
	by www-virt.enabled.com (Postfix) with ESMTPSA id 1ED1F2E6C3
	for <pgsql-admin@lists.postgresql.org>; Sun, 13 Feb 2022 02:02:30 +0000 (UTC)
Message-ID: <16326bbe-2efc-b27a-31d2-92021ff31a7c@enabled.com>
Date: Sat, 12 Feb 2022 18:02:29 -0800
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.6.0
Subject: Re: setting up postgres replication FATAL: database system identifier
 differs between the primary and standby
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <2247e5f0-1e40-be50-1ca7-726afacb6d87@enabled.com>
 <CAB8KJ=ixY6H1TDuWKSvcCdE9uU-jT7E4-aWD7ZKXtVDKLRT3jg@mail.gmail.com>
 <dd83b495-616f-1e03-585d-e76930c6cdd5@gmail.com>
From: Noah <noah-list@enabled.com>
In-Reply-To: <dd83b495-616f-1e03-585d-e76930c6cdd5@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/16326bbe-2efc-b27a-31d2-92021ff31a7c%40enabled.com>
Precedence: bulk

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