Re: Database not starting up for hot standby

On 19 September 2010 12:49, Thom Brown <thom(at)linux(dot)com> wrote:
> I'm using PostgreSQL 9.0rc1 and can't fathom what I've missed here.  I
> ran pg_start_backup() on my primary, did a filesystem backup and
> restore to the standby, configured the conf files on both
> appropriately and issued a pg_stop_backup() on the primary again.  I
> brought the standby up but I'm getting the following in the log:
>
> 2010-09-19 12:32:35 BST LOG:  entering standby mode
> 2010-09-19 12:32:35 BST LOG:  redo starts at 1/2F000020
> 2010-09-19 12:32:35 BST LOG:  record with zero length at 1/2F0000B0
> 2010-09-19 12:32:35 BST LOG:  streaming replication successfully
> connected to primary
> 2010-09-19 12:32:36 BST LOG:  incomplete startup packet
> 2010-09-19 12:32:36 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:37 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:37 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:38 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:38 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:39 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:39 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:40 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:40 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:41 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:41 BST LOG:  consistent recovery state reached at 1/30000000
> 2010-09-19 12:32:41 BST FATAL:  the database system is starting up
> 2010-09-19 12:32:41 BST LOG:  incomplete startup packet
> 2010-09-19 12:32:54 BST FATAL:  the database system is starting up
> 2010-09-19 12:35:26 BST FATAL:  the database system is starting up
> 2010-09-19 12:38:44 BST FATAL:  the database system is starting up
>
> Got these appearing in the process list too:
>
> postgres: startup process   recovering 000000010000000100000030
> postgres: wal receiver process   streaming 1/300001D0
>
> That last one changed to its current value after I issued a checkpoint
> on the primary, but the first one doesn't change.
>
> But I cannot connect to the standby as it's stuck in recovery.
>
> The standby's recovery.conf has hot_standby set to "on" (set before
> bringing online of course).
>
> What am I not doing?

What I wasn't doing was checking to see where it's really reading
postgresql.conf from. On IRC, Stefan Kaltenbrunner correctly
theorised I was reading in from the wrong place, and because I'm used
to Gentoo's installation rather than Ubuntu's, I didn't notice that it
was indeed coming from a separate location, so updated that one
instead.

Problem solved :) Feel free to pelt rotten vegetables at me.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935