Re: streaming replication question

Dear List,

Firstly thanks to the postgresql global development team for releasing
postgresql
with such a great enterprise feature of SR & HS.

As an enduser i setup SR based on
http://wiki.postgresql.org/wiki/Streaming_Replication
The master & standby are both powerful machines and are on same gigabit
switch.
Things worked as expected I updated 1000's of records in single txn and saw
them
appearing on the standby instantly.

Then i wanted to see the impact of shutting down the standby momentarily .
This apparently
failed with following messages in standby logs:

DETAIL: last completed transaction was at log time 2010-10-02
20:57:26.17677+05:30
LOG: restartpoint starting: time
LOG: received smart shutdown request
FATAL: terminating walreceiver process due to administrator command
LOG: restartpoint complete: wrote 1568 buffers (0.3%); write=146.237 s,
sync=0.251 s, total=146.489 s
LOG: recovery restart point at 25/EA87BA18
DETAIL: last completed transaction was at log time 2010-10-02
21:02:21.89303+05:30
LOG: shutting down <--- - - - - - - - - - - - - -- - - shutdown command
issued here
LOG: database system is shut down
LOG: database system was shut down in recovery at 2010-10-02 21:02:22 IST
<---------------- first attempt to start was made
LOG: entering standby mode
LOG: restored log file "0000000100000025000000EA" from archive
LOG: redo starts at 25/EA87BA18
FATAL: too many KnownAssignedXids
CONTEXT: xlog redo insert: rel 1663/16399/303892827; tid 1503/119
LOG: startup process (PID 20527) exited with exit code 1
LOG: terminating any other active server processes

LOG: database system was interrupted while in recovery at log time
2010-10-02 20:57:12 IST <--------------- second attempt was made.
HINT: If this has occurred more than once some data might be corrupted and
you might need to choose an earlier recovery target.
LOG: entering standby mode
LOG: restored log file "0000000100000025000000EA" from archive
LOG: redo starts at 25/EA87BA18
FATAL: too many KnownAssignedXids
CONTEXT: xlog redo insert: rel 1663/16399/303892827; tid 1503/119
LOG: startup process (PID 20693) exited with exit code 1
LOG: terminating any other active server processes

I confess that i have not RT(Fine)M yet .

Regds
Rajesh Kumar Mallah.

On Sun, Oct 3, 2010 at 1:02 AM, Rajesh Kumar Mallah
<mallah(dot)rajesh(at)gmail(dot)com> wrote:
> As an enduser i setup SR based on
> http://wiki.postgresql.org/wiki/Streaming_Replication
> The master & standby are both powerful machines and are on same gigabit
> switch.
> Things worked as expected I updated 1000's of records in single txn and saw
> them
> appearing on the standby instantly.
>
> Then i wanted  to see the impact of shutting down the standby momentarily .
> This apparently
> failed with following messages in standby logs:
>
> DETAIL:  last completed transaction was at log time 2010-10-02
> 20:57:26.17677+05:30
> LOG:  restartpoint starting: time
> LOG:  received smart shutdown request
> FATAL:  terminating walreceiver process due to administrator command
> LOG:  restartpoint complete: wrote 1568 buffers (0.3%); write=146.237 s,
> sync=0.251 s, total=146.489 s
> LOG:  recovery restart point at 25/EA87BA18
> DETAIL:  last completed transaction was at log time 2010-10-02
> 21:02:21.89303+05:30
> LOG:  shutting down <--- - - - - - - - - - - - - -- - -  shutdown command
> issued here
> LOG:  database system is shut down
> LOG:  database system was shut down in recovery at 2010-10-02 21:02:22 IST
> <---------------- first attempt to start was made
> LOG:  entering standby mode
> LOG:  restored log file "0000000100000025000000EA" from archive
> LOG:  redo starts at 25/EA87BA18
> FATAL:  too many KnownAssignedXids
> CONTEXT:  xlog redo insert: rel 1663/16399/303892827; tid 1503/119
> LOG:  startup process (PID 20527) exited with exit code 1
> LOG:  terminating any other active server processes
>
> LOG:  database system was interrupted while in recovery at log time
> 2010-10-02 20:57:12 IST <--------------- second attempt was made.
> HINT:  If this has occurred more than once some data might be corrupted and
> you might need to choose an earlier recovery target.
> LOG:  entering standby mode
> LOG:  restored log file "0000000100000025000000EA" from archive
> LOG:  redo starts at 25/EA87BA18
> FATAL:  too many KnownAssignedXids
> CONTEXT:  xlog redo insert: rel 1663/16399/303892827; tid 1503/119
> LOG:  startup process (PID 20693) exited with exit code 1
> LOG:  terminating any other active server processes

