Re: Backup&Restore Postgres DB

Hi Scott,

I m real new in this so be patient :)

I check in postgres and:

radius-# \l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
 postgres  | postgres | UTF8
 radius    | postgres | UTF8
 root      | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8


The database that i real need is radius, i suppose that the others are
defaults databases from postgres, i was thinking and probably the  best is
to backup and restore all since i don't know what they are and well since is
to restore...

So probably the best is to backup and restore radius with no data (i don't
want data), and the rest with data.

pg_dumpall -g > users.sql --> it will give ALL needed stuff (usr,
permissions,etc) for all DBs?

psql -d [database] -f users.sql --> i have to this command for each DBs that
i have (5)?


----supposing that now i have all permissions stuff--

For radius
pg_dump -c -C -s  radius > radius.out --> structure of the database radius
(i probably made confusion with what is a schema before)

psql -d radius -f radius.out


For all others
pg_dump [db_name] > [db_name].out  --> this is dumping schema and data,
correct?
psql -d [db_name]-f [db_name].out --> this is restoring schema and data,
correct?


i read the manual and there are something called large objects, how do i
know if i have that, because the commands are different if i have it.

Thanks



Scott Mead-3 wrote:
>
> 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
>>
>
>

--
View this message in context: http://old.nabble.com/Backup-Restore-Postgres-DB-tp27984540p27998906.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.