pgBadger: Cannot find any log entries from systemd-journald

[Frank Lanitz]

Hello,

I think I just might did a small configuration issue, but somehow I cannot 
figure it out by myself.

I want to let pgbadger directly fetch data from journald -- so I was using with 
a unexpected outcome:

$ pgbadger --journalctl "journalctl -u postgresql.service"
LOG: Ok, generating html report...s: 0, events: 0

Having a look into the journal there is a lot of

---cut
Mar 05 09:06:23 myhost postgres[441820]: 2024-03-05 08:06:23 GMT [441820]: 
user=[unknown],db=[unknown],app=[unknown],client=xxx LOCATION:  
BackendInitialize, postmaster.c:4373
Mar 05 09:06:23 myhost postgres[441820]: 2024-03-05 08:06:23 GMT [441820]: 
user=[unknown],db=[unknown],app=[unknown],client=xxx LOG:  0: connection 
received: host=xxx port=34574
Mar 05 09:06:10 myhost postgres[441805]: 2024-03-05 08:06:10 GMT [441805]: 
user=postgres,db=xxx,app=psql,client=[local] LOCATION:  log_disconnections, 
postgres.c:4722
Mar 05 09:06:10 myhost postgres[441805]: 2024-03-05 08:06:10 GMT [441805]: 
user=postgres,db=xxx,app=psql,client=[local] LOG:  0: disconnection: 
session time: 0:00:00.016 user=postgres database=xxx host=[local]
Mar 05 09:06:10 myhost postgres[441805]: 2024-03-05 08:06:10 GMT [441805]: 
user=postgres,db=xxx,app=[unknown],client=[local] LOCATION:  
PerformAuthentication, postinit.c:292
Mar 05 09:06:10 myhost postgres[441805]: 2024-03-05 08:06:10 GMT [441805]: 
user=postgres,db=xxx,app=[unknown],client=[local] LOG:  0: connection 
authorized: user=postgres database=xxx application_name=psql
---cut---

My psotgresql.conf looks like:
---cut
log_autovacuum_min_duration = 0
log_checkpoints = yes
log_connections = yes
log_destination = 'stderr'
log_disconnections = yes
log_error_verbosity = 'verbose'
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
log_lock_waits = yes
log_min_duration_statement = 100
log_temp_files = 0
---cut

Any idea what I missed or did wrong?

Cheers,
Frank

Connection remains idle for more than 15 minutes, then the connection drops by IPVS setting in k8s kube-proxy

[M Tarkeshwar Rao]

Hi Tarkesh,

"
We are using libpq c client (Version : 14.10) of postgresql in our application 
to connect with postgresql database server (Version 14.10).
For making connection we are using libpq's API "PQsetdbLogin()".

Problem Statement : If our connection remains idle for more than 15 minutes, 
then the connection drops by IPVS setting in k8s kube-proxy.

Query : Is there any way we can provide values of "keepalives_idle, 
keepalives_interval, keepalives_count" client parameters in the API 
"PQsetdbLogin()" while creating the connection.
If yes, then can someone please provide the sample code for the same.
 We need these parameters support at client side 
only.
"

Is there any other alternative approach using some configuration files from 
client side?

Thanks,
Tarkeshwar

Recall: Connection remains idle for more than 15 minutes, then the connection drops by IPVS setting in k8s kube-proxy

[M Tarkeshwar Rao]

M Tarkeshwar Rao would like to recall the message, "Connection remains idle for 
more than 15 minutes, then the connection drops by IPVS setting in k8s 
kube-proxy".

Connection remains idle for more than 15 minutes, then the connection drops by IPVS setting in k8s kube-proxy

[M Tarkeshwar Rao]

Hi all,

"
We are using libpq c client (Version : 14.10) of postgresql in our application 
to connect with postgresql database server (Version 14.10).
For making connection we are using libpq's API "PQsetdbLogin()".

Problem Statement : If our connection remains idle for more than 15 minutes, 
then the connection drops by IPVS setting in k8s kube-proxy.

Query : Is there any way we can provide values of "keepalives_idle, 
keepalives_interval, keepalives_count" client parameters in the API 
"PQsetdbLogin()" while creating the connection.
If yes, then can someone please provide the sample code for the same.
 We need these parameters support at client side 
only.
"

Is there any other alternative approach using some configuration files from 
client side?

Thanks,
Tarkeshwar

Re: Connection remains idle for more than 15 minutes, then the connection drops by IPVS setting in k8s kube-proxy

[Matthias Apitz]

