Re: Cluster solution for Postgresql 9.5

[Scott Mead <scottm@xxxxxxxxxxx>]

On Wed, Oct 5, 2016 at 9:07 AM, Poul Kristensen <bcc5226@xxxxxxxxx> 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@xxxxxxxxxxx>:

On Wed, Oct 5, 2016 at 7:56 AM, Poul Kristensen <bcc5226@xxxxxxxxx> 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). 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

--

Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

--

--
Scott Mead
Sr. Architect
OpenSCG

http://openscg.com