Postgres Pro
Downloads
Home   >   mailing lists

Re: Backup&Restore Postgres DB - Mailing list pgsql-admin

From Scott Mead
Subject Re: Backup&Restore Postgres DB
Date
Msg-id d3ab2ec81003221057g199f44f5qa37a31b31bb87e79@mail.gmail.com
Whole thread Raw
In response to Backup&Restore Postgres DB  (blast <carlosd2002@hotmail.com>)
Responses Re: Backup&Restore Postgres DB  (blast <carlosd2002@hotmail.com>)
List pgsql-admin
Tree view
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

pgsql-admin by date:

Previous
From: blast
Date:
Subject: Backup&Restore Postgres DB
Next
From: Scott Mead
Date:
Subject: Re: Store database users in custom table