El día martes, marzo 05, 2024 a las 11:08:27 +, M Tarkeshwar Rao escribió:

> Hi all,
> 
> "
> We are using libpq c client (Version : 14.10) of postgresql in our 
> application to connect with postgresql database server (Version 14.10).
> For making connection we are using libpq's API "PQsetdbLogin()".
> 
> Problem Statement : If our connection remains idle for more than 15 minutes, 
> then the connection drops by IPVS setting in k8s kube-proxy.
> 
> Query : Is there any way we can provide values of "keepalives_idle, 
> keepalives_interval, keepalives_count" client parameters in the API 
> "PQsetdbLogin()" while creating the connection.
> If yes, then can someone please provide the sample code for the same.
>  We need these parameters support at client side 
> only.
> "
> 
> Is there any other alternative approach using some configuration files from 
> client side?

An option could be to run the connection through an SSH tunnel and use
there the sshd(8) config parameter ClientAliveInterval.

HIH

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

I am not at war with Russia.  Я не воюю с Россией.
Ich bin nicht im Krieg mit Russland.

RE: Connection remains idle for more than 15 minutes, then the connection drops by IPVS setting in k8s kube-proxy

[M Tarkeshwar Rao]

Hi Matthias,

Currently many customers are using this. 
We want to use libpq library APIs only. Is there any support of keepalive at 
client side?

Regards
Tarkeshwar

-Original Message-
From: Matthias Apitz  
Sent: Tuesday, March 5, 2024 4:59 PM
To: M Tarkeshwar Rao 
Cc: pgsql-gene...@postgresql.org; Piyush Anand ; 
Neeraj Gupta G 
Subject: Re: Connection remains idle for more than 15 minutes, then the 
connection drops by IPVS setting in k8s kube-proxy

El día martes, marzo 05, 2024 a las 11:08:27 +, M Tarkeshwar Rao escribió:

> Hi all,
> 
> "
> We are using libpq c client (Version : 14.10) of postgresql in our 
> application to connect with postgresql database server (Version 14.10).
> For making connection we are using libpq's API "PQsetdbLogin()".
> 
> Problem Statement : If our connection remains idle for more than 15 minutes, 
> then the connection drops by IPVS setting in k8s kube-proxy.
> 
> Query : Is there any way we can provide values of "keepalives_idle, 
> keepalives_interval, keepalives_count" client parameters in the API 
> "PQsetdbLogin()" while creating the connection.
> If yes, then can someone please provide the sample code for the same.
>  We need these parameters support at client side 
> only.
> "
> 
> Is there any other alternative approach using some configuration files from 
> client side?

An option could be to run the connection through an SSH tunnel and use there 
the sshd(8) config parameter ClientAliveInterval.

HIH

matthias

--
Matthias Apitz, ✉ g...@unixarea.de, 
https://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-45444731-8bc62051d01460c7&q=1&e=8f12e60a-aafb-44bb-8a35-b4781b90d8c1&u=http%3A%2F%2Fwww.unixarea.de%2F
 +49-176-38902045 Public GnuPG key: 
https://protect2.fireeye.com/v1/url?k=31323334-501d5122-313273af-45444731-a831eed841ce4cbb&q=1&e=8f12e60a-aafb-44bb-8a35-b4781b90d8c1&u=http%3A%2F%2Fwww.unixarea.de%2Fkey.pub

I am not at war with Russia.  Я не воюю с Россией.
Ich bin nicht im Krieg mit Russland.

Re: Connection remains idle for more than 15 minutes, then the connection drops by IPVS setting in k8s kube-proxy

[Laurenz Albe]

On Tue, 2024-03-05 at 11:37 +, M Tarkeshwar Rao wrote:
> We want to use libpq library APIs only. Is there any support of keepalive at 
> client side?

Yes, there is.

Just add "keepalives_idle=60" to the connection string.  See
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-KEEPALIVES-IDLE

Yours,
Laurenz Albe

Re: CTEs and concurrency

[Brecht De Rooms]

My apologies, when I said: 
"When logging the input values, we can see that there are indeed two times
the same value sets (exactly the same)"

It seems I was wrong, the UUIDs differed on 1-2 characters which means that the 
problem lies elsewhere in the system. It does seem that my assumptions of how 
this query should behave are correct and that the problem lies elsewhere.


