pgbadger for AlloyDB

[maheshetty20]

Hi,

Is it possible anyway to run pgbadger for Alloydb log files ?

Basically the logs are in Json format (Multilines)

[darold]

Hi, if you can post a sample log file I can tell you if this is doable.

[maheshetty20]

{ "textPayload": "2022-12-16 04:00:17.448 UTC [1598973]: [2-1] db=bsc,user=postgres STATEMENT: select count(*) from transactions;", "insertId": "s=e9d7e6f195e741b68ef58ce88496f88d;i=a5b7ea;b=b8a345909c8b4263b14249a164616ca5;m=8a4f5d4a44;t=5efea01f0101c;x=42a813d365eae352-0-0@a1", "resource": { "type": "alloydb.googleapis.com/Instance", "labels": { "instance_id": "us-p10-instance-1", "location": "us-central1", "resource_container": "projects/5568746790", "cluster_id": "fl-db-us-p10" } }, "timestamp": "2022-12-16T04:00:17.448988Z", "severity": "ERROR", "labels": { "NODE_ID": "r1hm", "CONSUMER_PROJECT": "fl" }, "logName": "projects/fl/logs/alloydb.googleapis.com%2Fpostgres.log", "receiveTimestamp": "2022-12-16T04:00:18.904371316Z" }, { "textPayload": "2022-12-16 04:00:37.721 UTC [1598979]: [13-1] db=alloydbadmin,user=alloydbadmin ERROR: [nbtinsert.c:671] duplicate key value violates unique constraint "pg_class_oid_index"", "insertId": "s=e9d7e6f195e741b68ef58ce88496f88d;i=a5b9c7;b=b8a345909c8b4263b14249a164616ca5;m=8a50929f53;t=5efea0325652a;x=6c2a4e45c3cf0c83-0-0@a1", "resource": { "type": "alloydb.googleapis.com/Instance", "labels": { "cluster_id": "fl-db-us-p10", "instance_id": "us-p10-instance-1", "resource_container": "projects/5568746790", "location": "us-central1" } }, "timestamp": "2022-12-16T04:00:37.721386Z", "severity": "ERROR", "labels": { "NODE_ID": "r1hm", "CONSUMER_PROJECT": "fl" }, "logName": "projects/fl/logs/alloydb.googleapis.com%2Fpostgres.log", "receiveTimestamp": "2022-12-16T04:00:38.675304140Z" },

[darold]

Thanks, I think this is doable especially if log_min_duration_statement can be enabled on PostgreSQL side. Like for others multi-line json log format, this needs work for integration in pgbadger. I don't have time for the moment so this is at a very low priority unless Google wants to support pgBadger development.

[maheshetty20]

Yes log_min_duration_statement can be enabled in ALLOYDB

[tanujbolisetty]

Does that mean that cloudsql is also not supported ?

[darold]

pgbadger documentation is claiming the following:

Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but encapsulated
in JSON format. It is autodetected by pgBadger but in case you need to force
the log format use jsonlog:

    pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log

but there is no follup of the Google log format change so it is possible that is doesn't work any more. A sample of your log would help me to check that.