pg_snippets.sql

-- postgres snippets

-- show databases owned by a specific user
psql -d postgres -c "SELECT datname FROM pg_database JOIN pg_authid ON pg_database.datdba = pg_authid.oid WHERE rolname = 'rolename'"

-- see all active sessions
SELECT * FROM pg_stat_activity;

-- kill sessions
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'dbname' AND pid <> pg_backend_pid();

-- Change the database ownership
alter database dbname owner to dbuser;

-- and do the same for all tables
psql -tc "select 'alter table ' || tablename || ' owner to dbuser;' from pg_tables where schemaname not in ('pg_catalog', 'information_schema');" dbname | psql -a dbname