Search code examples
postgresqlpg-restore

pg_restore complains about integrity errors on a dump. Is that even possible?

I have dumped an OpenERP DB like this:

pg_dump -Fc -xO -f o7db.dump o7db

The source machine has:

$ pg_dump --version
pg_dump (PostgreSQL) 9.3.5

The I scp the dump to a target machine (an OpenVZ container), where pg_restore is:

$ 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 postgres user is not the same in both machines (but that is supposed to be dealt by -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 issued several times for several tables. After that, many so errors about missing tuples follow:

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 don't see how this is possible, since the source DB seems to be Ok.

The restored DB has many empty tables (each that failed cause too long values):

 $ psql -d o7db -Ac "select * from account_move_line"  | tail -1
 (0 rows)

Furthermore, I do the 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?

Solution

  • The answer is actually given in Moving PostgreSQL database fails on non-ascii characters with 'value too long'

    It seems the target server creates DB with a different encoding, so creating the DB with UTF8 before restoring solves the problem.

    Credit goes to @habe (https://stackoverflow.com/users/216458/habe)

    So, I have voted my question to be closed.