PostgreSQL Cheat Sheet

List PostgreSQL Databases

postgres=# \l

List PostgreSQL Schemas

postgres=# \dn

List PostgreSQL Tables

postgres=# \dt

List PostgreSQL Users

postgres=# \du

Create New PostgreSQL User

postgres=# CREATE USER <user-name> with encrypted password '<password>';;

Change or Reset PostgreSQL User Password

postgres=# ALTER USER <user-name> WITH PASSWORD '<new-password>';

Grant Spesific Permission to All Tables for User

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO <user-name>;
postgres=# GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA PUBLIC TO <user-name>;
postgres=# GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA PUBLIC TO <user-name>;
postgres=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PUBLIC TO <user-name>;

Delete or Drop User from Database

postgres=# DROP USER IF EXISTS <user_name_1>, <user_name_2>, ..., <user_name_n>;

Delete or Drop User from Database If Some Object Depends on It

postgres=# REASSIGN OWNED BY <user-name> to postgres;   # The query changes the object ownership to the postgres user.
postgres=# DROP OWNED BY <user-name>;
postgres=# DROP USER IF EXISTS <user-name>;

Or follow this example:

postgres=# DROP USER IF EXISTS edtadmingidedemo1;
ERROR:  role "edtadmingidedemo1" cannot be dropped because some objects depend on it
DETAIL:  59 objects in database edtdbgidedemo2
13 objects in database edtdbgidedemo3
postgres=#
postgres=# \c edtdbgidedemo2
You are now connected to database "edtdbgidedemo2" as user "postgres".
edtdbgidedemo2=# reassign owned by edtadmingidedemo1 to postgres;
REASSIGN OWNED
postgres=#
edtdbgidedemo2=# drop owned by edtadmingidedemo1;
DROP OWNED
edtdbgidedemo2=#
edtdbgidedemo2=# \c edtdbgidedemo3
You are now connected to database "edtdbgidedemo3" as user "postgres".
edtdbgidedemo2=#
edtdbgidedemo3=#  reassign owned by edtadmingidedemo1 to postgres;
REASSIGN OWNED
edtdbgidedemo3=#
edtdbgidedemo3=# drop owned by edtadmingidedemo1;
DROP OWNED
edtdbgidedemo3=#
edtdbgidedemo3=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# DROP USER IF EXISTS edtadmingidedemo1;
DROP ROLE
postgres=#

Find PostgreSQL Active Connection

postgres=# SELECT * FROM pg_stat_activity WHERE datname = '<database_name>';

Terminate All Active Connections in Database

postgres=# SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<database_name>';

Check If The Server is Postgres Master or Slave

postgres=# SELECT pg_is_in_recovery();

If it's true, we're on a slave; if false, we're in master.

Find Postgresql Configuration Location

postgres=# SELECT name, source, sourcefile, sourceline, pending_restart FROM pg_settings;

Check Replication Slots

postgres=# SELECT * from pg_replication_slots;

Create New Database

postgres=# CREATE DATABASE <database_name>;

Delete or Drop Database

postgres=# DROP DATABASE IF EXISTS <database_name>;

Export PostgreSQL Database to Dump File

[root@gejoreuy ~]# pg_dump -U <username> -h <hostname> -p <port> <source_database_name> -f <dump_file_name>

Export PostgreSQL Database to Dump File with Password

[root@gejoreuy ~]# PGPASSWORD="password" pg_dump -U <username> -h <hostname> -p <port> <source_database_name> -f <dump_file_name>

Import PostgreSQL Database from Dump File

[root@gejoreuy ~]# psql -U <username> -h <hostname> -p <port> -d <destination_database_name> -f <dump_file_name>