PostgreSQL Discussion - Re: SQL command to dump the contents of table failed: PQendcopy()

Author

Re: SQL command to dump the contents of table failed: PQendcopy()

ruben

2005-09-23, 11:24 am

Hi Michael:

The operating system is Red Hat Linux release 8.0 (Psyche) and
PostgreSQL version is 7.4.6., without non-standard extensions.

I cannot find any core dump in the PGDATA directory /usr/local/pgsql (I
don't know how to debug it to get a stack trace, I'll find out).

Thanks, Ruben.

Michael Fuhr wrote:

> On Fri, Sep 23, 2005 at 01:34:18PM +0200, ruben wrote:
>
>
>
> This suggests a bug in the backend. There should be a core dump
> somewhere under $PGDATA (unless resource limits prevent it or your
> system is configured to put core dumps elsewhere) -- can you use a
> debugger to get a stack trace from it? What exact version of
> PostgreSQL are you running and on what operating system? Do you
> have any non-standard extensions to PostgreSQL (custom types,
> third-party modules, etc.)?
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane

2005-09-23, 1:23 pm

ruben <ruben20@superguai.com> writes:
> I cannot find any core dump in the PGDATA directory /usr/local/pgsql (I
> don't know how to debug it to get a stack trace, I'll find out).

It would normally be in the per-database subdirectory
($PGDATA/base/NNN/) for the database where the problem occurs. However,
if you don't see a core file there either, that probably means the
postmaster was started under "ulimit -c 0" to prevent core dumps. Add
"ulimit -c unlimited" to the postmaster start script and restart it.

(For the sake of the archives, I'll mention that as of PG 8.1 core dumps
will appear directly in $PGDATA, not in its subdirectories.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

ruben

2005-09-26, 8:24 pm

Thanks Tom and Michael:

Michael Fuhr wrote:

> On Fri, Sep 23, 2005 at 05:11:19PM +0200, ruben wrote:
>
>
>
> Did you look everywhere under $PGDATA or just in that directory?
> As I recall, released versions of PostgreSQL usually dump core under
> $PGDATA/base/<database oid>. However, it's also possible that your
> coredumpsize resource limit prevents core dumps; you could fix that
> by putting a command like "ulimit -c unlimited" in your PostgreSQL
> startup script and then stopping and restarting PostgreSQL.
>
> Once you have a core dump, you can get a stack trace with gdb:
>
> $ gdb /path/to/postgres /path/to/core
> ...
> (gdb) bt
>
> If your postgres binary was built with debugging symbols then the
> stack trace should show function names, file names, and line numbers.

I cannot find a core file, ulimit is set to unlimit. I guess I'm doing
something wrong:

-bash-2.05b$ ulimit
unlimited
-bash-2.05b$ find /usr/local/pgsql -name '*core*' -print
-bash-2.05b$

> Can you duplicate the backend crash from psql if you issue the COPY
> command that pg_dump complained about?

-bash-2.05b$ /usr/local/pgsql/bin/pg_dump -Fc -t llamadas heos -f
/home/buheos/5/llamadas3.dump
pg_dump: socket not open
pg_dump: SQL command to dump the contents of table "llamadas" failed:
PQendcopy() failed.
pg_dump: Error message from server: socket not open
pg_dump: The command was: COPY public.llamadas (cod_empresa,
fecha_llamada, tfno_origen, tfno_destino, duracion_llamada,
hora_llamada, cod_destino_llamada,
cod_pais_destino,
cod_destino_internac
ional, franja_horaria, importe, cod_fuente,
precio_coste_llamada
, observaciones_llamad
a, coment_llamada,
fecha_factura, num_factura, fecha_alta, fecha_ult_mod, fecha_sis_alta,
usuario_alta, i_a_alta, fecha_sis_ult_mod, usuario_ult_mod, i_a_ult_mod,
periodicidad_factura
cion, cod_operador, franja_horaria_opera
dor,
fichero_origen, cod_destino_internac
ional_operador) TO stdout;

> What about if you issue a SELECT for all records in the table?

heos=# select * from llamadas;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING:
terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
Failed.

In the logfile:

LOG: server process (PID 7069) was terminated by signal 11
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2005-09-26 16:49:43 CEST
LOG: checkpoint record is at 125/858E0144
LOG: redo record is at 125/858E0144; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 2270061; next OID: 30820346
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at 125/858E0184
LOG: redo is not required
LOG: database system is ready
LOG: unexpected EOF on client connection

> What does "\d tablename" show for the table in question?

heos=# \d llamadas;
Table "public.llamadas"
Column | Type |
Modifiers
------------------------------------+--------------------------+----------------------------------------------------
cod_empresa | smallint | not null
fecha_llamada | date |
tfno_origen | character(15) |
tfno_destino | character(15) |
duracion_llamada | integer |
hora_llamada | time without time zone |
default ('now'::text)::time(
6) with time zone
cod_destino_llamada | character(1) |
cod_pais_destino | integer |
cod_destino_internac
ional | character(15) |
franja_horaria | character(1) |
importe | real |
cod_fuente | integer |
precio_coste_llamada
| real |
observaciones_llamad
a | character varying(100) |
coment_llamada | character varying(100) |
fecha_factura | date |
num_factura | integer |
fecha_alta | date |
fecha_ult_mod | date |
fecha_sis_alta | timestamp with time zone |
default ('now'::text)::times
tamp(6) with time zone
usuario_alta | character(10) |
i_a_alta | character(15) |
fecha_sis_ult_mod | timestamp with time zone |
usuario_ult_mod | character(10) |
i_a_ult_mod | character(15) |
periodicidad_factura
cion | character(1) |
cod_operador | character(2) |
franja_horaria_opera
dor | character(1) |
fichero_origen | character varying(100) |
cod_destino_internac
ional_operador | character(15) |
Indexes:
"llamadas_i01" btree (cod_empresa, fecha_llamada, tfno_origen)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2005-09-26, 8:24 pm

ruben <ruben20@superguai.com> writes:
[color=darkred]
> I cannot find a core file, ulimit is set to unlimit. I guess I'm doing
> something wrong:

> -bash-2.05b$ ulimit
> unlimited
> -bash-2.05b$ find /usr/local/pgsql -name '*core*' -print

(1) The fact that it's unlimited in your user environment doesn't prove
that it's unlimited in the environment the postmaster is started in.

(2) I forget which constraint ulimit-with-no-argument prints, but it's
not core file size. (Try "ulimit -a")

Please actually follow the advice given to you above.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Peter Wiersig

2005-09-26, 8:24 pm

On Mon, Sep 26, 2005 at 07:03:06PM +0200, ruben wrote:
>
> I guess I'm doing something wrong:
>
> -bash-2.05b$ ulimit
> unlimited

Please read manpages, in this case bash: ulimit -a

Peter

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

ruben

2005-09-28, 7:23 am

Hi Tom:

No way to get the core dump, this is what I did:

1) Add "ulimit" to /etc/rc.d/init.d/postgresql just before pg_ctl start
in the "start" section of the script:

