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') );
Last updated