Re: Logging the query executed on the server

[Steve Baldwin]

On Sun, Jul 24, 2022 at 4:29 PM Igor Korot  wrote:

>
> 2 things:
> 1. How do I turn this off? ;-)
>

When you change the setting via 'set', that change is only for the current
session. You can revert it with 'set {some param} to default;' or just
terminate the session. If you want to make the change permanent, you need
to set it in the config file.

2. The log does show the query but it shows it with the placeholders.
> Is there a way to see the actual query?
>

Not sure what you mean here. The query shown in the log should be what is
actually executed by the server. If you are using placeholders, there are
probably 3 different log entries - one for the parse step, one for the bind
step and one for the execute step. If you are asking what are the bind
variable values, they are shown in the bind step. For example:

2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:LOG:
duration: 0.072 ms bind :
select public.currency_on_mismatch() as on_mismatch,
set_config('search_path', $1, true),
set_config('application.user_id', $2, true),
set_config('application.app_client', $3, true),
set_config('application.api_client_id', $4 , true),
set_config('application.source', $5 , true),
set_config('application.request_id', $6 , true),
set_config('application.in_test_context', $7, true),
set_config('lock_timeout', $8, true),
txid_current()
2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:DETAIL:
parameters: $1 = 'public', $2 = 'Admin/Support', $3 = 'Admin/Support', $4 =
'ce34e2bc-2c65-4fc1-9b95-878aef19a348', $5 = '?', $6 =
'4432dbb2-ab1c-4bd8-a413-ff5c704209a6', $7 = 'f', $8 = '10s'

If this doesn't help, maybe post what you're seeing in the log.

Cheers,

Steve

Re: Logging the query executed on the server

[Igor Korot]

Hi, Steve,

On Sun, Jul 24, 2022 at 12:51 AM Steve Baldwin  wrote:
>
> Hi Igor,
>
> Before you issue your query, try something like this:
>
> (from psql, but hopefully you get the idea)
>
> b2bcreditonline=# set log_min_duration_statement to 0;
> SET
> b2bcreditonline=# set log_statement to 'all';
> SET
>
> Ref: https://www.postgresql.org/docs/current/sql-set.html, 
> https://www.postgresql.org/docs/current/config-setting.html
>
> Then submit your query and it will be set to the server log. You can get the 
> name of the current logfile with:
>
> b2bcreditonline=# select pg_current_logfile();
>  pg_current_logfile
> 
>  /log/pg.csv

2 things:
1. How do I turn this off? ;-)
2. The log does show the query but it shows it with the placeholders.
Is there a way to see the actual query?

Thank you.


>
> HTH,
>
> Steve
>
> On Sun, Jul 24, 2022 at 3:26 PM Igor Korot  wrote:
>>
>> Hi,
>> Is it possible to log the query that will be executed
>> on the server?
>>
>> I'm writing an application that connects to the server
>> through ODBC and libpq.
>> For some reason ODBC interface is failing - it desn't
>> return any rows
>>
>> So I'm thinking if I have a proof that the query I am
>> actually executing is the same as the one I run through
>> the psql - I will know where to look.
>>
>> I am actually binding some parameters and trying to
>> execute the query.
>>
>> Thank you.
>>
>>

Re: Logging the query executed on the server

[Steve Baldwin]

Hi Igor,

Before you issue your query, try something like this:

(from psql, but hopefully you get the idea)

b2bcreditonline=# set log_min_duration_statement to 0;
SET
b2bcreditonline=# set log_statement to 'all';
SET

Ref: https://www.postgresql.org/docs/current/sql-set.html,
https://www.postgresql.org/docs/current/config-setting.html

Then submit your query and it will be set to the server log. You can get
the name of the current logfile with:

b2bcreditonline=# select pg_current_logfile();
 pg_current_logfile

 /log/pg.csv

HTH,

Steve

On Sun, Jul 24, 2022 at 3:26 PM Igor Korot  wrote:

> Hi,
> Is it possible to log the query that will be executed
> on the server?
>
> I'm writing an application that connects to the server
> through ODBC and libpq.
> For some reason ODBC interface is failing - it desn't
> return any rows
>
> So I'm thinking if I have a proof that the query I am
> actually executing is the same as the one I run through
> the psql - I will know where to look.
>
> I am actually binding some parameters and trying to
> execute the query.
>
> Thank you.
>
>
>

Re: Logging [RESOLVED]

[Peter J. Holzer]

On 2019-12-04 16:03:24 -0800, Rich Shepard wrote:
> On Wed, 4 Dec 2019, Stephen Eilert wrote:
> > Usually, this is done by logrotate or a similar mechanism in your system.
> > You’ll likely find that other logs in your system follow a similar
> > pattern, not just Postgresql.
> 
> I just checked /etc/logrotate.d/postgres and it was set at daily with rotate
> 7. I changed rotate to 4 but the dates are 3-4 days apart, not sequential.

