PostgreSQL
🤔 Why PostgreSQL?
PostgreSQL is for:
🏗️ Linked data - Customers → Orders → Products
💰 Precise calculations - Money, accounting (no rounding bugs)
🔒 Security - Data is never lost
📊 Complex reports - Statistics, dashboards
Use it for:
E-commerce sites (products, orders, customers)
Management applications (invoices, inventory, employees)
Anything that looks like Excel but bigger
🚀 Installation
📌 System 🧠 Command 🐧 Ubuntu/Debian sudo apt install postgresql postgresql-contrib🎩 CentOS/RHEL sudo yum install postgresql-server postgresql-contrib🍎 macOS (Homebrew) brew install postgresql🐳 Docker docker run -d postgres:15🏢 Official version postgresql.org/download
⚙️ Basic Configuration
📌 Action 🧠 Command 🚀 Initialize the database sudo postgresql-setup initdb (CentOS) ▶️ Start the service sudo systemctl start postgresql🔄 Enable at boot sudo systemctl enable postgresql👤 Connect as postgres sudo -u postgres psql🔐 Change postgres password ALTER USER postgres PASSWORD 'password';🚪 Exit psql \q
🗄️ Database Management
📌 Action 🧠 Command 📋 List databases \l (in psql) 🆕 Create a database CREATE DATABASE myapp;🗑️ Delete a database DROP DATABASE myapp;🔗 Connect to a database \c myapp (in psql) 📊 Database size \l+ (in psql) 💾 Backup a database pg_dump myapp > backup.sql📥 Restore a database psql myapp < backup.sql
👥 User Management
📌 Action 🧠 Command 📋 List users \du (in psql) 👤 Create a user CREATE USER john WITH PASSWORD 'password';🔐 Change password ALTER USER john PASSWORD 'new_password';👑 Grant admin rights ALTER USER john WITH SUPERUSER;🚫 Revoke admin rights ALTER USER john WITH NOSUPERUSER;🗑️ Delete a user DROP USER john;
🔒 Permissions Management
📌 Action 🧠 Command 🔑 Grant all rights on DB GRANT ALL PRIVILEGES ON DATABASE myapp TO john;📖 Grant read-only rights GRANT SELECT ON ALL TABLES IN SCHEMA public TO john;✏️ Grant read/write rights GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO john;🚫 Revoke rights REVOKE ALL PRIVILEGES ON DATABASE myapp FROM john;📋 View table permissions \dp table_name (in psql)
💾 Backup and Restore
📌 Action 🧠 Command 💾 Full backup pg_dump -U postgres myapp > backup.sql🗜️ Compressed backup pg_dump -U postgres -Fc myapp > backup.dump📥 SQL restore psql -U postgres myapp < backup.sql📦 Dump restore pg_restore -U postgres -d myapp backup.dump🌐 Remote backup pg_dump -h server -U user database > backup.sql⏰ Backup with timestamp pg_dump myapp > backup_$(date +%Y%m%d_%H%M%S).sql
🔍 Monitoring and Debug
📌 Action 🧠 Command 📈 View active connections SELECT * FROM pg_stat_activity;🐌 Running slow queries SELECT query, state, query_start FROM pg_stat_activity WHERE state = 'active';💾 Database sizes SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;📊 Table sizes `SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname 🔧 Kill a connection SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'user';⚡ Clear cache SELECT pg_reload_conf();
📝 pg_hba.conf Configuration
Network access configuration file
Location # Find the file sudo -u postgres psql -c " SHOW hba_file; "
# Edit (usually) sudo nano /var/lib/pgsql/data/pg_hba.conf # CentOS sudo nano /etc/postgresql/15/main/pg_hba.conf # Ubuntu Configuration Examples # The postgres user can connect locally without password local all postgres trust
# Only PCs in network 192.168.1.0 to 192.168.1.255 can connect # with password (md5 = encrypted password) host all all 192.168.1.0/24 md5
# Concrete example: allow only web server 192.168.1.10 host myapp webuser 192.168.1.10/32 md5
# DANGER: allows everyone on the internet (avoid this!) host all all 0.0.0.0/0 md5
# SSL required for external connections (more secure) hostssl all all 0.0.0.0/0 md5 Restart after modification:
sudo systemctl reload postgresql 
Syspirit