Dump the PostgreSQL configuration of a Nuxeo DB, this script will output a /tmp/pgconf.txt file, you can run it using psql using the Nuxeo db account.

dump-nuxeo-postgres-config.sql

\o /tmp/pgconf.txt
\timing
SELECT now(), Version();
SELECT current_database() AS db_name,  pg_size_pretty(pg_database_size(current_database())) AS db_size, pg_size_pretty(SUM(pg_relation_size(indexrelid))::int8) AS index_size FROM pg_index;

-- pg buffer cache first so they are not modified by stats
SELECT (SELECT pg_size_pretty(COUNT(*) * 8192) FROM  pg_buffercache) AS buffer_size,
  pg_size_pretty(count(*) * 8192) as buffered FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
WHERE pg_relation_size(c.oid) != 0;
SELECT c.relname, pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) /
    (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent, round(100.0 * count(*) * 8192 / coalesce(pg_relation_size(c.oid),1), 1)
AS percent_of_relation FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
WHERE pg_relation_size(c.oid) != 0 GROUP BY c.oid,c.relname ORDER BY 3 DESC LIMIT 20;
SELECT SUM(pgClass.reltuples) AS totalRowCount FROM pg_class pgClass LEFT JOIN pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND pgClass.relkind='r';
-- Nuxeo specific part
SELECT COUNT(*) AS documents_count FROM hierarchy WHERE NOT isproperty;
SELECT primarytype, COUNT(*) AS count FROM hierarchy WHERE NOT isproperty GROUP BY primarytype ORDER BY count DESC;
SELECT COUNT(*) AS hierarchy_count FROM hierarchy;
SELECT COUNT(*) AS aces_count FROM acls;
SELECT COUNT(DISTINCT(id)) AS acls_count FROM acls;
SELECT count(1) AS ACLS_COUNT, primarytype FROM hierarchy WHERE id IN (SELECT DISTINCT(id) FROM acls) GROUP BY primarytype ORDER BY 1 DESC;
SELECT COUNT(*) AS read_acls_count FROM aclr;
SELECT LENGTH(acl) AS aclrmax_size, acl FROM aclr WHERE LENGTH(acl) = (SELECT MAX(LENGTH(acl)) FROM aclr);
SELECT COUNT(1) AS aclr_user_size FROM aclr_user;
SELECT COUNT(1) AS aclr_user_map_size FROM aclr_user_map;
SELECT COUNT(1) AS aclr_count, u."users" AS principal FROM aclr_user_map um LEFT JOIN aclr_user u ON um.user_id = u.user_id GROUP BY u."users" ORDER BY 1 DESC LIMIT 50;
SELECT (SELECT COUNT(*) FROM users) AS users, (SELECT COUNT(*) FROM user2group) AS user2groups,
    (SELECT COUNT(*) FROM groups) AS group,  (SELECT COUNT(*) FROM group2group) AS group2group;
SELECT COUNT(1), log_event_category FROM nxp_logs GROUP BY log_event_category ORDER BY 1 DESC;
\d+ fulltext
-- End of nuxeo specific part
SELECT stat.relname AS "Table",
    pg_size_pretty(pg_total_relation_size(stat.relid)) AS "Total size",
    pg_size_pretty(pg_relation_size(stat.relid)) AS "Table size",
    CASE WHEN cl.reltoastrelid = 0 THEN 'None' ELSE
        pg_size_pretty(pg_relation_size(cl.reltoastrelid)+
        COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)) END AS "TOAST table size",
    pg_size_pretty(COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0)) AS "Index size",
    CASE WHEN pg_relation_size(stat.relid) = 0 THEN 0.0 ELSE
    round(100 * COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0) /  pg_relation_size(stat.relid)) / 100 END AS "Index ratio"
FROM pg_stat_all_tables stat
  JOIN pg_statio_all_tables statio ON stat.relid = statio.relid
  JOIN pg_class cl ON cl.oid=stat.relid AND stat.schemaname='public'
