How do I fix issue causing "incomplete startup packet" log message trying to implement replication in Postgresql?

Question

I've got two cloud servers running Ubuntu 13.04 and PostgreSQL 9.2.

I've primarily used this blog post to aid me in setting things up. However, to do the initial database dump to the slave I'm using pg_start_backup/pg_stop_backup strategy used in this other blog post. I've read through the docs and postgres wikis as well. I ran into several problems I was able to solve, but I can't get past this wretched "the database is starting up" failure.

I'm not sure if seeing:

cp: cannot stat /var/lib/postgresql/9.2/archive/00000001000000000000003A': No such file or directory 

after consistent recover state reached is normal or the first sign of a problem. The searching I've done on the database is starting up and incomplete startup packet tells me that something is sending empty TCP packets to the slave. The only thing that even knows about the slave is the master, so I'm not sure why it's sending empty packets...

Has anyone worked with this and have an idea what might be going wrong?

The postgres log on the slave looks like so:

2013-08-26 13:01:38 CDT LOG: entering standby mode 2013-08-26 13:01:38 CDT LOG: restored log file "000000010000000000000039" from archive 2013-08-26 13:01:38 CDT LOG: incomplete startup packet 2013-08-26 13:01:39 CDT LOG: redo starts at 0/39000020 2013-08-26 13:01:39 CDT LOG: consistent recovery state reached at 0/390000E0 cp: cannot stat '/var/lib/postgresql/9.2/archive/00000001000000000000003A': No such file or directory 2013-08-26 13:01:39 CDT LOG: streaming replication successfully connected to primary 2013-08-26 13:01:39 CDT FATAL: the database system is starting up 2013-08-26 13:01:39 CDT FATAL: the database system is starting up 2013-08-26 13:01:40 CDT FATAL: the database system is starting up 2013-08-26 13:01:40 CDT FATAL: the database system is starting up 2013-08-26 13:01:41 CDT FATAL: the database system is starting up 2013-08-26 13:01:42 CDT FATAL: the database system is starting up 2013-08-26 13:01:42 CDT FATAL: the database system is starting up 2013-08-26 13:01:43 CDT FATAL: the database system is starting up 2013-08-26 13:01:43 CDT FATAL: the database system is starting up 2013-08-26 13:01:44 CDT FATAL: the database system is starting up 2013-08-26 13:01:44 CDT FATAL: the database system is starting up 2013-08-26 13:01:44 CDT LOG: incomplete startup packet 2013-08-26 13:03:27 CDT FATAL: the database system is starting up 2013-08-26 13:03:27 CDT FATAL: the database system is starting up 2013-08-26 13:03:30 CDT FATAL: the database system is starting up 2013-08-26 13:03:30 CDT FATAL: the database system is starting up 

thanks! brad

Answer 1

You're failing to copy the last WAL archive(s) after pg_stop_backup. You need to have WAL archiving set up or manually copy the WAL if you're going to use this method.

In 9.2 and above it's much easier to do one-off copies with pg_basebackup --xlog-method=stream. This copies the transaction logs over the replication protocol, as well as the base backup its self, and takes care of pg_start_backup and pg_stop_backup automatically.

See the pg_basebackup manual.

The "incomplete startup packet" errors mentioned in the title are likely unrelated, and caused by attempts to make SSL connections to a non-SSL-aware server from clients that have sslmode=prefer.