Re: Cluster solution for Postgresql 9.5

On Wed, Oct 5, 2016 at 9:07 AM, Poul Kristensen <bcc5226(at)gmail(dot)com> wrote:

> Thanks a lot for a very fast responce. The reason for asking is this video
> of march 2016
> https://www.youtube.com/watch?v=iruaCgeG7qs /Josh Berkin is the speaker.
>
> Josh Berkin talks about the possibility of datafile corruption on the
> Postgres server connected by
> a lot of Docker/Pod(Redhats Kubernetes with etcd to manage the failover)
> images. In my understanding a Postgres cluster will act exactly the same
> way nomatter which way the Postgres is connected.
> So how come that Josh Berkin is worried about databasefile corruption?
>

That's Josh Berkus :)

It is true, if you are using shared disk, the possibility of physical
corruption is definitely an issue. In that case, you really should be:

1) Have your shared disk cluster
2) Have a 3rd, streaming / log-ship replica
3) Take frequent backups (physical)
4) Periodically test-restore those physical backups and take a logical
'pg_dump'

You need to add a logical export in to the infrastructure somewhere in
order to protect against corruption that is replicated.

--Scott

>
>
> TIA !
>
> Poul
>
>
>
>
>
>
>
>
>
> 2016-10-05 14:22 GMT+02:00 Scott Mead <scottm(at)openscg(dot)com>:
>
>>
>>
>> On Wed, Oct 5, 2016 at 7:56 AM, Poul Kristensen <bcc5226(at)gmail(dot)com>
>> wrote:
>>
>>> Hi !
>>>
>>> According to the documentation a shared disk failover is possible as
>>> shown below.
>>> According to discussions on the Internet it is a problem missing 1-2
>>> seconds
>>> in a failover situation.
>>> Does anyone know of the latest news in this case?
>>>
>>
>> I would be very surprised if it were capable of causing that little
>> downtime. It's possible, but you'd have to fail at exactly the right
>> moment. Depending on your network, hardware and other factors, I would say
>> this would more likely take 15 seconds to 1 minute. This would *almost*
>> always be the case when using shared disk failover.
>>
>> There are other strategies for low-downtime failover, but, most of them
>> will be in the same 15 - 60 seconds. The only real scenario would be to
>> use a multi-master solution (no promotion required), but, these have their
>> own issues and may have a performance impact.
>>
>> --Scott
>>
>>
>>
>>
>>>
>>> Comparison of Different Solutions
>>> Shared Disk Failover
>>>
>>> Shared disk failover avoids synchronization overhead by having only one
>>> copy of the database. It uses a single disk array that is shared by
>>> multiple servers. If the main database server fails, the standby server is
>>> able to mount and start the database as though it were recovering from a
>>> database crash. This allows rapid failover with no data loss.
>>>
>>> Shared hardware functionality is common in network storage devices.
>>> Using a network file system is also possible, though care must be taken
>>> that the file system has fullPOSIX behavior (see Section 17.2.2
>>> <https://www.postgresql.org/docs/9.5/static/creating-cluster.html#CREATING-CLUSTER-NFS>).
>>> One significant limitation of this method is that if the shared disk array
>>> fails or becomes corrupt, the primary and standby servers are both
>>> nonfunctional. Another issue is that the standby server should never access
>>> the shared storage while the primary server is running.
>>> TIA !
>>>
>>> Poul
>>>
>>>
>>
>>
>> --
>> --
>> Scott Mead
>> Sr. Architect
>> *OpenSCG <http://openscg.com>*
>> http://openscg.com
>>
>
>
>
> --
> Med venlig hilsen / Best regards
> Poul Kristensen
> Linux-OS/Virtualizationexpert and Oracle DBA
>

--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com>*
http://openscg.com