CODEWITHSUNDEEP
databasepostgresql
Jim Lesinski
Jim Lesinski

Reputation: 43

How to list all databases and their schemas in postgresql?

I am looking for a way to list all databases and all schemas on an OpenSuse postgresql server. I know that I can use the meta commands such as \l+ to list all of the databases, but is there any way to list all information for all databases including databases, schema and size information?

I'd like a way to look at everything overall.

Thanks!

Upvotes: 4

Views: 6481

Answers (2)

potatosalad
potatosalad

Reputation: 4887

The following query will provide the same output as \l+ in psql as of PostgreSQL 9.4.1:

SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  pg_catalog.array_to_string(d.datacl, E'\n') as "Access privileges",
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE 'No Access'
  END as "Size",
  t.spcname as "Tablespace",
  pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM
  pg_catalog.pg_database d
JOIN
  pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

Here is the source: src/bin/psql/describe.c

Upvotes: 4

Houari
Houari

Reputation: 5651

I don't see a way to get the list of databases's schemas without connecting to it first. Based on this, i think you can get it using db_link with something like:

CREATE OR replace FUNCTION databases_schemas_size 
  () returns setof RECORD 
AS 
  $body$ 

  DECLARE 

    connection_info CHARACTER varying; 
    i RECORD; 
    r RECORD; 

  BEGIN 

    FOR r IN SELECT cast(datname AS CHARACTER varying) FROM  pg_database  WHERE  datistemplate = FALSE LOOP 

    SELECT format ('dbname=%s user=%s password=%s', r.datname, 'YOUR_ALLOWED_USERNAME', 'PASSWORD') INTO   connection_info; 

    perform dblink_connect(connection_info); 
    FOR i IN SELECT r.datname, * 
    FROM   dblink('SELECT nspname , pg_size_pretty(sum(pg_relation_size(C.oid))) AS size FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (''pg_catalog'', ''information_schema'') group by nspname') 
    AS t(schema_name CHARACTER varying, schema_size text) 

    LOOP 
    RETURN NEXT i; 
    END LOOP; 

  perform dblink_disconnect(); 
END LOOP; 

RETURN; 

END; 
$body$ LANGUAGE plpgsql volatile cost 100; 


SELECT * FROM  databases_schemas_size() 
AS (datname CHARACTER varying, schema_name CHARACTER varying, schema_size text);

You can improve it to not hard code the login/password by using the FOREIGN DATA WRAPPER functionality

Upvotes: 2

Related Questions