PostgreSQL Commands Cheat Sheet

This post is my cheat sheet for the most commonly used commands in the PostgreSQL console.

When installed, PostgreSQL creates a separate Linux user named postgres. In my examples, I will run commands under this user using sudo. Although you can use this account to login shell and run commands directly from the postgres user session.

Display a list of databases with extended information:

# sudo -u postgres psql -U postgres -l+

Create a text dump of the database:

# sudo -u postgres pg_dump -U postgres dbtemp01 \
> ~/ dbtemp01.sql

Compress a PostgreSQL database dump on the fly:

# sudo -u postgres pg_dump -U postgres dbtemp01 \
| pigz > ~/dbtemp01.sql.gz

Restore a database from a dump file to a new database:

# sudo -u postgres createdb -U postgres \
-T template0 dbtemp02

# sudo -u postgres psql -U postgres dbtemp02 \
< ~/ dbtemp01.sql

Exit psql console:

$ \q

Create a new PostgreSQL user:

# sudo -u postgres createuser -U postgres usrsite1

Set user password:

# sudo -u postgres psql -U postgres -c \
"ALTER USER usrsite1 PASSWORD 'SecRet2pwd'"

List PostgreSQL users:

# sudo -u postgres psql -U postgres -c \
"select * from pg_user"

Give full privileges to the database:

# sudo -u postgres psql -U postgres -c \
"GRANT ALL PRIVILEGES ON DATABASE sitedb to usrsite1"

Assign a user as the database owner:

# sudo -u postgres psql -U postgres -c \
"ALTER DATABASE sitedb OWNER TO usrsite1"

Clean up (-f) and analyze (-z) the database (Postgres Pro):

# sudo -u postgres vacuumdb -U postgres -f -z -d sitedb

Reindex database:

# sudo -u postgres reindexdb -U postgres -d sitedb

Delete database:

# sudo -u postgres psql -U postgres -c \
"DROP DATABASE sitedb"

List user permissions:

select * from INFORMATION_SCHEMA.table_privileges WHERE grantee = '<USERNAME>';

Display permissions for groups that USER_NAME is a member of:

select * from INFORMATION_SCHEMA.role_table_grants WHERE grantee = '<USERNAME>';

Check table permissions:

select relacl from pg_catalog.pg_class where relname='<TABLENAME>';

Some useful commands for psql interactive mode:

\connect db_name – connect to the database

\du – list users

\dp – display a list of tables, views, sequences, and access permissions

\dt * – list tables