X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id C5B3511F6CE7 for ; Fri, 21 Apr 2006 02:18:44 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.65 X-Spam-Level: X-Spam-Status: No, score=-1.65 required=5 tests=[AWL=-0.267, BAYES_00=-2.599, HTML_40_50=0.496, HTML_MESSAGE=0.001, SARE_SPEC_REPLICA=0.72, SPF_PASS=-0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id YEQGVtqNFEUB for ; Fri, 21 Apr 2006 02:18:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id E379F11F6CE4 for ; Fri, 21 Apr 2006 02:18:34 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C66503.084E9F7D" Subject: Re: Howto: Using PITR recovery for standby replication X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Thu, 20 Apr 2006 23:14:59 -0600 Message-ID: <8511B4970E0D124898E973DF496F9B4357E0FC@stash.stackdump.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Howto: Using PITR recovery for standby replication Thread-Index: AcZk8RQKZzKKwROQQzipS1+4B91uUAAArsRfAAOufNs= References: <443F9BF3.4060101@laposte.net> <1145028323.5597.1.camel@localhost.localdomain> <443FDF05.4060600@laposte.net> <20060421014243.GR49405@pervasive.com> <8511B4970E0D124898E973DF496F9B4357E0F4@stash.stackdump.local> <2764.1145588998@sss.pgh.pa.us> <8511B4970E0D124898E973DF496F9B4357E0F5@stash.stackdump.local> From: "Benjamin Krajmalnik" To: "Benjamin Krajmalnik" , "Tom Lane" Cc: X-Archive-Number: 200604/222 X-Sequence-Number: 21391 This is a multi-part message in MIME format. ------_=_NextPart_001_01C66503.084E9F7D Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Tom, =20 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). =20 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 :) =20 Thanks for all your help! ________________________________ From: pgsql-admin-owner@postgresql.org on behalf of Benjamin Krajmalnik Sent: Thu 4/20/2006 9:52 PM To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication=20 Tom, =20 =20 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. =20 Regards, =20 Benjamin=20 ________________________________ From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Thu 4/20/2006 9:09 PM To: Benjamin Krajmalnik Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication=20 "Benjamin Krajmalnik" writes: > I have tried using pg_dump, but discovered that the backup was not a = =3D > consistent backup. Really? > Back to the problem I faced when testing backups with pg_dump, it =3D > appears that the backup was not a consistent backup of the data. For = =3D > example, sequences which are used by some tables bo longer held the = =3D > 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 ------_=_NextPart_001_01C66503.084E9F7D Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0A= =0A= =0A= =0A= =0A= =0A= =0A= Re: [ADMIN] Howto: Using PITR recovery for standby replication = =0A= =0A= =0A=
=0A=
Tom,
=0A=
 
=0A=
Just wanted to let you know = that I found =0A= the problem with the constraint violation on insert.
=0A=
When I restored from a = backup, I forgot to =0A= update the sequences current values to thje macval of the associated =0A= tables.
=0A=
The problem had nothing to do = with SP call =0A= sequencing, but rather with the sequence assigning a value which was = already in =0A= use (and was way below the current calue in the table).
=0A=
 
=0A=
After your message concerning = the sequence =0A= values the light bulb came on in my head and I went to check = those.
=0A=
Now I can go to sleep wothout = worrying =0A= :)
=0A=
 
=0A=
Thanks for all your =0A= help!
=0A=

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

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

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

=0A=
=0A=

"Benjamin Krajmalnik" <kraj@illumen.com> = writes:
> I =0A= have tried using pg_dump, but discovered that the backup was not a = =3D
> =0A= consistent backup.

Really?

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

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

        =0A=         =0A=         regards, tom =0A= lane

=0A= =0A= =0A= ------_=_NextPart_001_01C66503.084E9F7D--