Re: postgres streaming replication for HA

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(at)reddoc(dot)net]
Sent: 08 September 2017 12:22
To: pgsql-admin(at)postgresql(dot)org; Sunil N Shinde <Sunil(dot)Shinde(at)mastek(dot)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(dot)shinde(at)mastek(dot)com<mailto:sunil(dot)shinde(at)mastek(dot)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