From enio@pmpf.rs.gov.br Wed Aug 13 17:13:11 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 86C9FD1B56C for ; Wed, 13 Aug 2003 17:13:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 48799-01 for ; Wed, 13 Aug 2003 14:11:34 -0300 (ADT) Received: from mail.passofundo.rs.gov.br (unknown [200.182.187.131]) by svr1.postgresql.org (Postfix) with ESMTP id 69C9AD1C4BF for ; Wed, 13 Aug 2003 14:12:41 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.passofundo.rs.gov.br (Postfix) with ESMTP id 5C1D046E23 for ; Wed, 13 Aug 2003 14:07:48 -0300 (BRT) Received: from compaq (unknown [10.0.5.226]) by mail.passofundo.rs.gov.br (Postfix) with SMTP id CDB6446E1A for ; Wed, 13 Aug 2003 14:07:45 -0300 (BRT) Message-ID: <001a01c361bd$f134d2f0$e205000a@compaq> From: "Enio Schutt Junior" To: Subject: Backup procedure Date: Wed, 13 Aug 2003 14:11:31 -0300 Organization: pmpf MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0017_01C361A4.CB9905C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS perl-11 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_30_40 X-Spam-Level: X-Archive-Number: 200308/159 X-Sequence-Number: 9881 This is a multi-part message in MIME format. ------=_NextPart_000_0017_01C361A4.CB9905C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, According to some answers about the consistency of a database in a snapshot= of a whole=20 HD (excluding proc, tmp, and dev, of course) and without stopping the postm= aster service,=20 it is said it should work because it's like if the system has crashed, so r= estoring files from=20 the HD and starting the server would be ok. According to postgresql administrator's guide, section 9.2 (File system lev= el backup),=20 item #1: "The database server *must* be shutdown in order to get a usable b= ackup. Half-way=20 measures such as disallowing all connections will not work as there is alwa= ys some buffering=20 going on. For this reason it is also not advisable to trust file systems th= at claim to support=20 "consistent snapshots" ". Assuming the admin's guide is a "little bit more" right, my backup restore = procedure would be as follows: 1-Restore data from the backup Hd into a new Hd. 2-Copy some files from /usr/local/pgsql/data like postgresql.conf, pg_hba.c= onf, pg_ident.conf,=20=20 and some other authentication files whose names appear in pg_hba.conf (I su= spect PG_VERSION and=20 postmaster.opts would not be needed as initdb should create these files) to= another directory. 3-Remove the entire /usr/local/pgsql/data directory 4-Run initdb -D "/usr/local/pgsql/data" 5-Copy those files back to /usr/local/pgsql/data 6-Start the server 7-Restore information generated by pg_dump (tables) and pg_dumpall -g (user= s and groups) Would this work well? I know there are other procedures and I would be plea= sed to know that this procedure works fine.=20 Thanks in advance, Enio ------=_NextPart_000_0017_01C361A4.CB9905C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
According to some answers about the consis= tency of=20 a database in a snapshot of a whole
HD (excluding proc, tmp, and dev, of=20 course) and without stopping the postmaster service,
it is said it should work because it's lik= e=20 if th= e system has=20 crashed, so restoring files from
the HD and starting the server would be=20 ok.
 
According to postgresql administrator's gu= ide,=20 section 9.2 (File system level backup),
item #1: "The database server *must* be sh= utdown in=20 order to get a usable backup. Half-way
measures such as disallowing all connectio= ns will=20 not work as there is always some bu= ffering=20
going on. For this reason it is also not a= dvisable=20 to trust file systems that claim to supp= ort=20
"consistent snapshots" ".
 
Assuming the admin's guide is a "little bi= t more"=20 right, my backup restore procedure would
be as follows:
 
1-Restore data from the backup Hd into a n= ew=20 Hd.
2-Copy some files from /usr/local/pgsql/data like postgresql.conf,= =20 pg_hba.conf, pg_ident.conf, 
and some other authentication files w= hose=20 names appear in pg_hba.conf (I suspect PG_VERSION and
postmaster.opts would not be needed as ini= tdb=20 should create these files) to another directory.
3-Remove the entire=20 /usr/local/pgsql/data directory
4-Run initdb -D=20 "/usr/local/pgsql/data"
5-Copy those files back to=20 /usr/local/pgsql/data
6-Start the server
7-Restore information genera= ted=20 by pg_dump (tables) and pg_dumpall -g (users and groups)
 
