PostgreSQL Installation on Ubuntu 22.04

Installing and Configuring PostgreSQL on Ubuntu 22.04

Set up PostgreSQL database server on Ubuntu 22.04 with optimal configuration for production use.

Installation

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

Initial Configuration

Configure PostgreSQL by editing /etc/postgresql/14/main/postgresql.conf:

max_connections = 200
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB

Create Database and User

sudo -u postgres psql
CREATE DATABASE myapp;
CREATE USER myuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

Performance Tuning

Tune shared_buffers to 25% of RAM. Use connection pooling (pgBouncer) for high-traffic applications.

Backup Strategy

Use pg_dump for logical backups or pg_basebackup for physical backups. Consider setting up streaming replication.

Security Recommendations

• Change default port from 5432 to a custom port
• Enable SSL/TLS connections
• Configure firewall to allow only specific IPs
• Regular security updates