This looks like the bug of HS. But I'm not sure why KnownAssignedXids
overflowed.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Dear Masao ,

Thanks so much for the response ,

I actually i am in a position to do rigorous stress testing because i have
the machines at my disposal.
I am sorry that i was not in touch with the lists for a prolong duration and
could not followup much
on the discussions while HS & SR were being developed.hence i am mostly
relying on the published
documents only. The wiki that i used does not say much about how to manage
the wal files that are
archived in primary and standby(possibly).

I am currently aiming to setup only SR between 2 servers only.

in primary server i have
---------------------------------------------------------------------
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /mnt/disk1/pgdatadir/wal_archives/%f'
-----------------------------------------------------------------------------

in slave sever i have additionally
hot_standby = on # in postgresql.conf

and recovery.con in slave is:
--------------
standby_mode = 'on'
primary_conninfo = 'host=172.16.10.139 port=5432 user=postgres'
trigger_file = '/mnt/disk1/pgdatadir/trigger'
restore_command = 'cp /mnt/disk1/pgdatadir/wal_archives/%f "%p"'
-----------------------

My question is in SR setup do i need to keep transferring the archived WAL
files also ?
when the slave requests WAL records from the master does the master consult
the archived
wal files also for sending the records ?

anticipating your enlightening answers

your respectfully
Rajesh Kumar Mallah.

Dear Masao,
I would also like to inform that i observed the phenomenon only once. And i
am still not
too clear on the correct setup.
Regds
Rajesh Kumar Mallah.

>
> This looks like the bug of HS. But I'm not sure why KnownAssignedXids
> overflowed.
>
> Regards,
>
> --
> Fujii Masao
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>

On Tue, Oct 5, 2010 at 2:57 AM, Rajesh Kumar Mallah
<mallah(dot)rajesh(at)gmail(dot)com> wrote:
> I am currently aiming to setup only SR  between 2 servers only.

"only SR" means that you don't need Hot Standby (i.e., you don't need to
run any query on the standby server)? If so, you can set wal_level to
archive instead of hot_standby, and disable hot_standby parameter on the
standby.

> My question is in SR setup do i need to keep transferring the archived WAL
> files also ?

No if wal_keep_segments is high enough that WAL files required for the standby
can be saved in pg_xlog directory of the master.

> when the slave requests WAL records from the master does the master consult
> the archived
> wal files also for sending the records ?

No. In 9.0, the master doesn't read and send the archived WAL files.
But I agree to make the master send the archived WAL files, and I'm
now proposing that.
http://archives.postgresql.org/pgsql-hackers/2010-09/msg02040.php

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Tue, Oct 5, 2010 at 12:09 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> On Tue, Oct 5, 2010 at 2:57 AM, Rajesh Kumar Mallah
> <mallah(dot)rajesh(at)gmail(dot)com> wrote:
> > I am currently aiming to setup only SR between 2 servers only.
>
> "only SR" means that you don't need Hot Standby (i.e., you don't need to
> run any query on the standby server)? If so, you can set wal_level to
> archive instead of hot_standby, and disable hot_standby parameter on the
> standby.
>

Thanks for the kind replies,
May be I am mistaken in understanding of the terms. I do issue read only
queries to the standby server. So I think its HS. But I also stream the
wal records.

