Steevo Wiki

You are here: Home » database » postgres

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
database:postgres [2024/03/05 17:10]
admin created 		database:postgres [2025/10/22 08:21] (current)
admin
Line 1: Line 1:
 +==== 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 ==== ==== Dump local database to file ====
 +<code>
 PGDATABASE=mydb PGDATABASE=mydb
 pg_dump -Fc -Z 9 -d $PGDATABASE -U $POSTGRES_USER > /tmp/$PGDATABASE.dump pg_dump -Fc -Z 9 -d $PGDATABASE -U $POSTGRES_USER > /tmp/$PGDATABASE.dump
 +</code>
  
  
Line 7: Line 28:
 <code> <code>
 PGDATABASE=mydb PGDATABASE=mydb
 +POSTGRES_USER=postgres
 pg_restore --clean -Fc -j 8 -d $PGDATABASE -U $POSTGRES_USER /tmp/db.sql pg_restore --clean -Fc -j 8 -d $PGDATABASE -U $POSTGRES_USER /tmp/db.sql
 </code> </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>