Would this work well? I know there are oth= er=20 procedures and I would be pleased to know that
this procedure works fine. 
 
Thanks in advance,
Enio
------=_NextPart_000_0017_01C361A4.CB9905C0-- From murthy.kambhampaty@goeci.com Wed Aug 13 22:11:31 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0BD7BD1B53A for ; Wed, 13 Aug 2003 22:11:31 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 75782-06 for ; Wed, 13 Aug 2003 19:09:55 -0300 (ADT) Received: from THOR.goeci.com (thor.goeci.com [66.28.220.99]) by svr1.postgresql.org (Postfix) with ESMTP id 68702D1B52C for ; Wed, 13 Aug 2003 19:11:17 -0300 (ADT) Received: by THOR.goeci.com with Internet Mail Service (5.5.2653.19) id ; Wed, 13 Aug 2003 18:10:39 -0400 Message-ID: <2D92FEBFD3BE1346A6C397223A8DD3FC0923A6@THOR.goeci.com> From: Murthy Kambhampaty To: 'Enio Schutt Junior' , pgsql-admin@postgresql.org Subject: Re: Backup procedure Date: Wed, 13 Aug 2003 18:10:39 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-1" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/165 X-Sequence-Number: 9887 I think its fair to say that the documentation is outdated on this subject, becuase it had in mind a different definition of "consistent snapshots". See: http://marc.theaimsgroup.com/?l=postgresql-admin&w=2&r=1&s=LVM+snapshots&q=b where Tom Lane and Peter Eisentraut agree that if your hardware or software can copy the data and log files from a given instant in time, you have a consistent backup. (In that thread, you can even see examples for the postmaster logs at startup with the backup data. ) Bruce Momjian makes the same point earlier in this thread as well. Note that the way to recover data from filesystem level backup is to start a postmaster on the backup copy of the $PGDATA cluster, dump from the backup and restore into the "production" databases. (Piping this will avoid having to create dump files to restore from.) You can't simply copy files around to recover data from backup. The bottom line is that if you want backups that are as reliable as pgdump-s (the gold standard), you can get them off of filesystem snapshots. Filesystem snapshots are a lot faster than pgdump-s. Cheers, Murthy -----Original Message----- From: Enio Schutt Junior [mailto:enio@pmpf.rs.gov.br] Sent: Wednesday, August 13, 2003 13:12 To: pgsql-admin@postgresql.org Subject: [ADMIN] Backup procedure Hi, According to some answers about the consistency of a database in a snapshot of a whole HD (excluding proc, tmp, and dev, of course) and without stopping the postmaster service, it is said it should work because it's like if the system has crashed, so restoring files from the HD and starting the server would be ok. According to postgresql administrator's guide, section 9.2 (File system level backup), item #1: "The database server *must* be shutdown in order to get a usable backup. Half-way measures such as disallowing all connections will not work as there is always some buffering going on. For this reason it is also not advisable to trust file systems that claim to support "consistent snapshots" ". Assuming the admin's guide is a "little bit more" right, my backup restore procedure would be as follows: 1-Restore data from the backup Hd into a new Hd. 2-Copy some files from /usr/local/pgsql/data like postgresql.conf, pg_hba.conf, pg_ident.conf, and some other authentication files whose names appear in pg_hba.conf (I suspect PG_VERSION and postmaster.opts would not be needed as initdb should create these files) to another directory. 3-Remove the entire /usr/local/pgsql/data directory 4-Run initdb -D "/usr/local/pgsql/data" 5-Copy those files back to /usr/local/pgsql/data 6-Start the server 7-Restore information generated by pg_dump (tables) and pg_dumpall -g (users and groups) Would this work well? I know there are other procedures and I would be pleased to know that this procedure works fine. Thanks in advance, Enio From gourish@gmail.com Mon Mar 7 15:34:09 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EE175564C7 for ; Mon, 7 Mar 2005 15:34:09 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94662-08 for ; Mon, 7 Mar 2005 15:34:01 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.199]) by svr1.postgresql.org (Postfix) with ESMTP id 860C75645A for ; Mon, 7 Mar 2005 15:33:59 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id j1so1098435rnf for ; Mon, 07 Mar 2005 07:34:04 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=V/uVCpUEPr/UVeVQt1L0+UysBuysIzS7gklMB+IPWUFrKJY85PijSnXmjo+7ob9PYqnbBfeyhlafyVopwKk7cRNoO3hBlGyh5B9Xn+MTFj68iRxLk9c+gCJ/Jgu/UsHgfLI4mBaPrWZ8Kduej71FMDDgPszJrug2wKzfRaQr2Xo= Received: by 10.38.14.12 with SMTP id 12mr47410rnn; Mon, 07 Mar 2005 07:34:04 -0800 (PST) Received: by 10.38.97.43 with HTTP; Mon, 7 Mar 2005 07:34:04 -0800 (PST) Message-ID: <674d1f8a050307073432c0f475@mail.gmail.com> Date: Mon, 7 Mar 2005 21:04:04 +0530 From: Gourish Singbal Reply-To: Gourish Singbal To: pgsql-admin@postgresql.org Subject: Backup procedure Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/88 X-Sequence-Number: 16586 Hi, Can anybody provided me the procedure to take online backups in 8.0.1. Simple steps would be really great.. -- Best, Gourish Singbal From mike@fuhr.org Mon Mar 7 16:00:32 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8091756520 for ; Mon, 7 Mar 2005 16:00:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09300-02 for ; Mon, 7 Mar 2005 16:00:31 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id D4C1456513 for ; Mon, 7 Mar 2005 16:00:29 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j27G0OE3009797 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Mon, 7 Mar 2005 09:00:26 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j27G0NHK027975; Mon, 7 Mar 2005 09:00:23 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j27G0NSR027974; Mon, 7 Mar 2005 09:00:23 -0700 (MST) (envelope-from mfuhr) Date: Mon, 7 Mar 2005 09:00:23 -0700 From: Michael Fuhr To: Gourish Singbal Cc: pgsql-admin@postgresql.org Subject: Re: Backup procedure Message-ID: <20050307160023.GA27925@winnie.fuhr.org> References: <674d1f8a050307073432c0f475@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <674d1f8a050307073432c0f475@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/93 X-Sequence-Number: 16591 On Mon, Mar 07, 2005 at 09:04:04PM +0530, Gourish Singbal wrote: > Can anybody provided me the procedure to take online backups in 8.0.1. > Simple steps would be really great.. Does the "Backup and Restore" chapter in the documentation not answer your questions? If not, could you be more specific about what you're trying to do and what problems you've having? http://www.postgresql.org/docs/8.0/interactive/backup.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ From adler@pobox.com Mon Mar 7 16:47:23 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CDC14564AB for ; Mon, 7 Mar 2005 16:47:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27148-02 for ; Mon, 7 Mar 2005 16:47:21 +0000 (GMT) Received: from mail3.panix.com (mail3.panix.com [166.84.1.74]) by svr1.postgresql.org (Postfix) with ESMTP id 6EE5556482 for ; Mon, 7 Mar 2005 16:47:20 +0000 (GMT) Received: from panix2.panix.com (panix2.panix.com [166.84.1.2]) by mail3.panix.com (Postfix) with ESMTP id BFCEF981A7; Mon, 7 Mar 2005 11:47:19 -0500 (EST) Received: (from adler@localhost) by panix2.panix.com (8.11.6p3/8.8.8/PanixN1.1) id j27GlJv01359; Mon, 7 Mar 2005 11:47:19 -0500 (EST) Date: Mon, 7 Mar 2005 11:47:17 -0500 From: Michael Adler To: Gourish Singbal Cc: pgsql-admin@postgresql.org Subject: Re: Backup procedure Message-ID: <20050307164717.GA12129@pobox.com> References: <674d1f8a050307073432c0f475@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <674d1f8a050307073432c0f475@mail.gmail.com> User-Agent: Mutt/1.4.2.1i X-Hashcash: 1:20:050307:gourish@gmail.com::Uk45bcOlzeydnX/0:00000000000000000000 00000000000000000000000068O8 X-Hashcash: 1:20:050307:pgsql-admin@postgresql.org::Nds//mIC/Zw1/nY3:00000000000 0000000000000000000000002Do/ X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/95 X-Sequence-Number: 16593 On Mon, Mar 07, 2005 at 09:04:04PM +0530, Gourish Singbal wrote: > Hi, > > Can anybody provided me the procedure to take online backups in 8.0.1. > Simple steps would be really great.. > > -- > Best, > Gourish Singbal > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster try pg_dump(1) http://www.postgresql.org/docs/current/static/app-pgdump.html -Mike