PostgreSQL Database Management Commands

This cheatsheet provides essential PostgreSQL commands for managing databases, users, and data. It covers installation, setup, user roles, database creation, table operations, and backup/restore procedures.

External PostgreSQL Resources

• Getting Started with PostgreSQL on Mac OS X
• PostgreSQL Schema Tutorial
• PostgreSQL Roles and Permissions Management
• PostgreSQL Cheat Sheet
• PostgreSQL SERIAL Data Type
• Create Users, Databases, and Grant Access in PostgreSQL

Setting Up PostgreSQL

Follow these steps to install and start PostgreSQL on your system.

Installation

brew install postgresql

Starting PostgreSQL Service

brew services start postgresql

Accessing PostgreSQL CLI

psql postgres

Core PostgreSQL Commands

A collection of frequently used PostgreSQL commands for database and user management.

Database Operations

Create a new database:

CREATE DATABASE foo;

List all databases:

\l

Switch to a different database:

\c dbname

User and Role Management

Create a new role with login and password:

CREATE ROLE user1 WITH LOGIN PASSWORD 'secret';

List all roles:

\du

Allow a role to create databases:

ALTER ROLE user1 CREATEDB;

Exit the current psql session and log in as a specific user:

psql postgres -U user1

Create a new user with an encrypted password:

CREATE USER testuser with encrypted password 'sekretpw';

Permissions and Grants

Grant all privileges on a database to a role:

GRANT ALL PRIVILEGES ON DATABASE "foo" to user1;

Grant all privileges on a database to a user:

GRANT ALL PRIVILEGES ON database foo TO testuser;

Table Management

Create a table with an auto-incremental primary key:

CREATE TABLE fruits(id SERIAL PRIMARY KEY, name VARCHAR NOT NULL);
INSERT INTO fruits(id,name) VALUES(DEFAULT,'Apple');

List tables in the current database:

\dt
\dt+

Database Backup and Restore

Backup a database:

pg_dump -h 127.0.0.1 -U postgres -p 5432 dbname > dbname.bak

Restore a database:

psql -h dbname.x.eu-west-1.rds.amazonaws.com -U postgres dbname < dbname.bak