postgres

https://cheatography.com/tme520/cheat-sheets/postgresql/

Fundamentals

View PostgreSQL server versionpostgres=# SELECT version();

View PostgreSQL client versionpsql -V

Check if Postgres is running/etc/init.d/postgresql status

Start/Stop/Restart Postgres (SysVinit)/etc/init.d/postgresql start | stop | restart

Start/Stop/Restart Postgres (Systemd)systemctl start | stop | restart postgresql.service

Enter our queries in our favorite editorpostgres=# \e

Change PostgreSQL root passwordpsql postgres postgres

postgres=# ALTER ROLE postgres WITH PASSWORD 'toto';

postgres=# \q

List available functionspostgres=# \df

View Helppostgres=# \?

postgres=# \h CREATE

postgres=# \h CREATE INDEX

List DBspostgres=# \l

Create a DB from Postgrespostgres=# CREATE DATABASE maDB WITH OWNER akiko;

Create a DB from the Shellpostgres@debianVM:~$ /usr/bin/createdb maDB -O midori

Delete a database from Postgrespostgres=# DROP DATABASE maDB;

Delete a database from the Shellpostgres@debianVM:~$ dropdb maDB

List the tables of a DBpostgres-# \c postgres

postgres-# \d

Get a list of data types available in PostgreSQLpostgres=# SELECT typname,typlen from pg_type where typtype='b';

Redirect query results to a filepostgres=# \o nom_fichier (enables redirection)

postgres=# \o (cancels redirect)

Backups

Back up a single databasepg_dump -U user nom_DB -f madb.sql

Back up all databasespg_dumpall > all.sql

Back up global objects (users + tablespaces)pg_dumpall -g > everything.sql

Back up a tablepg_dump --table articles -U midori nom_DB -f unetable.sql

Restore a databasepsql -U midori -d nom_DB -f madb.sql

Restore all databasespsql -f all.sql

Restore a tablepsql -f a table.sql nom_DB

Back up a local database and restore it to a remote server in one linepg_dump nom_DB_source | psql -h server nom_DB_cible

Index

See the indexes of a tablepostgres=# \d nom_table

Creating an index on a tableCREATE INDEX name ON table USING type_index (column);

Postgres supports 5 types of indexes: Balanced-Tree (btree; used by default), hash (unlogged transactions, deprecated), Generalized Search Tree (gist), Generalized Inverted Indexes (gin) and Space-Partitioned GIST (spgist).

Investigate, analyze

View the history filecat ~/.psql_history

Enable/disable timingpostgres=# \timing

Get execution details of a query without running itpostgres=# EXPLAIN SELECT typname,typlen from pg_type where typtype='b';

Get statistics for a query that just turnedpostgres=# EXPLAIN ANALYSE SELECT typname,typlen from pg_type where typtype='b';

A bit like Linux with its bash_history, PostgreSQL keeps a list of commands executed in the $HOME/.psql_history file. Timing saves the time it takes a query to execute.

Users and roles

Create a user from the Shell/usr/bin/createuser midori

Create a user from Postgrespostgres=# CREATE USER akiko WITH password 'yamete';

Grant privileges to a userpostgres=# GRANT ALL PRIVILEGES ON DATABASE CityHunter TO akiko;

Delete a user from the Shell/usr/bin/dropuser midori

Delete a user from Postgrespostgres=# DROP USER akiko;

List rolespostgres=# \du

Create a rolepostgres=# CREATE ROLE trainer WITH LOGIN ENCRYPTED PASSWORD 'learn' CREATEDB;

Create a role with multiple privilegespostgres=# CREATE ROLE demidieu WITH LOGIN ENCRYPTED PASSWORD 'toto' CREATEDB CREATEROLE REPLICATION SUPERUSER;

Edit a rolepostgres=# ALTER ROLE demidieu CREATEROLE CREATEDB REPLICATION SUPERUSER;

Delete a rolepostgres=# DROP ROLE trainer;

These two variants require to be a postgres user (su - postgres).

Disk space

Calculate the disk space occupied by a databasepostgres=# SELECT pg_database_size('foodb');

postgres=# SELECT pg_size_pretty(pg_database_size('foodb'));

Calculate disk space occupied by a table (including indexes)postgres=# SELECT pg_size_pretty(pg_total_relation_size('grosse_table'));

Calculate the disk space occupied by a table (without the index)postgres=# SELECT pg_size_pretty(pg_relation_size('grosse_table'));

Find the largest table (variant 1)postgres=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Find the largest table (variant 2)postgres=# SELECT nspname || '.' || relname AS tablename, pg_size_pretty(pg_table_size((nspname || '.' || relname)::regclass)) AS size FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE relkind = 'r' ORDER BY pg_table_size((nspname || '.' || relname)::regclass) DESC LIMIT 10;

Transactions

Start a transactionpostgres=# BEGIN

Make a rollbackpostgres=# ROLLBACK

Make a commit (end of transaction)postgres=# COMMIT

.SQL

Count rows in a tablepostgres=# select count(*) from table;

Generate a series of numbers and insert it into a tablepostgres=# INSERT INTO numbers (num) VALUES ( generate_series(1,100));

Retrieve the second smallest value in a columnpostgres=# SELECT MIN(num) from number_table WHERE num > ( SELECT MIN(num) FROM number_table );

Retrieve the second largest value in a columnpostgres=# SELECT MAX(num) FROM number_table WHERE num < ( SELECT MAX(num) FROM number_table );

Encrypt, then save a passwordpostgres=# SELECT crypt ( 'midori', gen_salt('md5') );