postgres cheat sheet

change to postgres user and open psql prompt

sudo -u postgres psql postgres
list databases

postgres=# \l
list roles

postgres=# \du
create role

postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD ‘password1’ CREATEDB;
create role with multiple privileges

postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD
postgres=# ‘password1’ CREATEDB CREATEROLE REPLICATION SUPERUSER;
alter role

postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;
drop role

postgres=# DROP ROLE demorole1;
create database

postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING ‘UTF8’;
grant privileges to new user

postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;
drop database

postgres=# DROP DATABASE demodb1;
connect to database

postgres=# \c
list tables in connected database

postgres=# \dt
list columns on table

postgres=# \d backup database

$ pg_dump >

Leave a Reply

Your email address will not be published. Required fields are marked *