Subject: pg_dump problems

From: Martín Marqués <[email-masked]>
Date: Wed, 23 Mar 2011 09:33:00 -0300

Hi all,

I'm having problems with pg_dump, apparently, from one of our servers.

The scenario is like this. I tried today to dump a DB from our
production server to load on our development server and got some
errors, which I show below.

Production server:

SELECT version();
                                            version
-----------------------------------------------------------------------------------------------
 PostgreSQL 8.3.14 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.2-1.1) 4.3.2

Development server:

SELECT version();
                                               version
-----------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Debian 4.4.5-10) 4.4.5, 32-bit

Anyway, the problem is in the output of the pg_dump in the production server:

$ pg_dump -V
pg_dump (PostgreSQL) 8.3.14

The first error looks like related to encoding of bytea:

ERROR:  sintaxis de entrada no válida para tipo bytea
CONTEXTO:  COPY novedades_fotos, línea 23, columna foto:
«\377\330\377\340\000\020JFIF\000\001\002\001\000H\000H\000\000\377\355\020\006Photoshop
3.0\0008BIM\...»

But more strange is what comes later:

ERROR:  la sintaxis de entrada no es válida para integer: «954516ILa
educación superior en el sector aducativo del mercosur»
CONTEXTO:  COPY objeto_datos_rep, línea 1627732, columna codiobjeto:
«954516ILa educación superior en el sector aducativo del mercosur»

Checking that register in the production DB I get this:

SELECT * from objeto_datos_rep where codigo = 2357634;
 codigo  | codiobjeto |                        descripcion
           | tcampo
---------+------------+-----------------------------------------------------------+--------
 2357634 |     954516 | La educación superior en el sector aducativo
del mercosur |      5

So, why didn't pg_dump add the tabs between 954516 and "La educación..."?

Right now I'm trying to do dump with INSERTs instead of COPY command
to see what happens.

-- 
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

---

From: Vick Khera <[email-masked]>
Date: Wed, 23 Mar 2011 08:40:42 -0400

> errors, which I show below.

Try using the pg_dump from the 8.4 install to create your dump file.

Also make sure your 8.3 installation doesn't have a non-default bytea
encoding setting (not sure when that setting was introduced, so there
may not even be such a setting).

---

From: Martín Marqués <[email-masked]>
Date: Wed, 23 Mar 2011 10:57:35 -0300

Now I'm getting weirder things. I did a pg_dump with -d option and the
dump adds strange caracters:

ERROR:  error de sintaxis en o cerca de «I»
LÍNEA 1: I^NSERT INTO objeto_datos_rep VALUES (1172339, 387943, 'Wilde...

Could it be binary corruption (pg_dump for example)?

> Try using the pg_dump from the 8.4 install to create your dump file.

I'll try this.

> may not even be such a setting).

bytea has encoding? I thought it was plain binary data.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 23 Mar 2011 07:18:39 -0700

> dump adds strange caracters:

As previously stated try using the 8.4 version of pg_dump to dump the 8.3 
database. FYI in 8.4+ there is no -d option anymore, it has become --inserts to 
reduce confusion with other commands where -d specified the database.

> bytea has encoding? I thought it was plain binary data.

I think he was talking about bytea_output, which appeared in 9.0.

In any event you may want to verify that the encodings/locales for the two 
databases are the same.

-- 
Adrian Klaver
[email-masked]

---

From: Martín Marqués <[email-masked]>
Date: Wed, 23 Mar 2011 11:56:42 -0300

El día 23 de marzo de 2011 11:18, Adrian Klaver
<[email-masked]> escribió:
> database. FYI in 8.4+ there is no -d option anymore, it has become --inserts
> to reduce confusion with other commands where -d specified the database.

Ok, did a remote dump (from the development server which has pg 8.4)
and I got a similar error:

psql:siprebi-bu.sql:27374: ERROR:  sintaxis de entrada no
v<C3><A1>lida para tipo bytea
CONTEXT:  COPY novedades_fotos, l<C3><AD>nea 273, columna foto:
<C2><AB>\377\330\377\340\000\020JFIF\000\001\001\001\000`\000`\000\000\377\341<\310Exif\000\000II
*\000\010\0...<C2><BB>
psql:siprebi-bu.sql:3477848: ERROR:  la sintaxis de entrada no es
v<C3><A1>lida para integer: <C2><AB>q43116<C2><BB>
CONTEXT:  COPY objetos, l<C3><AD>nea 99185, columna codigo:
<C2><AB>q43116<C2><BB>

> In any event you may want to verify that the encodings/locales for the two

All configurations have UTF-8

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 23 Mar 2011 10:03:23 -0700

On 03/23/2011 07:56 AM, Martín Marqués wrote:
> v<C3><A1>lida para integer:<C2><AB>q43116<C2><BB>

So you are feeding a plain text dump to psql correct?
Does the data in the file look correct for the affected tables?
Are there any other errors being reported?

-- 
Adrian Klaver
[email-masked]

---

From: Martín Marqués <[email-masked]>
Date: Wed, 23 Mar 2011 14:33:02 -0300

> So you are feeding a plain text dump to psql correct?

How can I verify this? It's already dificult to manage the file, asi
it has over 700Mb.

The second error is very curious, as it looks like pg_dump changed 1
for a 'q' in an integer field:

psql:siprebi-bu.sql:2219245: ERROR:  la sintaxis de entrada no es
válida para integer: «q721695»
CONTEXT:  COPY objeto_datos_rep, línea 991894, columna codigo: «q721695»

Looking at the dump file I see this:

1721693 657083  Eclairage...    5
1721694 657083   photometrie    5
q721695 657084  Keitz, H. A. E. 2
1721696 657084  Ligth calculations and measurements...  5
1721697 657085  La Toison, M.   2

> Are there any other errors being reported?

Thats the first error. After that there are similar errores and others
related to that register not gentting inserted (foreign keys that
don't have there corresponding primary key).

---

From: Vick Khera <[email-masked]>
Date: Wed, 23 Mar 2011 13:37:13 -0400

> CONTEXT:  COPY objeto_datos_rep, línea 991894, columna codigo: «q721695»

That's a single-bit error.  I'm voting hardware problems.  Does your
memory have ECC?  How reliable is your disk?

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 23 Mar 2011 10:38:18 -0700

> How can I verify this? It's already dificult to manage the file, asi

One way would be to dump only the table(s) that are causing the problem.

> for a 'q' in an integer field:

My guess is that it is combining data from two fields. In other words 
the 'q' is the end of the previous field.

-- 
Adrian Klaver
[email-masked]

---

From: Martín Marqués <[email-masked]>
Date: Wed, 23 Mar 2011 16:28:02 -0300

> memory have ECC?  How reliable is your disk?

The production server is a Compaq Prolaint, with ECC memory and RAID
by hardware.

How can I discard, or affirm that we are dealing with hardware problems?

---

From: Martín Marqués <[email-masked]>
Date: Wed, 30 Mar 2011 11:40:03 -0300

Just to to finish this thread:

We had a corrupted memory bank in the development server, and that
was, for some reason corrupting the data that got written to disk.

All is good now. Sorry for the noise.