Re: Problems with Binary Replication

Am 31.03.2012 23:38, schrieb Tom Lane:
> Andreas<maps(dot)on(at)gmx(dot)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
:)