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 A92F211F605F for ; Fri, 21 Apr 2006 01:46:23 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.238 X-Spam-Level: X-Spam-Status: No, score=0.238 required=5 tests=[AWL=0.237, 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 NSs8VR25n0xX for ; Fri, 21 Apr 2006 01:46:20 -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 AFAA411F6077 for ; Fri, 21 Apr 2006 01:46:20 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C664FE.88861850" Subject: Re: Howto: Using PITR recovery for standby replication X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Thu, 20 Apr 2006 22:43:23 -0600 Message-ID: <8511B4970E0D124898E973DF496F9B4357E0F9@stash.stackdump.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Howto: Using PITR recovery for standby replication Thread-Index: AcZk/PnNAyOl+yvyTgy96to1Oq4+EgAASVGF 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> <20060421040224.GM5426@surnet.cl> <8511B4970E0D124898E973DF496F9B4357E0F7@stash.stackdump.local> <20060421043507.GB12614@surnet.cl> From: "Benjamin Krajmalnik" To: "Alvaro Herrera" Cc: "Tom Lane" , X-Archive-Number: 200604/221 X-Sequence-Number: 21390 This is a multi-part message in MIME format. ------_=_NextPart_001_01C664FE.88861850 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 1. Dropped database 2. Recreated blank databas 3. C:\Program Files\PostgreSQL\8.1\bin\pg_restore.exe -i -h 172.20.0.32 = -p 5432 -U postgres -d events -v "C:\Documents and = Settings\administrator.MS\testbk.backup" pg_restore: connecting to database for restore pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating TABLE appointments pg_restore: executing SEQUENCE SET appointments_id_seq pg_restore: restoring data for table "appointments" pg_restore: setting owner and privileges for SCHEMA public pg_restore: setting owner and privileges for COMMENT SCHEMA public pg_restore: setting owner and privileges for ACL public pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql pg_restore: setting owner and privileges for TABLE appointments Process returned exit code 0. (the above is the result of a sample restore - the database is a simple = database with very few records, and was backed up when no activity was = taking place against it, unlike our production database). =20 Maybe I am using the wrong switches altogether to accomplish my end = results. =20 Thanks a million for your willingness to point me in the right = direction. ________________________________ From: Alvaro Herrera [mailto:alvherre@commandprompt.com] Sent: Thu 4/20/2006 10:35 PM To: Benjamin Krajmalnik Cc: Tom Lane; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication Benjamin Krajmalnik wrote: > I am a newbie, so I essentially invoked pg_dump from with pgAdmin3, > with the defaults (including large objects). This is the command > being issued: >=20 > .C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe -i -h 172.20.0.32 -p = 5432 -U postgres -F c -b -v -f "C:\Documents and = Settings\administrator.MS\testbk.backup" events >=20 > What I assumed was happening (and I may have very well been wrong) was > that I was getting a consistent backup of the object at the time that > it was processed, but not the database as a whole. This command should produce a consistent dump of all the objects in the database. (Not a consistent view of each object in isolation, which is AFAIU what you are saying.) Next question is, how are you restoring this dump? -- Alvaro Herrera = http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ------_=_NextPart_001_01C664FE.88861850 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=
1.  = Dropped =0A= database
=0A=
2.  Recreated blank =0A= databas
=0A=
3.  C:\Program =0A= Files\PostgreSQL\8.1\bin\pg_restore.exe -i -h 172.20.0.32 -p 5432 -U = postgres -d =0A= events -v "C:\Documents and =0A= Settings\administrator.MS\testbk.backup"
pg_restore: connecting to = database =0A= for restore
pg_restore: creating SCHEMA public
pg_restore: = creating =0A= COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE =0A= plpgsql
pg_restore: creating TABLE appointments
pg_restore: = executing =0A= SEQUENCE SET appointments_id_seq
pg_restore: restoring data for table =0A= "appointments"
pg_restore: setting owner and privileges for SCHEMA =0A= public
pg_restore: setting owner and privileges for COMMENT SCHEMA =0A= public
pg_restore: setting owner and privileges for ACL = public
pg_restore: =0A= setting owner and privileges for PROCEDURAL LANGUAGE = plpgsql
pg_restore: =0A= setting owner and privileges for TABLE appointments
=0A=
Process returned exit code =0A= 0.
=0A=
(the above is the result of a = sample =0A= restore - the database is a simple database with very few records, and = was =0A= backed up when no activity was taking place against it, unlike our = production =0A= database).
=0A=
 
=0A=
Maybe I am using the wrong = switches =0A= altogether to accomplish my end results.
=0A=
 
=0A=
Thanks a million for your = willingness to =0A= point me in the right direction.
=0A=

=0A=
=0A= From: Alvaro Herrera =0A= [mailto:alvherre@commandprompt.com]
Sent: Thu 4/20/2006 10:35 =0A= PM
To: Benjamin Krajmalnik
Cc: Tom Lane; =0A= pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Howto: Using = PITR =0A= recovery for standby replication

=0A=
=0A=

Benjamin Krajmalnik wrote:

> I am a newbie, = so I =0A= essentially invoked pg_dump from with pgAdmin3,
> with the = defaults =0A= (including large objects).  This is the command
> being =0A= issued:
> 
> .C:\Program = Files\PostgreSQL\8.1\bin\pg_dump.exe =0A= -i -h 172.20.0.32 -p 5432 -U postgres -F c -b -v -f "C:\Documents and =0A= Settings\administrator.MS\testbk.backup" events
> 
> = What I =0A= assumed was happening (and I may have very well been wrong) was
> = that I =0A= was getting a consistent backup of the object at the time that
> = it was =0A= processed, but not the database as a whole.

This command should = produce a =0A= consistent dump of all the objects in the
database.  (Not a = consistent =0A= view of each object in isolation, which is
AFAIU what you are =0A= saying.)

Next question is, how are you restoring this =0A= dump?

--
Alvaro =0A= Herrera           =             &= nbsp;        =0A= http://www.CommandPrompt.com/<= BR>PostgreSQL =0A= Replication, Consulting, Custom Development, 24x7 =0A= support

=0A= =0A= =0A= ------_=_NextPart_001_01C664FE.88861850--