pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres streaming replication for HA
8+ messages / 3 participants
[nested] [flat]
* postgres streaming replication for HA
@ 2017-09-08 05:49 Sunil N Shinde <Sunil.Shinde@mastek.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sunil N Shinde @ 2017-09-08 05:49 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am forming 2 node cluster of streaming replication for HA. I could able to succeed with the replication process.
But I am not able to complete the "switchover".
Version : postgresql-9.6
OS :RHEL7
Master Node Parameters:
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.6/data/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
pb_hba.conf
# Localhost
host replication replica 127.0.0.1/32 md5
# PostgreSQL Master IP address
host replication replica 10.0.3.4/32 md5
# PostgreSQL SLave IP address
host replication replica 10.0.3.5/32 md5
Slave Node Parameters:
wal_level = hot_standby
synchronous_commit = local
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
hot_standby = on
Replication is properly happening from master to slave. But I am not able to switchover between the nodes.
Can anyone please guide me in this regard?
Thanks & Regards,
Sunil N Shinde
Assistant Manager - DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres streaming replication for HA
@ 2017-09-08 06:51 Samed YILDIRIM <samed@reddoc.net>
parent: Sunil N Shinde <Sunil.Shinde@mastek.com>
0 siblings, 1 reply; 8+ messages in thread
From: Samed YILDIRIM @ 2017-09-08 06:51 UTC (permalink / raw)
To: pgsql-admin; Sunil N Shinde <sunil.shinde@mastek.com>
Hi Shinde,<div><br /></div><div>Do you trying swithover manually? Or do you use any manager or script to handle switchover operation?</div><div><br /></div><div>Best regards.</div><div><br />-- <br />Yandex.Mail mobil uygulamasından gönderildi<br /><br />08:51, 8 Eylül 2017, Sunil N Shinde <sunil.shinde@mastek.com>:<br /><blockquote><br /><br /><br /><br /><br /><br /><div lang="EN-IN" link="blue" vlink="purple"><br /><div><br /><p>Hi,</p><br /><p> </p><br /><p>I am forming 2 node cluster of streaming replication for HA. I could able to succeed with the replication process.</p><br /><p>But I am not able to complete the “<b>switchover</b>”. <br /></p><br /><p> </p><br /><p>Version : postgresql-9.6</p><br /><p>OS :RHEL7</p><br /><p> </p><br /><p>Master Node Parameters:</p><br /><p> <i><span style="font-size:9pt;font-family:Cambria,serif;">wal_level = hot_standby <br /></span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> synchronous_commit = local</span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> archive_mode = on <br /></span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> archive_command = 'cp %p /var/lib/pgsql/9.6/data/archive/%f' <br /></span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> max_wal_senders = 2</span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> wal_keep_segments = 10</span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> synchronous_standby_names = 'UWPGRECLUS2'</span></i></p><br /><p> </p><br /><p> pb_hba.conf </p><br /><p style="margin-left:54pt;"><i><span style="font-size:9pt;font-family:Cambria,serif;"># Localhost</span></i></p><br /><p style="margin-left:72pt;"><i><span style="font-size:9pt;font-family:Cambria,serif;">host replication replica 127.0.0.1/32 md5 <br /></span></i></p><br /><p style="margin-left:72pt;"><i><span style="font-size:9pt;font-family:Cambria,serif;"># PostgreSQL Master IP address</span></i></p><br /><p style="margin-left:72pt;"><i><span style="font-size:9pt;font-family:Cambria,serif;">host replication replica 10.0.3.4/32 md5</span></i></p><br /><p style="margin-left:72pt;"><i><span style="font-size:9pt;font-family:Cambria,serif;"># PostgreSQL SLave IP address</span></i></p><br /><p style="margin-left:72pt;"><i><span style="font-size:9pt;font-family:Cambria,serif;">host replication replica 10.0.3.5/32 md5</span></i></p><br /><p> </p><br /><p>Slave Node Parameters:</p><br /><p> <i><span style="font-size:9pt;font-family:Cambria,serif;">wal_level = hot_standby <br /></span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> synchronous_commit = local</span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> max_wal_senders = 2</span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> wal_keep_segments = 10</span></i></p><br /><p><i><span style="font-size:9pt;font-family:Cambria,serif;"> synchronous_standby_names = 'UWPGRECLUS2'</span></i></p><br /><p> <i><span style="font-size:9pt;font-family:Cambria,serif;">hot_standby = on</span></i></p><br /><p> </p><br /><p></p><br /><p><b>Replication is properly happening from master to slave. But I am not able to switchover between the nodes.</b></p><br /><p> </p><br /><p>Can anyone please guide me in this regard?</p><br /><p> </p><br /><p> </p><br /><p><span style="font-size:10pt;color:#004080;">Thanks & Regards,</span><span style="font-size:12pt;font-family:"Times New Roman",serif;"><br /><br /><br /></span><b><span style="font-size:10pt;font-family:"Trebuchet MS",sans-serif;color:#004080;">Sunil N Shinde</span></b><span style="font-size:12pt;font-family:"Times New Roman",serif;"><br /><br /><br /></span><b><span style="font-size:10pt;font-family:"Trebuchet MS",sans-serif;color:#004080;">Assistant Manager – DBA</span></b><span style="font-size:12pt;font-family:"Times New Roman",serif;"><br /><br /><br /></span><b><span style="font-size:10pt;font-family:"Trebuchet MS",sans-serif;color:#004080;">Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .</span></b><span style="font-size:12pt;font-family:"Times New Roman",serif;"><br /><br /><br /></span><b><span style="font-size:10pt;font-family:"Trebuchet MS",sans-serif;color:#004080;">Mob:- <span>9819002393</span></span></b><span style="font-size:12pt;font-family:"Times New Roman",serif;"></span></p><br /><p> </p><br /></div><br /></div><br /><br /></blockquote></div>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres streaming replication for HA
@ 2017-09-08 07:07 Sunil N Shinde <Sunil.Shinde@mastek.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 2 replies; 8+ messages in thread
From: Sunil N Shinde @ 2017-09-08 07:07 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin
Hi Samed,
Thanks for the quick reply.
I am trying it manually with below step:
1. Shut down the primary node
2. Creating trigger file on standby node before promoting it to primay.
3. "recovery_target_timline='latest'” adding this parameter in recovery file on standby and restarting it.
On current standby :
psql -p 5432 -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
But on Current Primary:
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)
Thanks & Regards,
Sunil N Shinde
From: Samed YILDIRIM [mailto:samed@reddoc.net]
Sent: 08 September 2017 12:22
To: pgsql-admin@postgresql.org; Sunil N Shinde <Sunil.Shinde@mastek.com>
Subject: Re: [ADMIN] postgres streaming replication for HA
Hi Shinde,
Do you trying swithover manually? Or do you use any manager or script to handle switchover operation?
Best regards.
--
Yandex.Mail mobil uygulamasından gönderildi
08:51, 8 Eylül 2017, Sunil N Shinde <sunil.shinde@mastek.com<mailto:sunil.shinde@mastek.com>>:
Hi,
I am forming 2 node cluster of streaming replication for HA. I could able to succeed with the replication process.
But I am not able to complete the “switchover”.
Version : postgresql-9.6
OS :RHEL7
Master Node Parameters:
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.6/data/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
pb_hba.conf
# Localhost
host replication replica 127.0.0.1/32 md5
# PostgreSQL Master IP address
host replication replica 10.0.3.4/32 md5
# PostgreSQL SLave IP address
host replication replica 10.0.3.5/32 md5
Slave Node Parameters:
wal_level = hot_standby
synchronous_commit = local
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
hot_standby = on
Replication is properly happening from master to slave. But I am not able to switchover between the nodes.
Can anyone please guide me in this regard?
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres streaming replication for HA
@ 2017-09-08 08:21 Sumeet Shukla <sumeet.k.shukla@gmail.com>
parent: Sunil N Shinde <Sunil.Shinde@mastek.com>
1 sibling, 0 replies; 8+ messages in thread
From: Sumeet Shukla @ 2017-09-08 08:21 UTC (permalink / raw)
To: Sunil N Shinde <Sunil.Shinde@mastek.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin
Hi Guys,
Even I have been trying to implement the automatic failover using a script
in failover_command of pgpool-II
I've setup streaming replication using replication slots and 2 separate
pgpool servers (primary and standby).
The postgresql failover works sometime and doesn't work sometimes. On
further analysis it seem to be an issue with the systemctl utility in
centos7 i.e., even when the postgres services are up and running fine, the
systemctl shows the postgresql service in failed state and systemctl is not
reliably starting/stopping/restarting the pstgresql service.
Any insight here on how to fix the issue and make the HA environment
reliable would be highly appreciated.
Thanks & Regards,
Sumeet Shukla
On Fri, Sep 8, 2017 at 12:37 PM, Sunil N Shinde <Sunil.Shinde@mastek.com>
wrote:
> Hi Samed,
>
>
>
> Thanks for the quick reply.
>
>
>
> I am trying it manually with below step:
>
>
>
> 1. Shut down the primary node
>
> 2. Creating trigger file on standby node before promoting it to
> primay.
>
> 3. "recovery_target_timline='latest'” adding this parameter in
> recovery file on standby and restarting it.
>
>
>
> On current standby :
>
> psql -p 5432 -c "select pg_is_in_recovery();"
>
> pg_is_in_recovery
>
> -------------------
>
> t
>
> (1 row)
>
>
>
>
>
> But on Current Primary:
>
> psql -c "select application_name, state, sync_priority, sync_state from
> pg_stat_replication;"
>
> application_name | state | sync_priority | sync_state
>
> ------------------+-------+---------------+------------
>
> (0 rows)
>
>
>
>
>
> Thanks & Regards,
> *Sunil N Shinde*
>
>
>
> *From:* Samed YILDIRIM [mailto:samed@reddoc.net]
> *Sent:* 08 September 2017 12:22
> *To:* pgsql-admin@postgresql.org; Sunil N Shinde <Sunil.Shinde@mastek.com>
> *Subject:* Re: [ADMIN] postgres streaming replication for HA
>
>
>
> Hi Shinde,
>
>
>
> Do you trying swithover manually? Or do you use any manager or script to
> handle switchover operation?
>
>
>
> Best regards.
>
>
> --
> Yandex.Mail mobil uygulamasından gönderildi
>
> 08:51, 8 Eylül 2017, Sunil N Shinde <sunil.shinde@mastek.com>:
>
>
>
>
>
>
>
>
>
>
> Hi,
>
>
>
>
>
>
>
> I am forming 2 node cluster of streaming replication for HA. I could able
> to succeed with the replication process.
>
>
>
> But I am not able to complete the “*switchover*”.
>
>
>
>
>
>
>
> Version : postgresql-9.6
>
>
>
> OS :RHEL7
>
>
>
>
>
>
>
> Master Node Parameters:
>
>
>
> *wal_level = hot_standby *
>
>
>
> * synchronous_commit =
> local*
>
>
>
> * archive_mode = on *
>
>
>
> * archive_command = 'cp %p
> /var/lib/pgsql/9.6/data/archive/%f' *
>
>
>
> * max_wal_senders = 2*
>
>
>
> * wal_keep_segments = 10*
>
>
>
> * synchronous_standby_names
> = 'UWPGRECLUS2'*
>
>
>
>
>
>
>
> pb_hba.conf
>
>
>
> *# Localhost*
>
>
>
> *host replication replica 127.0.0.1/32
> <http://127.0.0.1/32; md5 *
>
>
>
> *# PostgreSQL Master IP address*
>
>
>
> *host replication replica 10.0.3.4/32
> <http://10.0.3.4/32; md5*
>
>
>
> *# PostgreSQL SLave IP address*
>
>
>
> *host replication replica 10.0.3.5/32
> <http://10.0.3.5/32; md5*
>
>
>
>
>
>
>
> Slave Node Parameters:
>
>
>
> *wal_level = hot_standby *
>
>
>
> * synchronous_commit =
> local*
>
>
>
> * max_wal_senders = 2*
>
>
>
> * wal_keep_segments = 10*
>
>
>
> * synchronous_standby_names
> = 'UWPGRECLUS2'*
>
>
>
> *hot_standby = on*
>
>
>
>
>
>
>
>
>
> *Replication is properly happening from master to slave. But I am not able
> to switchover between the nodes.*
>
>
>
>
>
>
>
> Can anyone please guide me in this regard?
>
>
>
>
>
>
>
>
>
>
>
> Thanks & Regards,
>
>
> *Sunil N Shinde*
>
>
> *Assistant Manager – DBA*
>
>
> *Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape,
> Navi Mumbai-400710 .*
>
>
> *Mob:- 9819002393*
>
>
>
>
>
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres streaming replication for HA
@ 2017-09-08 08:38 Sunil N Shinde <Sunil.Shinde@mastek.com>
parent: Sunil N Shinde <Sunil.Shinde@mastek.com>
1 sibling, 1 reply; 8+ messages in thread
From: Sunil N Shinde @ 2017-09-08 08:38 UTC (permalink / raw)
To: Sunil N Shinde <Sunil.Shinde@mastek.com>; Samed YILDIRIM <samed@reddoc.net>; pgsql-admin
Hi Samed,
Please find below the log details also :
cp: cannot stat ‘/var/lib/pgsql/9.6/data/archive/000000030000000000000013’: No such file or directory
< 2017-09-08 08:36:25.852 UTC > LOG: started streaming WAL from primary at 0/13000000 on timeline 3
< 2017-09-08 08:36:25.852 UTC > FATAL: could not receive data from WAL stream: ERROR: requested starting point 0/13000000 is ahead of the WAL flush position of this server 0/1102FFF0
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Sunil N Shinde
Sent: 08 September 2017 12:38
To: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgres streaming replication for HA
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing;
Feedback<http://aka.ms/SafetyTipsFeedback;
Hi Samed,
Thanks for the quick reply.
I am trying it manually with below step:
1. Shut down the primary node
2. Creating trigger file on standby node before promoting it to primay.
3. "recovery_target_timline='latest'” adding this parameter in recovery file on standby and restarting it.
On current standby :
psql -p 5432 -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
But on Current Primary:
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)
Thanks & Regards,
Sunil N Shinde
From: Samed YILDIRIM [mailto:samed@reddoc.net]
Sent: 08 September 2017 12:22
To: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>; Sunil N Shinde <Sunil.Shinde@mastek.com<mailto:Sunil.Shinde@mastek.com>>
Subject: Re: [ADMIN] postgres streaming replication for HA
Hi Shinde,
Do you trying swithover manually? Or do you use any manager or script to handle switchover operation?
Best regards.
--
Yandex.Mail mobil uygulamasından gönderildi
08:51, 8 Eylül 2017, Sunil N Shinde <sunil.shinde@mastek.com<mailto:sunil.shinde@mastek.com>>:
Hi,
I am forming 2 node cluster of streaming replication for HA. I could able to succeed with the replication process.
But I am not able to complete the “switchover”.
Version : postgresql-9.6
OS :RHEL7
Master Node Parameters:
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.6/data/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
pb_hba.conf
# Localhost
host replication replica 127.0.0.1/32 md5
# PostgreSQL Master IP address
host replication replica 10.0.3.4/32 md5
# PostgreSQL SLave IP address
host replication replica 10.0.3.5/32 md5
Slave Node Parameters:
wal_level = hot_standby
synchronous_commit = local
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
hot_standby = on
Replication is properly happening from master to slave. But I am not able to switchover between the nodes.
Can anyone please guide me in this regard?
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres streaming replication for HA
@ 2017-09-08 10:54 Samed YILDIRIM <samed@reddoc.net>
parent: Sunil N Shinde <Sunil.Shinde@mastek.com>
0 siblings, 2 replies; 8+ messages in thread
From: Samed YILDIRIM @ 2017-09-08 10:54 UTC (permalink / raw)
To: Sunil N Shinde <sunil.shinde@mastek.com>; pgsql-admin
<div>Hi Shinde,</div><div> </div><div>Could you please share recovery.conf file?</div><div> </div><div>Logs means that xlog file (000000030000000000000013) which is required by your standby has been removed before streamed to standby. To avoid this kind of situations you can use replication slot or you should set wal_keep_segment high enough.</div><div> </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>08.09.2017, 11:40, "Sunil N Shinde" <sunil.shinde@mastek.com>:</div><blockquote type="cite"><div link="blue" vlink="purple" lang="EN-IN"><div><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">Hi Samed,</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">Please find below the log details also :</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="font-family:calibri,sans-serif;font-size:11pt;">cp: cannot stat ‘/var/lib/pgsql/9.6/data/archive/<span>000000030000000000000013</span>’: No such file or directory</span></p><p><span style="font-family:calibri,sans-serif;font-size:11pt;">< <span>2017-09-08 08</span>:36:25.852 UTC > LOG: started streaming WAL from primary at 0/<span>13000000</span> on timeline 3</span></p><p><span style="font-family:calibri,sans-serif;font-size:11pt;">< <span>2017-09-08 08</span>:36:25.852 UTC > FATAL: could not receive data from WAL stream: ERROR: requested starting point 0/<span>13000000</span> is ahead of the WAL flush position of this server 0/1102FFF0</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><div><p><span style="color:#004080;font-family:calibri,sans-serif;font-size:10pt;">Thanks & Regards,</span><span style="color:#1f497d;"> </span><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Sunil N Shinde</span></strong><span style="color:#1f497d;"> </span><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Assistant Manager – DBA</span></strong><span style="color:#1f497d;"> </span><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .</span></strong><span style="color:#1f497d;"> </span><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Mob:- <span>9819002393</span></span></strong></p></div><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><div><div style="border:none;border-top:solid #E1E1E1 1pt;padding:3pt 0cm 0cm 0cm;"><p><strong><span lang="EN-US" style="font-family:calibri,sans-serif;font-size:11pt;">From:</span></strong><span lang="EN-US" style="font-family:calibri,sans-serif;font-size:11pt;"> <a href="mailto:pgsql-admin-owner@postgresql.org">pgsql-admin-owner@postgresql.org</a> [mailto:<a href="mailto:pgsql-admin-owner@postgresql.org">pgsql-admin-owner@postgresql.org</a>] <strong>On Behalf Of </strong>Sunil N Shinde<br /><strong>Sent:</strong> 08 September 2017 12:38<br /><strong>To:</strong> Samed YILDIRIM <<a href="mailto:samed@reddoc.net">samed@reddoc.net</a>>; <a href="mailto:pgsql-admin@postgresql.org">pgsql-admin@postgresql.org</a><br /><strong>Subject:</strong> Re: [ADMIN] postgres streaming replication for HA</span></p></div></div><p> </p><table style="width:100%;" cellspacing="0" cellpadding="0" align="left" width="100%" border="0"><tbody><tr><td style="background:#910A19;padding:5.25pt 1.5pt 5.25pt 1.5pt;"> </td><td style="width:100%;background:#FDF2F4;padding:5.25pt 3.75pt 5.25pt 11.25pt;" width="100%"><div><p><span style="color:#212121;font-family:segoe ui,sans-serif;font-size:9pt;">This sender failed our fraud detection checks and may not be who they appear to be. Learn about <a href="http://aka.ms/LearnAboutSpoofing">spoofing</a></span></p></div></t... style="width:56.25pt;background:#FDF2F4;padding:5.25pt 3.75pt 5.25pt 3.75pt;" width="60"><p><span style="color:#212121;font-family:segoe ui,sans-serif;font-size:9pt;"><a href="http://aka.ms/SafetyTipsFeedback">Feedback</a></span></p></td></tr... style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">Hi Samed,</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">Thanks for the quick reply.</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">I am trying it manually with below step:</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"><span>1.<span style="font:7pt "Times New Roman";"> </span></span></span><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">Shut down the primary node</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"><span>2.<span style="font:7pt "Times New Roman";"> </span></span></span><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">Creating trigger file on standby node before promoting it to primay.</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"><span>3.<span style="font:7pt "Times New Roman";"> </span></span></span><span style="background:white;color:#222222;font-family:trebuchet ms,sans-serif;font-size:10.5pt;">"recovery_target_timline='latest'” adding this parameter in recovery file on standby and restarting it.</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">On current standby :</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">psql -p 5432 -c "select pg_is_in_recovery();"</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">pg_is_in_recovery</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">-------------------</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">t</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">(1 row)</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">But on Current Primary:</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">application_name | state | sync_priority | sync_state</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">------------------+-------+---------------+------------</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;">(0 rows)</span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><span style="color:#004080;font-family:calibri,sans-serif;font-size:10pt;">Thanks & Regards,</span><span style="color:#1f497d;"> </span><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Sunil N Shinde</span></strong><span style="color:#1f497d;"> </span></p><p><span style="color:#1f497d;font-family:calibri,sans-serif;font-size:11pt;"> </span></p><p><strong><span lang="EN-US" style="font-family:calibri,sans-serif;font-size:11pt;">From:</span></strong><span lang="EN-US" style="font-family:calibri,sans-serif;font-size:11pt;"> Samed YILDIRIM [<a href="mailto:samed@reddoc.net">mailto:samed@reddoc.net</a>]<br /><strong>Sent:</strong> 08 September 2017 12:22<br /><strong>To:</strong> <a href="mailto:pgsql-admin@postgresql.org">pgsql-admin@postgresql.org</a>; Sunil N Shinde <<a href="mailto:Sunil.Shinde@mastek.com">Sunil.Shinde@mastek.com</a>><br /><strong>Subject:</strong> Re: [ADMIN] postgres streaming replication for HA</span></p><p> </p><p>Hi Shinde,</p><div><p> </p></div><div><p>Do you trying swithover manually? Or do you use any manager or script to handle switchover operation?</p></div><div><p> </p></div><div><p>Best regards.</p></div><div><p><br />--<br />Yandex.Mail mobil uygulamasından gönderildi<br /><br />08:51, 8 Eylül 2017, Sunil N Shinde <<a href="mailto:sunil.shinde@mastek.com">sunil.shinde@mastek.com</a>>:</p><blockquote style="margin-top:5pt;margin-bottom:5pt;"><p style="margin-bottom:12pt;"><br /><br /><br /> </p><div><p> </p><div><p> </p><p>Hi,</p><p> </p><p> </p><p> </p><p>I am forming 2 node cluster of streaming replication for HA. I could able to succeed with the replication process.</p><p> </p><p>But I am not able to complete the “<strong>switchover</strong>”.</p><p> </p><p> </p><p> </p><p>Version : postgresql-9.6</p><p> </p><p>OS :RHEL7</p><p> </p><p> </p><p> </p><p>Master Node Parameters:</p><p> </p><p> <em><span style="font-family:cambria,serif;font-size:9pt;">wal_level = hot_standby </span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> synchronous_commit = local</span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> archive_mode = on </span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> archive_command = 'cp %p /var/lib/pgsql/9.6/data/archive/%f' </span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> max_wal_senders = 2</span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> wal_keep_segments = 10</span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> synchronous_standby_names = 'UWPGRECLUS2'</span></em></p><p> </p><p> </p><p> </p><p> pb_hba.conf </p><p> </p><p style="margin-left:54pt;"><em><span style="font-family:cambria,serif;font-size:9pt;"># Localhost</span></em></p><p> </p><p style="margin-left:72pt;"><em><span style="font-family:cambria,serif;font-size:9pt;">host replication replica 127.0.0.1/32 md5 </span></em></p><p> </p><p style="margin-left:72pt;"><em><span style="font-family:cambria,serif;font-size:9pt;"># PostgreSQL Master IP address</span></em></p><p> </p><p style="margin-left:72pt;"><em><span style="font-family:cambria,serif;font-size:9pt;">host replication replica 10.0.3.4/32 md5</span></em></p><p> </p><p style="margin-left:72pt;"><em><span style="font-family:cambria,serif;font-size:9pt;"># PostgreSQL SLave IP address</span></em></p><p> </p><p style="margin-left:72pt;"><em><span style="font-family:cambria,serif;font-size:9pt;">host replication replica 10.0.3.5/32 md5</span></em></p><p> </p><p> </p><p> </p><p>Slave Node Parameters:</p><p> </p><p> <em><span style="font-family:cambria,serif;font-size:9pt;">wal_level = hot_standby </span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> synchronous_commit = local</span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> max_wal_senders = 2</span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> wal_keep_segments = 10</span></em></p><p> </p><p><em><span style="font-family:cambria,serif;font-size:9pt;"> synchronous_standby_names = 'UWPGRECLUS2'</span></em></p><p> </p><p> <em><span style="font-family:cambria,serif;font-size:9pt;">hot_standby = on</span></em></p><p> </p><p> </p><p> </p><p> </p><p><strong>Replication is properly happening from master to slave. But I am not able to switchover between the nodes.</strong></p><p> </p><p> </p><p> </p><p>Can anyone please guide me in this regard?</p><p> </p><p> </p><p> </p><p> </p><p> </p><p><span style="color:#004080;font-size:10pt;">Thanks & Regards,</span><br /><br /><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Sunil N Shinde</span></strong><br /><br /><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Assistant Manager – DBA</span></strong><br /><br /><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .</span></strong><br /><br /><br /><strong><span style="color:#004080;font-family:trebuchet ms,sans-serif;font-size:10pt;">Mob:- <span>9819002393</span></span></strong></p><p> </p><p> </p><p> </p></div><p> </p></div><p style="margin-bottom:12pt;"> </p></blockquote></div></div></div></div></blockquote>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres streaming replication for HA
@ 2017-09-08 11:11 Sunil N Shinde <Sunil.Shinde@mastek.com>
parent: Samed YILDIRIM <samed@reddoc.net>
1 sibling, 0 replies; 8+ messages in thread
From: Sunil N Shinde @ 2017-09-08 11:11 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin
Hi Samed,
Thanks a lot. Will try this.
Please find below the contents of recovery.conf file.
standby_mode = 'on'
primary_conninfo = 'host=*.*.*.* port=5432 user=replica password=***** application_name=****'
restore_command = 'cp /var/lib/pgsql/9.6/data/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
recovery_target_timeline = 'latest'
I have tried below steps. Can you please tell me whether this is right way?
1. Brought down master node.
2. Copied missing archive logs from master to slave.
3. On slave --- touch /tmp/postgresql.trigger.5432 --- Hence it has became NEW MASTER.
4. On old master --- rm /tmp/postgresql.trigger.5432 & addred recovery.conf file with old slave’s connectinfo --- Hence it has become NEW SLAVE.
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
From: Samed YILDIRIM [mailto:samed@reddoc.net]
Sent: 08 September 2017 16:25
To: Sunil N Shinde <Sunil.Shinde@mastek.com>; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgres streaming replication for HA
Hi Shinde,
Could you please share recovery.conf file?
Logs means that xlog file (000000030000000000000013) which is required by your standby has been removed before streamed to standby. To avoid this kind of situations you can use replication slot or you should set wal_keep_segment high enough.
Best regards.
Samed YILDIRIM
08.09.2017, 11:40, "Sunil N Shinde" <sunil.shinde@mastek.com<mailto:sunil.shinde@mastek.com>>:
Hi Samed,
Please find below the log details also :
cp: cannot stat ‘/var/lib/pgsql/9.6/data/archive/000000030000000000000013’: No such file or directory
< 2017-09-08 08:36:25.852 UTC > LOG: started streaming WAL from primary at 0/13000000 on timeline 3
< 2017-09-08 08:36:25.852 UTC > FATAL: could not receive data from WAL stream: ERROR: requested starting point 0/13000000 is ahead of the WAL flush position of this server 0/1102FFF0
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> [mailto:pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org>] On Behalf Of Sunil N Shinde
Sent: 08 September 2017 12:38
To: Samed YILDIRIM <samed@reddoc.net<mailto:samed@reddoc.net>>; pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] postgres streaming replication for HA
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing;
Feedback<http://aka.ms/SafetyTipsFeedback;
Hi Samed,
Thanks for the quick reply.
I am trying it manually with below step:
1. Shut down the primary node
2. Creating trigger file on standby node before promoting it to primay.
3. "recovery_target_timline='latest'” adding this parameter in recovery file on standby and restarting it.
On current standby :
psql -p 5432 -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
But on Current Primary:
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)
Thanks & Regards,
Sunil N Shinde
From: Samed YILDIRIM [mailto:samed@reddoc.net]
Sent: 08 September 2017 12:22
To: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>; Sunil N Shinde <Sunil.Shinde@mastek.com<mailto:Sunil.Shinde@mastek.com>>
Subject: Re: [ADMIN] postgres streaming replication for HA
Hi Shinde,
Do you trying swithover manually? Or do you use any manager or script to handle switchover operation?
Best regards.
--
Yandex.Mail mobil uygulamasından gönderildi
08:51, 8 Eylül 2017, Sunil N Shinde <sunil.shinde@mastek.com<mailto:sunil.shinde@mastek.com>>:
Hi,
I am forming 2 node cluster of streaming replication for HA. I could able to succeed with the replication process.
But I am not able to complete the “switchover”.
Version : postgresql-9.6
OS :RHEL7
Master Node Parameters:
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.6/data/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
pb_hba.conf
# Localhost
host replication replica 127.0.0.1/32 md5
# PostgreSQL Master IP address
host replication replica 10.0.3.4/32 md5
# PostgreSQL SLave IP address
host replication replica 10.0.3.5/32 md5
Slave Node Parameters:
wal_level = hot_standby
synchronous_commit = local
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
hot_standby = on
Replication is properly happening from master to slave. But I am not able to switchover between the nodes.
Can anyone please guide me in this regard?
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgres streaming replication for HA
@ 2017-09-08 11:22 Sunil N Shinde <Sunil.Shinde@mastek.com>
parent: Samed YILDIRIM <samed@reddoc.net>
1 sibling, 0 replies; 8+ messages in thread
From: Sunil N Shinde @ 2017-09-08 11:22 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin
Hi Samed,
This is the output of ps –ef | grep post, on slave node , after I followed the steps given in my last email.
00:00:00 -bash
00:00:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
00:00:00 postgres: logger process
00:00:00 postgres: startup process recovering 000000040000000000000014
00:00:00 postgres: checkpointer process
00:00:00 postgres: writer process
00:00:00 postgres: stats collector process
00:00:01 postgres: wal receiver process streaming 0/140030E8
00:00:00 ps -ef
00:00:00 grep --color=auto post
Thanks,
Sunil
From: Sunil N Shinde
Sent: 08 September 2017 16:41
To: 'Samed YILDIRIM' <samed@reddoc.net>; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] postgres streaming replication for HA
Hi Samed,
Thanks a lot. Will try this.
Please find below the contents of recovery.conf file.
standby_mode = 'on'
primary_conninfo = 'host=*.*.*.* port=5432 user=replica password=***** application_name=****'
restore_command = 'cp /var/lib/pgsql/9.6/data/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
recovery_target_timeline = 'latest'
I have tried below steps. Can you please tell me whether this is right way?
1. Brought down master node.
2. Copied missing archive logs from master to slave.
3. On slave --- touch /tmp/postgresql.trigger.5432 --- Hence it has became NEW MASTER.
4. On old master --- rm /tmp/postgresql.trigger.5432 & addred recovery.conf file with old slave’s connectinfo --- Hence it has become NEW SLAVE.
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
From: Samed YILDIRIM [mailto:samed@reddoc.net]
Sent: 08 September 2017 16:25
To: Sunil N Shinde <Sunil.Shinde@mastek.com<mailto:Sunil.Shinde@mastek.com>>; pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] postgres streaming replication for HA
Hi Shinde,
Could you please share recovery.conf file?
Logs means that xlog file (000000030000000000000013) which is required by your standby has been removed before streamed to standby. To avoid this kind of situations you can use replication slot or you should set wal_keep_segment high enough.
Best regards.
Samed YILDIRIM
08.09.2017, 11:40, "Sunil N Shinde" <sunil.shinde@mastek.com<mailto:sunil.shinde@mastek.com>>:
Hi Samed,
Please find below the log details also :
cp: cannot stat ‘/var/lib/pgsql/9.6/data/archive/000000030000000000000013’: No such file or directory
< 2017-09-08 08:36:25.852 UTC > LOG: started streaming WAL from primary at 0/13000000 on timeline 3
< 2017-09-08 08:36:25.852 UTC > FATAL: could not receive data from WAL stream: ERROR: requested starting point 0/13000000 is ahead of the WAL flush position of this server 0/1102FFF0
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
From: pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org> [mailto:pgsql-admin-owner@postgresql.org<mailto:pgsql-admin-owner@postgresql.org>] On Behalf Of Sunil N Shinde
Sent: 08 September 2017 12:38
To: Samed YILDIRIM <samed@reddoc.net<mailto:samed@reddoc.net>>; pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] postgres streaming replication for HA
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing;
Feedback<http://aka.ms/SafetyTipsFeedback;
Hi Samed,
Thanks for the quick reply.
I am trying it manually with below step:
1. Shut down the primary node
2. Creating trigger file on standby node before promoting it to primay.
3. "recovery_target_timline='latest'” adding this parameter in recovery file on standby and restarting it.
On current standby :
psql -p 5432 -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
But on Current Primary:
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)
Thanks & Regards,
Sunil N Shinde
From: Samed YILDIRIM [mailto:samed@reddoc.net]
Sent: 08 September 2017 12:22
To: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>; Sunil N Shinde <Sunil.Shinde@mastek.com<mailto:Sunil.Shinde@mastek.com>>
Subject: Re: [ADMIN] postgres streaming replication for HA
Hi Shinde,
Do you trying swithover manually? Or do you use any manager or script to handle switchover operation?
Best regards.
--
Yandex.Mail mobil uygulamasından gönderildi
08:51, 8 Eylül 2017, Sunil N Shinde <sunil.shinde@mastek.com<mailto:sunil.shinde@mastek.com>>:
Hi,
I am forming 2 node cluster of streaming replication for HA. I could able to succeed with the replication process.
But I am not able to complete the “switchover”.
Version : postgresql-9.6
OS :RHEL7
Master Node Parameters:
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.6/data/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
pb_hba.conf
# Localhost
host replication replica 127.0.0.1/32 md5
# PostgreSQL Master IP address
host replication replica 10.0.3.4/32 md5
# PostgreSQL SLave IP address
host replication replica 10.0.3.5/32 md5
Slave Node Parameters:
wal_level = hot_standby
synchronous_commit = local
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'UWPGRECLUS2'
hot_standby = on
Replication is properly happening from master to slave. But I am not able to switchover between the nodes.
Can anyone please guide me in this regard?
Thanks & Regards,
Sunil N Shinde
Assistant Manager – DBA
Mastek Ltd | Mastek Millennium Center, Millennium Business Park, Mahape, Navi Mumbai-400710 .
Mob:- 9819002393
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2017-09-08 11:22 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-09-08 05:49 postgres streaming replication for HA Sunil N Shinde <Sunil.Shinde@mastek.com>
2017-09-08 06:51 ` Samed YILDIRIM <samed@reddoc.net>
2017-09-08 07:07 ` Sunil N Shinde <Sunil.Shinde@mastek.com>
2017-09-08 08:21 ` Sumeet Shukla <sumeet.k.shukla@gmail.com>
2017-09-08 08:38 ` Sunil N Shinde <Sunil.Shinde@mastek.com>
2017-09-08 10:54 ` Samed YILDIRIM <samed@reddoc.net>
2017-09-08 11:11 ` Sunil N Shinde <Sunil.Shinde@mastek.com>
2017-09-08 11:22 ` Sunil N Shinde <Sunil.Shinde@mastek.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