Your log files are extremely small. At only a few hundred bytes every 3
or 4 days it is very likely that nothing is logged on most days. If your
log file is empty, logrotate won't rotate it if the option "notifempty"
is set (which is probably the case).

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Logging

[Rich Shepard]

On Wed, 4 Dec 2019, Adrian Klaver wrote:

Take a look at the logging section of postgresql.conf to see if Postgres is 
handing off to the system and logrotate


Adrian,

That conf file is in several places (different flavors). I'll check them
all.

Thanks,

Rich

Re: Logging [RESOLVED]

[Rich Shepard]

On Wed, 4 Dec 2019, Stephen Eilert wrote:


Usually, this is done by logrotate or a similar mechanism in your system.
You’ll likely find that other logs in your system follow a similar
pattern, not just Postgresql.


Stephen,

Other logs, controlled by logrotate, rotate daily for a maximum of 4
backups.

I just checked /etc/logrotate.d/postgres and it was set at daily with rotate
7. I changed rotate to 4 but the dates are 3-4 days apart, not sequential.

Thanks,

Rich

Re: Logging

[Adrian Klaver]

On 12/4/19 3:20 PM, Rich Shepard wrote:

Running Slackware-14.2/x86_64 and postgresql-11.5.

In /var/log/ are these files:

-rw-r- 1 postgres wheel   0 Nov 23 04:40 postgresql-11
-rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1
-rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz
-rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz
-rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz
-rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz
-rw-r- 1 postgres wheel 325 Nov  6 04:40 postgresql-11.6.gz
-rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz

I assume that they're an automatic backup that runs every 3-4 days. What's
backed up and where is this controlled?


Looks like logrotate.
Take a look at the logging section of postgresql.conf to see if Postgres 
is handing off to the system and logrotate




I ask because I have a cron job that does a pg_dumpall each night at 11:30
pm. (It's a small installation for my business use so the files are not
excessive and I keep them for only short periods.)

Regards,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Logging

[Stephen Eilert]

Usually, this is done by logrotate or a similar mechanism in your system. 
You’ll likely find that other logs in your system follow a similar pattern, not 
just Postgresql.

— Stephen
On Dec 4, 2019, 3:21 PM -0800, Rich Shepard , wrote:
> Running Slackware-14.2/x86_64 and postgresql-11.5.
>
> In /var/log/ are these files:
>
> -rw-r- 1 postgres wheel 0 Nov 23 04:40 postgresql-11
> -rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1
> -rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz
> -rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz
> -rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz
> -rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz
> -rw-r- 1 postgres wheel 325 Nov 6 04:40 postgresql-11.6.gz
> -rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz
>
> I assume that they're an automatic backup that runs every 3-4 days. What's
> backed up and where is this controlled?
>
> I ask because I have a cron job that does a pg_dumpall each night at 11:30
> pm. (It's a small installation for my business use so the files are not
> excessive and I keep them for only short periods.)
>
> Regards,
>
> Rich
>
>

Re: logging proxy

[Bruce Momjian]

On Fri, Nov 1, 2019 at 01:58:10AM +0300, Олег Самойлов
wrote:
> Does anyone know PostgresQL proxy which can log queries with username,
> ip and affected rows for security reason. PostgresQL itself can log
> almost all, except affected rows.

You can use the server logs to get the non-row information, then use
streaming replication with logical decoding to get the rows for each
transaction started by the user.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: logging "raise" to file

[wambacher]

Thank Georg,

regards
walter

> the red part writes your "raise notice" to your log
>
> psql (+your connection string) -f /path/to/file.sql *>
> /path/to/log/xxx.log 2>&1*
>
> -- 
My projects:

Admin Boundaries of the World 
Missing Boundaries

Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 
OSM Software Watchlist

Re: logging "raise" to file

[Georg H.]

Hi,

Am 28.07.2019 um 12:32 schrieb wambac...@posteo.de:


Hi,

is there a way to log output from "raise ..." to a local file? \o file 
does not work (for me).


regards
walter



the red part writes your "raise notice" to your log

psql (+your connection string) -f /path/to/file.sql *> 
/path/to/log/xxx.log 2>&1*


see

https://dba.stackexchange.com/questions/107199/how-to-log-custom-messages-from-inside-a-postgresql-transaction

regards

Georg

Re: logging "raise" to file

[Guillaume Lelarge]

Hi,

Le dim. 28 juil. 2019 à 12:32,  a écrit :

> Hi,
>
> is there a way to log output from "raise ..." to a local file? \o file
> does not work (for me).
>

No, RAISE messages can only go to log files. You would need to call a
function that could write to a file (though without calling RAISE).


-- 
Guillaume.

Re: Logging

[Andrew Bartley]

Thanks All,

It seems that the related DETAIL message appears if the query is
successful.  On error the DETAIL log line is missing...   This makes
debugging difficult, considering the insert is coming from Dreamfactory via
a rest POST.

