Skip to content

PostgreSQL¶

References¶

Tools¶

pgx_scripts - A collection of useful little scripts for database analysis and administration
- bloat - queries to estimate bloat in tables and indexes
pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes.

1. Export to CSV with `\copy`¶

References

PostgreSQL Documentation: COPY — copy data between a file and a table
Atlassian: Export to CSV with \copy
SO: Export specific rows from a PostgreSQL table as INSERT SQL script

myquery.sql

\COPY (SELECT abc FROM tbl_name WHERE <query>) TO 'sample-result.csv' CSV header;

➜ psql -af myquery.sql

2. Terminating long running connections¶

If you need to terminate a long running query or a connection e.g. because it is in IDLE in transaction admins can run:

select user_management.terminate_backend(<pid>);

Or to terminate all application connections:

select user_management.terminate_backend(pid)
  from pg_stat_activity
 where usename = 'your_application_user';

or use pg_terminate_backend ( pid integer ) → boolean sends SIGTERM signal to backend processes identified by process ID ¹

Terminates the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend, however only superusers can terminate superuser backends (see Server Signaling Functions) ↩