X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7879611F6CC5 for ; Thu, 20 Apr 2006 23:58:57 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.593 X-Spam-Level: X-Spam-Status: No, score=0.593 required=5 tests=[AWL=-0.353, HTML_10_20=0.945, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id WVi3MzOgilIf for ; Thu, 20 Apr 2006 23:58:47 -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 0FA5311F66B7 for ; Thu, 20 Apr 2006 23:58:51 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C664EF.851215B1" Subject: Howto: Using PITR recovery for standby replication X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Thu, 20 Apr 2006 20:58:51 -0600 Message-ID: <8511B4970E0D124898E973DF496F9B4357E0F4@stash.stackdump.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Howto: Using PITR recovery for standby replication Thread-Index: AcZk5RPQcy9nQxCIRO+YNDCeIV5dYgABsxDj References: <443F9BF3.4060101@laposte.net> <1145028323.5597.1.camel@localhost.localdomain> <443FDF05.4060600@laposte.net> <20060421014243.GR49405@pervasive.com> From: "Benjamin Krajmalnik" To: X-Archive-Number: 200604/210 X-Sequence-Number: 21379 This is a multi-part message in MIME format. ------_=_NextPart_001_01C664EF.851215B1 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am running PostgreSQL 8.1.3 on Windows. The project itself is a real time data acquisition system, so it cannot = be taken off-line for backups. I have tried using pg_dump, but discovered that the backup was not a = consistent backup. The application currently inserts about 1 million = rows per day (will ramp to about 5 million when in full production). = All of the insertion of the data is controlledby a master stored = procedure which inserts rows into a raw log, and dynamically aggregates = data into anclliary tables which enable us to see statistical data of = the systems being monitored withut having to mine the raw data. The original prototype of this system was running onder MS SQL Server = 2000, but once PostgreSQL 8.1 was released I decided to port it. The = biggest challenge which I have right now is to ensure that we can have = data recovery in case of a catastrophic failure in the primary system - = with the ability to load a "cold spare". 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), and ths would = indicate to me that the backup of an aggregate table may not match the = underlying raw data which created it. As such, my only option is to create a "hot backup" using PITR. I would = like to know if the following scenario would work: A secondary server with the same version of PostgreSQL is loaded on a = secondary server. The PostgreSQL service on the second box would not be = running. I would issue a pg_start_backup. I would then copy the the = database directory to the second box. Issue a pg_stop_backup. I would = delete the WAL logs form the secondary box's pg_xlog. I wuld then copy = the archived WAL's as well as the current WAL to the secondary pg_xlog = location. In could then backup the snapshot from the secondatry box to a lesser = media for archival purposes, and in the event of a problem, I would = simply start the service on he secondary box. Is this a workable solution? Or, better yet, could th secondary be ive = and, after the initial backup and restore from the main box, could = replication be acomplished by somehow moving the new archived logs to = the secondary box, thereby creating a timed replication (forexample, = every hour we cold create anothe backup ad just move the WAL file oer, = since the state of the secondary database shoud reflect the state of the = previous bacukp)? While I absolutely love PotgreSQL, and together with some of the add-ons = (pgAdmin, pgAgent, the add-ons from EMS) there is alost nothing missing, = the relative difficulty of backing up / restoring vis-a-vis the = commercial solutions is frustrating. Not hat it is a PostgreQL = problem, but rather a learning curve, but until I get this working = atisfactorily I am a bit worried. As always, any insight and assistance ill be deeply appreciated. =20 Regards, Benjamin =20 =20 ------_=_NextPart_001_01C664EF.851215B1 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0A= =0A= =0A= =0A= =0A= =0A= Re: [ADMIN] Slony1 or DRBD for replication ?=0A= =0A= =0A=

I am running PostgreSQL 8.1.3 on Windows.

=0A=

The project itself is a real time data acquisition system, so it = cannot be =0A= taken off-line for backups.

=0A=

I have tried using pg_dump, but discovered that the backup was not a =0A= consistent backup. The application currently inserts about 1 million = rows per =0A= day (will ramp to about 5 million when in full production).  All of = the =0A= insertion of the data is controlledby a master stored procedure which = inserts =0A= rows into a raw log, and dynamically aggregates data into anclliary = tables which =0A= enable us to see statistical data of the systems being monitored withut = having =0A= to mine the raw data.

=0A=

The original prototype of this system was running onder MS SQL Server = 2000, =0A= but once PostgreSQL 8.1 was released I decided to port it.  The = biggest =0A= challenge which I have right now is to ensure that we can have data = recovery in =0A= case of a catastrophic failure in the primary system - with the ability = to load =0A= a "cold spare".

=0A=

Back to the problem I faced when testing backups with pg_dump, it = appears =0A= that the backup was not a consistent backup of the data.  For = example, =0A= sequences which are used by some tables bo longer held the correct = values (the =0A= tables now held higher values), and ths would indicate to me that the = backup of =0A= an aggregate table may not match the underlying raw data which created = it.

=0A=

As such, my only option is to create a "hot backup" using PITR.  = I would =0A= like to know if the following scenario would work:

=0A=

A secondary server with the same version of PostgreSQL is loaded on a =0A= secondary server.  The PostgreSQL service on the second box would = not be =0A= running.  I would issue a pg_start_backup.  I would then = copy  =0A= the the database directory to the second box.  Issue a =0A= pg_stop_backup.  I would delete the WAL logs form the secondary = box's =0A= pg_xlog.  I wuld then copy the archived WAL's as well as the = current WAL to =0A= the secondary pg_xlog location.

=0A=

In could then backup the snapshot from the secondatry box to a lesser = media =0A= for archival purposes, and in the event of a problem, I would simply = start the =0A= service on he secondary box.

=0A=

Is this a workable solution?  Or, better yet, could th secondary = be ive =0A= and, after the initial backup and restore from the main box, could = replication =0A= be acomplished by somehow moving the new archived logs to the secondary = box, =0A= thereby creating a timed replication (forexample, every hour we cold = create =0A= anothe backup ad just move the WAL file oer, since the state of the = secondary =0A= database shoud reflect the state of the previous bacukp)?

=0A=

While I absolutely love PotgreSQL, and together with some of the = add-ons =0A= (pgAdmin, pgAgent, the add-ons from EMS) there is alost nothing missing, = the =0A= relative difficulty of backing up / restoring vis-a-vis the commercial = solutions =0A= is  frustrating.  Not hat it is a PostgreQL problem, but = rather a =0A= learning curve, but until I get this working atisfactorily I am a bit =0A= worried.

=0A=

As always, any insight and assistance ill be deeply appreciated.

=0A=

 

=0A=

Regards,

=0A=

Benjamin

=0A=

 

=0A=

 

=0A= =0A= =0A= ------_=_NextPart_001_01C664EF.851215B1--