skip to content

Search

➤PostgreSQL

PostgreSQL Database: administration, queries and backup!

Databases
Published on
Karl Certa
Updated on
#postgresql #database

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/Debiansudo apt install postgresql postgresql-contrib
🎩 CentOS/RHELsudo yum install postgresql-server postgresql-contrib
🍎 macOS (Homebrew)brew install postgresql
🐳 Dockerdocker run -d postgres:15
🏢 Official versionpostgresql.org/download

⚙️ Basic Configuration

📌 Action🧠 Command
🚀 Initialize the databasesudo postgresql-setup initdb (CentOS)
▶️ Start the servicesudo systemctl start postgresql
🔄 Enable at bootsudo systemctl enable postgresql
👤 Connect as postgressudo -u postgres psql
🔐 Change postgres passwordALTER USER postgres PASSWORD 'password';
🚪 Exit psql\q

🗄️ Database Management

📌 Action🧠 Command
📋 List databases\l (in psql)
🆕 Create a databaseCREATE DATABASE myapp;
🗑️ Delete a databaseDROP DATABASE myapp;
🔗 Connect to a database\c myapp (in psql)
📊 Database size\l+ (in psql)
💾 Backup a databasepg_dump myapp > backup.sql
📥 Restore a databasepsql myapp < backup.sql

👥 User Management

📌 Action🧠 Command
📋 List users\du (in psql)
👤 Create a userCREATE USER john WITH PASSWORD 'password';
🔐 Change passwordALTER USER john PASSWORD 'new_password';
👑 Grant admin rightsALTER USER john WITH SUPERUSER;
🚫 Revoke admin rightsALTER USER john WITH NOSUPERUSER;
🗑️ Delete a userDROP USER john;

🔒 Permissions Management

📌 Action🧠 Command
🔑 Grant all rights on DBGRANT ALL PRIVILEGES ON DATABASE myapp TO john;
📖 Grant read-only rightsGRANT SELECT ON ALL TABLES IN SCHEMA public TO john;
✏️ Grant read/write rightsGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO john;
🚫 Revoke rightsREVOKE ALL PRIVILEGES ON DATABASE myapp FROM john;
📋 View table permissions\dp table_name (in psql)

💾 Backup and Restore

📌 Action🧠 Command
💾 Full backuppg_dump -U postgres myapp > backup.sql
🗜️ Compressed backuppg_dump -U postgres -Fc myapp > backup.dump
📥 SQL restorepsql -U postgres myapp < backup.sql
📦 Dump restorepg_restore -U postgres -d myapp backup.dump
🌐 Remote backuppg_dump -h server -U user database > backup.sql
⏰ Backup with timestamppg_dump -Fc myapp > backup_$(date +%Y%m%d).dump
🚢 Backup on custom portpg_dump -p 5433 -U postgres -Fc myapp > backup.dump
📦 Restore with logpg_restore -U postgres -d myapp --verbose backup.dump >> restore.log 2>&1

🔍 Monitoring and Debug

📌 Action🧠 Command
📈 View active connectionsSELECT * FROM pg_stat_activity;
🐌 Running slow queriesSELECT query, state, query_start FROM pg_stat_activity WHERE state = 'active';
💾 Database sizesSELECT 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 connectionSELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'user';
⚡ Clear cacheSELECT 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