469,643 Members | 1,890 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

pdql, pg_dump, and pg_restore

Hello,

I have an SQL database which I create with:

psql -f create.sql foodb

I then access this database and perform several insertions, modifications,
and deletions. Finally, I want to backup my database. I do not want to
backup the schema. All I want is a set of insert statements stored in
a file insert.sql which I can run on a set of empty database tables
with the command:

psql -f insert.sql foodb

in order to restore the contents of the database to what it was previously.

I have tried the utilities pg_dump and pg_restore and
read the manual pages, but after running pg_dump I get a file with the whole
schema, not exactly what I wanted. And when I restore it my application
does not find what it needs any more.

So my question is, how do I properly do the type of dump and restore that
I have described above. I don't care which file format, as long as I can
just repopulate empty tables. I am not sure what I am doing wrong, but
the problem I am having is that upon restoring the dump my application
does not find the appropriate fields in the appropriate tables. ???

All that should be needed is one command to dump, and one to restore, right?

Thanks,

Neil
Nov 23 '05 #1
1 1683
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Probably you haven't read the manpage good enough :-)
There are switches to pg_dump to turn off the schema, reconnects, owner
information etc.
Here's what you're probably looking for:

pg_dump -R -O -a -i -d -f backupfile.sql database_name

This will do:

no reconnect
no owner
ignore postgres server version missmatch
dump data only
dump as full sql insert statements (no copy)
it will backup everything in database "database_name" to file backupfile.sql.

Hope that helps

UC

On Saturday 31 July 2004 12:57 pm, Neil Zanella wrote:
Hello,

I have an SQL database which I create with:

psql -f create.sql foodb

I then access this database and perform several insertions, modifications,
and deletions. Finally, I want to backup my database. I do not want to
backup the schema. All I want is a set of insert statements stored in
a file insert.sql which I can run on a set of empty database tables
with the command:

psql -f insert.sql foodb

in order to restore the contents of the database to what it was previously.

I have tried the utilities pg_dump and pg_restore and
read the manual pages, but after running pg_dump I get a file with the
whole schema, not exactly what I wanted. And when I restore it my
application does not find what it needs any more.

So my question is, how do I properly do the type of dump and restore that
I have described above. I don't care which file format, as long as I can
just repopulate empty tables. I am not sure what I am doing wrong, but
the problem I am having is that upon restoring the dump my application
does not find the appropriate fields in the appropriate tables. ???

All that should be needed is one command to dump, and one to restore,
right?

Thanks,

Neil

---------------------------(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


- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBEv/AjqGXBvRToM4RAk8sAJ9015oyCufcIIb7pLnC2H4IpZK1oQCgq/sM
dJOPHL7KWbLnYZgyytb6JnQ=
=pOuI
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sam | last post: by
7 posts views Thread by Howard Lowndes | last post: by
1 post views Thread by Ruth Hsieh | last post: by
reply views Thread by Otto Blomqvist | last post: by
7 posts views Thread by Tim Penhey | last post: by
1 post views Thread by ruben | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.