PostgreSQL Cheat Sheet

We gathered a list of handy PostgreSQL commands and queries when working with PostgreSQL databases. Please ping us @ForestAdmin if you’d like to add something to the cheat sheet!

Getting started with PostgreSQL

Login and enter PostgreSQL command line utility psql

$ sudo su - postgres psql

List postgres databases

\l

Connect to postgres database

\c databaseName

Exit postgreSQL command line utility psql

\q

Managing PostgreSQL

Check PostgreSQL version

 SELECT version();

Check if PostgreSQL is installed

$ which psql

Check if PostgreSQL is running

$ pgrep -fa -- -D | grep postgres

Restart PostgreSQL on Linux

# systemctl restart postgresql
// or
# service postgresql restart
// or
# /etc/init.d/postgresql restart
// or
# /sbin/service postgresql restart

Restart PostgreSQL on OSX

# brew services restart postgres
// or
# pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart

Restart PostgreSQL on Windows

Winkey + R
Type "services.msc"
Click "restart"

Change PostgreSQL user password

$ sudo -u postgres psql
\password postgres

Exit from psql

\q

Interacting with databases in PostgreSQL

List postgres databases

\l

Connect or switch to postgres database

\c databaseName

Create new postgres database

CREATE DATABASE databaseName

Delete postgres database

DROP DATABASE databaseName

Rename postgres database

ALTER DATABASE old_databaseName TO new_databaseName

Query postgres JSON data

SELECT *
FROM mytable
WHERE myfield @> '{"key1":"valueA"}'

Interacting with tables in PostgreSQL

List postgres tables

\dt

Describe postgres table

\d tableName

Create postgres table

CREATE TABLE tableName(	
	columnName columnType,
	columnName columnType
);

Delete postgres table

DROP TABLE tableName CASCADE

Backup and restore PostgreSQL database

Backup postgres database

$ pg_dump -d databaseName -U userName > backupFile

Restore postgres database

psql databaseName < backupFile

Managing roles and permissions in PostgreSQL

List postgres roles

SELECT rolname FROM pg_roles;

Create postgres user

CREATE USER userName WITH PASSWORD 'userPassword';

Delete postgres user

DROP USER userName;

Change postgres user password

ALTER ROLE userName WITH PASSWORD 'userPassword';

List all assigned roles postgres

SELECT
	r.rolname,
	r.rolsuper,
	r.rolinherit,
	r.rolcreaterole,
	r.rolcreatedb,
	r.rolcanlogin,
	r.rolconnlimit,
	r.rolvaliduntil,
	ARRAY(SELECT b.rolname
		FROM pg_catalog.pg_auth_members m
		JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
		WHERE m.member = r.oid) as memberof,
	r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;

Grant all permissions on postgres database

GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;

Grant connection permissions on postgres database

GRANT CONNECT ON DATABASE databaseName TO userName;

List permissions for specific role postgres

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = userName ORDER BY table_name;

ProstgreSQL psql commands recap

Need to build admin panels or a GUI tool for PostgreSQL? Check out Forest Admin for PostgreSQL