Queries in pg_stat_activity are truncated?

Postgresql Problem Overview

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?

Postgresql Solutions

Solution 1 - Postgresql

ALTER SYSTEM SET track_activity_query_size = 16384;

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

Solution 2 - Postgresql

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

Solution 3 - Postgresql

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

See http://darthanthony.wordpress.com/2012/10/11/viewing-running-postgres-queries-and-what-if-they-are-too-long/

gdb [path_to_postgres] [pid]

printf "%s\n", debug_query_string

Solution 4 - Postgresql

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

Solution 5 - Postgresql

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.

Solution 6 - Postgresql

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

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"
# ...

If you do not use docker, you can set the setting at /var/lib/postgresql/data/postgresql.conf. Be sure to restart database to apply the setting. You can use this query to check if was applied:

SHOW track_activity_query_size;

Solution 7 - Postgresql

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.

Content Type	Original Author	Original Content on Stackoverflow
Question	ibz	View Question on Stackoverflow
Solution 1 - Postgresql	gregn	View Answer on Stackoverflow
Solution 2 - Postgresql	Magnus Hagander	View Answer on Stackoverflow
Solution 3 - Postgresql	Tim Kane	View Answer on Stackoverflow
Solution 4 - Postgresql	user80168	View Answer on Stackoverflow
Solution 5 - Postgresql	hilsenrat	View Answer on Stackoverflow
Solution 6 - Postgresql	Konard	View Answer on Stackoverflow
Solution 7 - Postgresql	Anand Tripathi	View Answer on Stackoverflow