[ About | Licence | Contacts ]
Written by Oleksandr Gavenko (AKA gavenkoa), compiled on 2020-02-13 from rev d88d5d1a8cd9+.

Postgres

Installing on Debian

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
https://wiki.debian.org/PostgreSql
Debian wiki instructions.

Check version

By SQL query:

SELECT version();
SHOW server_version;
SHOW server_version_num;

From executable:

$ postgres -V

Check locations

SHOW hba_file;
SHOW config_file;

Check settings

SELECT name, setting FROM pg_settings;
SELECT name, current_setting(name), source FROM pg_settings;

Check extensions

SELECT * FROM pg_available_extensions;

Set application name

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;

Limitations

https://wiki.postgresql.org/wiki/TOAST
TOAST is a mechanism PostgreSQL uses to keep physical data rows from exceeding the size of a data block (typically 8KB).

List privileges

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;

Grant role options

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;
https://www.postgresql.org/docs/current/sql-createrole.html
CREATE ROLE.

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;

Revoke privileges and drop role

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;

Change password

ALTER USER me WITH PASSWORD 'PassWord';

Monitor connections

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;
https://www.postgresql.org/docs/current/monitoring-stats.html
The Statistics Collector.

Profile queries

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;
https://www.postgresql.org/docs/current/pgstatstatements.html
pg_stat_statements.
https://www.postgresql.org/docs/current/indexes-examine.html
Examining Index Usage.

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;
https://www.postgresql.org/docs/11/sql-explain.html
EXPLAIN.

List databases, schemas and tables

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
https://www.postgresql.org/docs/current/catalog-pg-constraint.html
pg_constraint.
https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys
List table foreign keys.

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();
https://www.postgresql.org/docs/current/functions-info.html
System Information Functions.

Set default schema.

set search_path to NAME;
set schema 'NAME';

Database, table, index size

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+

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 other):

SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));

SELECT pg_relation_size(t.schemaname ||'.'|| t.tablename), t.* FROM pg_tables t;

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;

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;

Size of types:

SELECT pg_column_size(5::smallint);
SELECT pg_column_size(5::int);
SELECT pg_column_size(5::bigint);

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;
https://www.postgresql.org/docs/9.6/functions-admin.html
Database Object Management Functions.
https://wiki.postgresql.org/wiki/Disk_Usage
Finding the size of various object in your database.
https://wiki.postgresql.org/wiki/Index_Maintenance
Index size/usage statistics.

MVCC and transaction id

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;

Vacuum

Stats on vacuum oprtations per table:

select * from pg_stat_user_tables;

Table statistics

Full table statistics is in pg_statistics table which is accessible only by superuser.

Human readable stats are in pg_stats view.

Using psql client

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

Export data

Export from psql:

\copy (SELECT * FROM foo) TO '/tmp/my.csv' WITH CSV

Import data

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"

JDBC driver

https://jdbc.postgresql.org/about/about.html
The current version of the driver should be compatible with PostgreSQL 8.2 and higher, and Java 6 (JDBC 4.0), Java 7 (JDBC 4.1), Java 8 (JDBC 4.2) and Java 9.
https://jdbc.postgresql.org/download.html
Java/JDBC/Postgres compatibility table.

plpgsql

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.

https://www.postgresql.org/docs/current/sql-createfunction.html
CREATE FUNCTION.
https://www.postgresql.org/docs/current/plpgsql-control-structures.html
plpgsql control structures.
https://www.postgresql.org/docs/current/errcodes-appendix.html
PostgreSQL Error Codes.

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$$;