Long Running Queries

Do not run in looker or against read replica since you won't see relevant data.

Listing:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
order by duration desc;

Politely cancelling:

SELECT pg_cancel_backend(__pid__);

Killing, for instance if something is "idle in transaction" for hours

SELECT pg_terminate_backend(__pid__);

Seeing which queries are block by which queries

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query
FROM pg_stat_activity
WHERE pg_blocking_pids(pid)::text != '{}';

---

How I (Jared) did this Dec 6 2022 (with guidance from Dima):

1. Connect to a production database. Either via local SSH setup with Bastian or topfunnel console web production and then psql $DATABASE_URL.

2. Run

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query
FROM pg_stat_activity
WHERE pg_blocking_pids(pid)::text != '{}';

3. The most popular value in the pg_blocking_pids column will probably be the pid for the migration so note its id.

4. Now rerun the query from 1 with pid=(the id from 2). Note the blocked_query. Is probably altering tables, fields, or indices and from your migration.

5. Kill it via SELECT pg_terminate_backend(pid from 2);

6. The deploy times out at 10 minutes but should finish the migration it was running. If it fails, rerun it so that it runs other migrations and deploys new code. Repeat the above steps again as needed.

Worked for me for two migrations. Should be able to automate parts or all of 2-5.

May also be worth suspending workers and/or UI when certain migrations are run. Can detect via commands and involved tables.

Replication slot problems https://aws.amazon.com/blogs/database/best-practices-for-amazon-rds-postgresql-replication/

a. Find inactive replications slots:

SELECT slot_name FROM pg_replication_slots WHERE active='f';

b. Drop inactive replication slots:

SELECT pg_drop_replication_slot('slot_name');