psql

Postgres Cheatsheet

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 Community

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

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

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

CREATE DATABASE <database_name> WITH OWNER <username>;

delete database

DROP DATABASE IF EXISTS <database_name>;

rename database

ALTER DATABASE <old_name> RENAME TO <new_name>;

Users

list roles

SELECT rolname FROM pg_roles;

create user

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

drop user

DROP USER IF EXISTS <user_name>;

alter user password

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

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

CREATE SCHEMA IF NOT EXISTS <schema_name>;

drop schema

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

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

DROP TABLE IF EXISTS <table_name> CASCADE;

Columns

add column

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

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

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

edit data

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

delete all data

DELETE FROM <table_name>;

delete specific data

DELETE FROM <table_name>
WHERE <column_name> = <value>;

Scripting

run local script, on remote host

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

psql --username=<username> --dbname=<database> --host=<host> --file=<local_file>

backup database data, everything

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

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

\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

\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

Using EXPLAIN Error Reporting and Logging