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 9776511F6E68 for ; Fri, 21 Apr 2006 00:52:34 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.973 X-Spam-Level: X-Spam-Status: No, score=-1.973 required=5 tests=[AWL=-0.094, BAYES_00=-2.599, 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 tcNihaPq6Xae for ; Fri, 21 Apr 2006 00:52:31 -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 ADCBF11F61C5 for ; Fri, 21 Apr 2006 00:52:30 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C664F7.035B20A8" Subject: Re: Howto: Using PITR recovery for standby replication X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Thu, 20 Apr 2006 21:52:30 -0600 Message-ID: <8511B4970E0D124898E973DF496F9B4357E0F5@stash.stackdump.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Howto: Using PITR recovery for standby replication Thread-Index: AcZk8RQKZzKKwROQQzipS1+4B91uUAAArsRf 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> From: "Benjamin Krajmalnik" To: "Tom Lane" Cc: X-Archive-Number: 200604/215 X-Sequence-Number: 21384 This is a multi-part message in MIME format. ------_=_NextPart_001_01C664F7.035B20A8 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Tom, =20 First of all forgive me if I am totally incorrect - I may very well be:) = If so, believe me I will be a very happy camper since my concerns will = be void. My concern was raised when I backed up the server which was = receiving production data, and I restored it in a developmen server. = The difference between both of them is tht the production server has a = very high row insertion rate, while the development server has about 10 = rows per minute inserted (just to enable us to check tht our real time = aggregation code and graphical display routines are working properly). =20 After restoring, when we fired up the service responsible for record = insertion, I began receiving the constraint violations on the columns = controlled by the sequences. The table had higher values in them than = the sequences. This raised a huge red flag for me. My concern was that = the aggreegated data tables may not reflect the data in the raw inserted = tables - essentially, that they may be out of sync. =20 The particular table which was problematic (and for which I posted = another message due to the unique constraint violation which I am seeing = intermittently) is the one with the high insertion rate. The sequence = is currently being used to facilitate purginf of old records. However, = as I study and play more with PostgreSQL, I found the ability to = partition a table. Once I move to table partitioning, my problem of = ourgin data past retention periods will be fixed. =20 My entire conecpt may have been incorrect and is based with my = experiences with MS SQL Server whereby when I purged records based on = the date, due to the large amounts of data huge transaction logs were = created, and in some cases ended up using so much diskspace that the = database imploded! The workaroound which I created under SQL Server was = to assign an identity field to each row, select the minimum value for = the day to be purged, and then purge records 10,000 at a time within = transactions. This kept the transaction file small and the database = from exploding dye to running out of disk space. =20 It is very possible that this may not have been an issue with = PostgreSQL, but I could not take a chance, so I ported the methodology = over. The new architecture will have a table partition for each month = (12 partitions). Once the retention period of the given partition = expires it will simply be truncated. =20 Sorry for the rambling, but, if I understand correctly from you, the = only items which were out of synch were the sequences, but all of the = tables would have maintained consistency relative to each other? If so, = once I get rid of the unnecessary sequences, I can create a small = function to be run after a restore which can reset the sequences to the = proper value. That would be simple enough, and would provide an easily = implemented solution. =20 You'll probably see me in here asking lots of questins as I cut my teeth = on PostgreSQL. Hopefully, at some point in the future I will be able to = contribute back with solutions :) =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_01C664F7.035B20A8 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0A= =0A= =0A= =0A= =0A= =0A= Re: [ADMIN] Howto: Using PITR recovery for standby replication = =0A= =0A= =0A=
=0A=
Tom,
=0A=
 
=0A=
First of all forgive me if I = am totally =0A= incorrect - I may very well be:)  If so, believe me I will be a = very happy =0A= camper since my concerns will be void.  My concern was raised when = I backed =0A= up the server which was receiving production data, and I restored it in = a =0A= developmen server.  The difference between both of them is tht the =0A= production server has a very high row insertion rate, while the = development =0A= server has about 10 rows per minute inserted (just to enable us to check = tht our =0A= real time aggregation code and graphical display routines are working =0A= properly).
=0A=
 
=0A=
After restoring, when we = fired up the =0A= service responsible for record insertion, I began receiving the = constraint =0A= violations on the columns controlled by the sequences.  The table = had =0A= higher values in them than the sequences.  This raised a huge red = flag for =0A= me.  My concern was that the aggreegated data tables may not = reflect the =0A= data in the raw inserted tables - essentially, that they may be out of =0A= sync.
=0A=
 
=0A=
The particular table = which was =0A= problematic (and for which I posted another message due to = the unique =0A= constraint violation which I am seeing intermittently) is the one with =0A= the high insertion rate.  The sequence is currently being used = to =0A= facilitate purginf of old records.  However, as I study and = play more =0A= with PostgreSQL, I found the ability to partition a table.  = Once I =0A= move to table partitioning, my problem of ourgin data past retention = periods =0A= will be fixed.
=0A=
 
=0A=
My entire conecpt may have = been =0A= incorrect and is based with my experiences with MS SQL Server = whereby when =0A= I purged records based on the date, due to the large amounts of = data huge =0A= transaction logs were created, and in some cases ended up using so much =0A= diskspace that the database imploded!  The workaroound which I = created =0A= under SQL Server was to assign an identity field to each row, select the = minimum =0A= value for the day to be purged, and then purge records 10,000 = at a =0A= time within transactions.  This kept the transaction file small =0A= and the database from exploding dye to running out of disk =0A= space.
=0A=
 
=0A=
It is very possible that this = may not have =0A= been an issue with PostgreSQL, but I could not take a chance, so I = ported the =0A= methodology over.  The new architecture will have a table = partition =0A= for each month (12 partitions).  Once the retention period of the = given =0A= partition expires it will simply be truncated.
=0A=
 
=0A=
Sorry for the rambling, but, = if I =0A= understand correctly from you, the only items which were out of = synch were =0A= the sequences, but all of the tables would have maintained consistency = relative =0A= to each other?  If so, once I get rid of the unnecessary sequences, = I can =0A= create a small function to be run after a restore which can reset = the =0A= sequences to the proper value.   That would be simple enough, = and =0A= would provide an easily implemented solution.
=0A=
 
=0A=
You'll probably see me in = here asking lots =0A= of questins as I cut my teeth on PostgreSQL.  Hopefully, at some = point in =0A= the future I will be able to contribute back with solutions = :)
=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_01C664F7.035B20A8--