pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup&Restore Postgres DB
3+ messages / 2 participants
[nested] [flat]
* Backup&Restore Postgres DB
@ 2010-03-22 10:38 blast <carlosd2002@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: blast @ 2010-03-22 10:38 UTC (permalink / raw)
To: pgsql-admin
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-tp27984540p27984540.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Backup&Restore Postgres DB
@ 2010-03-22 17:57 Scott Mead <scott.lists@enterprisedb.com>
parent: blast <carlosd2002@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Mead @ 2010-03-22 17:57 UTC (permalink / raw)
To: blast <carlosd2002@hotmail.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Backup&Restore Postgres DB
@ 2010-03-23 10:29 blast <carlosd2002@hotmail.com>
parent: Scott Mead <scott.lists@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: blast @ 2010-03-23 10:29 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-03-23 10:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-22 10:38 Backup&Restore Postgres DB blast <carlosd2002@hotmail.com>
2010-03-22 17:57 ` Scott Mead <scott.lists@enterprisedb.com>
2010-03-23 10:29 ` blast <carlosd2002@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox