Backup&Restore Postgres DB

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

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

So, to restore the db from scratch, for example to a new system, what should
i do?
it automatically creates all users, rolls, permissions etc?How?

What is the best way of doing it?


Many thanks

--
View this message in context: http://old.nabble.com/Backup-Restore-Postgres-DB-tp27984540p 27984540.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

Re: Backup&Restore Postgres DB

--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 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

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-tp27984540p 27984540.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's break it down individually:

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 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
>
> 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-tp27984540p 27984540.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-tp27984540p 27998906.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