... | @@ -72,6 +72,43 @@ Same with databases: |
... | @@ -72,6 +72,43 @@ Same with databases: |
|
|
|
|
|
Source: [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Disk_Usage). See also [the upstream manual](https://www.postgresql.org/docs/11/disk-usage.html).
|
|
Source: [PostgreSQL wiki](https://wiki.postgresql.org/wiki/Disk_Usage). See also [the upstream manual](https://www.postgresql.org/docs/11/disk-usage.html).
|
|
|
|
|
|
|
|
## Show running queries
|
|
|
|
|
|
|
|
If the server seems slow, it's possible to inspect running queries
|
|
|
|
with this query:
|
|
|
|
|
|
|
|
SELECT datid,datname,pid,backend_start,query FROM pg_stat_activity;
|
|
|
|
|
|
|
|
## Killing a slow query
|
|
|
|
|
|
|
|
This will kill all queries to `database_name`:
|
|
|
|
|
|
|
|
```
|
|
|
|
SELECT
|
|
|
|
pg_terminate_backend(pid)
|
|
|
|
FROM
|
|
|
|
pg_stat_activity
|
|
|
|
WHERE
|
|
|
|
-- don't kill my own connection!
|
|
|
|
pid <> pg_backend_pid()
|
|
|
|
-- don't kill the connections to other databases
|
|
|
|
AND datname = 'database_name'
|
|
|
|
;
|
|
|
|
```
|
|
|
|
|
|
|
|
A more selective approach is to list threads (above) and then kill
|
|
|
|
only one PID, say:
|
|
|
|
|
|
|
|
```
|
|
|
|
SELECT
|
|
|
|
pg_terminate_backend(pid)
|
|
|
|
FROM
|
|
|
|
pg_stat_activity
|
|
|
|
WHERE
|
|
|
|
-- don't kill my own connection!
|
|
|
|
pid = 1234;
|
|
|
|
```
|
|
|
|
|
|
## Running a full backup
|
|
## Running a full backup
|
|
|
|
|
|
Backups are normally automatically ran on the backup server (currently
|
|
Backups are normally automatically ran on the backup server (currently
|
... | | ... | |