PSQL

Connect to DB

psql -U postgres

# then enter password for user `postgres`

Change password:

ALTER ROLE rootuser WITH PASSWORD 'secret123';

# with expiration
ALTER ROLE username
WITH PASSWORD 'new_password'
VALID UNTIL timestamp;

# with expiration for current password
ALTER ROLE bob
VALID UNTIL 'December 31, 2050';

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

\q: Quit/Exit
\c <database>: Connect to a database
\d <table>: Show table definition including triggers
\l: List databases
\dy: List events
\df: List functions
\di: List indexes
\dn: List schemas
\dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
\dv: List views
\df+ __function__ : Show function SQL code.
\x: Pretty-format query results instead of the not-so-useful ASCII tables
\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV

Some interesting flags (to see all, use -h or --help depending on your psql version):

-E: will describe the underlaying queries of the \ commands (cool for learning!)
-l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

User Related:

\du: List users
\du __username__: List a username if present.
create role __test1__: Create a role with an existing username.
create role __test2__ noinherit login password __passsword__;: Create a role with username and password.
set role __test__;: Change role for current session to __test__.
grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__.

Configuration

Service management commands

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

Changing verbosity & querying Postgres log:
1) First edit the config file, set a decent verbosity, save and restart postgresql:

sudo vim /etc/postgresql/9.3/main/postgresql.conf

# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1

sudo service postgresql restart

Now you will get tons of details of every statement, error, and even background tasks like VACUUMs

tail -f /var/log/postgresql/postgresql-9.3-main.log

How to add user who executed a PG statement to log (editing postgresql.conf):

log_line_prefix = '%t %u %d %a '

Database-level commands

Create Database

docs.

-- To create a new database
CREATE DATABASE lusiadas;

-- create a database `sales` owned by user `salesapp` with a default tablespace of `salesspace`
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

-- create a database `music` with a different locale and a different character set encoding
CREATE DATABASE music
    LOCALE 'sv_SE.iso885915'
    ENCODING LATIN9
    TEMPLATE template0;

Drop Database

docs.

-- may encounter errors:
-- ERROR:  database "lusiadas" is being accessed by other users
-- DETAIL:  There are 2 other sessions using the database.
DROP DATABASE lusiadas;

-- disconnect sessions then drop
DROP DATABASE lusiadas WITH (FORCE);

Rename Database

Given a database named db, wanna change to newdb.

1/ Switch to a different database

\c postgres

2/ Retrieve all active connections to db:

SELECT  *
FROM pg_stat_activity
WHERE datname = 'db';

3/ Terminate all connections to db:

SELECT
  pg_terminate_backend (pid)
FROM
  pg_stat_activity
WHERE
  datname = 'db';

4/ Safely rename:

ALTER DATABASE db RENAME TO newdb;

Reference.

`CREATE` command

There are many CREATE choices, like CREATE DATABASE __database_name__, CREATE TABLE __table_name__ ... Parameters differ but can be checked at the official documentation.

Roles

Postgres uses the concept of roles to represent user accounts. When roles contain other roles, they are referred to as group roles.
- Typically, roles that can log in to the PostgreSQL server are called login role (equivalent to user accounts in other DB system).
postgres is a superuser role created by the Postgres installer.
- System roles have names start with pg_.
\du command to show all roles that you create including the postgres role in the current PostgreSQL server.

CREATE ROLE role_name;

# retrieve all roles
SELECT rolname FROM pg_roles;

Roles attributes

Attributes define privileges for that role, including:

login
superuser status
database creation
password management

CREATE ROLE name [WITH] option

WITH is optional
option can be:
- SUPERUSER | NOSUPERUSER – determine if the role is a superuser or not.
- CREATEDB | NOCREATEDB– allow the role to create new databases.
- CREATEROLE | NOCREATEROLE – allow the role to create or change roles.
- INHERIT | NOINHERIT – determine if the role inherits the privileges of roles of which it is a member.
- LOGIN | NOLOGIN – allow the role to log in.
- REPLICATION | NOREPLICATION – determine if the role is a replication role.
- BYPASSRLS | NOBYPASSRLS – determine if the role is to bypass the row-level security (RLS) policy.
- CONNECTION LIMIT limit – specify the number of concurrent connections a role can make, -1 means unlimited.
- PASSWORD 'password' | PASSWORD NULL – change the role’s password.
- VALID UNTIL 'timestamp' – set the date and time after which the role’s password is no longer valid.

Create login roles

CREATE ROLE bonniss
LOGIN
PASSWORD '12345678'

Create superuser roles

CREATE ROLE john
SUPERUSER
LOGIN
PASSWORD 'secretkey'

Create role with database creation permission

CREATE ROLE dba
CREATEDB
LOGIN
PASSWORD 'secretkey'

Create roles with a validity period

CREATE ROLE dev
LOGIN
PASSWORD 'devpassword'
VALID UNTIL '2050-01-01';

Create roles with connection limit

CREATE ROLE api
LOGIN
PASSWORD 'secretkey'
CONNECTION LIMIT 1000;

Alter roles

ALTER ROLE role_name [WITH] option;

Change role to supseruser:

ALTER ROLE bonniss SUPERUSER;

Rename role:

ALTER ROLE oldname TO newname;

Handy queries

SELECT * FROM pg_proc WHERE proname='__procedurename__': List procedure/function
SELECT * FROM pg_views WHERE viewname='__viewname__';: List view (including the definition)
SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Show DB table space in use
SELECT pg_size_pretty(pg_database_size('__database_name__'));: Show DB space in use
show statement_timeout;: Show current user's statement timeout
SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';: Show table indexes
Get all indexes from all tables of a schema:

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,
    pg_namespace n
WHERE
   t.oid = ix.indrelid
   AND i.oid = ix.indexrelid
   AND a.attrelid = t.oid
   AND a.attnum = ANY(ix.indkey)
   AND t.relnamespace = n.oid
    AND n.nspname = 'kartones'
ORDER BY
   t.relname,
   i.relname

Execution data:
- Queries being executed at a certain DB:

SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query
  FROM pg_stat_activity
  WHERE datname='__database_name__';

Get all queries from all dbs waiting for data (might be hung):

SELECT * FROM pg_stat_activity WHERE waiting='t'

Currently running queries with process pid:

SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
  pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Casting:

CAST (column AS type) or column::type
'__table_name__'::regclass::oid: Get oid having a table name

Query analysis:

EXPLAIN __query__: see the query plan for the given query
EXPLAIN ANALYZE __query__: see and execute the query plan for the given query
ANALYZE [__table__]: collect statistics

Keyboard shortcuts

CTRL + R: reverse-i-search

Tools

ptop and pg_top: top for PG. Available on the APT repository from apt.postgresql.org.
pg_activity: Command line tool for PostgreSQL server activity monitoring.
Unix-like reverse search in psql:

$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc

PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way. Highly recommended.
A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more. Quite interesting if you need to tune-up a postgres setup.

bonniss/postgres-cheatsheet.md