Re: Set a specific database to log_statement='ddl' but others to be log_statement='all'

Thanks David. I tried that and this is what I get:

Feb 16 11:27:23 db1 postgres[27675]: [3595-1] <username(at)[local]->etx_ecom>
LOG: connection authorized: user=username database=etx_ecom
Feb 16 11:27:23 db1 postgres[26184]: [3598-1] <username(at)[local]->core> LOG:
disconnection: session time: 0:00:47.727 user=username database=core
host=[local]
Feb 16 11:27:31 db1 postgres[27675]: [3596-1] <username(at)[local]->etx_ecom>
LOG: statement: show log_statement;
Feb 16 11:27:46 db1 postgres[27675]: [3597-1] <username(at)[local]->etx_ecom>
LOG: statement: ALTER DATABASE etx_ecom SET log_statement='ddl';
Feb 16 11:28:54 db1 postgres[30323]: [3595-1] <username(at)[local]->core> LOG:
connection authorized: user=username database=core
Feb 16 11:28:54 db1 postgres[27675]: [3598-1] <username(at)[local]->etx_ecom>
LOG: disconnection: session time: 0:01:31.052 user=username
database=etx_ecom host=[local]
Feb 16 11:29:45 db1 postgres[31998]: [3595-1] <username(at)[local]->etx_ecom>
LOG: connection authorized: user=username database=etx_ecom
Feb 16 11:29:45 db1 postgres[30323]: [3596-1] <username(at)[local]->core> LOG:
disconnection: session time: 0:00:51.028 user=username database=core
host=[local]
Feb 16 11:29:56 db1 postgres[31998]: [3596-1] <username(at)[local]->etx_ecom>
LOG: statement: show log_statement;
Feb 16 11:30:05 db1 postgres[31998]: [3597-1] <username(at)[local]->etx_ecom>
LOG: statement: select * from card_type;
Feb 16 11:30:28 db1 postgres[31998]: [3598-1] <username(at)[local]->etx_ecom>
LOG: statement: INSERT INTO card_type VALUES('Z', 'QTEST');
Feb 16 11:30:57 db1 postgres[31998]: [3599-1] <username(at)[local]->etx_ecom>
LOG: statement: DELETE from card_type where type='Q' or type='Z';

Correction on the earlier statement about postgresql.conf. The
log_statement is configured inside postgresql_puppet_extras.conf. Not sure
if that makes any difference. The reason for this endeavor is to run a
simple SQL command in puppet to ALTER the database and log_statement
changed to 'ddl' for specific databases but somehow doesn't work. Hence the
short and small manual test to see.

Abdul Qoyyuum Bin Haji Abdul Kadir
System Engineer at Card Access Services
HP: +673 720 8043

On Tue, 16 Feb 2021, 11:23 David G. Johnston, <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Monday, February 15, 2021, Abdul Qoyyuum <aqoyyuum(at)cardaccess(dot)com(dot)au>
> wrote:
>
>> Hi all,
>>
>> I have a Postgresql cluster with master and multiple slaves running on
>> version 9.6. I'm trying to adjust the log_statement from all to ddl on
>> specific databases (i.e. postgresql.conf has log_statement='all' but I need
>> a couple of databases set to log_statement='ddl').
>>
>> etx_ecom=# ALTER DATABASE etx_ecom SET log_statement='ddl';
>> ALTER DATABASE
>> etx_ecom=# show log_statement;
>> log_statement
>> ---------------
>> all
>> (1 row)
>>
>> etx_ecom=# INSERT into card_type VALUES('Q','TEST');
>> INSERT 0 1
>>
>> etx_ecom=#
>>
>> Feb 16 10:56:11 db1 postgres[21682]: [3602-1] <myusername(at)[local]->etx_ecom>
>> LOG: statement: INSERT into card_type VALUES('Q','TEST');
>>
>> Why doesn't the Alter Database work?
>>
>>
> It did - you just didn’t start a new session as the documentation
> directs. You only altered a default that is only considered during user
> sign-in.
>
> David J.
>
>