ORDER BY pg_total_relation_size(stat.relid) DESC
LIMIT 20;
SELECT nspname,relname,
    round(100 * pg_relation_size(indexrelid) / pg_relation_size(indrelid)) / 100 AS index_ratio, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, pg_size_pretty(pg_relation_size(indrelid)) AS table_size
FROM pg_index I
LEFT JOIN pg_class C ON (C.oid = I.indexrelid)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND C.relkind='i' AND pg_relation_size(indrelid) > 0  
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 15;
SELECT relname, idx_tup_fetch + seq_tup_read AS total_reads
FROM pg_stat_all_tables WHERE idx_tup_fetch + seq_tup_read != 0
ORDER BY total_reads desc LIMIT 15;
-- indexes
SELECT relname, seq_scan, n_live_tup AS rows, idx_scan, last_autovacuum, last_autoanalyze  FROM pg_stat_user_tables ORDER BY seq_scan * n_live_tup DESC LIMIT 40;
SELECT
 schemaname as nspname,
 relname,
 indexrelname AS useless_indexrelname,
 idx_scan,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
 pg_stat_user_indexes i
 JOIN pg_index USING (indexrelid)
WHERE
 indisunique IS false
ORDER BY idx_scan,pg_relation_size(i.indexrelid) DESC LIMIT 10;
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM
  pg_statio_user_tables;
SELECT
  relname,
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
  n_live_tup rows_in_table
FROM
  pg_stat_user_tables
ORDER BY
  n_live_tup DESC LIMIT 50;
SELECT
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
  pg_statio_user_indexes;
\di+
-- Speed test
SELECT sum(generate_series) AS "speedTest" FROM generate_series(1,1000000);
EXPLAIN ANALYZE SELECT sum(generate_series) AS "speedTest" FROM generate_series(1,1000000);
-- Running queries
SELECT now() - query_start AS duration, query FROM pg_stat_activity
  WHERE query != '<IDLE>' ORDER BY duration DESC;
-- XA
SELECT database, gid AS prepared_xacts_gid FROM pg_prepared_xacts;
-- all activity
SELECT usename, count(1) AS count from pg_catalog.pg_stat_activity group by usename;
SELECT * FROM pg_catalog.pg_stat_activity;
-- locks
SELECT relation::regclass, * FROM pg_locks WHERE NOT granted;
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.query AS blocking_statement, now() - ka.query_start AS blocking_duration,
    kl.pid as blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement, now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
    JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid
    JOIN pg_catalog.pg_locks kl
    JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid  ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted;

-- bgwriter stats
SELECT * FROM pg_stat_bgwriter;

-- functions stats
SELECT funcname, calls, total_time, self_time, self_time/calls AS elapsed_ms FROM pg_stat_user_functions ORDER BY total_time DESC;

-- config
SELECT name, unit, current_setting(name), source FROM pg_settings WHERE source!='default';
SHOW ALL;
-- ordering
SELECT regexp_split_to_table('D d a A c b é e z à 1', ' ') ORDER BY 1;

-- Stats if available
SELECT count(1) AS uniq_queries, round(sum(total_time)*1000)/1000 AS sum_total_time, sum(calls) AS sum_calls, round(sum(total_time)/sum(calls)*1000)/1000 AS avg FROM pg_stat_statements;
-- Queries that took the most time (N)
SELECT round(total_time*1000)/1000 AS total_time, calls, round(total_time/calls*1000)/1000 AS avg, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 50;
-- Most frequent queries (N)
SELECT round(total_time*1000)/1000 AS total_time, calls, round(total_time/calls*1000)/1000 AS avg, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 50;
-- Slowest queries (N)
SELECT round(total_time*1000)/1000 AS total_time, calls, round(total_time/calls*1000)/1000 AS avg, query FROM pg_stat_statements ORDER BY total_time/calls DESC LIMIT 50;
\q