http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/index.html
lost of processes:
SELECT * FROM pg_stat_activity;
select * from pg_database_size('jiradb2');
select * from pg_catalog.pg_trigger;
или по таблицам
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
Drop all sessions to the DB;
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'jira859uat' AND pid <> pg_backend_pid();
pg_dump
pg_dump -F c -Z 9 -h localhost -U jiratestdbuser jiratestdb -f jiratestdb_2016-02-02.dump
pg_restore -d jiradb jiradb_installed.dump
$ pg_dump mydb > db.sql
psql -d newdb -f db.sql
Triggers:
\i name_of_file - load sql file
\d table name - show triggers for table
\df+ func name - show function source
Hot Standby
https://habrahabr.ru/post/213409/
Tables size:
SELECT *, pg_size_pretty(total_bytes) AS total , pg_size_pretty(index_bytes) AS INDEX , pg_size_pretty(toast_bytes) AS toast , pg_size_pretty(table_bytes) AS TABLE FROM ( SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM ( SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME , c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' ) a ) a;
DB Cleanup:
SELECT 'DROP TABLE "' || c.relname || '" CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid);
select pg_terminate_backend(122316);
Update config on the fly
show idle_in_transaction_session_timeout;
ALTER SYSTEM SET idle_in_transaction_session_timeout TO '30s';
SELECT pg_reload_conf();
SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
schemaname = 'public' and indexname like '%action_actionlevel%'
ORDER BY
tablename,
indexname;
DB Sizes:
SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner,
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
FROM pg_catalog.pg_database d
order by
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END desc -- nulls first
LIMIT 20;
PSQL
\dn - List schemas
\dt schema1. - List tables inside particular schemas