POSTGRESQL DAILY USEFUL QUERIES AND COMMAND.
PG-META COMMANDS
\dd object-name : Display comments for object-name
\db : List all tablespaces
\dn : List all schemas
\dt : List all tables
\di : List all indexes
\ds : List all sequences
\dv : List all views
\dS : List all PostgreSQL-defined tables
\d table-name : Show table definition
\d index-name : Show index definition
\d view-name : Show view definition
\d sequence-name : Show sequence definition
\dp : List all privileges
\dl : List all large objects
\da : List all aggregates
\df : List all functions
\dc : List all conversions
\dC : List all casts
\df function-name : List all functions with given name
\do : List all operators
\do operator-name : List all operators with given name
\dT : List all types
\dD : List all domains
\dg : List all groups
\du : List all users
\l : List all databases in this cluster
FOLLOW THE BELOW QUERY TO GIVE SPECIFIC ACCESS TO THE DATABASE.
ADMINROLE: Grants privileges to execute DDLs and DML on test database.
WRITERROLE: Grants write privileges to perform DMLs on objects within the test database.
READERROLE: Grants read privileges on objects within the test database.
READER
GRANT CONNECT,TEMP on DATABASE <DATABASENAME> to “<ROLENAME-READER>”;
GRANT USAGE on SCHEMA <SCHEMANAME> to "<ROLENAME-READER>";
GRANT SELECT ON ALL TABLES IN SCHEMA <SCHEMANAME> to "<ROLENAME-READER>";
GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA <SCHEMANAME> to "<ROLENAME-READER>";
GRANT EXECUTE on ALL FUNCTIONS IN SCHEMA <SCHEMANAME> to "<ROLENAME-READER>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<ROLENAME-ADMIN>" IN SCHEMA <SCHEMANAME> GRANT SELECT on TABLES to "<ROLENAME-READER>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<ROLENAME-ADMIN>" IN SCHEMA <SCHEMANAME> GRANT USAGE,SELECT on SEQUENCES to "<ROLENAME-READER>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<ROLENAME-ADMIN>" IN SCHEMA <SCHEMANAME> GRANT EXECUTE on FUNCTIONS to "<ROLENAME-READER>";
WRITER
GRANT CONNECT,TEMP on DATABASE <DATABASENAME> to "<ROLENAME-WRITER>";
GRANT USAGE on SCHEMA <SCHEMANAME> to "<ROLENAME-WRITER>";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <SCHEMANAME> to "<ROLENAME-WRITER>";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <SCHEMANAME> to "<ROLENAME-WRITER>";
GRANT ALL PRIVILEGES on all FUNCTIONS IN SCHEMA <SCHEMANAME> to "<ROLENAME-WRITER>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<ROLENAME-ADMIN>" IN SCHEMA <SCHEMANAME> GRANT ALL PRIVILEGES on TABLES to "<ROLENAME-WRITER>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<ROLENAME-ADMIN>" IN SCHEMA <SCHEMANAME> GRANT ALL PRIVILEGES on SEQUENCES to "<ROLENAME-WRITER>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<ROLENAME-ADMIN>" IN SCHEMA <SCHEMANAME> GRANT ALL PRIVILEGES on FUNCTIONS to "<ROLENAME-WRITER>";
ADMIN
GRANT "<ROLENAME-ADMIN>" TO "<OTHERADMINGROUP>" ; //optional
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <SCHEMANAME> to "<ROLENAME-ADMIN>";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <SCHEMANAME> to "<ROLENAME-ADMIN>";
GRANT ALL PRIVILEGES on all FUNCTIONS IN SCHEMA <SCHEMANAME> to "<ROLENAME-ADMIN>";
GRANT ALL on DATABASE <DATABASENAME> to "<ROLENAME-ADMIN>";
GRANT ALL on SCHEMA <SCHEMANAME> to "<ROLENAME-ADMIN>";
Postmaster uptime
select now() - pg_postmaster_start_time () as uptime;
Create a database using tablespace:
Database name: demo
Tablespace name : demo_tbs
Tablespace location: /u02/pgdata02/demo
1) First create the directory where tablespace
Mkdir -p /u02/pgdata02/demo
2) Create the tablespace demo_tbs.
create tablespace demo_tbs location '/u02/pgdata02/demo;
3) Create the database with tablespace demo_tbs
create database demo with tablespace demo_tbs;
Assign tablespace to database
Alter database databasename set tablespace tablespacename;
Disk space occupied by a tablespace:
select pg_size_pretty(pg_tablespace_size('tbs1'));
List of databases
SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;
Schema size:
CREATE OR REPLACE FUNCTION pg_schema_getsize(text) returns bigint AS $$
SELECT sum(pg_relation_size(schemaname || '.' || tablename))::bigint FROM pg_tables WHERE schemaname = $1
$$ LANGUAGE sql;
To execute this:
select pg_size_pretty(pg_schema_getsize('backup_tables'));
Number of tables in database
SELECT nspname AS schemaname,relname as tablename,reltuples as row_count FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r' ORDER BY reltuples DESC;
Query executed over the entire cluster or instance.
SELECT datname as database,pid,usename as username,application_name as application,client_addr as client_address,query FROM pg_stat_activity;
Query to find out user is part of which roles or group.
select rolname from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.member) join pg_roles on (pg_roles.oid=pg_auth_members.roleid) where pg_user.usename=’username';
Query to delete all tables inside schema
DO $$ DECLARE
tabname RECORD;
BEGIN
FOR tabname IN (SELECT tablename
FROM pg_tables
WHERE schemaname = current_schema())
LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(tabname.tablename) || ' CASCADE';
END LOOP;
END $$;
Script to kill all running connections by specifying a database name.
This is special case where you want to kill are the connection established with database. Please review and query before executing on production environment.
Before executing this query, you must REVOKE the CONNECT privileges to avoid new connections:
1) REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
2) SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = 'datbase_name' AND pid <> pg_backend_pid();
Script to kill all running connections of a current database:
1) SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();
Identify the Blocking user:
1) CREATE VIEW blocking_procs AS
SELECT
kl.pid as blocking_pid,
ka.usename as blocking_user,
ka.current_query as blocking_query,
bl.pid as blocked_pid,
a.usename as blocked_user,
a.current_query as blocked_query,
to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.procpid
JOIN pg_catalog.pg_locks kl
ON bl.locktype = kl.locktype
and bl.database is not distinct from kl.database
and bl.relation is not distinct from kl.relation
and bl.page is not distinct from kl.page
and bl.tuple is not distinct from kl.tuple
and bl.virtualxid is not distinct from kl.virtualxid
and bl.transactionid is not distinct from kl.transactionid
and bl.classid is not distinct from kl.classid
and bl.objid is not distinct from kl.objid
and bl.objsubid is not distinct from kl.objsubid
and bl.pid <> kl.pid
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.procpid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;
2) SELECT * FROM blocking_procs;
How to test the query on a test server (not your production DB server)
a) Connect to your database open a transaction and manually lock a table:
BEGIN;
LOCK your_table;
Leave the transaction and connection open.
b) Open another client that accesses that data:
# SELECT count(*) from your_table;
It now should be blocked.
c) View the currently held locks with a third client:
# SELECT * FROM blocking_procs;
blocking_pid | 25842
blocking_user | postgres
blocking_query | in transaction
blocked_pid | 25844
blocked_user | postgres
blocked_query | SELECT COUNT(*) FROM "your_table"
age | 00h:00m:23s
d) It's now possible to kill the offending process holding the lock using:
# SELECT pg_terminate_backend(25842);
This will kill the connection where you've set the lock and the open transaction is rolled back but it seems
to leave everything else intact. The second client should now get the response from the server.
Query state and query timing
1) Select pid, now()- query_start as duration, query,state from pg_stat_activity where (now()- query_start) > interval '5 minutes';
Execute the below query if you want to cancel the long running query.
2) SELECT pg_cancel_backend(__pid__);