pg_restore toc error

Question

i was using the following syntax for pg_dump and restore

pg_dump eval --inserts -b -c --encoding UTF8 -Fc -f eval.sql.tar.gz -x -U postgres
createdb -T template0 test -U postgres
pg_restore -d test eval.sql.tar.gz -e -U postgres

the dump was successfull with no errors, but restore makes a some errors, i am dumping and restoring in same machine with same user and privilege all...

i have tried out with other formats also, plain, tar, compressed all gets the same error..

my version of pg is 8.4.11 and psql version is 8.4.11

i am not sure what makes these errors.. can anyone help me

 pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4965; 0 138871 TABLE DATA ir_act_report_xml insigni
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid input syntax for integer: "purchase.order"
LINE 1: ...st for Quotation', 'ir.actions.report.xml', NULL, 'purchase....
                                                             ^
    Command was: INSERT INTO ir_act_report_xml VALUES (350, 'Request for Quotation', 'ir.actions.report.xml', NULL, 'purchase.order', 'purcha...

Sweetscented

Seems you dump is a SQL script that needs to be run using psql, not pg_restore – Lemberg

Answer 1

this did the trick

pg_dump database_name -c -Ft -f file_name.tar 

pg_restore -d database_name -c file_name.tar

before this i was trying to restore with out including -c(clean)

even though -c is included in pg_dump it is not used in pg_restore unless we say to use...

Sweetscented

Using the "clean" flag can cause a lot of problems, too, because it will complain about missing objects when using a fresh database. – Peasecod

-c flag didnt work. Still getting the same error: <br/><code> $ pg_restore -d development -t integrations -c b7c5 pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 210; 1259 16535 TABLE integrations u5j7 pg_restore: [archiver (db)] could not execute query: ERROR: role "u5j7" does not exist Command was: ALTER TABLE public.integrations OWNER TO u5j7; WARNING: errors ignored on restore: 1 – Starlike

Answer 2

The solution in my case:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U username -d database_name dump_name.dump

Histamine

--if-exists is convenient if you want to get rid of unuseful errors. postgresql.org/docs/current/app-pgrestore.html – Mella

verbose is useful – Tie

Answer 3

This worked for me:

Increase the max_wal_size postgresql setting (max_wal_size = 2GB) in postgresql.conf

Cheek