Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ea4cs-0006AP-Bd for pgsql-admin@arkaria.postgresql.org; Fri, 12 Jan 2018 19:04:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ea4cr-0002WP-Ug for pgsql-admin@arkaria.postgresql.org; Fri, 12 Jan 2018 19:04:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ea4cr-0002WF-GU for pgsql-admin@lists.postgresql.org; Fri, 12 Jan 2018 19:04:25 +0000 Received: from mr28p48im-tydg10101201.me.com ([17.110.88.44]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ea4co-0007sl-9l for pgsql-admin@lists.postgresql.org; Fri, 12 Jan 2018 19:04:25 +0000 Received: from process-dkim-sign-daemon.mr28p48im-tydg10101201.me.com by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) id <0P2G00800IAV2X00@mr28p48im-tydg10101201.me.com> for pgsql-admin@lists.postgresql.org; Fri, 12 Jan 2018 19:04:17 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=04042017; t=1515783857; bh=FLDfv4DosbAzRfoSSChsgOsTIwSy1C7xBa29Q8yKWiE=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=M6iP4HCMVdZ+Ug4uI/ErbLscBXW6Jnoju1MqPkRFjrVlJ0RXlqJhqOklU6N5VjpO1 0Em/nKHbaR8Q8ZENC9QmqYixyIxqTavim49v1jOibDPpeBOdrfuqM4js8i/r+Xi+nN YpWuSyLAFxO0qkSRSn9aebC7gvp9hmo3afssOiJzaOgh99JeAqhIm60efiQNHHj3mW /lcr2qD9gHbWREwOIDgXe2OCYftaR2MPFfsHxr/qRph8pFGPCkRb/plI3tdzZ+kBaz a8UsBEU0UQ+6ebna/m1fErvr5EBxZxRGnGukCzQLDfonevH6kUswuCGmTESNIaBI/X 3Ooo8+2sViy7Q== Received: from icloud.com ([127.0.0.1]) by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) with ESMTPSA id <0P2G00D6JIAWIO30@mr28p48im-tydg10101201.me.com>; Fri, 12 Jan 2018 19:04:16 +0000 (GMT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2018-01-12_10:,, signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1015 suspectscore=0 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1801120257 From: Rui DeSousa Message-id: <45FEB64D-CE15-40CA-9BF3-DECEAC21B268@icloud.com> Content-type: multipart/alternative; boundary="Apple-Mail=_07B9B85E-93B7-4186-9CB8-A2BC9D9D085B" MIME-version: 1.0 (Mac OS X Mail 11.2 \(3445.5.20\)) Subject: Re: Broken replication in 3 node setup, master halt, standby 1 promoted, standby 2 - replication not working Date: Fri, 12 Jan 2018 14:04:07 -0500 In-reply-to: Cc: pgsql-admin@lists.postgresql.org To: Pritam Barhate References: X-Mailer: Apple Mail (2.3445.5.20) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_07B9B85E-93B7-4186-9CB8-A2BC9D9D085B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii You might want to consider sync replication instead to avoid split brain = syndrome. It readily depending on how things are promoted and managed. > On Jan 12, 2018, at 10:50 AM, Pritam Barhate = wrote: >=20 > I am using async replication.=20 >=20 > As both of you said:=20 >=20 > recovery_target_timeline=3Dlatest >=20 > Was missing. Adding it to the recovery.conf did the trick.=20 >=20 > Thanks a lot.=20 >=20 > Regards, >=20 > Pritam. >=20 > On Thu, Jan 11, 2018 at 4:09 AM, Rui DeSousa > wrote: > It appears that your missing is following in the recovery.conf file. = It instructs the replica to follow a new timeline when presented; = without it will refuse to follow the promoted node. >=20 > recovery_target_timeline=3Dlatest >=20 > Are you using sync replication? >=20 >=20 > > On Jan 10, 2018, at 2:06 PM, Pritam Barhate > wrote: > > > > Hi, > > > > I am trying to test the failover scenario on a brand new 3 node = setup. > > > > pg1 is master > > pg2 and pg3 are standby hosts in streaming replication, both = following pg1. > > > > When I halt pg1, promote pg2 and update recovery.conf on pg3 to = point to pg2, the replication is broken. Can you please help me identify = the correct procedure to achieve this failover? > > > > Here are the details: > > > > Software Versions: > > > > PostgreSQL: 9.6 > > Ubuntu 16.04 > > PgBackRest 1.25 > > > > Here is the scenario: > > > > Set up 3 nodes in streaming replication: > > > > master (pg1), standby 1 (pg2), standby 2 (pg3) > > > > Also, I have set up pgbackrest, to back up from standby. It's = working correctly. > > > > Run the following on pg1: > > > > sudo -u postgres psql -x -c "select * from pg_stat_replication;" > > > > -[ RECORD 1 ]----+------------------------------ > > pid | 20714 > > usesysid | 16384 > > usename | replicator > > application_name | walreceiver > > client_addr | 10.0.15.11 > > client_hostname | > > client_port | 48640 > > backend_start | 2018-01-10 18:17:46.105694+00 > > backend_xmin | > > state | streaming > > sent_location | 0/6000060 > > write_location | 0/6000060 > > flush_location | 0/6000060 > > replay_location | 0/6000060 > > sync_priority | 0 > > sync_state | async > > -[ RECORD 2 ]----+------------------------------ > > pid | 20715 > > usesysid | 16384 > > usename | replicator > > application_name | walreceiver > > client_addr | 10.0.15.12 > > client_hostname | > > client_port | 42198 > > backend_start | 2018-01-10 18:17:48.712575+00 > > backend_xmin | > > state | streaming > > sent_location | 0/6000060 > > write_location | 0/6000060 > > flush_location | 0/6000060 > > replay_location | 0/6000060 > > sync_priority | 0 > > sync_state | async > > > > > > It reports both the standbys working correctly. > > > > Halt pg1. > > > > On pg2: > > > > sudo -u postgres pg_ctlcluster 9.6 main promote > > > > On pg3: > > > > sudo service postgresql stop > > > > sudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf > > > > modify it's contents are as follows: > > > > ``` > > primary_conninfo =3D 'host=3Dpg2 port=3D5432 user=3Dreplicator' > > standby_mode =3D 'on' > > restore_command =3D '/usr/bin/pgbackrest --stanza=3Dmain archive-get = %f "%p"' > > ``` > > > > sudo service postgresql start > > > > On pg2: > > > > sudo -u postgres psql -x -c "select * from pg_stat_replication;" > > > > -[ RECORD 1 ]----+------------------------------ > > pid | 21238 > > usesysid | 16384 > > usename | replicator > > application_name | walreceiver > > client_addr | 10.0.15.12 > > client_hostname | > > client_port | 44852 > > backend_start | 2018-01-10 18:22:20.980701+00 > > backend_xmin | > > state | startup > > sent_location | > > write_location | > > flush_location | > > replay_location | > > sync_priority | 0 > > sync_state | async > > > > As you can see sent_location, write_location, flush_location all are = empty. > > > > On pg3: > > > > tail -f /var/log/postgresql/postgresql-9.6-main.log > > 2018-01-10 18:22:19.490 UTC [21229] LOG: restored log file = "000000010000000000000005" from archive > > 2018-01-10 18:22:19.497 UTC [21229] LOG: redo starts at 0/5000028 > > 2018-01-10 18:22:19.757 UTC [21243] postgres@postgres FATAL: the = database system is starting up > > 2018-01-10 18:22:20.188 UTC [21229] LOG: restored log file = "000000010000000000000006" from archive > > 2018-01-10 18:22:20.195 UTC [21229] LOG: consistent recovery state = reached at 0/7000000 > > 2018-01-10 18:22:20.195 UTC [21228] LOG: database system is ready = to accept read only connections > > 2018-01-10 18:22:20.812 UTC [21229] LOG: invalid magic number 0000 = in log segment 000000010000000000000007, offset 0 > > 2018-01-10 18:22:20.824 UTC [21255] LOG: fetching timeline history = file for timeline 2 from primary server > > 2018-01-10 18:22:20.828 UTC [21255] LOG: primary server contains no = more WAL on requested timeline 1 > > 2018-01-10 18:22:21.417 UTC [21255] LOG: primary server contains no = more WAL on requested timeline 1 > > > > > > At this point is I insert data in pg2, it doesn't reflect in pg3. > > > > Please help. > > > > Regards, > > > > Pritam. > > >=20 >=20 --Apple-Mail=_07B9B85E-93B7-4186-9CB8-A2BC9D9D085B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii You = might want to consider sync replication instead to avoid split brain = syndrome.  It readily depending on how things are promoted and = managed.


On Jan 12, 2018, at 10:50 AM, = Pritam Barhate <pritambarhate@gmail.com> wrote:

I am using async replication. 

As both of you said: 

recovery_target_timeline=3Dlatest

Was missing. = Adding it to the recovery.conf did the trick. 

Thanks a lot. 

Regards,

Pritam.

On Thu, Jan 11, 2018 at 4:09 AM, = Rui DeSousa <rui.desousa@icloud.com> wrote:
It appears that your = missing is following in the recovery.conf file.  It instructs the = replica to follow a new timeline when presented; without it will refuse = to follow the promoted node.

recovery_target_timeline=3Dlatest

Are you using sync replication?


> On Jan 10, 2018, at 2:06 PM, Pritam Barhate <pritambarhate@gmail.com> wrote:
>
> Hi,
>
> I am trying to test the failover scenario on a brand new 3 node = setup.
>
> pg1 is master
> pg2 and pg3 are standby hosts in streaming replication, both = following pg1.
>
> When I halt pg1, promote pg2 and update recovery.conf on pg3 to = point to pg2, the replication is broken. Can you please help me identify = the correct procedure to achieve this failover?
>
> Here are the details:
>
> Software Versions:
>
> PostgreSQL: 9.6
> Ubuntu 16.04
> PgBackRest 1.25
>
> Here is the scenario:
>
> Set up 3 nodes in streaming replication:
>
> master (pg1), standby 1 (pg2), standby 2 (pg3)
>
> Also, I have set up pgbackrest, to back up from standby. It's = working correctly.
>
> Run the following on pg1:
>
> sudo -u postgres psql -x -c "select * from pg_stat_replication;"
>
> -[ RECORD 1 ]----+------------------------------
> pid              | 20714
> usesysid         | 16384
> usename          | replicator
= > application_name | walreceiver
> client_addr      | 10.0.15.11
> client_hostname  |
> client_port      | 48640
> backend_start    | 2018-01-10 18:17:46.105694+00
> backend_xmin     |
> state            | streaming
> sent_location    | 0/6000060
> write_location   | 0/6000060
> flush_location   | 0/6000060
> replay_location  | 0/6000060
> sync_priority    | 0
> sync_state       | async
> -[ RECORD 2 ]----+------------------------------
> pid              | 20715
> usesysid         | 16384
> usename          | replicator
= > application_name | walreceiver
> client_addr      | 10.0.15.12
> client_hostname  |
> client_port      | 42198
> backend_start    | 2018-01-10 18:17:48.712575+00
> backend_xmin     |
> state            | streaming
> sent_location    | 0/6000060
> write_location   | 0/6000060
> flush_location   | 0/6000060
> replay_location  | 0/6000060
> sync_priority    | 0
> sync_state       | async
>
>
> It reports both the standbys working correctly.
>
> Halt pg1.
>
> On pg2:
>
> sudo -u postgres pg_ctlcluster 9.6 main promote
>
> On pg3:
>
> sudo service postgresql stop
>
> sudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf
>
> modify it's contents are as follows:
>
> ```
> primary_conninfo =3D 'host=3Dpg2 port=3D5432 user=3Dreplicator'
> standby_mode =3D 'on'
> restore_command =3D '/usr/bin/pgbackrest --stanza=3Dmain = archive-get %f "%p"'
> ```
>
> sudo service postgresql start
>
> On pg2:
>
> sudo -u postgres psql -x -c "select * from pg_stat_replication;"
>
> -[ RECORD 1 ]----+------------------------------
> pid              | 21238
> usesysid         | 16384
> usename          | replicator
= > application_name | walreceiver
> client_addr      | 10.0.15.12
> client_hostname  |
> client_port      | 44852
> backend_start    | 2018-01-10 18:22:20.980701+00
> backend_xmin     |
> state            | startup
> sent_location    |
> write_location   |
> flush_location   |
> replay_location  |
> sync_priority    | 0
> sync_state       | async
>
> As you can see sent_location, write_location, flush_location all = are empty.
>
> On pg3:
>
> tail -f /var/log/postgresql/postgresql-9.6-main.log
> 2018-01-10 18:22:19.490 UTC [21229] LOG:  restored log file = "000000010000000000000005" from archive
> 2018-01-10 18:22:19.497 UTC [21229] LOG:  redo starts at = 0/5000028
> 2018-01-10 18:22:19.757 UTC [21243] postgres@postgres FATAL:  = the database system is starting up
> 2018-01-10 18:22:20.188 UTC [21229] LOG:  restored log file = "000000010000000000000006" from archive
> 2018-01-10 18:22:20.195 UTC [21229] LOG:  consistent recovery = state reached at 0/7000000
> 2018-01-10 18:22:20.195 UTC [21228] LOG:  database system is = ready to accept read only connections
> 2018-01-10 18:22:20.812 UTC [21229] LOG:  invalid magic number = 0000 in log segment 000000010000000000000007, offset 0
> 2018-01-10 18:22:20.824 UTC [21255] LOG:  fetching timeline = history file for timeline 2 from primary server
> 2018-01-10 18:22:20.828 UTC [21255] LOG:  primary server = contains no more WAL on requested timeline 1
> 2018-01-10 18:22:21.417 UTC [21255] LOG:  primary server = contains no more WAL on requested timeline 1
>
>
> At this point is I insert data in pg2, it doesn't reflect in = pg3.
>
> Please help.
>
> Regards,
>
> Pritam.
>



= --Apple-Mail=_07B9B85E-93B7-4186-9CB8-A2BC9D9D085B--