PostgreSQL Administration

PostgreSQL Administration

PostgreSQL (often called "Postgres") is an advanced open-source relational database with a stronger feature set than MySQL/MariaDB: support for JSON documents, full-text search, arrays, geometric types, and complex query planning. It is the preferred database for applications that need ACID compliance, complex queries, or advanced data types. PostgreSQL's user model differs from MySQL's — it uses "roles" instead of "users" and peer authentication for the postgres system user.

PostgreSQL vs MySQL

Installing PostgreSQL

sudo apt update
sudo apt install -y postgresql postgresql-contrib

sudo systemctl status postgresql
psql --version

psql version output

psql (PostgreSQL) 16.2 (Ubuntu 16.2-1.pgdg20.04+1)

psql basics

# Connect as the postgres system user (peer auth):
sudo -u postgres psql

# psql meta-commands (not SQL — start with backslash):
\l          -- list databases
\c mydb     -- connect to database mydb
\dt         -- list tables in current database
\du         -- list roles (users)
\d tablename -- describe table structure
\q          -- quit psql

# Run a SQL query from the shell without entering psql:
sudo -u postgres psql -c "SELECT version();"

Roles and databases

# Create a role (user) and database for an application:
sudo -u postgres psql

PostgreSQL role and database creation

-- Create role with login capability:
CREATE ROLE appuser WITH LOGIN PASSWORD 'SecurePass!42';

-- Create database owned by the role:
CREATE DATABASE appdb OWNER appuser;

-- Grant connect privilege (usually automatic when owned):
GRANT CONNECT ON DATABASE appdb TO appuser;

-- Connect to the database and grant schema permissions:
\c appdb
GRANT USAGE ON SCHEMA public TO appuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO appuser;

Configuration and tuning

# Main config files:
# /etc/postgresql/16/main/postgresql.conf  ← server settings
# /etc/postgresql/16/main/pg_hba.conf      ← authentication rules

# Key performance settings to adjust (in postgresql.conf):
# shared_buffers = 25% of RAM              ← main cache (default 128MB is too low)
# effective_cache_size = 75% of RAM        ← hint to query planner
# work_mem = 4MB-64MB                      ← memory per sort operation
# maintenance_work_mem = 256MB             ← for VACUUM, CREATE INDEX

# For a 4GB RAM server:
sudo nano /etc/postgresql/16/main/postgresql.conf
# shared_buffers = 1GB
# effective_cache_size = 3GB
# work_mem = 16MB

sudo systemctl restart postgresql

# Allow remote connections (pg_hba.conf):
# host    all    appuser    192.168.1.0/24    md5
# Then in postgresql.conf: listen_addresses = '*'

Conclusion

PostgreSQL's biggest configuration mistake is leaving shared_buffers at the default 128MB on a server with 4GB+ RAM — this severely limits caching performance. Set it to 25% of RAM and effective_cache_size to 75% of RAM as your starting point. PostgreSQL's documentation is exceptional; the wiki at wiki.postgresql.org has tuning guides for common hardware configurations. For most new applications where you have a choice, PostgreSQL is worth the slightly steeper initial learning curve.