Programming/PostgreSQL/Databases

Note: Unless otherwise specified, all commands from this page assume you're in the PostgreSQL shell.

General Commands

Show All Databases

\l

Show Currently Selected Database

SELECT current_database();

Create a New Database

CREATE DATABASE <database_name>;

Load a Database

\c <database_name>

Delete a Database

Warn: Note that this action cannot be undone.

DROP DATABASE <database_name>;

Set Database Privileges

GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;

On newer installations of Postgres, you may also need the following command for the user to properly create/modify tables:

# First, load the desired database:
\c <database_name>

# Then run command:
GRANT ALL ON SCHEMA public TO <user>;

Database Backups

Export Database to file

pg_dump -Fc -U <user_name> -d <database_name> > <file_location>

Optional Flags:

• -h <host> - Allows specification of host, such as for connecting to a remote server.
• -p <port> - Allows specification of port, such as for non-standard installations that don't use the default port 5432.

Import Database from File

pg_restore -U <user_name> -d <database_name> <file_location>

Optional Flags:

• -h <host> - Allows specification of host, such as for connecting to a remote server.
• -p <port> - Allows specification of port, such as for non-standard installations that don't use the default port 5432.