Database:postgres
This is an old revision of the document!
Table of Contents
Drop database & close connections
drop database [db] WITH (force)
Dump local database to file
PGDATABASE=mydb pg_dump -Fc -Z 9 -d $PGDATABASE -U $POSTGRES_USER > /tmp/$PGDATABASE.dump
Import dump file in existing local database
PGDATABASE=mydb POSTGRES_USER=postgres pg_restore --clean -Fc -j 8 -d $PGDATABASE -U $POSTGRES_USER /tmp/db.sql
Run postgresqltuner script in alpine docker image
apk add perl perl-dbd-pg && wget -O /tmp/postgresqltuner.pl https://postgresqltuner.pl && chmod +x /tmp/postgresqltuner.pl && /tmp/postgresqltuner.pl
Check longest queries
SELECT *
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT
5;
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
query_start,
state
FROM pg_stat_activity
Check locks
select l.pid, l.mode, sa.pid, sa.query
from pg_locks l
inner join pg_stat_activity sa
on l.pid = sa.pid
where l.mode like '%xclusive%';