Queries in pg_stat_activity are truncated?

I'm using SELECT current_query FROM pg_stat_activity; to see the currently executing queries, but I noticed that the query is truncated. Is there any workaround or any other way to see the currently executing queries?

7 Answers

ALTER SYSTEM SET track_activity_query_size = 16384; 

You will still need to restart the service for that to take effect

PostgreSQL 8.4 adds the parameter "track_activity_query_size". The value will still be truncated, but you can control at what length.

An alternative in the extreme case is to use the gdb debugger to attach to the process and print out the query.

See

gdb [path_to_postgres] [pid] printf "%s\n", debug_query_string 

Starting from PostgreSQL 13 the maximum value of track_activity_query_size is increased to 1MB.

The default value of track_activity_query_size is 1024 bytes (as of PostgreSQL 13-16).

Note that if a query is larger than track_activity_query_size, it will be truncated.

With docker

If you use docker, you can configure your docker-compose file like this:

# ... services: postgres: container_name: postgres image: postgis/postgis:13-3.1 command: - "postgres" - "-c" - "track_activity_query_size=1048576" # ... 

Without docker

You can set the setting at /var/lib/postgresql/data/postgresql.conf like this:

track_activity_query_size=1048576 

If you don't find the .conf file at that path, you can determine where it is using this SQL statement:

SHOW config_file; 

Check/Test

Be sure to restart the database to apply the setting. You can use this query to check if it was applied:

SHOW track_activity_query_size; 

If the setting is updated to your desired size, you can now see the effect of this setting on pg_stat_activity table:

SELECT current_query FROM pg_stat_activity; 

Get your postgres.conf file path using below command

psql -U postgres -c 'SHOW config_file' 

Or you are using the root user then simply

SHOW config_file; 

It will output something like

/var/lib/pgsql/data/postgresql.conf # Output of above command 

then just edit the file using vim and change the below parameter to let's say 16kb

track_activity_query_size=16384 

And restart your Postgres server

After that if you run SELECT current_query FROM pg_stat_activity; it will show you the more query but it will truncate till 16KB you can increase the size that you want.

you can just enable statement logging in postgresql (log_statement), and check the logs.

If you run a Google Cloud SQL instance, you can configure track_activity_query_size in Edit Instance --> Flags section.

I personally set it to 32786.