PostgreSQL Command Line Quick Guide

 ·  · 

Here is a quick guide and cheat sheet for PostgreSQL command line.

 

1. Common

1.1 How to connect PostgreSQL shell prompt

$ sudo -u postgres psql postgres

1.2 How to see the help information for a command

postgres=# \h CREATE TABLE

 

2. User

2.1 How to create a new user in PostgreSQL

Method one:

postgres=# CREATE ROLE ausername WITH LOGIN;

Method two, using the shell wrapper command for CREATE ROLE:

$ createuser ausername

2.2 How to delete a user in PostgreSQL

Method one:

postgres=# DROP ROLE ausername;

Method two, using the shell wrapper command for DROP ROLE:

$ dropuser ausername

2.3 How to list all roles/users in PostgreSQL

Method one:

postgres=# \du

Or method two:

postgres=# SELECT rolname FROM pg_roles;

2.4 How to set/update user's password in PostgreSQL

Method one:

postgres=# \password username

Or method two:

postgres=# ALTER ROLE username WITH PASSWORD 'new password here';

 

3. Database

3.1 How to connect to a database in PostgreSQL

Method one:

$ sudo -u postgres psql postgres -d dbname

Or method two:

postgres=# \c dbname

3.2 How to list all databases in PostgreSQL

Method one:

postgres=# \l

Or method two:

postgres=# SELECT datname FROM pg_database;

3.3 How to create a databases with its owner

postgres=# CREATE DATABASE dbname OWNER username;

3.4 How to list the overview info about a database in PostgreSQL

postgres=# \l dbname

 

4. Table

4.1 How to list all tables of current connected database in PostgreSQL

postgres=# \dt

4.2 How to show details of a table in PostgreSQL

postgres=# \d tablename

 

5. Index

5.1 How to show all indexes in current database

postgres=# \di

5.2 How to show details of an index

postgres=# \d indexname

 

6. Backup

6.1 How to dump a database into a file

$ sudo -u postgres pg_dump dbname -h dbserverhost -p serverport > backupfilename

6.2 How to dump portions of a database into a file

# only dump a schema
$ sudo -u postgres pg_dump dbname -n scheam -h dbserverhost -p serverport > backupfilename

# only dump a table
$ sudo -u postgres pg_dump dbname -t tablename -h dbserverhost -p serverport > backupfilename

6.3 How to restore a dump file

$ sudo -u postgres psql dbname < dumpfile

Or directly dump from one server to another server:

$ sudo -u postgres pg_dump -h pgserver1 dbname | psql -h pgserver2 dbname