Обсуждение: - PostgreSQL Replication Types
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?
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
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.
Whereas Streaming replication basically sends at the transaction level?
I'm sure this is somewhat misinformed!
Thanks,
Will
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.klaver@aklaver.com
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@pgmasters.net
Вложения
I've read most of this but some it was not clear until we discussed.
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."
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.
/cp: cannot stat `/opt/postgres/9.1/archive/000000070000000F00000057':
No such file or directory/
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.
See here for more detail: WLM: Reading now :)
http://www.postgresql.org/docs/9.1/interactive/high-availability.html
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.klaver@aklaver.com
On 12/17/2015 07:56 AM, Will McCormick wrote: > 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 ... What is the full hint message? The functions are supposed to be able to be run while the server is in recovery. > > > > 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"? Not really see the section below(#STREAMING-REPLICATION) I posted previously. It is either or, if streaming is set up and the standby can reach the master xlog directory then it will stream the WAL files from there. If the standby cannot access the xlog directory and if you have WAL archiving set up on the master and archive restore setup on the standby it will switch to full WAL log shipping from the archived WAL directory, assuming the files it needs are there. > > 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? Yes. The WALs created in the xlog directory are the basis for replication. Leaving out logical replication, not available in 9.1, the only way to do replication using the Postgres core tools is to move those files from the master to the standby. You can either ship them whole which is log shipping or you can stream them, which is streaming. It also possible, which is what you are seeing, to do the belt and suspenders approach. That is to use streaming for the responsiveness, but have a parallel process that ships the whole logs to a archive directory where they can be accessed also. Having an archive directory also allows for PITR(Point In Time Recovery), but that is another topic: http://www.postgresql.org/docs/9.1/static/continuous-archiving.html > > 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
What is the full hint message?On 12/17/2015 07:56 AM, Will McCormick wrote: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 ...
The functions are supposed to be able to be run while the server is in recovery.Not really see the section below(#STREAMING-REPLICATION) I posted previously. It is either or, if streaming is set up and the standby can reach the master xlog directory then it will stream the WAL files from there. If the standby cannot access the xlog directory and if you have WAL archiving set up on the master and archive restore setup on the standby it will switch to full WAL log shipping from the archived WAL directory, assuming the files it needs are there.
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"?Yes. The WALs created in the xlog directory are the basis for replication. Leaving out logical replication, not available in 9.1, the only way to do replication using the Postgres core tools is to move those files from the master to the standby. You can either ship them whole which is log shipping or you can stream them, which is streaming. It also possible, which is what you are seeing, to do the belt and suspenders approach. That is to use streaming for the responsiveness, but have a parallel process that ships the whole logs to a archive directory where they can be accessed also. Having an archive directory also allows for PITR(Point In Time Recovery), but that is another topic:
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?
http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
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 Klaveradrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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 Klaver
adrian.klaver@aklaver.com
Almost forgot this:
SELECT pg_current_xlog_location();
ERROR: recovery is in progress
HINT: WAL control functions cannot be executed during recovery.
bms=> SELECT pg_current_xlog_location();
ERROR: recovery is in progress
HINT: WAL control functions cannot be executed during recovery.
What is the full hint message?On 12/17/2015 07:56 AM, Will McCormick wrote: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 ...
The functions are supposed to be able to be run while the server is in recovery.Not really see the section below(#STREAMING-REPLICATION) I posted previously. It is either or, if streaming is set up and the standby can reach the master xlog directory then it will stream the WAL files from there. If the standby cannot access the xlog directory and if you have WAL archiving set up on the master and archive restore setup on the standby it will switch to full WAL log shipping from the archived WAL directory, assuming the files it needs are there.
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"?Yes. The WALs created in the xlog directory are the basis for replication. Leaving out logical replication, not available in 9.1, the only way to do replication using the Postgres core tools is to move those files from the master to the standby. You can either ship them whole which is log shipping or you can stream them, which is streaming. It also possible, which is what you are seeing, to do the belt and suspenders approach. That is to use streaming for the responsiveness, but have a parallel process that ships the whole logs to a archive directory where they can be accessed also. Having an archive directory also allows for PITR(Point In Time Recovery), but that is another topic:
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?
http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
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 Klaveradrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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 Klaver
adrian.klaver@aklaver.com
On 12/17/2015 07:56 AM, Will McCormick wrote: > Thanks a ton for the prompt response. > > I've read most of this but some it was not clear until we discussed. > > > See here for more detail: WLM: Reading now :) > While reading I would suggest having the postgres.conf files on the master and the standby and the recovery.conf file on the standby open so you can see how your settings match up. I would also take a look at what is in: /opt/postgres/9.1/archive/ -- Adrian Klaver adrian.klaver@aklaver.com
On 12/17/2015 08:43 AM, Will McCormick wrote: > Almost forgot this: > > > SELECT pg_current_xlog_location(); I was not paying attention earlier that should be: pg_last_xlog_receive_location() from: http://www.postgresql.org/docs/9.1/interactive/functions-admin.html Table 9-58. Recovery Information Functions > > ERROR: recovery is in progress > > HINT: WAL control functions cannot be executed during recovery. > > bms=> SELECT pg_current_xlog_location(); > > ERROR: recovery is in progress > > HINT: WAL control functions cannot be executed during recovery. > > -- Adrian Klaver adrian.klaver@aklaver.com