Re: Howto: Using PITR recovery for standby replication

Tom,

Just wanted to let you know that I found the problem with the constraint violation on insert.
When I restored from a backup, I forgot to update the sequences current values to thje macval of the associated tables.
The problem had nothing to do with SP call sequencing, but rather with the sequence assigning a value which was already in use (and was way below the current calue in the table).

After your message concerning the sequence values the light bulb came on in my head and I went to check those.
Now I can go to sleep wothout worrying :)

Thanks for all your help!

________________________________

From: pgsql-admin-owner(at)postgresql(dot)org on behalf of Benjamin Krajmalnik
Sent: Thu 4/20/2006 9:52 PM
To: Tom Lane
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication

Tom,


Once again, thank you. Also, did you receive the snippet of the stored procedure which I sent you? As I mentioned, the only place where row insertion is performed is via that stored procedure, and the sequences were created by defining the columns as "bigserial", which still has me puzzled as to why I am experiencing the contraing violation on the unique primary key.

Regards,

Benjamin

________________________________

From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thu 4/20/2006 9:09 PM
To: Benjamin Krajmalnik
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication

"Benjamin Krajmalnik" <kraj(at)illumen(dot)com> writes:
> I have tried using pg_dump, but discovered that the backup was not a =
> consistent backup.

Really?

> Back to the problem I faced when testing backups with pg_dump, it =
> appears that the backup was not a consistent backup of the data. For =
> example, sequences which are used by some tables bo longer held the =
> correct values (the tables now held higher values),

Sequences are non-transactional, so pg_dump might well capture a higher
value of the sequence counter than is reflected in any table row, but
there are numerous other ways by which a gap can appear in the set of
sequence values. That's not a bug. If you've got real discrepancies
in pg_dump's output, a lot of us would like to know about 'em.

regards, tom lane