Re: Streaming replication: sequences on slave seemingly ahead of sequences on master

On Friday 04 May 2012 09:47:16 Merlin Moncure wrote:
> On Fri, May 4, 2012 at 8:55 AM, Vincent de Phily
>
> <vincent.dephily@mobile-devices.fr> wrote:
> > Hi list,
> >
> > we have two 9.1.2 servers on debian squeeze, and are setting up a simple
> > streaming replication between the two.
> >
> > * wal_keep_segments is set high on the master
> > * the slave's recovery.conf contains just standbay_mode=on and
> >  primary_conninfo=foo
> > * we use a simple start_backup/rsync/stop_backup to create the base copy
> >  before starting the slave.
> >
> >
> > It all seems to be working fine, except that when checking the data
> > (selecting latest primary key and sequence value for all tables) on
> > master and slave, some sequence ids are higher on the slave than on the
> > master. I could understand if they were lower, but this is weird.
> >
> > * The slave's sequences can be anywhere between 1 and 50 ids ahead.
>
> how did you determine that exactly?

Quick and dirty :

SQL=$(psql -tA -h $MASTER $DB <<< "select E'select \''||table_name||E'\', '||
column_name||' from '||table_name||' order by '||column_name||' desc limit 1;'
from information_schema.columns where table_schema='public' and
ordinal_position=1 order by table_name;select E'select \''||sequence_name||
E'\', last_value from '||sequence_name||';' from information_schema.sequences
where sequence_schema='public' order by sequence_name;")
psql -tA -h $SLAVE $DB <<< "select pg_last_xlog_replay_location();$SQL" >
$SLAVE.check
psql -tA -h $MASTER $DB <<< "select pg_current_xlog_location();$SQL" >
$MASTER.check
if diff -u $MASTER.check $SLAVE.check; then
    cat $MASTER.check
    echo -e "\e[32msync ok\e[m"
else
    echo -e "\e[31msync bad\e[m"
fi


> how do you know the transactions
> are committing in sequence order?

I dont, actually. But whichever order the transactions eventually commit in,
I'd expect that order to be the same on the slave and the host ? And I
wouldn't expect anything to finish on the slave before it finishes on the
master ?

--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.dephily@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.