PostgreSQL Queries¶

What I wish someone told me about Postges - Hazel Bachrach (2024)

Drop all tables of current schema¶

The following command will generate a DROP TABLE ... query per table that exists in the current schema. To drop all tables, you need first to run the following command and after that the output of this command (the DROP TABLE ... commands).

select 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' from pg_tables where schemaname = current_schema();

Show previliges for user on current DB¶

SELECT * FROM information_schema.role_table_grants WHERE grantee = '<user>';

Show Database previliges¶

> \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 somedb1   | somedb1  | UTF8     | de_DE.utf8 | de_DE.utf8 |
 somedb    | someuser | UTF8     | de_DE.utf8 | de_DE.utf8 | =Tc/somedb           +
           |          |          |            |            | someuser=CTc/somedb    +
           |          |          |            |            | telegraf=c/somedb
 postgres  | postgres | UTF8     | de_DE.utf8 | de_DE.utf8 |
 template0 | postgres | UTF8     | de_DE.utf8 | de_DE.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | de_DE.utf8 | de_DE.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres

Show Schema previliges¶

of Database somedb

> \dn+
                                 List of schemas
        Name         |  Owner   |  Access privileges    |      Description
---------------------+----------+-----------------------+------------------------
 public              | postgres | postgres=UC/postgres+ | standard public schema
                     |          | =UC/postgres          |
 gugu                | someuser | someuser=UC/somedb   +|
                     |          | telegraf=U/somedb     |
 gaga                | someuser | someuser=UC/somedb   +|
                     |          | telegraf=U/somedb     |

Show default privileges¶

> \ddp
          Default access privileges
 Owner   | Schema |  Type  | Access privileges
---------+--------+--------+-------------------
 someuser|        | schema | someuser=UC/somedb +
         |        |        | telegraf=U/somedb

Show tables, views and sequences with privileges¶

> \dp
                                             Access privileges
   Schema   |   Name    | Type  | Access privileges | Column privileges | Policies
------------+-----------+-------+-------------------+-------------------+----------
 someschema | sometable | table |                   |                   |

Default privileges¶

This alters the default privileges

ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT ALL ON TABLES TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT ALL ON SEQUENCES TO backup;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON Tables TO "some-user";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO "some-user";

-- https://stackoverflow.com/a/22684537
ALTER DEFAULT PRIVILEGES FOR USER "database-owner-user" IN SCHEMA public GRANT SELECT ON TABLES TO "some-user";
ALTER DEFAULT PRIVILEGES FOR USER "database-owner-user" IN SCHEMA public GRANT SELECT ON SEQUENCES TO "some-user";

Processes and Connections¶

Show all processes¶

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE state = 'active';

Kill hanging queries¶

SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT pg_cancel_backend(PID);
SELECT pg_terminate_backend(PID);

Show amount of current connections¶

select  * from
(select count(*) used from pg_stat_activity) q1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;

used | res_for_super | max_conn
------+---------------+----------
   48 |             3 |      500

List all non idle connections¶

select
    datname as db_name,
    count(1) as num_non_idle_connections
from pg_stat_activity
where state!='idle'
group by 1
order by 2 desc;

List currently active queries¶

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age FROM pg_stat_activity
    WHERE (state <> 'idle' or state is null)
    AND query NOT LIKE '% FROM pg_stat_activity %' ORDER BY age desc;

Replication¶

Check replication lag¶

select checkpoint_lsn,  pg_current_wal_lsn,
       round((pg_current_wal_lsn - checkpoint_lsn) / 1024 / 1024 / 1024, 2) AS GB_behind
from pg_current_wal_lsn(), pg_control_checkpoint();