Skip to content

PostgreSQL

Connect to PSQL client

psql postgres
psql -U <user> -h <hostname> postgres

Users operations

List Users

postgres=# \du

Add User

# TODO

Set password for user

postgres=# \password user
postgres=# [Enter password]
postgres=# \q

Delete User

postgres=# DROP ROLE [USER_NAME];

Database operations

List Databases

postgres=# \list
postgres=# \l

List tables in current database

postgres=# \dt

Delete database

postgres=# DROP DATABASE [IF EXISTS] <databasename>;

Warning

If the following message occurs:

ERROR:  database "databasename" is being accessed by other users
DETAIL:  There are N other sessions using the database.

Execute the following command to drop database connections

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '<databasename>'
  AND pid <> pg_backend_pid();

Configuration file

Get config file path

postgres=# show hba_file;