Pg_restore complains about integrity errors on the dump. Is it possible?
I dumped DB OpenERP like this:
pg_dump -Fc -xO -f o7db.dump o7db
The original machine has:
$ pg_dump --version
pg_dump (PostgreSQL) 9.3.5
I scp
dump to target machine (OpenVZ container), where pg_restore:
$ pg_restore --version
pg_restore (PostgreSQL) 9.3.5
I run pg_restore like this:
pg_restore -d o7db -xO -j3 o7db.dump
The only difference I can see is that the postgres user is not the same machines (but that should be accepted -O
). pg_restore complains about:
pg_restore: [archiver (db)] Error from TOC entry 8561; 0 1161831 TABLE DATA account_move_line manu
pg_restore: [archiver (db)] COPY failed for table "account_move_line": ERROR: value too long for type character varying(64)
CONTEXT: COPY account_move_line, line 172, column name: "<MASKED DATA HERE....>"
This error is thrown multiple times for multiple tables. After that, many errors appear about missing tuples:
pg_restore: [archiver (db)] Error from TOC entry 6784; 2606 1182924 FK CONSTRAINT account_account_currency_id_fkey manu
pg_restore: [archiver (db)] could not execute query: ERROR: insert or update on table "account_account" violates foreign key constraint "account_account_currency_id_fkey"
DETAIL: Key (currency_id)=(1) is not present in table "res_currency".
Command was: ALTER TABLE ONLY account_account
ADD CONSTRAINT account_account_currency_id_fkey FOREIGN KEY (currency_id) REFERENCES re..
I can't see how this is possible as the original DB seems to be OK.
The restored database has many empty tables (each of which didn’t fire for too long):
$ psql -d o7db -Ac "select * from account_move_line" | tail -1
(0 rows)
Also, I am doing pg_restore on the same source machine:
pg_restore -d o7db_restore -xO -j3 o7db.dump
Everything works as expected. Not a single warning.
What should I do? What am I doing wrong?
source to share
The answer is actually given in Moving PostgreSQL database fails on non-ascii characters with value too long
It seems that the target server is creating the DB with a different encoding, so creating the DB with UTF8 before recovery solves the problem.
Credit goes to @habe ( https://stackoverflow.com/users/216458/habe )
So I voted my question to be closed.
source to share