Thread: Problems with PG_DUMP and restore
Folks, In an effort to do some general cleanup in my database functions, I dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text files. I editied and restored the schema fine. However, when I attemped to restore the data (via \i filename), it failed selectively; some tables were restored but many were not. No errors were logged. Can someone give me some help? The application is due next week, and I can't afford this setback. Grazie. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Attachment
From: "Josh Berkus" <josh@agliodbs.com> > Folks, > > In an effort to do some general cleanup in my database functions, I > dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text > files. > > I editied and restored the schema fine. However, when I attemped to > restore the data (via \i filename), it failed selectively; some tables > were restored but many were not. No errors were logged. Try having a look at the order the tables get inserted esp. with regard to any foreign keys etc. - I'm not sure pgdump is that clever about such things. - Richard Huxton
Richard, > > Try having a look at the order the tables get inserted esp. with > regard to > any foreign keys etc. - I'm not sure pgdump is that clever about such > things. Thanks. I did try that; however: 1. Even trying re-loading the tables twice did not work, as it should have with missing foriegn keys. 2. I did not see any Unmatched Reference errors in the log, as I typically do with missing keys. Further, I *was* able to re-load the database from a unitary pg_dump file ... one that includes both DDL and data. It's just when I split the pg_dump into two files -- one for DDL, one for data -- that it fails. BTW, I'm using 7.1RC2 -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Hi, >In an effort to do some general cleanup in my database functions, I >dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text >files. I'm using a similar method for my own project. But I dump the data with the -d or -D option. This ist not as fast as the raw copy but more flexible. Maybe you should give it a try. If you rename some colums of your table, you should use -d (propert INSERT commands instead of COPY). If you reorder the colums of your tables you should use -D (propert INSERT with explicit Attributenames). You cannot do both (rename and reorder columns)! Andre
From: "Josh Berkus" <josh@agliodbs.com> > Richard, > > Try having a look at the order the tables get inserted esp. with > > regard to > > any foreign keys etc. - I'm not sure pgdump is that clever about such > > things. > > Thanks. I did try that; however: > 1. Even trying re-loading the tables twice did not work, as it should > have with missing foriegn keys. > 2. I did not see any Unmatched Reference errors in the log, as I > typically do with missing keys. > > Further, I *was* able to re-load the database from a unitary pg_dump > file ... one that includes both DDL and data. It's just when I split > the pg_dump into two files -- one for DDL, one for data -- that it > fails. > > BTW, I'm using 7.1RC2 Was there not some fix to pgdump in 7.1.1? Yep - pg_dump fixes (Philip) pg_dump can dump 7.0 databases (Philip) Might be worth a quick upgrade & see what happens. Failing that, it isn't something to do with permissions and pgdump connecting as various users? - Richard Huxton