Mirage Garden

postgres

2 min read

🐘 PostgreSQL Cheat Sheet

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

Graph View

Backlinks