Обсуждение: errors reloading with pg_dump
Howdy: Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7-10. This past weekend, I was trying to test PostgreSQL 7.2.2 on a backup box. I did a pg_dump of my production database (7.2.1) and tried to load it on my development DB (7.2.2). When I did, I got a slew of errors pertaining to invalid data inside the tables. For example, if I tried to restore a table that has an invalid date in the date column, an error is generated. My questions are: 1) If the errors are generated, does that mean that the record is skipped each time there is invalid data in the table or the table is skipped? 2) How can I check the integrity of the new database if pg_dump is not loading all of the information from the production database? 3) I tried doing something like: [snip] pg_dump -i -Fc -S postgres -d testdb | pg_restore -v -h 192.168.0.2 -S \ postgres -d testdb [/snip] And got a list or errors - mostly because a few tables did not have read permissions set to them. I have to go threw them by hand to change permissions (It's odd: how is it that you can create a table but the table will NOT have the owner or it's permissions on it? like so: [snip permissions] testdb=> \dp forums_auth Access privileges for database "testdb" Table | Access privileges -----------------------------------+------------------- forums_auth | forums_auth_id_seq | [/snip permissions] I'm sure I can do a count or something of each table, but, this also means my backup / recovery method is screwy because I can't recover anything that PostgreSQL throws errors at. How is everyone else in the world reloading tables that may have invalid data? Thanks! -X
Hi Shaunnx you may consider something like... pg_dumpall -d -U postgres | psql etc regds mallah > Howdy: > > Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7-10. > > This past weekend, I was trying to test PostgreSQL 7.2.2 on a backup box. I did a pg_dump of > my production database (7.2.1) and tried to load it on my development DB (7.2.2). When I did, > I got > a slew of errors pertaining to invalid data inside the tables. > > For example, if I tried to restore a table that has an invalid date in the date column, an > error is generated. My questions are: > > 1) If the errors are generated, does that mean that the record is skipped each time there is > invalid data in the table or the table is skipped? > > 2) How can I check the integrity of the new database if pg_dump > is not loading all of the information from the production database? > > 3) I tried doing something like: > > [snip] > > pg_dump -i -Fc -S postgres -d testdb | pg_restore -v -h 192.168.0.2 -S \ postgres -d testdb > > [/snip] > > And got a list or errors - mostly because a few tables did not have read permissions set to > them. I have to go threw them by hand to change permissions (It's odd: how is it that you can > create a table but the table will NOT have the owner or it's permissions on it? like so: > > [snip permissions] > > testdb=> \dp forums_auth > Access privileges for database "testdb" > Table | Access privileges > -----------------------------------+------------------- > forums_auth | > forums_auth_id_seq | > > [/snip permissions] > > > > I'm sure I can do a count or something of each table, but, this also means my backup / > recovery method is screwy because I can't > recover anything that PostgreSQL throws errors at. > > How is everyone else in the world reloading tables that may have > invalid data? > > Thanks! > > -X > > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and > unsubscribe commands go to majordomo@postgresql.org ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/