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.

[martyphee]

Here is a sample file if it helps. Having same issue. The file isn't being parsed. Going to try csv export from Logs Explorer

sample.log

This should work though to get proper files: jq -r '.[] | select(.textPayload != null) | .textPayload' downloaded-logs-20251203-085442.json > postgres.log