Aller au contenu

How to Find and Terminate Process#

List all running process#

Postgres
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    client_hostname,
    client_port,
    backend_start,
    state,
    query,
    NOW() - backend_start AS runtime
FROM
    pg_stat_activity
WHERE
    state = 'active'
    -- AND query LIKE 'prefix%' -- filter on query
;

Delete all task#

Postgres
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
DO
$$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT pid
        FROM pg_stat_activity
        WHERE state = 'active'
          AND now() - backend_start > INTERVAL '15 minutes'
          AND usename <> 'postgres'  -- Exclude superuser processes
          -- AND query LIKE 'prefix%' -- filter on query
    LOOP
        EXECUTE 'SELECT pg_terminate_backend(' || r.pid || ');';
    END LOOP;
END

Install a periodic Job#

This can be done with PG_CRON

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html

1. Verify if PG_CRON is installed#

SQL
1
2
3
SELECT *
FROM pg_extension
WHERE extname = 'pg_cron';

1.1 Install pg_cron#

Modify the custom parameter group associated with your PostgreSQL DB instance by adding pg_cron to the shared_preload_libraries parameter value.

1.2 Load pg_cron#

SQL
1
CREATE EXTENSION pg_cron;

2. Current Cron Jobs#

Configured Cron Jobs
1
SELECT * FROM cron.job;
Ran Jobs Status
1
SELECT * FROM cron.job_run_details;
Cron Configuration
1
SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;

3. Adding a Cron JOb#

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT cron.schedule(
    'delete_old_tasks',  -- job name
    '0 * * * *',         -- cron syntax for every hour
    $$
    DO
    $$
    DECLARE
        r RECORD;
    BEGIN
        FOR r IN
            SELECT pid
            FROM pg_stat_activity
            WHERE state = 'active'
              AND now() - backend_start > INTERVAL '15 minutes'
              AND usename <> 'postgres'  -- Exclude superuser processes
              -- AND query LIKE 'prefix%' -- filter on query
        LOOP
            EXECUTE 'SELECT pg_terminate_backend(' || r.pid || ');';
        END LOOP;
    END
    $$;
$$);