Re: - PostgreSQL Replication Types

Thanks a ton for the prompt response.

I've read most of this but some it was not clear until we discussed.

Updated with WLM:

On 12/17/2015 07: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
> 2) hot standby
> 3) streaming replication
>
> 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?
>

WLM: I also see streaming replication in the logs.

On standby:

http://www.postgresql.org/docs/9.1/interactive/functions-admin.html
"
pg_last_xlog_receive_location()

Get last transaction log location received and synced to disk by streaming
replication. While streaming replication is in progress this will increase
monotonically. If recovery has completed this will remain static at the
value of the last WAL record received and synced to disk during recovery.
If streaming replication is disabled, or if it has not yet started, the
function returns NULL."

WLM: When I do this on the standby I get an error:

ERROR: recovery is in progress

HINT: WAL control functions cannot be ...

> 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/
>

Assuming above is from standby log, correct? WLM: yes

The cp lines would seem to indicate a restore_command in the standby
recovery.conf, is that the case?:

http://www.postgresql.org/docs/9.1/static/archive-recovery-settings.html

restore_command (string) WLM: Correct

The FATAL indicates that the WAL file has already been recycled on the
master.

WLM: I had read this what confuses me a bit is:

/cp: cannot stat `/opt/postgres/9.1/archive/000000070000000F00000057':
No such file or directory/

Does Streaming replication automatically use Archived WAL files when WAL
XLOG files don't contain a transaction?

We did have wal_keep_segments set to 0. I changed this to 50 but want to
better understand this. Especially the correlation between the Archived
WALs and the XLOG WALs. My guess is the difference between Streaming
replication and the others is very simply that Streaming replication can
read the XLOG WALs as well? So if all the Archived WALs have been shipped
and processed to the Standby then the XLOGs are processed but not shipped?
This meaning at a transaction level "kindof"?

See:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER

"wal_keep_segments (integer)

Specifies the minimum number of past log file segments kept in the
pg_xlog directory, in case a standby server needs to fetch them for
streaming replication. Each segment is normally 16 megabytes. If a standby
server connected to the primary falls behind by more than wal_keep_segments
segments, the primary might remove a WAL segment still needed by the
standby, in which case the replication connection will be terminated.
(However, the standby server can recover by fetching the segment from
archive, if WAL archiving is in use.)

This sets only the minimum number of segments retained in pg_xlog; the
system might need to retain more segments for WAL archival or to recover
from a checkpoint. If wal_keep_segments is zero (the default), the system
doesn't keep any extra segments for standby purposes, so the number of old
WAL segments available to standby servers is a function of the location of
the previous checkpoint and status of WAL archiving. This parameter has no
effect on restartpoints. This parameter can only be set in the
postgresql.conf file or on the server command line.
"

http://www.postgresql.org/docs/9.1/interactive/warm-standby.html#STREAMING-REPLICATION

"If you use streaming replication without file-based continuous archiving,
you have to set wal_keep_segments in the master to a value high enough to
ensure that old WAL segments are not recycled too early, while the standby
might still need them to catch up. If the standby falls behind too much, it
needs to be reinitialized from a new base backup. If you set up a WAL
archive that's accessible from the standby, wal_keep_segments is not
required as the standby can always use the archive to catch up."

> /
> /
>
> 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.
>

Hot versus warm standby refer to whether it is possible to run read only
queries on the standby in the first case or not in the second case.

>
> Whereas Streaming replication basically sends at the transaction level?
>

The difference you are looking for is log shipping versus streaming, where
log shipping moves complete WAL files and streaming streams the same files.

WLM: I still am having trouble with distinction. By the same files do you
mean XLOG WALs?

See here for more detail: WLM: Reading now :)

http://www.postgresql.org/docs/9.1/interactive/high-availability.html

On Thu, Dec 17, 2015 at 10:37 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/17/2015 07: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
>> 2) hot standby
>> 3) streaming replication
>>
>> 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?
>>
>
> On standby:
>
> http://www.postgresql.org/docs/9.1/interactive/functions-admin.html
> "
> pg_last_xlog_receive_location()
>
> Get last transaction log location received and synced to disk by streaming
> replication. While streaming replication is in progress this will increase
> monotonically. If recovery has completed this will remain static at the
> value of the last WAL record received and synced to disk during recovery.
> If streaming replication is disabled, or if it has not yet started, the
> function returns NULL."
>
>
>> 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/
>>
>
> Assuming above is from standby log, correct?
>
> The cp lines would seem to indicate a restore_command in the standby
> recovery.conf, is that the case?:
>
> http://www.postgresql.org/docs/9.1/static/archive-recovery-settings.html
>
> restore_command (string)
>
>
> The FATAL indicates that the WAL file has already been recycled on the
> master.
>
> See:
>
>
> http://www.postgresql.org/docs/9.1/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER
>
> "wal_keep_segments (integer)
>
> Specifies the minimum number of past log file segments kept in the
> pg_xlog directory, in case a standby server needs to fetch them for
> streaming replication. Each segment is normally 16 megabytes. If a standby
> server connected to the primary falls behind by more than wal_keep_segments
> segments, the primary might remove a WAL segment still needed by the
> standby, in which case the replication connection will be terminated.
> (However, the standby server can recover by fetching the segment from
> archive, if WAL archiving is in use.)
>
> This sets only the minimum number of segments retained in pg_xlog; the
> system might need to retain more segments for WAL archival or to recover
> from a checkpoint. If wal_keep_segments is zero (the default), the system
> doesn't keep any extra segments for standby purposes, so the number of old
> WAL segments available to standby servers is a function of the location of
> the previous checkpoint and status of WAL archiving. This parameter has no
> effect on restartpoints. This parameter can only be set in the
> postgresql.conf file or on the server command line.
> "
>
>
> http://www.postgresql.org/docs/9.1/interactive/warm-standby.html#STREAMING-REPLICATION
>
> "If you use streaming replication without file-based continuous archiving,
> you have to set wal_keep_segments in the master to a value high enough to
> ensure that old WAL segments are not recycled too early, while the standby
> might still need them to catch up. If the standby falls behind too much, it
> needs to be reinitialized from a new base backup. If you set up a WAL
> archive that's accessible from the standby, wal_keep_segments is not
> required as the standby can always use the archive to catch up."
>
>
>> /
>> /
>>
>> 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.
>>
>
> Hot versus warm standby refer to whether it is possible to run read only
> queries on the standby in the first case or not in the second case.
>
>
>>
>> Whereas Streaming replication basically sends at the transaction level?
>>
>
> The difference you are looking for is log shipping versus streaming, where
> log shipping moves complete WAL files and streaming streams the same files.
>
> See here for more detail:
>
> http://www.postgresql.org/docs/9.1/interactive/high-availability.html
>
>
>
>>
>> I'm sure this is somewhat misinformed!
>>
>>
>> Thanks,
>>
>>
>> Will
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>