+
−.. -*- coding: utf-8; -*-
+
−
+
−==========
+
− Postgres
+
−==========
+
−.. contents::
+
−   :local:
+
−
+
−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;
+
−
+
−Check extensions
+
−================
+
−::
+
−
+
−   SELECT * FROM pg_available_extensions;
+
−
+
−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).
+
−
+
−Grant role options
+
−==================
+
−::
+
−
+
−   ALTER USER me WITH SUPERUSER;
+
−   ALTER USER me WITH CREATEDB CREATEROLE LOGIN;
+
−
+
−https://www.postgresql.org/docs/current/sql-createrole.html
+
−  ``CREATE ROLE``.
+
−
+
−Change password
+
−===============
+
−::
+
−
+
−   ALTER USER me WITH PASSWORD 'PassWord';
+
−
+
−Monitor connections
+
−===================
+
−::
+
−
+
−   select * from pg_stat_activity;
+
−
+
−https://www.postgresql.org/docs/current/monitoring-stats.html
+
−  The Statistics Collector.
+
−
+
−Profile queries
+
−===============
+
−
+
−Stats on query execution time::
+
−
+
−  SELECT * FROM pg_stat_statements;
+
−
+
−https://www.postgresql.org/docs/current/pgstatstatements.html
+
−  ``pg_stat_statements``.
+
−https://www.postgresql.org/docs/current/indexes-examine.html
+
−  Examining Index Usage.
+
−
+
−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;
+
−
+
−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%';
+
−
+
−https://www.postgresql.org/docs/current/catalog-pg-constraint.html
+
−  pg_constraint.
+
−
+
−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+
+
−
+
−List tables sizes::
+
−
+
−  \d+
+
−
+
−Table total size (with indexes)::
+
−
+
−  SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));
+
−
+
−Sole table size (without indexes and other)::
+
−
+
−  SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));
+
−
+
−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;
+
−
+
−Size of types::
+
−
+
−  SELECT pg_column_size(5::smallint);
+
−  SELECT pg_column_size(5::int);
+
−  SELECT pg_column_size(5::bigint);
+
−
+
−https://wiki.postgresql.org/wiki/Disk_Usage
+
−   Finding the size of various object in your database.
+
−
+
−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;
+
−
+
−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  $SCHEMA
+
−
+
−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
+
−
+
−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.