Short Notes on PSQL

PSQL aka Postgres aka PostgreSQL... I prefer psql.

Dump and Restore

dump:
$ pg_dump -h host -p 5432 -U user -F c -b -v -f /tmp/db_name.backup db_name
restore:
$ pg_restore -h host -p 5432 -U user -d db_name -v /tmp/db_name.backup

Delete Duplicate Lines

Before you can add a unique constraint to a table, you have to make sure it does satisfy this criteria.

With table_T and columns criteria_1, ..., criteria_N.

;;
;; list rows that do not satisfy the uniqueness constraint
;;
SELECT
    criteria_1,
    ...
    criteria_N,
    COUNT(*)
FROM
    table_T
GROUP BY
    criteria_1, ..., criteria_N
HAVING
    COUNT(*) > 1
ORDER BY
    criteria_1, ..., criteria_N;

;;
;; delete all rows that do not satisfy the constraint, keeping the ones with lowest id value
;;
DELETE  FROM
    table_T a
        USING table_T b
WHERE
    a.id > b.id
    AND a.criteria_1 = b.criteria_1
    ...
    AND a.criteria_N = b.criteria_N;

Set Sequence Value to Max of Table's ID

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

Find and Kill Stuck Queries

To get list of queries that are running for more than 5 minutes:

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';

Kill these by PID:

SELECT pg_terminate_backend(_pid_);