Re: Backup&Restore Postgres DB

Lots there, let's break it down individually:

On Mon, Mar 22, 2010 at 6:38 AM, blast <carlosd2002(at)hotmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>