Skip to content

Instantly share code, notes, and snippets.

@luisuribe
Created May 24, 2010 04:35
Show Gist options
  • Save luisuribe/411530 to your computer and use it in GitHub Desktop.
Save luisuribe/411530 to your computer and use it in GitHub Desktop.
Postgresql snippets
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment