Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 9FCA2633CE2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 22 Mar 2010 14:58:21 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 12991-03-5
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 22 Mar 2010 17:58:08 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og123.obsmtp.com (exprod7og123.obsmtp.com [64.18.2.24])
	by mail.postgresql.org (Postfix) with SMTP id 945AE6340CD
	for <pgsql-admin@postgresql.org>; Mon, 22 Mar 2010 14:57:58 -0300 (ADT)
Received: from source ([74.125.82.48]) by exprod7ob123.postini.com
	([64.18.6.12]) with SMTP
	ID DSNKS6evpVO5OlUF8PK8sZ7L+LlaCrQRuObt@postini.com;
	Mon, 22 Mar 2010 10:57:58 PDT
Received: by wwc33 with SMTP id 33so3277436wwc.35
	for <pgsql-admin@postgresql.org>; Mon, 22 Mar 2010 10:57:56 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.216.162.204 with SMTP id y54mr2933516wek.224.1269280675793;
	Mon, 22 Mar 2010 10:57:55 -0700 (PDT)
In-Reply-To: <27984540.post@talk.nabble.com>
References: <27984540.post@talk.nabble.com>
Date: Mon, 22 Mar 2010 13:57:55 -0400
Message-ID: <d3ab2ec81003221057g199f44f5qa37a31b31bb87e79@mail.gmail.com>
Subject: Re: Backup&Restore Postgres DB
From: Scott Mead <scott.lists@enterprisedb.com>
To: blast <carlosd2002@hotmail.com>
Cc: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary=0016367d6d92cd0cca0482676fe6
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.464 tagged_above=-10 required=5 tests=AWL=0.134,
	BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/153
X-Sequence-Number: 33952

--0016367d6d92cd0cca0482676fe6
Content-Type: text/plain; charset=ISO-8859-1

Lots there, let's break it down individually:


On Mon, Mar 22, 2010 at 6:38 AM, blast <carlosd2002@hotmail.com> wrote:

>
> Hi all,
>
> I need to backup and restore a DB.
> In this particular case the data in the database is not important (strange
> hum...) but only the schema to put new data...
>
> I m thinking use the pg_dump:
>
> pg_dump -c -C -s schema > file.out
>

Almost....

   -s says "only dump the structure of the database"
   If you want to dump a specific named schema inside the database, you'd
use:

   -n <schema>

So, what you end up with is:

 pg_dump -c -C -s -n [schema] [database] > file.out


>
> With this i have in file.out the schema, correct?
>

Yes


>
> So, to restore the db from scratch, for example to a new system, what
> should
> i do?
>

psql -d [database] -f file.out


> it automatically creates all users, rolls, permissions etc?How?
>

To create users, you need to dump them from the source db first (pg_dump
doesn't do users)...
so

pg_dumpall -g > users.sql

psql -d [database] -f users.sql

do this BEFORE you do the restore of your objects and all your permissions
etc... will be set.


>
> What is the best way of doing it?
>
>

 Good luck!

>
> Many thanks
>
> --
> View this message in context:
> http://old.nabble.com/Backup-Restore-Postgres-DB-tp27984540p27984540.html
> Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--0016367d6d92cd0cca0482676fe6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Lots there, let&#39;s break it down individually:<div><br></div><div><br><d=
iv class=3D"gmail_quote">On Mon, Mar 22, 2010 at 6:38 AM, blast <span dir=
=3D"ltr">&lt;<a href=3D"mailto:carlosd2002@hotmail.com">carlosd2002@hotmail=
.com</a>&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;"><br>
Hi all,<br>
<br>
I need to backup and restore a DB.<br>
In this particular case the data in the database is not important (strange<=
br>
hum...) but only the schema to put new data...<br>
<br>
I m thinking use the pg_dump:<br>
<br>
pg_dump -c -C -s schema &gt; file.out<br></blockquote><div><br></div><div>A=
lmost....=A0</div><div><br></div><div>=A0=A0 -s says &quot;only dump the st=
ructure of the database&quot;</div><div>=A0=A0 If you want to dump a specif=
ic named schema inside the database, you&#39;d use:</div>
<div><br></div><div>=A0=A0 -n &lt;schema&gt;</div><div><br></div><div>So, w=
hat you end up with is:</div><div><br></div><div>=A0pg_dump -c -C -s -n [sc=
hema] [database] &gt; file.out</div><div>=A0</div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex;">

<br>
With this i have in file.out the schema, correct?<br></blockquote><div><br>=
</div><div>Yes</div><div>=A0</div><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<br>
So, to restore the db from scratch, for example to a new system, what shoul=
d<br>
i do?<br></blockquote><div><br></div><div>psql -d [database] -f file.out=A0=
</div><div>=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0=
 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
it automatically creates all users, rolls, permissions etc?How?<br></blockq=
uote><div><br></div><div>To create users, you need to dump them from the so=
urce db first (pg_dump doesn&#39;t do users)...=A0</div><div>so</div><div>
<br></div><div>pg_dumpall -g &gt; users.sql</div><div><br></div><div>psql -=
d [database] -f users.sql=A0</div><div><br></div><div>do this BEFORE you do=
 the restore of your objects and all your permissions etc... will be set.</=
div>
<div>=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;=
border-left:1px #ccc solid;padding-left:1ex;">
<br>
What is the best way of doing it?<br>
<br></blockquote><div><br></div><div><br></div><div>=A0Good luck!=A0</div><=
blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px=
 #ccc solid;padding-left:1ex;">
<br>
Many thanks<br>
<br>
--<br>
View this message in context: <a href=3D"http://old.nabble.com/Backup-Resto=
re-Postgres-DB-tp27984540p27984540.html" target=3D"_blank">http://old.nabbl=
e.com/Backup-Restore-Postgres-DB-tp27984540p27984540.html</a><br>
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.<br>
<font color=3D"#888888"><br>
<br>
--<br>
Sent via pgsql-admin mailing list (<a href=3D"mailto:pgsql-admin@postgresql=
.org">pgsql-admin@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-admin" target=3D"_blank=
">http://www.postgresql.org/mailpref/pgsql-admin</a><br>
</font></blockquote></div><br></div>

--0016367d6d92cd0cca0482676fe6--