PostgreSQL: The World’s Most Advanced Open Source Relational Database.

Installation Link to heading

TODO

Commands Link to heading

Command Description
\l Display database
\c Connect to database
\dn List schemas
\dt List tables
\dt List tables with sizes
SHOW search_path; Show the current search puth (schema)
SET search_path TO myschema; Change a defauls search path

Useful scripts Link to heading

Queries Link to heading

Show running queries (9.2+) Link to heading 

1SELECT pid, age(clock_timestamp(), query_start), usename, query 
2FROM pg_stat_activity 
3WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
4ORDER BY query_start desc;

Kill running query Link to heading 

1SELECT pg_cancel_backend(procpid);

Kill idle query Link to heading 

1SELECT pg_terminate_backend(procpid);

Vacuum command Link to heading 

1VACUUM (VERBOSE, ANALYZE);

Show long running queries Link to heading 

1-- Example: show long queries more than 2 minutes
2SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
3  FROM  pg_stat_activity
4  WHERE now() - query_start > '2 minutes'::interval
5 ORDER BY runtime DESC;

Import/Export CSV Link to heading

Export to CSV file Link to heading 

1COPY persons(first_name,last_name,email) TO './persons_partial_db.csv' DELIMITER ',' CSV HEADER;

1\COPY (SELECT * FROM persons) TO './persons_partial_db.csv' CSV HEADER;

Import from CSV file Link to heading 

1COPY persons(first_name, last_name, dob, email) FROM './persons.csv' DELIMITER ',' CSV HEADER;

1\COPY <table name> FROM './persons.csv' DELIMITER ',' CSV HEADER;

Backup/Restore Link to heading

Dump database on remote host to file Link to heading 

1pg_dump -U username -h hostname databasename > dump.sql

Import dump into existing database Link to heading 

1psql -d newdb -f dump.sql

Schema Link to heading

Show size of all schemas Link to heading 

1SELECT schema_name, 
2    pg_size_pretty(SUM(table_size)::BIGINT) AS size,
3    CONCAT(CAST(TRUNC((SUM(table_size) / pg_database_size(CURRENT_DATABASE())) * 100, 2) AS TEXT), '%') AS percent
4FROM (SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_relation_size(pg_catalog.pg_class.oid) as table_size
5    FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid) t
6GROUP BY schema_name
7ORDER BY schema_name;

Show all databases and their sizes Link to heading 

1SELECT * FROM pg_user;

Show all tables and their size, with/without indexes Link to heading 

1SELECT datname, pg_size_pretty(pg_database_size(datname))
2FROM pg_database
3ORDER BY pg_database_size(datname) DESC;

Show all database users Link to heading 

1SELECT * FROM pg_stat_activity WHERE current_query NOT LIKE '<%';

Security Link to heading

TODO

Links Link to heading