Mirage's Notebook 📖

    Postgres

    🐘 PostgreSQL Cheat Sheet

    [!info] PostgreSQL is a powerful open-source relational database known for reliability, feature robustness, and performance. This sheet covers essential commands and configuration tips to get started and work efficiently.

    ⚙️ Overview

    PostgreSQL, or Postgres, supports advanced data types, powerful functions, and ACID compliance. It’s ideal for transactional applications, BI tools, and scalable web services.

    🚀 Features

    • ACID-compliant transactions

    • JSONB and XML support

    • Materialized views and triggers

    • Full-text search

    • Custom functions and stored procedures

    • Extensions like PostGIS, TimescaleDB

    🧰 Getting Started

    🛠️ Installation (Ubuntu 20.04)

    sudo apt update
sudo apt install -y postgresql postgresql-contrib postgresql-client
sudo systemctl status postgresql.service

    ☁️ Install on Kubernetes via Zalando Operator

    [!example]
    Useful for production-grade PostgreSQL in Kubernetes environments.

    Resources:

    🔐 Initial Access & Configuration

    Connect to PostgreSQL

    sudo -u postgres psql

    Set Password

    \password
-- or
ALTER USER postgres WITH PASSWORD 'SuperSecret';

    🔄 Enable Password Auth (pg_hba.conf)

    Edit:

    sudo vi /etc/postgresql/12/main/pg_hba.conf

    Change:

    local   all             postgres                                peer

    to:

    local   all             postgres                                md5

    Restart:

    sudo systemctl restart postgresql

    👥 User & Database Management

    Create User

    CREATE USER myuser WITH ENCRYPTED PASSWORD 'SuperSecret';

    Create Database

    CREATE DATABASE dbname OWNER myuser;

    Change Owner of Existing DB

    ALTER DATABASE dbname OWNER TO myuser;

    💾 Backup & Restore

    🧳 pg_dump

    SQL file:

    pg_dump -h host -U user -d dbname --create -Z 9 -f /tmp/db.sql.gz

    Custom format:

    pg_dump -h host -U user -d dbname -F c -f /tmp/db.dmp

    Restore SQL:

    psql -d newdb -f db.sql

    📦 pg_dumpall

    Full instance backup:

    pg_dumpall -h host -U postgres > database.out

    Restore:

    psql -h host -U postgres -f database.out

    🛠️ pg_restore

    Restore from custom format:

    pg_restore -h host -U user -d postgres --create -F c /tmp/db.dmp -v

    Explore More 🌍

    Tags 📚

    #postgresql #database #sql #kubernetes

    ← MongoDB
    Sqlite →