Install and create new user and database:
$ sudo apt-get install postgresql postgresql-client $ sudo su - postgres % psql postgres=# CREATE USER myuser WITH PASSWORD 'mypass'; postgres=# CREATE DATABASE mydb OWNER myuser; postgres=# \q
Connect as user mypguser to new database:
$ su - mypguser $ psql mypgdatabase
In order to create local host superuser:
$ sudo su - postgres $ createuser --superuser USER $ exit $ sudo -u USER psql
By SQL query:
SELECT version(); SHOW server_version; SHOW server_version_num;
From executable:
$ postgres -V
SHOW hba_file; SHOW config_file;
SELECT name, setting FROM pg_settings; SELECT name, current_setting(name), source FROM pg_settings;
SELECT * FROM pg_available_extensions;
application_name is displayed in pg_stat_activity.application_name.
It can help identify clients of DB. To alter it in current session:
SET application_name = 'some name';
To show current value:
SHOW application_name;
List databases with privileges:
\l
List schemas with privileges:
\dn+
List users with privileges:
\du
List privileges to objects (tables, views, sequences):
\dp SELECT * FROM information_schema.role_table_grants WHERE table_name = 'table_name';
List default privileges:
\ddp SELECT * FROM pg_default_acl;
Show role options and group membership:
\du
Grant role options:
ALTER USER me WITH SUPERUSER; ALTER USER me WITH CREATEDB CREATEROLE LOGIN;
Revoke role options:
ALTER USER me WITH NOSUPERUSER;
Granting selects:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rw_user; GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO rw_user;
Making read-only role:
GRANT CONNECT ON DATABASE mydb TO ro; GRANT USAGE ON SCHEMA myschema TO ro; GRANT SELECT ON TABLE myschema.myt1, myschema.myt2 TO ro;
Granting default privileges for new objects:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON SEQUENCES TO rw_user; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT EXECUTE ON FUNCTIONS TO rw_user;
List roles:
SELECT * FROM pg_roles;
SELECT oid, rolname FROM pg_roles
WHERE pg_has_role('xuser', oid, 'member');
Revoke privileges:
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM xuser;
REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM xuser;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM xuser;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM xuser;
do $$
DECLARE r record;
begin
for r in select * from pg_views where schemaname = 'myschem'
loop
execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| quote_ident(r.viewname) || ' from "xuser"';
end loop;
end $$;
To forbid any operations (read/write) without dealing with individual privileges (any pending user transactions will fail!):
REVOKE USAGE ON SCHEMA myschem FROM xuser;
To forbid connection remove privilege from public role as it inherited by all users and then from user:
REVOKE CONNECT ON DATABASE mydb FROM public; REVOKE CONNECT ON DATABASE mydb FROM xuser;
Role can't be dropped until it owns objects, need to reassign owner:
REASSIGN OWNED BY xuser TO myuser;
Some objects may left behind role (like default privileges), they can be dropped via:
DROP OWNED BY xuser;
Remove role:
DROP ROLE xuser;
ALTER USER me WITH PASSWORD 'PassWord';
Current user:
SELECT current_user; SELECT session_user;
Active sessions and queries:
select * from pg_stat_activity; select * from pg_stat_activity order by usename;
Stats on query execution time:
SELECT * FROM pg_stat_statements; SELECT * FROM pg_stat_statements ORDER BY calls DESC; SELECT * FROM pg_stat_statements WHERE query LIKE '%some%' ORDER BY calls DESC;
Reveal execution details:
EXPLAIN ANALYSE SELECT 1; EXPLAIN (TIMING false, COSTS false, ANALYSE) SELECT 1; EXPLAIN (TIMING false, COSTS false, BUFFERS false, VERBOSE false, ANALYSE) SELECT 1;
EXPLAIN ANALYSE actually executes a query, if it is destructive enclose the query into a transaction:
BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
Default database is postgres.
To list databases and database locales:
$ psql -U pgadmin -l
or:
=> SELECT datname FROM pg_database WHERE datistemplate = false; => \l
To switch databases:
=> \connect NAME
Schemas:
\dn * select schema_name from information_schema.schemata; select nspname from pg_catalog.pg_namespace;
To list all tables in the current database:
\dt SELECT table_schema, table_name FROM information_schema.tables ORDER BY table_schema, table_name; SELECT * FROM pg_class;
or in all schemas:
=> \dt *.*
List views:
\dv
List users:
\du \du+
List schemas:
\dn
List indexes:
\di select * from pg_indexes; select * from pg_indexes where schemaname = '...'; select * from pg_indexes where schemaname = '...' and tablename = '...'; select * from pg_indexes where schemaname = '...' and indexname = '...'; select t.relname as table_name, i.relname as index_name, a.attname as column_name from pg_class t, pg_class i, pg_index ix, pg_attribute a where t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and t.relkind = 'r' and t.relname like 'test%' order by t.relname, i.relname; select t.relname as table_name, i.relname as index_name, array_to_string(array_agg(a.attname), ', ') as column_names from pg_class t, pg_class i, pg_index ix, pg_attribute a where t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and t.relkind = 'r' and t.relname like 'test%' group by t.relname, i.relname order by t.relname, i.relname;
List index usage:
SELECT * FROM pg_stat_all_indexes; SELECT * FROM pg_stat_all_tables; SELECT * FROM pg_stat_user_indexes; SELECT * FROM pg_stat_user_tables;
List constraints:
SELECT * FROM pg_constraint; SELECT * FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'tblName'); select * from pg_constraint where conname like '%conName%';
Show constraints and columns to given table:
select
c.constraint_name,
x.table_schema as schema_name, x.table_name, x.column_name,
y.table_schema as foreign_schema_name, y.table_name as foreign_table_name, y.column_name as foreign_column_name
from information_schema.referential_constraints c
join information_schema.key_column_usage x
on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
on y.ordinal_position = x.position_in_unique_constraint
and y.constraint_name = c.unique_constraint_name
where y.table_name = 'sa_pnr'
order by c.constraint_name, x.ordinal_position
List functions:
\df
List triggers:
\dy
List misc info:
select current_database(); select current_schema(); select current_user; select session_user; select current_date; select version();
set search_path to NAME; set schema 'NAME';
Database size:
SELECT pg_database_size('geekdb'); -- in bytes
SELECT pg_size_pretty(pg_database_size('dbname'));
SELECT datname, pg_database_size(datname) FROM pg_database;
List of databases sizes:
\l+
SELECT pg_database_size('mydb');
List tables sizes (and constraints):
\d+
All tables row count:
SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname = 'public' ORDER BY n_live_tup DESC;
Table total size (with indexes):
SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));
Table size with TOAST (without indexes):
SELECT pg_table_size('schemaname.tablename');
Sole table size (without indexes and TOAST):
SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));
SELECT pg_relation_size(t.schemaname ||'.'|| t.tablename), t.* FROM pg_tables t ORDER BY 1 DESC;
Largest table in the PostgreSQL database:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; SELECT relname, pg_size_pretty(pg_relation_size(p.oid)) FROM pg_class ORDER BY relpages DESC; SELECT pg_table_size(t.schemaname ||'.'|| t.tablename), pg_relation_size(t.schemaname ||'.'|| t.tablename), t.* FROM pg_tables t ORDER BY 1 DESC;
Largest tables with TOAST:
SELECT p.relname, t.relname, pg_size_pretty(pg_relation_size(p.oid)), p.relpages FROM pg_class p LEFT JOIN pg_class t ON t.reltoastrelid = p.oid ORDER BY p.relpages DESC;
Largest TOAST:
select tbl.relname, toast.relname, toast.relpages from pg_class tbl, pg_class toast where tbl.reltoastrelid = toast.oid order by toast.relpages desc;
Index size:
SELECT pg_indexes_size('my_ix');
Largest index:
select pg_relation_size(oid), * from pg_class where relkind = 'i' order by pg_relation_size(oid) desc; select pg_relation_size(i.oid), t.relname as tname, i.relname as iname, * from pg_class i join pg_index ix on ix.indexrelid = i.oid join pg_class t on t.oid = ix.indrelid where i.relkind = 'i' order by pg_relation_size(i.oid) desc;
Size of types:
SELECT pg_column_size(5::smallint); SELECT pg_column_size(5::int); SELECT pg_column_size(5::bigint);
Size of column:
SELECT sum(pg_column_size(col)) FROM tbl;
Total of all pg_column_size is less then pg_relation_size because table is using pages to store rows and we might have gaps inside pages.
As being MVCC Postgres doesn't delete or update existing rows. Row visibility is controled by transaction id (monotonous incremented number): xmin - first transaction that can see row, xmax - last transaction that can see row:
SELECT xmin, xmax, * FROM foo WHERE id < 5 ORDER BY id;
Full table statistics is in pg_statistics table which is accessible only by superuser.
Human readable stats are in pg_stats view.
Using password file ~/.pgpass:
# comment hostname:port:database:username:password hostname:port:*:username:password hostname:*:*:username:password
Connect by:
$ psql -U $USER -h $HOST -p $PORT $SCHEMA
Switch to long lines format:
\x
How to view execution plan:
EXPLAIN query; EXPLAIN ANALYZE query;
Collect statistics:
ANALYZE table;
How to redirect the output of query to a file:
\o output_file SELECT * FROM pg_class;
Switch database:
\connect DBNAME \c DBNAME
Load external SQL file:
\i my.sql
Execute external file:
psql -f my.sql
Import CSV file:
\copy tbl_name from 'my.csv' csv; \copy tbl_name from 'my.csv' delimiter ':' csv; $ psql -U $USER -h $HOST $DB -c "\\copy tbl_name from 'my.csv' csv"
Create table with data copying using another table (no constraints & indices are recreated, including PK):
CREATE TABLE mycopy AS TABLE myorig;
Create table without data copying using another table:
CREATE TABLE mycopy AS TABLE myorig WITH NO DATA;
Create table from select and copy data (no constraints & indices are recreated, including PK):
CREATE TABLE mycopy AS (SELECT * FROM myorig);
Create table from select (without copying data):
CREATE TABLE mycopy AS (SELECT * FROM myorig) WITH NO DATA;
Simple function:
CREATE OR REPLACE FUNCTION incx(x INTEGER)
RETURNS INTEGER STABLE AS $$
BEGIN
RETURN x + 1;
END;
$$ LANGUAGE plpgsql;
Support for procedures is added only in Postgres v11.
Anonymous block:
do $$
begin
null;
end $$;
You cannot perform select without assigning resultset, special keyword perform is used insted of select in PLPQGSL:
do $$
begin
perform 1 union all select 2;
end$$;
Standard SQL syntax:
SELECT 1 AS num, 'a' AS str UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c';
Postgre extension:
SELECT num, str FROM ( VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS tbl(num, str); WITH tbl(num, str) AS (VALUES (1, 'a'), (2, 'b'), (3, 'c')) SELECT num, str FROM tbl;
To generate table:
SELECT val FROM generate_series(1, 10) val;
WITH t AS (SELECT val FROM generate_series(1, 10) val)
SELECT * FROM t;
WITH
seq1(num) AS (VALUES (1), (2), (3)),
seq2(str) AS (VALUES ('a'), ('b'), ('c'))
SELECT * FROM seq1
CROSS JOIN seq2;