> On Mar 4, 2024, at 22:44, Brecht De Rooms  wrote:
> 
> Dear, 
> 
> I am currently running a chaos test on a system (essentially starting nodes 
> that process something and randomly knockign them out). It appeared to work 
> fine with regular tests but I am seeing occasional duplicate key value 
> violattions of a uniqueness constraint on one of the complexer CTE-based 
> queries. Something that  only happens with concurrency where nodes restart 
> and ample load.
> I can not reproduce it by taking out the query and running it manually in PG 
> Admin, it behaves fine if I do so and does exactly what I expect. 
> 
> The query looks like this (it uses Rust SQLX which is why there is some 
> unnesting happening on the parameters). 
> 
> WITH unnested_inputs AS (
> SELECT * FROM (
> SELECT
> unnest($1::uuid[]) AS event_id,
> unnest($2::varchar[]) AS type,
> unnest($3::int[]) AS version,
> unnest($4::uuid[]) AS causation_id,
> unnest($5::uuid[]) AS correlation_id,
> unnest($6::text[]) AS idempotency_key,
> unnest($7::jsonb[]) AS data,
> unnest($8::jsonb[]) AS metadata,
> unnest($9::text[]) AS subscription_id,
> unnest($10::text[]) AS subscription_instance_identifier,
> unnest($11::bigint[]) AS applied_order_id
> ) AS inputs
> ),
> to_update_subscription_logs AS (
> SELECT sl.id  as subscription_log_id, sl.node_id, 
> sl.status, ui.*
> FROM subscription_log sl
> JOIN unnested_inputs ui
> ON sl.event_id = ui.causation_id
> AND sl.node_id = $12
> AND sl.status = 'assigned'
> AND sl.subscription_id = ui.subscription_id
> AND sl.subscription_instance_identifier = 
> ui.subscription_instance_identifier
> FOR UPDATE NOWAIT -- if something is updating it, we probably shouldn't 
> touch it anymore.
> ),
> updated_logs AS (
> UPDATE subscription_log sl
> SET status = 'processed',
> updated_at = CURRENT_TIMESTAMP
> FROM to_update_subscription_logs usl
> WHERE sl.id  = usl.subscription_log_id
> AND usl.node_id = $12
> ),
> inserted_event_log AS (
> INSERT INTO event_log (
> event_id, type, version, causation_id, correlation_id,
> idempotency_key, data, metadata, created_at
> )
> SELECT
> event_id, type, version, usl.causation_id, correlation_id,
> idempotency_key, data, metadata, CURRENT_TIMESTAMP
> FROM to_update_subscription_logs usl
> ),
> inserted_output_routing_info AS (
> INSERT INTO output_event_routing (event_id, subscription_id, 
> subscription_instance_identifier, applied_order_id)
> SELECT event_id, subscription_id, subscription_instance_identifier, 
> applied_order_id
> FROM to_update_subscription_logs usl
> ),
> 
> SELECT * FROM to_update_subscription_logs
> 
> The tables look as follows:
> 
> CREATE TABLE event_log (
> event_id UUID PRIMARY KEY,
> event_order_id BIGINT REFERENCES event(order_id),
> type varchar NOT NULL,
> version int NOT NULL,
> causation_id UUID,
> correlation_id UUID,
> idempotency_key TEXT NOT NULL,
> data JSONB NOT NULL,
> metadata JSONB,
> created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
> CONSTRAINT constraint_event_log_unique_idempotency_key 
> UNIQUE(idempotency_key) -- idempotent writes.
> );
> 
> CREATE TABLE output_event_routing (
> event_id UUID REFERENCES event_log(event_id),
> subscription_id TEXT NOT NULL,  
> subscription_instance_identifier TEXT,  
> applied_order_id BIGINT,
> CONSTRAINT constraint_output_event_routing_uniqueness 
> UNIQUE(subscription_id, subscription_instance_identifier, applied_order_id)
> );
> 
> CREATE TABLE subscription_log (
> id UUID NOT NULL PRIMARY KEY,
> event_id UUID NOT NULL,  
> event_order_id BIGINT NOT NULL, 
> event_correlation_id UUID NOT NULL,  
> subscription_instance_identifier TEXT NOT NULL,
> subscription_id TEXT NOT NULL REFERENCES subscription(name),
> status processing_status NOT NULL DEFAULT 'enqueued',
> node_id UUID references node(id), -- is null until assigned.
> );
> 
> Since I'm trying to avoid using PL/pgSQL upon request I tried to achieve the 
> following behaviour in CTEs: 
> - For given events, update the subscription log to 'processed' only if we 
> still are the node that is processing these and the status is still 
> 'assigned'.
> - Only for the events where the previous succeeded, continue pro

Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working

[Stephen Frost]

Greetings,

