Database:postgres | Steevo Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
==== Drop database & close connections ====

<code>
drop database [db] WITH (force)
</code>


==== Drop all tables ====
<code>
DO $$ 
DECLARE 
    r RECORD;
BEGIN 
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP; 
END $$;
</code>

==== Dump local database to file ====
<code>
PGDATABASE=mydb
pg_dump -Fc -Z 9 -d $PGDATABASE -U $POSTGRES_USER > /tmp/$PGDATABASE.dump
</code>


==== Import dump file in existing local database ====
<code>
PGDATABASE=mydb
POSTGRES_USER=postgres
pg_restore --clean -Fc -j 8 -d $PGDATABASE -U $POSTGRES_USER /tmp/db.sql
</code>

==== Run postgresqltuner script in alpine docker image ====
<code>
apk add perl perl-dbd-pg && wget -O /tmp/postgresqltuner.pl https://postgresqltuner.pl && chmod +x /tmp/postgresqltuner.pl && /tmp/postgresqltuner.pl
</code>

==== Check longest queries ====
<code>
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
</code>

==== Check locks ====
<code>
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%';
</code>