Re: [ADMIN] postgres streaming replication for HA

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

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 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

Feedback

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            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