Skip to content

Instantly share code, notes, and snippets.

@brossetti1

brossetti1/postgres-cheatsheet.md

Forked from Kartones/postgres-cheatsheet.md
Last active July 7, 2017 20:02
Show Gist options
  • Save brossetti1/1f916edef1d9908bb99262a1419cfce3 to your computer and use it in GitHub Desktop.
Save brossetti1/1f916edef1d9908bb99262a1419cfce3 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL command line cheatsheet
Raw
postgres-cheatsheet.md

PSQL - 00000001

Magic words:

psql -U postgres

If run with -E flag, it will describe the underlaying queries of the \ commands (cool for learning!).

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
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \l: List databases
  • \dn: List schemas
  • \df: List functions
  • \dv: List views
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables

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 postgres:
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
  1. 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
  1. How to add user who executed a PG statement to log (editing postgresql.conf):
log_line_prefix = '%t %u %d %a '

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

Tools

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

Postgres Cheatsheet - 00000002

This is a collection of the most common commands I run while administering Postgres databases. The variables shown between the open and closed tags, "<" and ">", should be replaced with a name you choose. Postgres has multiple shortcut functions, starting with a forward slash, "". Any SQL command that is not a shortcut, must end with a semicolon, ";". You can use the keyboard UP and DOWN keys to scroll the history of previous commands you've run.

Setup

installation, Ubuntu

http://www.postgresql.org/download/linux/ubuntu/ https://help.ubuntu.com/community/PostgreSQL

sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > \
  /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5

sudo su - postgres
psql
connect

http://www.postgresql.org/docs/current/static/app-psql.html

psql

psql -U <username> -d <database> -h <hostname>

psql --username=<username> --dbname=<database> --host=<hostname>
disconnect
\q
\!
clear the screen
(CTRL + L)
info
\conninfo
configure

http://www.postgresql.org/docs/current/static/runtime-config.html

sudo nano $(locate -l 1 main/postgresql.conf)
sudo service postgresql restart
debug logs
# print the last 24 lines of the debug log
sudo tail -24 $(find /var/log/postgresql -name 'postgresql-*-main.log')




Recon

show version
SHOW SERVER_VERSION;
show system status
\conninfo
show environmental variables
SHOW ALL;
list users
SELECT rolname FROM pg_roles;
show current user
SELECT current_user;
show current user's permissions
\du
list databases
\l
show current database
SELECT current_database();
show all tables in database
\dt
list functions
\df <schema>




Databases

list databasees
\l
connect to database
\c <database_name>
show current database
SELECT current_database();
create database

http://www.postgresql.org/docs/current/static/sql-createdatabase.html

CREATE DATABASE <database_name> WITH OWNER <username>;
delete database

http://www.postgresql.org/docs/current/static/sql-dropdatabase.html

DROP DATABASE IF EXISTS <database_name>;
rename database

http://www.postgresql.org/docs/current/static/sql-alterdatabase.html

ALTER DATABASE <old_name> RENAME TO <new_name>;




Users

list roles
SELECT rolname FROM pg_roles;
create user

http://www.postgresql.org/docs/current/static/sql-createuser.html

CREATE USER <user_name> WITH PASSWORD '<password>';
drop user

http://www.postgresql.org/docs/current/static/sql-dropuser.html

DROP USER IF EXISTS <user_name>;
alter user password

http://www.postgresql.org/docs/current/static/sql-alterrole.html

ALTER ROLE <user_name> WITH PASSWORD '<password>';




Permissions

become the postgres user, if you have permission errors
sudo su - postgres
psql
grant all permissions on database

http://www.postgresql.org/docs/current/static/sql-grant.html

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;
grant connection permissions on database
GRANT CONNECT ON DATABASE <db_name> TO <user_name>;
grant permissions on schema
GRANT USAGE ON SCHEMA public TO <user_name>;
grant permissions to functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;
grant permissions to select, update, insert, delete, on a all tables
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
grant permissions, on a table
GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;
grant permissions, to select, on a table
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;




Schema

list schemas
\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;
create schema

http://www.postgresql.org/docs/current/static/sql-createschema.html

CREATE SCHEMA IF NOT EXISTS <schema_name>;
drop schema

http://www.postgresql.org/docs/current/static/sql-dropschema.html

DROP SCHEMA IF EXISTS <schema_name> CASCADE;




Tables

list tables, in current db
\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
list tables, globally
\dt *.*.

SELECT * FROM pg_catalog.pg_tables
list table schema
\d <table_name>
\d+ <table_name>

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';
create table

http://www.postgresql.org/docs/current/static/sql-createtable.html

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <column_name> <column_type>
);
create table, with an auto-incrementing primary key
CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);
delete table

http://www.postgresql.org/docs/current/static/sql-droptable.html

DROP TABLE IF EXISTS <table_name> CASCADE;




Columns

add column

http://www.postgresql.org/docs/current/static/sql-altertable.html

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];
update column
ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];
delete column
ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;
update column to be an auto-incrementing primary key
ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;
insert into a table, with an auto-incrementing primary key
INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);


INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );




Data

read all data

http://www.postgresql.org/docs/current/static/sql-select.html

SELECT * FROM <table_name>;
read one row of data
SELECT * FROM <table_name> LIMIT 1;
search for data
SELECT * FROM <table_name> WHERE <column_name> = <value>;
insert data

http://www.postgresql.org/docs/current/static/sql-insert.html

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
edit data

http://www.postgresql.org/docs/current/static/sql-update.html

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;
delete all data

http://www.postgresql.org/docs/current/static/sql-delete.html

DELETE FROM <table_name>;
delete specific data
DELETE FROM <table_name>
WHERE <column_name> = <value>;




Scripting

run local script, on remote host

http://www.postgresql.org/docs/current/static/app-psql.html

psql -U <username> -d <database> -h <host> -f <local_file>

psql --username=<username> --dbname=<database> --host=<host> --file=<local_file>
backup database data, everything

http://www.postgresql.org/docs/current/static/app-pgdump.html

pg_dump <database_name>

pg_dump <database_name>
backup database, only data
pg_dump -a <database_name>

pg_dump --data-only <database_name>
backup database, only schema
pg_dump -s <database_name>

pg_dump --schema-only <database_name>
restore database data

http://www.postgresql.org/docs/current/static/app-pgrestore.html

pg_restore -d <database_name> -a <file_pathway>

pg_restore --dbname=<database_name> --data-only <file_pathway>
restore database schema
pg_restore -d <database_name> -s <file_pathway>

pg_restore --dbname=<database_name> --schema-only <file_pathway>
export table into CSV file

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> TO '<file_path>' CSV
export table, only specific columns, to CSV file
\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' CSV
import CSV file into table

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> FROM '<file_path>' CSV
import CSV file into table, only specific columns
\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' CSV




Debugging

http://www.postgresql.org/docs/current/static/using-explain.html

http://www.postgresql.org/docs/current/static/runtime-config-logging.html


Advanced Features

http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm

Ruby on Rails

ActiveRecord::Base.connection.execute(<<-SQL
  UPDATE coaches
  SET
    preferences->>'current_sport' = preferences->>'initcap(current_sport)'
  SQL
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment