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: