Thread: Problems with Binary Replication

Hi,
I configured a master and a hot-standby server as described here:
http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Starting_Replication_with_only_a_Quick_Master_Restart

At first everything looked ok.
Then some days later I find that at least some sequences on the
hot-standby are higher than those on the master.
I checked only one table with such a leaped sequence as primary key and
there where no entries for the higher sequence values.

How can this happen?

Now I added 1 row on the master.
Before both sequence start values were 403.

After the insert the master shows 404 as expected but the "hot-standby"
shows 436.
In the slaves table there is only the one row more, I just inserted on
the master and it's ID column shows 404.

Lets suppose - and hope - at least the data tables stay in sync.
But with this phenomena I can't trust the system, can I?

and I can't use the hot-standy to pull the backups from there to lighten
the load of the master.   :(




Am 31.03.2012 21:42, schrieb Andreas:
> Hi,
> I configured a master and a hot-standby server as described here:
> http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Starting_Replication_with_only_a_Quick_Master_Restart
>
>
> At first everything looked ok.
> Then some days later I find that at least some sequences on the
> hot-standby are higher than those on the master.
> I checked only one table with such a leaped sequence as primary key
> and there where no entries for the higher sequence values.
>
> How can this happen?
>
>

Andreas <maps.on@gmx.net> writes:
> Now I added 1 row on the master.
> Before both sequence start values were 403.

> After the insert the master shows 404 as expected but the "hot-standby"
> shows 436.

What are you doing to get it to "show" a value --- not nextval(),
presumably?

I think that this may be the expected behavior from pre-caching of
sequence values.  You would see the same thing on the master if you
were to forcibly crash and restart it.

            regards, tom lane

Am 31.03.2012 23:38, schrieb Tom Lane:
> Andreas<maps.on@gmx.net>  writes:
>> Now I added 1 row on the master.
>> Before both sequence start values were 403.
>> After the insert the master shows 404 as expected but the "hot-standby"
>> shows 436.
> What are you doing to get it to "show" a value --- not nextval(),
> presumably?
>
> I think that this may be the expected behavior from pre-caching of
> sequence values.  You would see the same thing on the master if you
> were to forcibly crash and restart it.
>
>             regards, tom lane
>

I checked with pgAdmin.
Even with nextval() the sequence shouldn't move because AFAIK a
hot-standby is readonly.

The sequence on the hot-standby jumped 32 counters when I inserted a row
on the master.
Before the insert it had the same value as the master.

Actually I stumbled over it, when I worked on a test pc to prepare some
sql updates for the master.
I installed there a backup from the hot-standby because I expected this
should mirror the master 1:1.
At least those tables where only I insert stuff.

I was worried because I thought there is a serious issue.

Now what could one do to prevent those sequence gaps?
There might be scenarios where it's important not to have gaps in the
numbering even when one has to switch to the standby if there is a
failiour on the master.
E.g. numbers of invoices need to be gapless.


Thanks for the reply
:)