Thread: Schema backup
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
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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Claudio Lapidus writes: > I tried pg_dump -s and pg_dumpall, but somewhere I'm missing something, so: Define "something". -- Peter Eisentraut peter_e@gmx.net
"Claudio Lapidus" <clapidus@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
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.
"Claudio Lapidus" <clapidus@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/cvsweb.cgi/pgsql-server/src/bin/pg_dump/pg_dumpall.c.diff?r1=1.19&r2=1.20 which probably wouldn't be very hard... regards, tom lane
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"" <clapidus@hotmail.com> wrote in message news:BAY7-F891pN0sLKDAOt0001f599@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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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