Wednesday, December 28, 2011

Postgresql - Sending wal segments to the standby servers

I'm reviewing this function in the context of this error that it's being generated in one hot standby server with streaming replication:

postgres@debian1:~/data_sr/pg_log$ tail -f postgresql-2011-12-27_172357.log 
2011-12-27 17:54:45 CST postgres postgres STATEMENT: SELECT pg_current_xlog_location()
2011-12-27 17:54:55 CST postgres postgres ERROR: recovery is in progress
2011-12-27 17:54:55 CST postgres postgres HINT: WAL control functions cannot be executed during recovery.
2011-12-27 17:54:55 CST postgres postgres STATEMENT: SELECT pg_current_xlog_location()
2011-12-27 17:55:05 CST postgres postgres ERROR: recovery is in progress
2011-12-27 17:55:05 CST postgres postgres HINT: WAL control functions cannot be executed during recovery.
2011-12-27 17:55:05 CST postgres postgres STATEMENT: SELECT pg_current_xlog_location()
2011-12-27 17:55:15 CST postgres postgres ERROR: recovery is in progress
2011-12-27 17:55:15 CST postgres postgres HINT: WAL control functions cannot be executed during recovery.
2011-12-27 17:55:15 CST postgres postgres STATEMENT: SELECT pg_current_xlog_location()

http://www.postgresql.org/docs/9.1/static/functions-admin.html

pg_last_xlog_receive_location()
text
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.

Apparently the recovery remains in progress eternally, as the recovery.conf file is still in the cluster (when the recovery process ends it should be renamed to recovery.done).
how to stop it?

I think, there's the issue:
The one thing that you absolutely must specify in recovery.conf is the restore_command, which tells PostgreSQL how to get back archived WAL file segments.

Example:
The simplest useful command is something like:
restore_command = 'cp /mnt/server/archivedir/%f %p'
which will copy previously archived WAL segments from the directory /mnt/server/archivedir.

http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html

The thing with this parameter setting is that is optional for streaming replication.
26.1. Archive Recovery Settings

restore_command (string)

The shell command to execute to retrieve an archived segment of the WAL file series. This parameter is required for archive recovery, but optional for streaming replication.

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

This document gave me very good insights about how streaming replication works "under the hood".
http://wiki.postgresql.org/images/9/90/FOSDEM2010-Streaming_Replication.pdf
And this one too:
http://www.korokithakis.net/posts/how-postgresqls-sr-streaming-replication-works/

Solution:
Send wal segments from master to slaves and include the restore_command to the recovery.conf file in the slaves. Sending wal segments to the standby servers assures that if they fall too far behind from the master they will be able to still resync using the archived wall segments.
The solution implies very small modifications:

On the master server, in the postgresql.conf change this:

archive_command = 'cd .'

to

archive_command ='scp %p postgres@10.0.0.201:/tmp/%f' # command to use to archive a logfile segment


and on the standby, add this to the recovery.conf

restore_command ='cp /tmp/%f "%p"'


and after that:

postgres@debian1:~/data_sr/pg_log$ tail -f postgresql-2011-12-28_130051.log 
2011-12-28 13:00:51 CST LOG: database system was interrupted; last known up at 2011-12-28 12:55:12 CST
2011-12-28 13:00:51 CST LOG: creating missing WAL directory "pg_xlog/archive_status"
2011-12-28 13:00:51 CST LOG: entering standby mode
2011-12-28 13:00:51 CST LOG: restored log file "000000010000000000000020" from archive
2011-12-28 13:00:52 CST LOG: redo starts at 0/20000020
2011-12-28 13:00:52 CST LOG: consistent recovery state reached at 0/21000000
2011-12-28 13:00:52 CST LOG: database system is ready to accept read only connections
cp: cannot stat `/tmp/000000010000000000000021': No such file or directory
2011-12-28 13:00:52 CST LOG: streaming replication successfully connected to primary

No more output, which usually means that everything is just fine. But the recovery.conf file is still there ... well, no errors, no problem, good enough to me.

It remains in recovery state:
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)


Now, how to prevent the wal directory in the standby from becoming bloated, waste and potentially consume all the space?

No comments:

Post a Comment