Re: pg_restore error-s after pg_dump

On 06/22/2016 04:00 AM, SDAG wrote:
> Hi
> Postgres version :
> *PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-3), 64-bit*
>
> We transfer from one server to another (with better cpu an ram) our system
> using vm converter and when I try to backup database have an error :

The above is going to need more explanation.

Did you convert the VM in place or move from one VM to another?

Is the architecture of the new VM the same as the old?

Did the conversion take place on a live VM or was it stopped?

Just to be clear the dump below was taken after the conversion, correct?

>
> *pg_dump: reading schemas
> pg_dump: reading user-defined tables
> pg_dump: reading extensions
> pg_dump: reading user-defined functions
> pg_dump: reading user-defined types
> pg_dump: reading procedural languages
> pg_dump: reading user-defined aggregate functions
> pg_dump: reading user-defined operators
> pg_dump: reading user-defined operator classes
> pg_dump: reading user-defined operator families
> pg_dump: reading user-defined text search parsers
> pg_dump: reading user-defined text search templates
> pg_dump: reading user-defined text search dictionaries
> pg_dump: reading user-defined text search configurations
> pg_dump: reading user-defined foreign-data wrappers
> pg_dump: reading user-defined foreign servers
> pg_dump: reading default privileges
> pg_dump: reading user-defined collations
> pg_dump: reading user-defined conversions
> pg_dump: reading type casts
> pg_dump: reading table inheritance information
> pg_dump: reading rewrite rules
> pg_dump: finding extension members
> pg_dump: finding inheritance relationships
> pg_dump: reading column info for interesting tables
> pg_dump: finding the columns and types of table "account_account"
> pg_dump: [archiver (db)] query failed: ERROR: missing chunk number 0 for
> toast value 3297740 in pg_toast_2619
> pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod,
> a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef,
> a.attisdropped, a.attlen, a.attalign, a.attislocal,
> pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname,
> array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation
> <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation,
> pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name)
> || ' ' || pg_catalog.quote_literal(option_value) FROM
> pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
> ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN
> pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid =
> '274619'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY
> a.attrelid, a.attnum*
>
> I solve it using *reindexdb *,after I try* pg_dump -U postgres my_db >
> /home/mydb.backup* and it was successfull. Then I try to restore database to
> ensure that backup is valid
> *psql -U postgres new_db < /home/mydb.backup*
>
> And have an errors :

Does table tbl1 have an id column?

>
> *ERROR : extra data after last expected column
> Context: COPY tbl1, line1: "1 2013-12-02 2013-12-02 9387.74
> 9775.46211485490864940000" 6180.9500000000 80262 ...."
> ERROR : column "id" of relation "tbl1" does not exists
> invalid command \N
> invalid command \N
> invalid command \N
> .....
> invalid command \N
> invalid command \.
> ERROR: syntax error at or near "87685"
> LINE 1: 87685 SO87690 1 170468 2015-05-30 2015 05 30
> ^
> invalid command \.

The below looks like you are trying to restore over existing data.

>
> ERROR: duplicate key value violates unique constraint "ir_act_client_pkey"
> DETAIL: Key (id)=(103) already exists.
> CONTEXT: COPY ir_act_client, line 21: ""
> ERROR: duplicate key value violates unique constraint
> "ir_act_report_xml_pkey"
> DETAIL: Key (id)=(733) already exists.
> CONTEXT: COPY ir_act_report_xml, line 59: ""
> ERROR: duplicate key value violates unique constraint "ir_act_server_pkey"
> DETAIL: Key (id)=(703) already exists.
> CONTEXT: COPY ir_act_server, line 6: ""
> ERROR: duplicate key value violates unique constraint "ir_act_window_pkey"
> DETAIL: Key (id)=(1) already exists.
> CONTEXT: COPY ir_act_window, line 235: "334 Last Product Inventories
> ir.actions.act_window \N 1 2013-07-03 10:39:48.399509
> 2013-12-16 16:38:..."
> ERROR: duplicate key value violates unique constraint
> "ir_act_window_group_rel_act_id_gid_key"
> DETAIL: Key (act_id, gid)=(76, 1) already exists.
> CONTEXT: COPY ir_act_window_group_rel, line 14: ""
> ERROR: duplicate key value violates unique constraint
> "ir_act_window_view_pkey"
> DETAIL: Key (id)=(100) already exists.
> CONTEXT: COPY ir_act_window_view, line 88: ""*
>
>
> Any advice to solve this problem ?
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/pg-restore-error-s-after-pg-dump-tp5909084.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com