Thread: "COPY TO stdout" statements occurrence in log files

Hi there,
I have one log file per week and logging all statements >= 500 ms execution time.
But, with "normal" statements are occuring something like this:

2011-01-13 00:11:38 BRT LOG:  duration: 2469.000 ms  statement: FETCH 1000 IN bloboid
2011-01-13 00:12:01 BRT LOG:  duration: 797.000 ms  statement: SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM pg_namespace
2011-01-13 00:12:06 BRT LOG:  duration: 766.000 ms  statement: COPY public.log (codlog, matricula, data, descricao, codcurso, ip) WITH OIDS TO stdout;
2011-01-13 00:12:10 BRT LOG:  duration: 2328.000 ms  statement: FETCH 1000 IN bloboid
2011-01-13 00:12:34 BRT LOG:  duration: 594.000 ms  statement: SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM pg_namespace
2011-01-13 00:12:38 BRT LOG:  duration: 672.000 ms  statement: COPY public.avaliacao_topico_opcao (codavaliacao_topico_opcao, codavaliacao_topico, descricao, selecao) WITH OIDS TO stdout;
2011-01-13 00:12:39 BRT LOG:  duration: 891.000 ms  statement: COPY public.log (codlog, matricula, data, descricao, codcurso, ip) WITH OIDS TO stdout;

Is this normal? I'm afraid because my application doesn't run this kind of statement, so how can I know what is doing these commands? Maybe pg_dump?

Thank you!
Fernando

> Is this normal? I'm afraid because my application doesn't run this kind of
> statement, so how can I know what is doing these commands? Maybe pg_dump?

I think pg_dump is likely, yes, if you have that scheduled. I don't
think anything in the log file will identify it as pg_dump explicitly
(I believe as far as the server is concerned, pg_dump is just another
client), but if you're concerned about this, you can add the client
pid (%p) to log_line_prefix in postgresql.conf, log the pg_dump pid
through whatever mechanism manages that, and compare.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
msakrejda@truviso.com
www.truviso.com

msakrejda@truviso.com (Maciek Sakrejda) writes:
>> Is this normal? I'm afraid because my application doesn't run this kind of
>> statement, so how can I know what is doing these commands? Maybe pg_dump?
>
> I think pg_dump is likely, yes, if you have that scheduled. I don't
> think anything in the log file will identify it as pg_dump explicitly
> (I believe as far as the server is concerned, pg_dump is just another
> client), but if you're concerned about this, you can add the client
> pid (%p) to log_line_prefix in postgresql.conf, log the pg_dump pid
> through whatever mechanism manages that, and compare.

That's an option...  More are possible...

1.  Our DBAs have been known to create users specifically for doing
backups ("dumpy").  It doesn't seem like a *huge* proliferation of users
to have some 'utility' user names for common processes.

2.  In 9.1, there will be a new answer, as there's a GUC to indicate the
"application_name".
--
"Programming today  is a race  between software engineers  striving to
build bigger and better  idiot-proof programs, and the Universe trying
to  produce  bigger  and  better  idiots.  So  far,  the  Universe  is
winning."  -- Rich Cook

On Fri, Jan 14, 2011 at 23:19, Chris Browne <cbbrowne@acm.org> wrote:
> 2.  In 9.1, there will be a new answer, as there's a GUC to indicate the
> "application_name".

Actually this was already introduced in PostgreSQL 9.0 :)

You can add application_name to your log_line_prefix with %a. For
pg_dump it will display "pg_dump"

Regards,
Marti