X-Original-To: pgsql-admin-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 6A76C9FA188
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Sun, 18 Jun 2006 18:30:03 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 18335-05 for <pgsql-admin-postgresql.org@postgresql.org>;
	Sun, 18 Jun 2006 18:29:59 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.200])
	by postgresql.org (Postfix) with ESMTP id 05BD79FA13E
	for <pgsql-admin@postgresql.org>; Sun, 18 Jun 2006 18:29:58 -0300 (ADT)
Received: by wx-out-0102.google.com with SMTP id h30so750891wxd
	for <pgsql-admin@postgresql.org>; Sun, 18 Jun 2006 14:29:58 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=sAzJiF3PZBR2WPX0ey61EELQI0wsXZrWTZxHgb96D2h9nPYxGKeB360IRrq95scuTx/JldGh9CFG+YqYkgDzBhyHHOTnilJ0bZG3MccxjPZ3xz9uPVkbS2EVV07+2T2L/64KbTlpYB95gPdnst9gmPxikQaqiQLtNYVrHzoN2Yo=
Received: by 10.70.32.20 with SMTP id f20mr7289324wxf;
	Sun, 18 Jun 2006 14:29:57 -0700 (PDT)
Received: by 10.70.66.15 with HTTP; Sun, 18 Jun 2006 14:29:57 -0700 (PDT)
Message-ID: <bf05e51c0606181429j75cfa4a9qdb7654d2b9b85d2c@mail.gmail.com>
Date: Sun, 18 Jun 2006 16:29:57 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: pg_restore
Cc: "Infor Gates" <info_gates@yahoo.com>, pgsql-admin@postgresql.org
In-Reply-To: <25775.1150607196@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_85085_15155495.1150666197635"
References: <20060618025541.91508.qmail@web31110.mail.mud.yahoo.com>
	<25775.1150607196@sss.pgh.pa.us>
X-Google-Sender-Auth: 502c67b744ce9714
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/144
X-Sequence-Number: 22106

------=_Part_85085_15155495.1150666197635
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I agree.  When restoring a database from back up, I do a drop database and
recreate it to make sure everything is properly intact (tables, columns,
views, triggers, foreign keys, etc...).  We do this a lot for testing.  We
backup the production database, copy to test server and do a restore on the
test database.  It helps us test our code rollouts.

-Aaron

On 6/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Infor Gates <info_gates@yahoo.com> writes:
> > I am having the impression that pg_restore would over-rides the "old"
> > data with the current one. Is my thinking wrong?
>
> Yeah.  By default, pg_restore will issue a CREATE TABLE (which of course
> fails if the table already exists) followed by COPY (which just tries to
> insert data in addition to what might be there already).
>
> There's a command line option to ask pg_restore to try to DROP TABLE
> before doing the CREATE TABLE.  By and large, though, that's a bad way
> to proceed unless you are specifically trying to merge two databases.
> The fast and easy way to proceed is to DROP DATABASE, CREATE a fresh
> empty database, and pg_restore into that.
>
>                         regards, tom lane

------=_Part_85085_15155495.1150666197635
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I agree.&nbsp; When restoring a database from back up, I do a drop database and recreate it to make sure everything is properly intact (tables, columns, views, triggers, foreign keys, etc...).&nbsp; We do this a lot for testing.&nbsp; We backup the production database, copy to test server and do a restore on the test database.&nbsp; It helps us test our code rollouts.
<br><br>-Aaron<br><br><div><span class="gmail_quote">On 6/18/06, <b class="gmail_sendername">Tom Lane</b> &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Infor Gates &lt;<a href="mailto:info_gates@yahoo.com">info_gates@yahoo.com</a>&gt; writes:<br>&gt; I am having the impression that pg_restore would over-rides the &quot;old&quot;<br>&gt; data with the current one. Is my thinking wrong?
<br><br>Yeah.&nbsp;&nbsp;By default, pg_restore will issue a CREATE TABLE (which of course<br>fails if the table already exists) followed by COPY (which just tries to<br>insert data in addition to what might be there already).<br><br>
There's a command line option to ask pg_restore to try to DROP TABLE<br>before doing the CREATE TABLE.&nbsp;&nbsp;By and large, though, that's a bad way<br>to proceed unless you are specifically trying to merge two databases.<br>The fast and easy way to proceed is to DROP DATABASE, CREATE a fresh
<br>empty database, and pg_restore into that.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;regards, tom lane</blockquote></div>

------=_Part_85085_15155495.1150666197635--