Rainlily ✨

Setup Your First PostgreSQL Database

This guide walks you through creating databases, users, and managing permissions in PostgreSQL on macOS.

Connect to PostgreSQL

First, connect to the default PostgreSQL database:


                psql postgres

You should see the PostgreSQL prompt:


                postgres=#

Create a Database

Using Command Line (psql)


                CREATE DATABASE my_database;

Using Command Line (createdb)

From your terminal (outside psql):


                createdb my_database

Verify Database Creation

List all databases:


                \l

Or using command line:


                psql -l

Connect to Your Database

From psql Prompt


                \c my_database

From Terminal


                psql my_database

Create a User

Create a Basic User


                CREATE USER my_user WITH PASSWORD 'secure_password';

Create a User with Additional Privileges


                CREATE USER my_user WITH 
    PASSWORD 'secure_password'
    CREATEDB
    CREATEROLE;

Grant Permissions

Grant All Privileges on a Database


                GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

Grant Privileges on All Tables


                -- Connect to the database first
\c my_database
 
-- Grant privileges on all existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
 
-- Grant privileges on all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT ALL ON TABLES TO my_user;

Grant Specific Privileges


                -- Grant SELECT, INSERT, UPDATE privileges
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO my_user;

Create Tables

Basic Table Creation


                -- Connect to your database
\c my_database
 
-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create Table with Foreign Key


                CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Common Database Operations

List All Databases


                \l

List All Tables in Current Database


                \dt

Describe Table Structure


                \d table_name

List All Users/Roles


                \du

Show Current User


                SELECT current_user;

Show Current Database


                SELECT current_database();

Drop Database

Using SQL


                -- First, disconnect from the database
\c postgres
 
-- Then drop it
DROP DATABASE my_database;

Using Command Line


                dropdb my_database

Drop User


                DROP USER my_user;

Or if the user owns objects:


                DROP USER my_user CASCADE;

Change User Password


                ALTER USER my_user WITH PASSWORD 'new_password';

Backup and Restore

Create a Backup


                pg_dump my_database > backup.sql

Restore from Backup


                psql my_database < backup.sql

Backup Specific Table


                pg_dump -t table_name my_database > table_backup.sql

Environment Variables

You can set PostgreSQL connection details as environment variables:


                export PGHOST=localhost
export PGPORT=5432
export PGUSER=my_user
export PGPASSWORD=my_password
export PGDATABASE=my_database

Then connect simply with:


                psql

Useful psql Commands

Exit psql


                \q

Get Help


                \?

Get SQL Help


                \h CREATE DATABASE

Show Connection Info


                \conninfo

Execute SQL from File


                \i /path/to/file.sql

Timing Commands


                \timing

Next Steps