PostgreSQL Administration Commands – (Version 9,10,11,12,13)-Part2

    In my previous post I have shown how we can extract database object details quickly through psql command prompt.

    In this post , I will list few standard sql queries which will help a DBA for their routine activity.

    Find out all session connection details.

    select * from pg_stat_activity;

    Terminate a connection(pid)

    pg_terminate_backend (pid)
    datname = 'dvdrental';
    3218 is the pid from pg_stat_activity which is terminated

    Find out size of a table/relation

    select pg_size_pretty(pg_relation_size('actor'));

    pg_size_pretty() will show size in kB

    Size of all relations including indexes in descending order

    relname AS "relation",
    pg_size_pretty (
    pg_total_relation_size (C .oid)
    ) AS "total_size"
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
    WHERE nspname NOT IN ('pg_catalog','information_schema')
    AND C .relkind <> 'i'
    AND nspname !~ '^pg_toast'
    ORDER BY pg_total_relation_size (C .oid) DESC
    LIMIT 30;

    Size of all databases

    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

    Find out size of all indexes of a relation/table

    SELECT pg_size_pretty (pg_indexes_size('actor'));

    Size of tablespace

    SELECT pg_size_pretty (
    pg_tablespace_size ('pg_default')

    Create a tablespace

    dvdrental=# create tablespace dvdrental_tbs owner postgres location '/usr/local/var/dvdrental_tbs';

    Rename a tablespace

    alter tablespace dvdrental_tbs rename to tbs_dvdrental;

    Change tablespace of a database or move database objects to a separate tablespace

    ALTER DATABASE dvdrental SET tablespace  tbs_dvdrental;

    If you get following error then drop connections using following commands and run the alter database command again.

    SELECT datname,pid FROM  pg_stat_activity WHERE   datname = 'dvdrental';

    SELECT pg_terminate_backend (<pid>) FROM pg_stat_activity WHERE datname = 'dvdrental';

    Recent Articles


    Related Stories

    1 Comment

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox