Thread: Streaming replication failover process - Pgsql 9.2

On 7/10/2016 2:19 PM, Patrick B wrote:
> 1. When the connection comes back, will the master and slave work as
> expected? The streaming replication should be ok?

as long as you have sufficient WAL available it should recover fine.
you might have to restart the slave to get it to reconnect.

>
> 2. As the master will be down, I don't wanna slave turns into a master
> by Failover. The trigger_file line on recovery.conf on the slave
> server is commented, so should be ok here right? The slave will be
> still a slave once master is down....
>

failover is a function of whatever cluster management software you use,
postgres won't failover on its own.


--
john r pierce, recycling bits in santa cruz

On 07/10/2016 02:19 PM, Patrick B wrote:
> Hi all,
>
> There will be a network maintenance at the company where my servers are...
>
> I've got one master and one slave server, running PostgreSQL 9.2.
>
> As the network will be down, the internet won't be working as well as
> the intranet. Both servers won't be able to communicate each other. Not
> by streaming replication and wal_files too.
>
> That should take at most 10 minutes.
>
> 1. When the connection comes back, will the master and slave work as
> expected? The streaming replication should be ok?

Assuming you have wal_keep_segments set high enough to keep the master
from recycling the WAL files before the network comes up again.

>
> 2. As the master will be down, I don't wanna slave turns into a master
> by Failover. The trigger_file line on recovery.conf on the slave server
> is commented, so should be ok here right? The slave will be still a
> slave once master is down....
>
> Thanks!
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com

Am 10.07.2016 um 23:19 schrieb Patrick B:
> Hi all,
>
> There will be a network maintenance at the company where my servers are...
>
> I've got one master and one slave server, running PostgreSQL 9.2.
>
> As the network will be down, the internet won't be working as well as
> the intranet. Both servers won't be able to communicate each other.
> Not by streaming replication and wal_files too.
>
> That should take at most 10 minutes.
>
> 1. When the connection comes back, will the master and slave work as
> expected? The streaming replication should be ok?
if the master holds all needed WAL's there should be no problem.

You can ensure that with wal_keep_segments, or, in newer version, via
replication slots.

1. When the connection comes back, will the master and slave work as expected? The streaming replication should be ok?

if the master holds all needed WAL's there should be no problem.

You can ensure that with wal_keep_segments, or, in newer version, via replication slots.

or via an external WAL archive, that the master writes, and the slave has access to.

-- 
john r pierce, recycling bits in santa cruz

On 07/10/2016 04:28 PM, Patrick B wrote:
> archive_command = 'cp %p /var/lib/pgsql/archive/%f'

This would be where?

And does the corresponding restore_command point to the same place?

>
>
> That would be ok right guys?
>
> I will also setup wal_keep_segments to 512
>

The WAL segments kept would be dependent the number of WAL segments your
database generates on average over the worse case downtime interval plus
a CYA buffer.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/10/2016 4:28 PM, Patrick B wrote:
>
> archive_command = 'cp %p /var/lib/pgsql/archive/%f'
>
>
> That would be ok right guys?
>


normally, you want to ship your WAL archives to a NFS server or
something similar, which the master and all the slaves can read.

--
john r pierce, recycling bits in santa cruz

On 7/10/2016 8:51 PM, Patrick B wrote:
> what if the network goes down?

that WAL server could be located in the same data center as the master
database server.  if your local area network goes down, well, you're
probably in a world of hurt.

if the wide area network is mission critical, it would be smart to have
multiple resilient network paths



--
john r pierce, recycling bits in santa cruz

On 07/10/2016 07:17 PM, Patrick B wrote:
> If the master server can't send the wal_files through the slaves,
> shouldn't the wal_files be in "background" waiting to be delivered?

Short version, yes, assuming you are talking about archiving the WAL
files somewhere  and assuming there is sufficient space for the quantity
of WAL files stored on what ever 'device' you are storing them on.

Long version and, I would say, required reading:

https://www.postgresql.org/docs/9.2/static/high-availability.html

>
> Otherwise what's the purpose of them? If a network fails I'd loose those
> files?
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com