...
ulimit -c unlimited
su -l postgres -s /bin/sh -c "/usr/local/pgsql/bin/pg_ctl -D $PGDATA
-p /usr/local/pgsql/bin/postmaster -l $DIRLOG/logfile start > /dev/null
2>&1" < /dev/null
...

I tried "man ulimit" but it didn't work, "man bash" returns the command
help.

2) Restart postmaster

/etc/rc.d/init.d/postgresql restart

3) Run the query that produces the crash.

4) Search core file:

-bash-2.05b$ find /usr/local/pgsql/ -name '*core*' -print
-bash-2.05b$

Nothing comes up.

Thanks for your help.
Ruben.

Tom Lane wrote:

> ruben <ruben20@superguai.com> writes:
>
>
>
>
>
>
>
> (1) The fact that it's unlimited in your user environment doesn't prove
> that it's unlimited in the environment the postmaster is started in.
>
> (2) I forget which constraint ulimit-with-no-argument prints, but it's
> not core file size. (Try "ulimit -a")
>
> Please actually follow the advice given to you above.
>
> regards, tom lane
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Martijn van Oosterhout

2005-09-28, 7:23 am

On Wed, Sep 28, 2005 at 11:40:11AM +0200, ruben wrote:
> Hi Tom:
>
> No way to get the core dump, this is what I did:
>
> 1) Add "ulimit" to /etc/rc.d/init.d/postgresql just before pg_ctl start
> in the "start" section of the script:

<snip>

> 2) Restart postmaster
>
> /etc/rc.d/init.d/postgresql restart

Does that run the start section? Maybe you need to stop/start. Maybe
the bash_profile/bashrc for the postgres user resets the core limit.

> 4) Search core file:
>
> -bash-2.05b$ find /usr/local/pgsql/ -name '*core*' -print
> -bash-2.05b$

It'll be under $PGDATA, where is that? It may be under
/var/lib/postgres, depending on how you installed...

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

ruben

2005-09-28, 7:23 am

Thanks Martijn:

Martijn van Oosterhout wrote:

> On Wed, Sep 28, 2005 at 11:40:11AM +0200, ruben wrote:
>
>
>
> <snip>
>
>
>
> Does that run the start section? Maybe you need to stop/start. Maybe
> the bash_profile/bashrc for the postgres user resets the core limit.

The server has been rebooted.

This is the postgres start script ($PGDATA is /usr/local/pgsql/):
http://80.33.3.245/temp/postgres.txt

Regading the core limit:

-bash-2.05b$ whoami
postgres

-bash-2.05b$ cat ~/.bash_profile
PGDATA=/var/lib/pgsql/data
[ -f $PGDATA/../initdb.i18n ] && source $PGDATA/../initdb.i18n
export PGDATA

-bash-2.05b$ ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited

>
>
> It'll be under $PGDATA, where is that? It may be under
> /var/lib/postgres, depending on how you installed...

Indeed I searched the whole file structure for core files.

Regards.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links