Schema backup

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

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&amp;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

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