Re: - PostgreSQL Replication Types

Hi Will,

On 12/17/15 10:17 AM, Will McCormick wrote:
> I inherited a 9.1 replication environment
>
> Few basic questions that I can't find clear answers / clarifications for
> if possible:
>
> 3 types of replication in 9.1 I've read about from the offical docs:
>
> 1) warm standby

This is a standby which is applying WAL segments from the master (via
recovery_command (log shipping) or streaming replication.

> 2) hot standby

Just like a warm standby but adds the ability to do read-only queries
(and the master must know it is a hot standby).

> 3) streaming replication

Another way to deliver WAL segments from the master, but the advantage
over recovery_command log shipping is that transactions are applied as
soon as they happen on the master, rather than when a WAL segment is
pushed from the master to the archive.

> I'm using streaming replication I believe, the only indication I have
> is that there is the primary_conninfo on the standby. Is this the only
> indication?
>
> Is it possible to get if using streaming replication under normal
> operations?
>
> /cp: cannot stat `/opt/postgres/9.1/archive/000000070000000F00000057':
> No such file or directory/
>
> /cp: cannot stat `/opt/postgres/9.1/archive/000000070000000F00000057':
> No such file or directory/
>
> /LOG: streaming replication successfully connected to primary/
>
> /FATAL: could not receive data from WAL stream: FATAL: requested WAL
> segment 000000070000000F00000057 has already been removed/

What this means is your stannby has been out-of-date for some time. The
WAL segment it needs has been expired from both the archive
(/opt/postgres/9.1/archive) and the master's pg_xlog directory.

Your only option now (unless you can find to required WAL segments
somewhere) is to do a new backup using pg_basebackup or some other
mechanism to bring the standby up to date.

> My understanding is that warm standby and hot standby do log shipping
> and there is a greater window for transactions not to be send to the
> standby because WAL XLOG must be filled.

A hot or warm standby can be maintained with either log shipping or
streaming replication.

> Whereas Streaming replication basically sends at the transaction level?

But yes, this is the advantage of streaming replication.

I have written a tutorial that covers setting up a hot standby with or
without streaming replication using pgBackRest:

http://www.pgbackrest.org/user-guide.html#replication

You can replace backup/restore/archive with other methods but the
principal remains the same. The tutorial is for 9.4 but should work
equally well with 9.1.

--
-David
david(at)pgmasters(dot)net