473,327 Members | 2,118 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 1845
-----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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sam | last post by:
I'm having trouble restoring databases that have to lo type installed in /contrib/lo. The dump seems to work just fine, I get no errors when I execute the following command #pg_dump -Fc -o -b...
4
by: Mark Mikulec | last post by:
Hi there, I wonder if anyone can shed some light on a very frustrating problem. I'm running a debian linux 3.0 "woody" server, nothing special, with the latest version of postres that apt-get...
7
by: Howard Lowndes | last post by:
My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2 I have no difficulty inserting and managing BLOBs into the Large Object system table, and I have a user table called images...
1
by: Ruth Hsieh | last post by:
The postgreSQL release used is 7.3.4. We would like to dump the objects for particular user. How? I used -U option, the result still dump all the objects with all the users. In order to...
0
by: Aienthiwan | last post by:
Hello all, I'm running a debian server and recently updated my version of PostgreSQL to "unstable", that being v7.4.1. I had no trouble at all getting everything up to date and going. But my...
0
by: Otto Blomqvist | last post by:
Hello ! I was just wondering if anyone knows if this is a bug or whats up. Im using psql 7.2.2 I can do full restores and dumps all day long, however when I do the following I run into...
7
by: Tim Penhey | last post by:
Maybe it's just me, but I can't seem to get pg_restore to restore a database... I am running 8.0 beta 2 (using the dev3 installer) on Windows XP. I created a very simple database with one...
1
by: ruben | last post by:
Hi: I'm trying to dump tableA and restore it to tableB: $ ./pg_dump -Fc -t tableA databaseA -f tableA.dump -v $ ./pg_restore -t tableB -d databaseA tableA.dump -v pg_dump creates...
2
by: amievil | last post by:
Hello. I'm developing an application and it has to backup and restore database using pg_dump & pg_restore. the database is PostgreSql7.2.3 and pg_dump.exe & pg_restore.exe is from pg admin...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.