PostgreSQL • Syspirit

PostgreSQL is an open source relational and object database management system, renowned for its robustness, advanced features and SQL standards compliance.

🤔 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 -Fc myapp > backup_$(date +%Y%m%d).dump`
🚢 Backup on custom port	`pg_dump -p 5433 -U postgres -Fc myapp > backup.dump`
📦 Restore with log	`pg_restore -U postgres -d myapp --verbose backup.dump >> restore.log 2>&1`

🔍 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

Search

➤PostgreSQL