>
> > My question is in SR setup do i need to keep transferring the archived
> WAL
> > files also ?
>
> No if wal_keep_segments is high enough that WAL files required for the
> standby
> can be saved in pg_xlog directory of the master.
>
> > when the slave requests WAL records from the master does the master
> consult
> > the archived
> > wal files also for sending the records ?
>
> No. In 9.0, the master doesn't read and send the archived WAL files.
> But I agree to make the master send the archived WAL files, and I'm
> now proposing that.
> http://archives.postgresql.org/pgsql-hackers/2010-09/msg02040.php
>
> Regards,
>

I just wanted to let you know how i am now managing the wal logs.
i have mounted the wal archive folder(Xp) of primary on the slave via NFS to
a folder (Ys).
The folder Xp of primary is periodically rsynced to folder Xs on slave with
option --delete
in recovery.conf of slave archive_cleanup_command has been specified to
cleanup Ys
(not Xs), since it is a NFS rw mount it removes the unneeded archived WAL
files from the
source also ie Xp . subsequently the files are removed from Xs also because
of the
--delete option of rsync.

My original requirement is that , I should be able to divert certain amount
of Read Only
queries from the master to salve so as to reduce load in primary. Our pilot
applications
have been modified so that they always contact master when that have to
modify data
and slave when they have to read data. The current setup provides for the
requirement
but I am concerned what happens if the slave is shutdown for a prolonged
duration how
will i get to know when a base backup is required. I am trying to read the
docs and
practically observing also the effects of various steps. We have a switch in
the software
that lets was not to use the slave at all ! .

Regds
Rajesh Kumar Mallah.
Tradeindia.com - India's Largest B2B MarketPlace.
(uses PostgreSQL for past 10 years)

>
> --
> Fujii Masao
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>

On Tue, Oct 5, 2010 at 4:04 PM, Rajesh Kumar Mallah
<mallah(dot)rajesh(at)gmail(dot)com> wrote:
>> > I am currently aiming to setup only SR  between 2 servers only.
>>
>> "only SR" means that you don't need Hot Standby (i.e., you don't need to
>> run any query on the standby server)? If so, you can set wal_level to
>> archive instead of hot_standby, and disable hot_standby parameter on the
>> standby.
>
> Thanks for the kind replies,
> May be I am mistaken in understanding of the terms. I do issue read only
> queries to the standby server. So I think its HS. But I also stream the
> wal records.

SR is capability to stream WAL records from the master to the standby and
keep the database on the standby up to date by applying the WAL records.
HS is capability to allow us to run read-only queries on the standby.
You seem to need both SR and HS. So you don't need to change wal_level and
hot_standby parameters.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Sat, 2010-10-02 at 12:02 -0400, Rajesh Kumar Mallah wrote:
> FATAL: too many KnownAssignedXids

That's a bug.

I've a few guesses about that, but I'll put some better instrumentation
in to see if we can prove what's causing it.

Can you reproduce that again, or was that failure isolated to that
particular point in the WAL stream? You can try turning HS off, then
turning it back on again later.

If you suspect a bug in Hot Standby, please set
trace_recovery_messages = DEBUG2
in postgresql.conf and repeat the action

Always useful to know
* max_connections
* current number of sessions
* whether we have two phase commits happening

Thanks

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

Dear Riggs,
I am so sorry that i am not able to replicate it this time. May be i did
something really stupid that day .
I remember at one point of time i ran the standy without any recorvery.conf
even.
If i ever get that again i will surely try to replicate it and inform.

Regds
Rajesh Kumar Mallah.

On Tue, Oct 5, 2010 at 5:08 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Sat, 2010-10-02 at 12:02 -0400, Rajesh Kumar Mallah wrote:
> > FATAL: too many KnownAssignedXids
>
> That's a bug.
>
> I've a few guesses about that, but I'll put some better instrumentation
> in to see if we can prove what's causing it.
>
> Can you reproduce that again, or was that failure isolated to that
> particular point in the WAL stream? You can try turning HS off, then
> turning it back on again later.
>
> If you suspect a bug in Hot Standby, please set
> trace_recovery_messages = DEBUG2
> in postgresql.conf and repeat the action
>
> Always useful to know
> * max_connections
> * current number of sessions
> * whether we have two phase commits happening
>
> Thanks
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Training and Services
>
>