Re: Can someone help explain what's going on from the attached logs?

On Wed, Oct 26, 2011 at 6:41 PM, Chris Redekop <chris(at)replicon(dot)com> wrote:
>> Caveat #2 applies here
>>
>> http://developer.postgresql.org/pgdocs/postgres/hot-standby.html#HOT-STANDBY-CAVEATS
>>
>> The consistent state is delayed until your long running transactions
>> end, which is workload dependent but transient.
>
> I'm not quite sure how this correlates to what I'm seeing in 9.1.1.  When
> attempting to start a hotstandby while the primary is under load it seems to
> get stuck in that 'starting up' mode even when there are no open
> transactions.  If I get it into this state, and then remove all the load
> from the primary it still will not finish starting up.  If I select from
> pg_stat_activity on the primary it shows a couple connections, but they all
> have null 'xact_start's and <IDLE> 'current_query's.  Even if I then kill
> all the connections to the primary (via pg_terminate_backend) the hotstandby
> still will not finish starting up.  I would assume there can't be any
> transactions in progress if there are no connections to the primary.
>  Attempting to restart the hotstandby when in this state produces the same
> result.  If there is a transaction in progress for the duration of the
> backup (or something like that) can it cause it to get into this state?

There's nothing in the log you've shown to indicate any problems.

Yes, when that caveat applies we may wait for some time to find a good
starting point. That could be anywhere from seconds to hours,
depending upon the exact load on the master, but shouldn't be any
longer than your longest running write transaction executing at that
time.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services