Postgresql snippets

Postgresl hints

-- select size of tables and indices in random order
SELECT relname, reltuples, relpages FROM pg_class ;

-- select size of tables and indices in descending order of size
SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC ;

-- select size of tables and indices in descending order of tuple- / recordcount
SELECT relname, reltuples, relpages FROM pg_class ORDER BY reltuples DESC ;

-- Change admin password
su - postgres
psql template1
alter user postgres with password 'postgres_password';

-- Show users
SELECT * FROM "pg_user";

-- change user for all tables
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

--change user for all seqs
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

-- change user for all views
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

-- Copy a database
create database NEWDB with template OLDDB;

-- Change DB owner/name
ALTER DATABASE name RENAME TO newname
ALTER DATABASE name OWNER TO new_owner

-- Showing transaction status in the psql prompt
\set PROMPT1 '%/%R%x%# '
-- from http://sql-info.de/postgresql/notes/transaction-status-in-the-psql-prompt.html

-- Show table colums

SELECT attname FROM pg_attribute, pg_type
WHERE typname = 'table_name'
AND attrelid = typrelid
AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax');

-- change database encoding
update pg_database set encoding=8 where datname=’PRUEBA’;
8: LATIN1

-- check active connections
SELECT datname,procpid,current_query FROM pg_stat_activity
SELECT count(*) as cnt, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC;
SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity ; -- ONLY in 9.2