* Matthew Dennison (m...@matty-uk.co.uk) wrote:
> Host file looks like:
> 
> 127.0.0.1   localhost hostname hostname.mydomain.net
> ::1 localhost hostname hostname.mydomain.net
> 10.204.50.65 hostname hostname.mydomain.net
> 
> I also tried commenting out the ::1 line, but the issue moved to 127.0.0.1, 
> commented that out and the issue moved to the IP.  I really don’t get it.  
> However, when I finally realised it was a localised issue (after days) I was 
> able to move forward.  No idea why it will not work on the server itself, but 
> it is something I can live with.

Once you got it to just the IP, it was trying to look up 'hostname' not
'hostname.mydomain.net' in the KDC, I suspect, since that's first.
Might be interesting to try it with:

10.204.50.65 hostname.mydomain.net hostname

instead.

I've no doubt that it can be made to work if we get the DNS bits sorted.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: pgBadger: Cannot find any log entries from systemd-journald

[grimy . outshine830]

On Tue, Mar 05, 2024 at 09:13:48AM +0100, Frank Lanitz wrote:

I don't actually know pgbadger, but:

> $ pgbadger --journalctl "journalctl -u postgresql.service"
> LOG: Ok, generating html report...s: 0, events: 0

Try as root? Or is pgbadger a setuid program?

-- 
Ian

Re: When manual analyze is needed

[Greg Sabino Mullane]

>
> We were planning to have the auto_explain extension added and set the
> log_min_duration to ~5 seconds and log_analyze to true. So that all the
> queries going above that time period will be logged and provide detailed
> information on the exact point of bottleneck. Will it be a good idea to set
> it on production DB which is a highly active database? or should we only
> have the extension added but only set the parameters while we debug some
> performance issue and then reset it back after we are done.
>

I would not use log_analyze on a highly active production db. Even on a dev
system, use it carefully as it has some downsides. The log_min_duration of
5s should be fine everywhere, however.

Cheers,
Greg

Re: Is partition pruning impacted by data type

[Lok P]

On Tue, Mar 5, 2024 at 1:09 AM sud  wrote:

>
> However the question we have is ,
> 1)If there is any downside of having the partition key with "timestamp
> with timezone" type? Will it impact the partition pruning of the queries
> anyway by appending any run time "time zone" conversion function during the
> query planning/execution phase?
> 2) As it will take the default server times , so during daylight saving
> the server time will change, so in that case, can it cause any unforeseen
> issue?
> 3)Will this cause the data to be spread unevenly across partitions and
> make the partitions unevenly sized? If will go for UTC/GMT as db time, the
> user's one day transaction might span across two daily partitions.
>
>
My 2 cents.
We have cases which use the "timestamp with timezone" column as partition
key  and the partition pruning happens for the read queries without any
issue, so we don't see any conversion functions applied to the predicate as
such which is partition key. I think if the users go global it's better to
have the database time in UTC time zone. and it's obvious that, In case of
global users the data ought to be span across multiple days as the days
won't be as per the users time zone rather UTC.

Re: Is partition pruning impacted by data type

[sud]

Thank you.

Yes, I tried creating a table manually with column timestamptz(6) type and
partitioned on that and then executed select query with the filter on that
column and I do see partition pruning happening. Not able to visualize any
other issues though, however some teammates say it may have a negative
impact on aggregation type queries , not sure how but will try to test it.
Thanks again for the response.

On Wed, Mar 6, 2024 at 12:35 AM Lok P  wrote:

>
> On Tue, Mar 5, 2024 at 1:09 AM sud  wrote:
>
>>
>> However the question we have is ,
>> 1)If there is any downside of having the partition key with "timestamp
>> with timezone" type? Will it impact the partition pruning of the queries
>> anyway by appending any run time "time zone" conversion function during the
>> query planning/execution phase?
>> 2) As it will take the default server times , so during daylight saving
>> the server time will change, so in that case, can it cause any unforeseen
>> issue?
>> 3)Will this cause the data to be spread unevenly across partitions and
>> make the partitions unevenly sized? If will go for UTC/GMT as db time, the
>> user's one day transaction might span across two daily partitions.
>>
>>
> My 2 cents.
> We have cases which use the "timestamp with timezone" column as partition
> key  and the partition pruning happens for the read queries without any
> issue, so we don't see any conversion functions applied to the predicate as
> such which is partition key. I think if the users go global it's better to
> have the database time in UTC time zone. and it's obvious that, In case of
> global users the data ought to be span across multiple days as the days
> won't be as per the users time zone rather UTC.
>
>
>
>