select table_schema,
table_name
from information_schema.tables
where table_name like 'event_of_interest_attribute%'
and table_schema not in ('information_schema', 'pg_catalog')
and table_type = 'BASE TABLE'
order by table_name,
table_schema;
SELECT * FROM information_schema.tables WHERE table_schema = '<schema_name>' ;
SELECT table_schema||'.'||table_name AS full_rel_name
FROM information_schema.tables
WHERE table_schema = '<schema_name>';
Privilege for user’s in a particular table
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='<table_name>' ;
List Schema details
\c database ;
select s.nspname as table_schema, s.oid as schema_id, u.usename as owner from pg_catalog.pg_namespace s join pg_catalog.pg_user u on u.usesysid = s.nspowner where nspname not in ('information_schema', 'pg_catalog', 'public') and nspname not like 'pg_toast%' and nspname not like 'pg_temp_%' order by table_schema;
select s.nspname as table_schema,
s.oid as schema_id,
u.usename as owner
from pg_catalog.pg_namespace s
join pg_catalog.pg_user u on u.usesysid = s.nspowner
order by table_schema;
List of all tables in a database
\c database ;
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
Drop User
DROP OWNED BY st_datapipeline;
DROP USER st_datapipeline;
Check Number of Connection in use
SELECT count(distinct(numbackends)) FROM pg_stat_database ;
Show Connection Limit
SHOW max_connections
Reloading Config without restart
SELECT pg_reload_config()
Alter Connection Limit of a User
ALTER ROLE jirauser CONNECTION LIMIT -1; -- infinite
ALTER ROLE data_analytics CONNECTION LIMIT 600 ;
Search User
SELECT rolname, rolconnlimit from pg_roles;
SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit <> -1;
How to determine the size of PostgreSQL databases and tables
select t1.datname AS db_name,
pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;
SELECT database_name, pg_size_pretty(size) from (SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname) AS size FROM pg_database ORDER by size DESC) as ordered;
---Databases to which the user cannot connect are sorted as if they were infinite size
SELECT
datname AS DatabaseName
,pg_catalog.pg_get_userbyid(datdba) AS OwnerName
,CASE
WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname))
ELSE 'No Access For You'
END AS DatabaseSize
FROM pg_catalog.pg_database
ORDER BY
CASE
WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
THEN pg_catalog.pg_database_size(datname)
ELSE NULL
END DESC;
Find the Status of Database connections
select state, count(*) from pg_catalog.pg_stat_activity psa where datname = '<database>' group by state;
Show all Blocked Queries
select pid,
datname,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
View Query of Blocking ID
select pid,
datname,
usename,
query
from pg_stat_activity
where pid = '<some_query_id>' ;
Terminate Query that is Blocking
select pg_terminate_backend(pg_stat_activity.pid)
from pg_stat_activity
where pid = '
select pid,
datname,
usename,
query
from pg_stat_activity
where pid = '<some_query_id>' ;
Show the parent pid and query that is causing the blocking and child queries that are being blocked by the parent
SELECT
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
List all the tables and data size of tables in a database
\c database ;
select schemaname as table_schema, relname as table_name, pg_size_pretty(pg_relation_size(relid)) as data_size from pg_catalog.pg_statio_user_tables order by pg_relation_size(relid) desc;
Select count(*) of tables in a database
\c database ;
SELECT schemaname as table_schema, relname as table_name, n_live_tup as row_count FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows from pg_class c join pg_namespace n on n.oid = c.relnamespace where c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog') order by c.reltuples desc;
Find the PostgreSQL Activities
select pid as process_id, usename as username, datname as database_name, client_addr as client_address, application_name, backend_start, state, state_change from pg_stat_activity;
Copy the Output to .csv file
\copy (select * from mobile_settings)To '/tmp/output.csv' With CSV DELIMITER ',' HEADER
psql -d dbname -t -A -F"," -c "select * from users" > output.csv
psql -d my_db_name -t -A -F"," -f input-file.sql -o output-file.csv
$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv
Verify the Master/Slave Replication
MASTER :
select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;
SLAVE:
select pg_is_in_recovery();
select pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp(), CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay;
List table columns in a database
select table_schema,
table_name,
ordinal_position as position,
column_name,
data_type,
case when character_maximum_length is not null
then character_maximum_length
else numeric_precision end as max_length,
is_nullable,
column_default as default_value
from information_schema.columns
where table_schema not in ('information_schema', 'pg_catalog')
order by table_schema,
table_name,
ordinal_position;
List of all column’s in a specific tables
select ordinal_position as position,
column_name,
data_type,
case when character_maximum_length is not null
then character_maximum_length
else numeric_precision end as max_length,
is_nullable,
column_default as default_value
from information_schema.columns
where table_name = 'Table name' -- enter table name here
-- and table_schema= 'Schema name'
order by ordinal_position;
List Views in a database
select table_schema as schema_name,
table_name as view_name
from information_schema.views
where table_schema not in ('information_schema', 'pg_catalog')
order by schema_name,
view_name;
List Views with their Scripts
select table_schema as schema_name,
table_name as view_name,
view_definition
from information_schema.views
where table_schema not in ('information_schema', 'pg_catalog')
order by schema_name,
view_name;
Lists views in a database with their definition
select u.view_schema as schema_name,
u.view_name,
u.table_schema as referenced_table_schema,
u.table_name as referenced_table_name,
v.view_definition
from information_schema.view_table_usage u
join information_schema.views v
on u.view_schema = v.table_schema
and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')
order by u.view_schema,
u.view_name;
Lists all materialized views, with their definition
select schemaname as schema_name,
matviewname as view_name,
matviewowner as owner,
ispopulated as is_populated,
definition
from pg_matviews
order by schema_name,
view_name;
Lists all primary keys constraints (PK) in the database
select kcu.table_schema,
kcu.table_name,
tco.constraint_name,
string_agg(kcu.column_name,', ') as key_columns
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
on kcu.constraint_name = tco.constraint_name
and kcu.constraint_schema = tco.constraint_schema
and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = 'PRIMARY KEY'
group by tco.constraint_name,
kcu.table_schema,
kcu.table_name
order by kcu.table_schema,
kcu.table_name;
Lists tables and their primary key (PK) constraint names
select tab.table_schema,
tab.table_name,
tco.constraint_name,
string_agg(kcu.column_name, ', ') as key_columns
from information_schema.tables tab
left join information_schema.table_constraints tco
on tco.table_schema = tab.table_schema
and tco.table_name = tab.table_name
and tco.constraint_type = 'PRIMARY KEY'
left join information_schema.key_column_usage kcu
on kcu.constraint_name = tco.constraint_name
and kcu.constraint_schema = tco.constraint_schema
and kcu.constraint_name = tco.constraint_name
where tab.table_schema not in ('pg_catalog', 'information_schema')
and tab.table_type = 'BASE TABLE'
group by tab.table_schema,
tab.table_name,
tco.constraint_name
order by tab.table_schema,
tab.table_name
Lists tables in a database without primary keys
select tab.table_schema,
tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema = tco.table_schema
and tab.table_name = tco.table_name
and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in ('pg_catalog', 'information_schema')
and tco.constraint_name is null
order by table_schema,
table_name;
Lists check constraints defined in the database ordered by constraint name
select pgc.conname as constraint_name,
ccu.table_schema as table_schema,
ccu.table_name,
ccu.column_name,
pgc.consrc as definition
from pg_constraint pgc
join pg_namespace nsp on nsp.oid = pgc.connamespace
join pg_class cls on pgc.conrelid = cls.oid
left join information_schema.constraint_column_usage ccu
on pgc.conname = ccu.constraint_name
and nsp.nspname = ccu.constraint_schema
where contype ='c'
order by pgc.conname;
Lists table triggers in a database with their details
select event_object_schema as table_schema,
event_object_table as table_name,
trigger_schema,
trigger_name,
string_agg(event_manipulation, ',') as event,
action_timing as activation,
action_condition as condition,
action_statement as definition
from information_schema.triggers
group by 1,2,3,4,6,7,8
order by table_schema,
table_name;
List stored procedures and information about it in PostgreSQL database
select n.nspname as schema_name,
p.proname as specific_name,
l.lanname as language,
case when l.lanname = 'internal' then p.prosrc
else pg_get_functiondef(p.oid)
end as definition,
pg_get_function_arguments(p.oid) as arguments
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype
where n.nspname not in ('pg_catalog', 'information_schema')
and p.prokind = 'p'
order by schema_name,
specific_name;
Finds tables which names start with specific prefix
select table_schema,
table_name
from information_schema.tables
where table_name like 'payment%'
and table_schema not in ('information_schema', 'pg_catalog')
and table_type = 'BASE TABLE'
order by table_name,
table_schema;
Find total number of tables in current database
select count(*) as tables
from information_schema.tables
where table_type = 'BASE TABLE';
List of users in current database
select usesysid as user_id,
usename as username,
usesuper as is_superuser,
passwd as password_md5,
valuntil as password_expiration
from pg_shadow
order by usename;
List Database Sessions
select pid as process_id,
usename as username,
datname as database_name,
client_addr as client_address,
application_name,
backend_start,
state,
state_change
from pg_stat_activity;
Kill a Session
select pg_terminate_backend(pid)
from pg_stat_activity
where pid = '18765';
Find empty tables in PostgreSQL database
select n.nspname as table_schema,
c.relname as table_name
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname not in ('information_schema','pg_catalog')
and c.reltuples = 0
order by table_schema,
table_name;
List of tables by the size of data and indexes
select schemaname as table_schema,
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
pg_size_pretty(pg_relation_size(relid)) as data_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
pg_relation_size(relid) desc;
table_schema – table’s schema name table_name – table name total_size – Total disk space used by the specified table, including all indexes and TOAST data data_size – Disk space used by specified table or index external_size – Disk space used by realted object to specified table
List table which uses 50% of summary space used by all database tables.
select schemaname as table_schema,
relname as table_name,
pg_size_pretty(pg_relation_size(relid))
from pg_catalog.pg_statio_user_tables
where pg_relation_size(relid) > 0.5 * (
select sum((pg_relation_size(relid)))
from pg_catalog.pg_statio_user_tables);
Backup using pg_dump
Backup one database
pg_dump -U postgres -W -F t dvdrental > c:\pgbackup\dvdrental.tar
Backup all databases
pg_dumpall -U postgres > c:\pgbackup\all.sql
Backup database object definitions
pg_dumpall --schema-only > c:\pgdump\definitiononly.sql
pg_dumpall --roles-only > c:\pgdump\allroles.sql
pg_dumpall --tablespaces-only > c:\pgdump\allroles.sql
To dump a single table named mytab:
$ pg_dump -t mytab mydb > db.sql
To dump all tables whose names start with emp in the detroit schema, except for the table named employee_log:
$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:
$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
