Обсуждение: Connections on cluster not being logged

Am 23.07.2018 um 17:14 schrieb Sandy Becker:
> I have postgresql 9.4 on a cluster, hardware based.  I need to be able 
> to see which users are connecting to which database and when to be in 
> compliance with our security policies.
>
> I have set the following in the postgresql.conf and did a pg_ctl reload:
>
>      log_connections = on
>      log_line_prefix = '%t [%p]:[%u]:[%h]-[%d] [%1-1]'

should work, can you check if those settings are in effect?

select name, setting from pg_settings where name in 
('log_connections','log_line_prefix');


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Am 23.07.2018 um 17:25 schrieb Sandy Becker:
> Yes, they are in effect.

strange. the logging is working? you can see other and actual entries in 
the logfile?

Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

On 07/23/2018 08:14 AM, Sandy Becker wrote:
> I have postgresql 9.4 on a cluster, hardware based.  I need to be able 
> to see which users are connecting to which database and when to be in 
> compliance with our security policies.
> 
> I have set the following in the postgresql.conf and did a pg_ctl reload:
> 
>       log_connections = on
>       log_line_prefix = '%t [%p]:[%u]:[%h]-[%d] [%1-1]'
> 
> Unfortunately, it doesn't seem to be logging the connections.  This is 
> what I have used on our non-clustered instances and it's working as 
> expected.  Have I missed something relating to logging on a cluster?  
> The second node is strictly for automatice failover, so nothing is 
> actually running there at the moment.

Can you define what you mean by a cluster?


> 
> Thank you in advance for any suggestions.
> 
> Sandy


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/23/2018 12:50 PM, Sandy Becker wrote:
Please reply to list also.
Ccing list.

> Two servers set up in a hardware cluster for automatic failover.  That's 
> all I know about it.

Alright, so which server's logs are you looking at?

Long term it would be a good thing to know how the cluster/failover is 
setup, as that is something folks on this list are going to ask when 
attempting to answer a question. Just trying to get ahead of the 
inevitable:)

> 
> On Mon, Jul 23, 2018 at 1:04 PM, Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 07/23/2018 08:14 AM, Sandy Becker wrote:
> 
>         I have postgresql 9.4 on a cluster, hardware based.  I need to
>         be able to see which users are connecting to which database and
>         when to be in compliance with our security policies.
> 
>         I have set the following in the postgresql.conf and did a pg_ctl
>         reload:
> 
>                log_connections = on
>                log_line_prefix = '%t [%p]:[%u]:[%h]-[%d] [%1-1]'
> 
>         Unfortunately, it doesn't seem to be logging the connections. 
>         This is what I have used on our non-clustered instances and it's
>         working as expected.  Have I missed something relating to
>         logging on a cluster?  The second node is strictly for
>         automatice failover, so nothing is actually running there at the
>         moment.
> 
> 
>     Can you define what you mean by a cluster?
> 
> 
> 
> 
>         Thank you in advance for any suggestions.
> 
>         Sandy
> 
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/24/2018 06:25 AM, Sandy Becker wrote:
> There is only one set of logs since it's a hardware cluster.  The two 
> nodes share the underlying database storage.  Not sure why, but when the 

The community Postgres can't do that, have two instances share the same 
data storage, at least AFAIK. So are you using some fork of Postgres or 
are there actually two data directories?

> log rolled over this morning, connections started getting logged.  All 
> is good now.  Thanks for  your help.
> 
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2018-07-24 06:46:18 -0700, Adrian Klaver wrote:
> On 07/24/2018 06:25 AM, Sandy Becker wrote:
> > There is only one set of logs since it's a hardware cluster.  The two
> > nodes share the underlying database storage.  Not sure why, but when the
>
> The community Postgres can't do that, have two instances share the same data
> storage, at least AFAIK. So are you using some fork of Postgres or are there
> actually two data directories?

Maybe Postgresql only runs on the active node? I.e. a classic failover
cluster.

(I'm still not sure what a "hardware cluster" is. Probably some kind of
appliance which packages two nodes, some storage and the HA software.)

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

On 07/24/2018 09:47 AM, Peter J. Holzer wrote:
> On 2018-07-24 06:46:18 -0700, Adrian Klaver wrote:
>> On 07/24/2018 06:25 AM, Sandy Becker wrote:
>>> There is only one set of logs since it's a hardware cluster.  The two
>>> nodes share the underlying database storage.  Not sure why, but when the
>>
>> The community Postgres can't do that, have two instances share the same data
>> storage, at least AFAIK. So are you using some fork of Postgres or are there
>> actually two data directories?
> 
> Maybe Postgresql only runs on the active node? I.e. a classic failover
> cluster.

Yeah, the OP emailed me off-list saying just that. My suspicion, they 
where looking at log for inactive node.


> 
> (I'm still not sure what a "hardware cluster" is. Probably some kind of
> appliance which packages two nodes, some storage and the HA software.)
> 
>          hp
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com