Re: errors reloading with pg_dump

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(at)postgresql(dot)org

-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/