postgres system dictionary - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/catalogs-overview.html
• https://pgpedia.info/version-charts/system-catalogue-tables.html
• https://pgpedia.info/p/pg_init_privs.html - lista slownikow/tabel systemowych
• https://github.com/ghdrako/doc_snipets/wiki/postgres-system-catalog

Note: In PostgreSQL, A new database is initialized with a pre-defined set of schemas and those are: public: A default Schema; Tables will get created in public schema by default if the schema detail is not provided during the table creation.

• information_schema: Contains information about the database objects defined in the current database
• pg_catalog: Stores the metadata information about the database and cluster
• pg_toast: Hold TOAST storage for large tables.

List databases

select oid as database_id,
       datname as database_name,
       datallowconn as allow_connect,
       datconnlimit as connection_limit
from pg_database
order by oid;

Namespace

SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;

Database size

SELECT sum(pg_database_size(datname)) from pg_database;

List tables

SELECT * FROM pg_catalog.pg_tables;
SELECT 'alter table '||tablename||' rename to bak_'||tablename||';' FROM pg_catalog.pg_tables where schemaname = 'ebk' and tablename like 'imp%' order by tablename ;
SELECT 'drop table if exists '||tablename||' cascade;' FROM pg_catalog.pg_tables where schemaname = 'ebk' and tablename like 'bak_imp%' order by tablename ;
SELECT 'truncate table '||tablename||';' FROM pg_catalog.pg_tables where schemaname = 'aci_tmp_db' and tablename like 'e%' order by tablename ;

List tables without PK

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;

Estimate rows in table

CREATE OR REPLACE FUNCTION estimated_row_count(text)
RETURNS bigint
LANGUAGE sql
AS $$
SELECT (CASE WHEN reltuples > 0 THEN
               pg_relation_size($1)*reltuples/(8192*relpages)
             ELSE 0
             END)::bigint
FROM pg_class
WHERE oid = $1::regclass;
$$;

select estimated_row_count('myschema.mytable');

SSL

select datname, usename, ssl, client_addr from pg_stat_ssl

List installed extensions

SELECT * FROM pg_extension;

List users

SELECT usename AS role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;