Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump and errors

pg_dump and errors

From
Ben-Nes Michael
Date:
Hello
 
Im getting strange errors when restoring data from pg_dump files:
 
ERROR:  duplicate key violates unique constraint "products_pkey"
CONTEXT:  COPY products, line 1: "98    Super Pak Ruby játék      J-0001  1502450E       Szuper Pokémon ajándékcso..."
ERROR:  duplicate key violates unique constraint "attributes_pkey"
CONTEXT:  COPY attributes, line 1: "2   Width"
ERROR:  duplicate key violates unique constraint "prod_attributes_pkey"
CONTEXT:  COPY prod_attributes, line 1: "1038   23      12      "
ERROR:  duplicate key violates unique constraint "prod_images_pkey"
CONTEXT:  COPY prod_images, line 1: "73 14      \\377\\330\\377\\340\\000\\020JFIF\\000\\001\\001\\001\\000H\\000H\\000\\000\\377\\333\\000C\\..."
ERROR:  duplicate key violates unique constraint "manufacturer_images_pkey"
CONTEXT:  COPY manufacturer_images, line 1: "296        81      \\377\\330\\377\\340\\000\\020JFIF\\000\\001\\001\\001\\000H\\000H\\000\\000\\377\\376\\000&F..."
 
Im using pg 7.4.1 on woody ( debian ).
this is not the first time such hapens and i have a constant fear from backup failures.
 
once I even had to move a db from 7.3 to 7.4 and pg_dump returned me some errors on not existing tables ( ones which i deleted before )
 
this is the line i use ( i started to seperate the scheme from the data becouse its easier to me to edit the files if something happens )
 
/usr/bin/pg_dump -s -f /var/pg_dumps/db1 db1
/usr/bin/pg_dump -a -f /var/pg_dumps/db1 db1
 
Any ideas how to avoid such errors ( if possible at all ) ?
 
Thanks
--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://www.canaan.net.il
--------------------------

Re: pg_dump and errors

From
Ben-Nes Michael
Date:
Here some more information:
 
Versions:
 
the pg_dump used is 7.4.2 ( under debian )
the Server im getting the DB from is 7.3.2
 
I did:
 
pg_dump -h www -U postgres db_name > 20040531-db_name.dump
psql db_name < 20040531-db_name.dump
 
got this alot. is this an Error ?
Query buffer reset (cleared).
 
On runing the psql two files created in the working directory, something like ( i was able to delete them using MC ):
?\012\301\006,B\0360@\006\330\000\021?@\004?\000.................
and
C?F\004
 
The following error accour becouse 7.3 was compiled by me under RH on a specific path, the current version use other paths.
ERROR:  could not access file "/usr/local/pgsql7.3/lib/plpgsql.so": No such file or directory
ERROR:  function plpgsql_call_handler() does not exist
 
if I use createlang before the dump i just get:
ERROR:  function "plpgsql_call_handler" already exists with same argument types
ERROR:  language "plpgsql" already exists
 
The next error is strange, the relation ( table ) exist as create in the dump file, maybe its and order issue ?
ERROR:  relation "forum_tree" does not exist
 
The next one is realy strange. the relation forum_tree2 exists in one of the development phase, it was deleted long time ago:
ERROR:  relation "forum_tree2_node_id_seq" does not exist
 
lots of:
invalid command \n<F0><E5><F9><E0><E9>
 
save as the error for forum_tree:
ERROR:  relation "forum_files" does not exist
 
same as the error for forum_tree2_node_id_seq
ERROR:  relation "forum_files2_file_id_seq" does not exist
 
I found a way to move it to 7.4.2 but its kinda pain.
I recreated all the scheme using my source code. then after edit the dump file by hand ( fixing the strange forum_tree2 relations ) dumping all the data into using Insert.
 
Am i doing something wrong ?
--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://www.canaan.net.il
--------------------------
----- Original Message -----
Sent: Monday, May 31, 2004 1:49 PM
Subject: [GENERAL] pg_dump and errors

Hello
 
Im getting strange errors when restoring data from pg_dump files:
 
ERROR:  duplicate key violates unique constraint "products_pkey"
CONTEXT:  COPY products, line 1: "98    Super Pak Ruby j&aacute;t&eacute;k      J-0001  1502450E       Szuper Pok&eacute;mon aj&aacute;nd&eacute;kcso..."
ERROR:  duplicate key violates unique constraint "attributes_pkey"
CONTEXT:  COPY attributes, line 1: "2   Width"
ERROR:  duplicate key violates unique constraint "prod_attributes_pkey"
CONTEXT:  COPY prod_attributes, line 1: "1038   23      12      "
ERROR:  duplicate key violates unique constraint "prod_images_pkey"
CONTEXT:  COPY prod_images, line 1: "73 14      \\377\\330\\377\\340\\000\\020JFIF\\000\\001\\001\\001\\000H\\000H\\000\\000\\377\\333\\000C\\..."
ERROR:  duplicate key violates unique constraint "manufacturer_images_pkey"
CONTEXT:  COPY manufacturer_images, line 1: "296        81      \\377\\330\\377\\340\\000\\020JFIF\\000\\001\\001\\001\\000H\\000H\\000\\000\\377\\376\\000&F..."
 
Im using pg 7.4.1 on woody ( debian ).
this is not the first time such hapens and i have a constant fear from backup failures.
 
once I even had to move a db from 7.3 to 7.4 and pg_dump returned me some errors on not existing tables ( ones which i deleted before )
 
this is the line i use ( i started to seperate the scheme from the data becouse its easier to me to edit the files if something happens )
 
/usr/bin/pg_dump -s -f /var/pg_dumps/db1 db1
/usr/bin/pg_dump -a -f /var/pg_dumps/db1 db1
 
Any ideas how to avoid such errors ( if possible at all ) ?
 
Thanks
--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Fax: 972-4-6990098
http://www.canaan.net.il
--------------------------