By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,693 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,693 IT Pros & Developers. It's quick & easy.

Schema backup

P: n/a
Hello all

I need to transfer a database installation from one host to another. I need
to dump all users, databases, schemas, stored procedures, triggers, etc. but
no actual data at all. What I try to achieve is a fresh clone ready to run.
I tried pg_dump -s and pg_dumpall, but somewhere I'm missing something, so:
what is the procedure to dump all database structure alone, and what is the
proper reload procedure?

TIA
cl.

__________________________________________________ _______________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Friday 26 September 2003 14:37, Claudio Lapidus wrote:
pg_dump -s -Fp -c -f <file> <databse>
is what i use
Hello all

I need to transfer a database installation from one host to another. I need
to dump all users, databases, schemas, stored procedures, triggers, etc.
but no actual data at all. What I try to achieve is a fresh clone ready to
run. I tried pg_dump -s and pg_dumpall, but somewhere I'm missing
something, so: what is the procedure to dump all database structure alone,
and what is the proper reload procedure?

TIA
cl.

__________________________________________________ _______________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2

P: n/a
Claudio Lapidus writes:
I tried pg_dump -s and pg_dumpall, but somewhere I'm missing something, so:


Define "something".

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #3

P: n/a
"Claudio Lapidus" <cl******@hotmail.com> writes:
I need to transfer a database installation from one host to another. I need
to dump all users, databases, schemas, stored procedures, triggers, etc. but
no actual data at all. What I try to achieve is a fresh clone ready to run.
I tried pg_dump -s and pg_dumpall, but somewhere I'm missing something, so:
what is the procedure to dump all database structure alone, and what is the
proper reload procedure?


The proper procedure IMHO would be
pg_dumpall -s >dumpfile
psql template1 <dumpfile

There is a small problem with this in 7.3.* (but not before or after):
due to an oversight, that version of pgdump_all doesn't support the -s
switch. So if you are using 7.3 you have to do something like

pg_dumpall -g >dumpfile
for each database:
pg_dump -C -s database >>dumpfile
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #4

P: n/a
Tom Lane wrote:
The proper procedure IMHO would be
pg_dumpall -s >dumpfile
psql template1 <dumpfile

There is a small problem with this in 7.3.* (but not before or after):
due to an oversight, that version of pgdump_all doesn't support the -s
switch. So if you are using 7.3 you have to do something like
Ah, ah. That's why it didn't work as I was expecting. This is 7.3.2, yes.
(Peter, this is the 'something' you were asking me to define in your
response. Sorry for not having been more specific the first time.)

pg_dumpall -g >dumpfile
for each database:
pg_dump -C -s database >>dumpfile


That was what we ended up doing. It worked very well.

Thanks to all
cl.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #5

P: n/a
"Claudio Lapidus" <cl******@hotmail.com> writes:
Tom Lane wrote:
pg_dumpall -g >dumpfile
for each database:
pg_dump -C -s database >>dumpfile
That was what we ended up doing. It worked very well.


BTW, if you find yourself wanting to do this a lot, it might be worth
your time to instead fix the oversight in pg_dumpall. You'd have to
backpatch this fix:

http://developer.postgresql.org/cvsw...1=1.19&r2=1.20

which probably wouldn't be very hard...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #6

P: n/a
I also have troubles with pg_dump. I have a database called 'shipping' and
it has many tables(I populated them via a script). To dump the database I
did these steps:

su greg and then pg_dump > /tmp/greg.dmp. A file greg.dmp gets created but
it has nothing in it(0 bytes). Could you advise what was wrong, please?

Thanks in advance, Greg
""Claudio Lapidus"" <cl******@hotmail.com> wrote in message
news:BA*************************@hotmail.com...
Hello all

I need to transfer a database installation from one host to another. I need to dump all users, databases, schemas, stored procedures, triggers, etc. but no actual data at all. What I try to achieve is a fresh clone ready to run. I tried pg_dump -s and pg_dumpall, but somewhere I'm missing something, so: what is the procedure to dump all database structure alone, and what is the proper reload procedure?

TIA
cl.

__________________________________________________ _______________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #7

P: n/a
On Tuesday 30 September 2003 09:15 pm, Greg wrote:
I also have troubles with pg_dump. I have a database called 'shipping' and
it has many tables(I populated them via a script). To dump the database I
did these steps:

su greg and then pg_dump > /tmp/greg.dmp. A file greg.dmp gets created but
it has nothing in it(0 bytes). Could you advise what was wrong, please?

Thanks in advance, Greg


You forgot to tell pg_dump the name of the database. Try:
pg_dump shipping > /tmp/greg.dmp

Or, for a gzipped backup:
pg_dump shipping | gzip -c > /tmp/greg.dmp.gz

Best of luck,

Andrew Gould
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.