I have set up a hot standby of a PostgreSQL server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log
I have the following:
LOG: creating missing WAL directory "pg_xlog/archive_status"
cp: cannot stat `/var/lib/pgsql/9.2/wal/00000002.history': No such file or directory
LOG: entering standby mode
cp: cannot stat `/var/lib/pgsql/9.2/wal/0000000200000031000000B4': No such file or directory
LOG: streaming replication successfully connected to primary
LOG: redo starts at 31/B47BFAC0
LOG: consistent recovery state reached at 31/B73624A0
LOG: database system is ready to accept read only connections
I am concerned about the missing WAL files. Can anyone confirm that, as long as it reaches a consistent state, the hot standby contains all the data of the master?
Everything else I check indicates that it's ok; for example, running psql -x -c "select * from pg_stat_replication;"
on the master looks good, and adding a new record on the master replicates. I just want to be sure that there won't be anything missing from the slave.
Best Answer
I think this is normal and expected if your
restore_command
is set to something like this example:The manual says that:
So you can expect to see exactly one
restore_command
failure when you start your standby, because PostgreSQL will keep calling it (with incrementing log file names/numbers) until it fails once.Then it will connect to the primary and start streaming as described above, and as you saw in your logs:
The slave is not guaranteed to be exactly up-to-date with the master, because it could be disconnected from the master for example. In particular, this line:
does not mean that "the hot standby contains all the data of the master". However, if you see it followed by this line, as you did:
then the database is "ready enough" to start functioning as a read-only standby, as the manual says:
In my case, I saw
consistent recovery state reached
not followed bydatabase system is ready to accept read only connections
. This turned out to be a problem with an embedded scripting language plugin (plpython2
) having a system-wide startup script (sitecustomize.py
) which did bad things to the PostgreSQL process (enablingfaulthandler
and installing a signal handler forSIGUSR2
) which caused it to never enter hot standby mode.