Thread: saving a view with pg_dump
Howdy:
Running PostgreSQL 7.1.3 (migrating to 7.2.1) and
I'm dumping the database with pg_dump (from the 7.2.1
version).
Is it possible to use pg_dump to create a file for
a view / sequence / etc? I don't have all of the scripts
that recreate all of the views, so I'm wondering if it's
better to use pg_dump (if I can) and make a copy of them.
Thanks!
-X
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> Running PostgreSQL 7.1.3 (migrating to 7.2.1) and
> I'm dumping the database with pg_dump (from the 7.2.1
> version).
> Is it possible to use pg_dump to create a file for
> a view / sequence / etc?
pg_dump should save all that stuff automatically.
regards, tom lane
--Thanks for the reply:
--I tried to do a dump of the entire database
to a partition and it choked on 2.1 Gig size
... the message was:
snip [error]
sh: line 34: 13302 Broken pipe /backup/pg_dump -U postgres bcn
13303 File size limit exceeded| /bin/gzip >/backup/tools/bcndmp.gz
[/snip error]
--If I could make some room on the same machine / filesystem, it
wouldn't be a problem. So now that I'm breaking it up,
I'm thinking I have to do the same for views, triggers, sequences,
etc ...
--Can pg_dump could do that?
-X
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, August 02, 2002 8:07 PM
To: Johnson, Shaunn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] saving a view with pg_dump
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> Running PostgreSQL 7.1.3 (migrating to 7.2.1) and
> I'm dumping the database with pg_dump (from the 7.2.1
> version).
> Is it possible to use pg_dump to create a file for
> a view / sequence / etc?
pg_dump should save all that stuff automatically.
regards, tom lane
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes: > sh: line 34: 13302 Broken pipe /backup/pg_dump -U postgres bcn > 13303 File size limit exceeded| /bin/gzip >/backup/tools/bcndmp.gz split is your friend. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/backup.html#BACKUP-DUMP-LARGE regards, tom lane
--Howdy
--I'm looking at the page for restoration and I'm having a few
problems.
--In the docs, it suggest that I do the cat file* | psql database,
but I get other errors with using the cat command
[snip error]
bash: /bin/cat: Argument list too long
[/snip error]
--I'm trying to do something like `ls -1` files* | psql database
and I'm not getting any successful messages.
--I got a few other errors ... I supposed I was thinking
that the system files from before would be save and I wouldn't
have to create those by hand (users, groups, etc).
--I know this seems silly, but, why can't I copy the original
files back to the database directory after I've upgraded
postgres? doesn't that give you most of the same things
(OID, system files, users, etc ... )?
--Just curious ...
-X
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, August 02, 2002 8:16 PM
To: Johnson, Shaunn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] saving a view with pg_dump
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> sh: line 34: 13302 Broken pipe /backup/pg_dump -U postgres bcn
> 13303 File size limit exceeded| /bin/gzip >/backup/tools/bcndmp.gz
split is your friend. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/backup.html#BACKUP-DUMP-LARGE
regards, tom lane
On Sat, 2002-08-03 at 22:53, Johnson, Shaunn wrote:
>
> --Howdy
>
> --I'm looking at the page for restoration and I'm having a few
> problems.
>
> --In the docs, it suggest that I do the cat file* | psql database,
> but I get other errors with using the cat command
>
> [snip error]
> bash: /bin/cat: Argument list too long
You could say:
for f in file*
do
psql -d database < $f
done
or
ls file* | xargs cat | psql -d database
but how do you come to have so many files defining the structure and
contents of a single database? That looks very odd to me. Or did you
just split it into too small chunks? (You should have used something
like "split -b 1900m" to make 1.9Gb chunks.)
> [/snip error]
>
> --I'm trying to do something like `ls -1` files* | psql database
> and I'm not getting any successful messages.
1. That should be `ls -l files*` with the backticks enclosing the whole
command. (You had file* above - which is right?)
2. That would feed the output of ls -l into psql, which is not at all
what you want to do - it is a long directory listing, not SQL.
> --I got a few other errors ... I supposed I was thinking
> that the system files from before would be save and I wouldn't
> have to create those by hand (users, groups, etc).
If this is for a database upgrade, you should be using pg_dumpall to
dump all databases at once, and the users and groups as well. (Then
don't specify the database to psql when you restore.)
> --I know this seems silly, but, why can't I copy the original
> files back to the database directory after I've upgraded
> postgres? doesn't that give you most of the same things
> (OID, system files, users, etc ... )?
pg_upgrade lets you do this, but its status is somewhat experimental.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ, nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20