I am finding it very difficult navigating my way through the brave new
world of SAS and Blackbox type applications  Maybe 32 years as a
database programmer is just too long.

Thanks

Andrew




On Wed, 13 Jun 2018 at 14:12 Adrian Klaver 
wrote:

> On 06/12/2018 08:25 PM, David G. Johnston wrote:
> > On Tuesday, June 12, 2018, Andrew Bartley  > > wrote:
> >
> >
> >
> > On Wed, 13 Jun 2018 at 12:43 Laurenz Albe  > > wrote:
> >
> >
> > log_min_duration_statement = 0
> >
> > [...]
> >
> >
> > log_min_duration_statement -1
> >
> >
> > You've disabled statement logging altogether.  The zero value you were
> > directed to use is what causes everything to be logged.
>
> Actually no:
>
>
> https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
>
> log_min_duration_statement (integer)
>
> "... Setting this to zero prints all statement durations. Minus-one (the
> default) disables logging statement durations.  ..."
>
> "
>
> So -1 only affects logging statements relative to duration.
>
> If you have log_statements set then you will still get statements logged
> if you have log_min_duration_statement = -1 :
>
> Note
>
> When using this option together with log_statement, the text of
> statements that are logged because of log_statement will not be repeated
> in the duration log message.
> "
>
> This is how I have my logging setup, log_min_duration_statement = -1
> and log_statements = 'mod' and I see statements in the logs.
>
> >
> > David J.
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Logging

[Adrian Klaver]

On 06/12/2018 08:25 PM, David G. Johnston wrote:
On Tuesday, June 12, 2018, Andrew Bartley > wrote:




On Wed, 13 Jun 2018 at 12:43 Laurenz Albe mailto:laurenz.a...@cybertec.at>> wrote:


log_min_duration_statement = 0

[...]


log_min_duration_statement -1


You've disabled statement logging altogether.  The zero value you were 
directed to use is what causes everything to be logged.


Actually no:

https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

log_min_duration_statement (integer)

"... Setting this to zero prints all statement durations. Minus-one (the 
default) disables logging statement durations.  ..."


"

So -1 only affects logging statements relative to duration.

If you have log_statements set then you will still get statements logged 
if you have log_min_duration_statement = -1 :


Note

When using this option together with log_statement, the text of 
statements that are logged because of log_statement will not be repeated 
in the duration log message.

"

This is how I have my logging setup, log_min_duration_statement = -1
and log_statements = 'mod' and I see statements in the logs.



David J.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Logging

[Andrew Bartley]

Ok, thanks.   will try

On Wed, 13 Jun 2018 at 13:25 David G. Johnston 
wrote:

> On Tuesday, June 12, 2018, Andrew Bartley  wrote:
>
>> On Wed, 13 Jun 2018 at 12:43 Laurenz Albe 
>> wrote:
>>
>
>>> log_min_duration_statement = 0
>>>
>> [...]
>
>>
>> log_min_duration_statement -1
>>
>
> You've disabled statement logging altogether.  The zero value you were
> directed to use is what causes everything to be logged.
>
> David J.
>
>
>

Re: Logging

[David G. Johnston]

On Tuesday, June 12, 2018, Andrew Bartley  wrote:

>
>
> On Wed, 13 Jun 2018 at 12:43 Laurenz Albe 
> wrote:
>
>>
>> log_min_duration_statement = 0
>>
> [...]

>
> log_min_duration_statement -1
>

You've disabled statement logging altogether.  The zero value you were
directed to use is what causes everything to be logged.

David J.

Re: Logging

[Andrew Bartley]

On Wed, 13 Jun 2018 at 12:43 Laurenz Albe  wrote:

> Andrew Bartley wrote:
> > Can someone please tell me how to log the values being inserted in this
> example..
> >
> > 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT:  insert into
> "api_consumers" ("consumer_id", "create_datetime") values ($1, $2).
> >
> > I have tried many different logging options and combinations.
>
> That should automatically be logged as a DETAIL message.
>
> log_min_duration_statement = 0
> log_min_error_statement = log or better
> log_min_messages = log or better
>
> That should do the trick.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

Thanks for the reply Laurenz,


Current settings

"PostgreSQL 9.6.7, compiled by Visual C++ build 1800, 64-bit"
Azure managed instance


log_min_duration_statement -1
log_min_error_statement DEBUG2
log_min_messages DEBUG2
log_statement ALL
log_error_verbosity VERBOSE

Still no joy.

Thanks Andrew

Re: Logging

[Laurenz Albe]

Andrew Bartley wrote:
> Can someone please tell me how to log the values being inserted in this 
> example..
> 
> 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT:  insert into 
> "api_consumers" ("consumer_id", "create_datetime") values ($1, $2).
> 
> I have tried many different logging options and combinations. 

That should automatically be logged as a DETAIL message.

log_min_duration_statement = 0
log_min_error_statement = log or better
log_min_messages = log or better

That should do the trick.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com