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
\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 1
3. Get all ID columns in PSQL¶
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name LIKE '%id';
Example
column_name | data_type
-----------------------+-------------------
id | integer
...
some_id | uuid
id | text
notification_id | uuid
(21 rows)
4. List and order tables by size¶
see SO: PostgreSQL: Get table size
This shows you the size of all tables in the schema pg_catalog
:
SELECT table_name,
PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(QUOTE_IDENT(table_name))),
PG_TOTAL_RELATION_SIZE(QUOTE_IDENT(table_name))
FROM information_schema.tables
WHERE table_schema = 'pg_catalog'
ORDER BY 3 DESC;
Example
table_name | pg_size_pretty | pg_total_relation_size |
---|---|---|
pg_proc | 1320 kB | 1351680 |
pg_attribute | 856 kB | 876544 |
pg_rewrite | 776 kB | 794624 |
This shows you the size of all tables in all schemas:
SELECT table_schema,
table_name,
PG_RELATION_SIZE('"' || table_schema || '"."' || table_name || '"')
FROM information_schema.tables
ORDER BY 3 DESC;
Example
table_schema | table_name | pg_relation_size |
---|---|---|
pg_catalog | pg_proc | 884736 |
public | content_production_briefings | 688128 |
pg_catalog | pg_attribute | 573440 |
pg_catalog | pg_collation | 114688 |
information_schema | sql_features | 65536 |
pg_catalog | pg_amop | 57344 |
-
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) ↩