Re: postgres streaming replication for HA

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(dot)Shinde(at)mastek(dot)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(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>:
>
>
>
>
>
>
>
>
>
>
> 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*
>
>
>
>
>
>
>
>
>
>
>
>