Thanks David. I tried that and this is what I get:
Feb 16 11:27:23 db1 postgres[27675]: [3595-1] <username@[local]->etx_ecom> LOG: connection authorized: user=username database=etx_ecom
Feb 16 11:27:23 db1 postgres[26184]: [3598-1] <username@[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@[local]->etx_ecom> LOG: statement: show log_statement;
Feb 16 11:27:46 db1 postgres[27675]: [3597-1] <username@[local]->etx_ecom> LOG: statement: ALTER DATABASE etx_ecom SET log_statement='ddl';
Feb 16 11:28:54 db1 postgres[30323]: [3595-1] <username@[local]->core> LOG: connection authorized: user=username database=core
Feb 16 11:28:54 db1 postgres[27675]: [3598-1] <username@[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@[local]->etx_ecom> LOG: connection authorized: user=username database=etx_ecom
Feb 16 11:29:45 db1 postgres[30323]: [3596-1] <username@[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@[local]->etx_ecom> LOG: statement: show log_statement;
Feb 16 11:30:05 db1 postgres[31998]: [3597-1] <username@[local]->etx_ecom> LOG: statement: select * from card_type;
Feb 16 11:30:28 db1 postgres[31998]: [3598-1] <username@[local]->etx_ecom> LOG: statement: INSERT INTO card_type VALUES('Z', 'QTEST');
Feb 16 11:30:57 db1 postgres[31998]: [3599-1] <username@[local]->etx_ecom> LOG: statement: DELETE from card_type where type='Q' or type='Z';
Feb 16 11:27:23 db1 postgres[26184]: [3598-1] <username@[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@[local]->etx_ecom> LOG: statement: show log_statement;
Feb 16 11:27:46 db1 postgres[27675]: [3597-1] <username@[local]->etx_ecom> LOG: statement: ALTER DATABASE etx_ecom SET log_statement='ddl';
Feb 16 11:28:54 db1 postgres[30323]: [3595-1] <username@[local]->core> LOG: connection authorized: user=username database=core
Feb 16 11:28:54 db1 postgres[27675]: [3598-1] <username@[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@[local]->etx_ecom> LOG: connection authorized: user=username database=etx_ecom
Feb 16 11:29:45 db1 postgres[30323]: [3596-1] <username@[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@[local]->etx_ecom> LOG: statement: show log_statement;
Feb 16 11:30:05 db1 postgres[31998]: [3597-1] <username@[local]->etx_ecom> LOG: statement: select * from card_type;
Feb 16 11:30:28 db1 postgres[31998]: [3598-1] <username@[local]->etx_ecom> LOG: statement: INSERT INTO card_type VALUES('Z', 'QTEST');
Feb 16 11:30:57 db1 postgres[31998]: [3599-1] <username@[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
System Engineer at Card Access Services
HP: +673 720 8043
On Tue, 16 Feb 2021, 11:23 David G. Johnston, <david.g.johnston@xxxxxxxxx> wrote:
On Monday, February 15, 2021, Abdul Qoyyuum <aqoyyuum@xxxxxxxxxxxxxxxxx> 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@[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.
