PostgreSQL Installation Guide for Amazon Linux 2023

PostgreSQL-aws-linux.md

PostgreSQL Installation Guide for Amazon Linux 2023

Prerequisites

• An Amazon Linux 2023 instance
• Sudo privileges
• Active internet connection

Installation Steps

1. Update System Packages

sudo dnf update -y

2. Install PostgreSQL Server

# Install the postgresql-server package
sudo dnf install -y postgresql15-server postgresql15

# Initialize the database
sudo postgresql-15-setup initdb

3. Start and Enable PostgreSQL Service

# Start PostgreSQL service
sudo systemctl start postgresql-15

# Enable PostgreSQL to start on boot
sudo systemctl enable postgresql-15

# Verify the service status
sudo systemctl status postgresql-15

4. Configure PostgreSQL Access (Optional but Recommended)

a. Edit PostgreSQL Configuration

# Backup the original configuration
sudo cp /var/lib/pgsql/15/data/postgresql.conf /var/lib/pgsql/15/data/postgresql.conf.bak
sudo cp /var/lib/pgsql/15/data/pg_hba.conf /var/lib/pgsql/15/data/pg_hba.conf.bak

# Edit postgresql.conf to allow remote connections
sudo vi /var/lib/pgsql/15/data/postgresql.conf

Add or modify these lines:

listen_addresses = '*'          # Listen on all available IP addresses
port = 5432                     # Default port

b. Configure Client Authentication

# Edit pg_hba.conf
sudo vi /var/lib/pgsql/15/data/pg_hba.conf

Add these lines for remote access (adjust according to your security needs):

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# Remote connections - replace with your specific IP range
host    all             all             0.0.0.0/0               scram-sha-256

5. Restart PostgreSQL to Apply Changes

sudo systemctl restart postgresql-15

6. Set Password for postgres User

# Switch to postgres user
sudo -u postgres psql

# In PostgreSQL prompt, set password
ALTER USER postgres WITH PASSWORD 'your_secure_password';

# Exit PostgreSQL prompt
\q

7. Configure Firewall (if enabled)

# Allow PostgreSQL port through firewall
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

Verification Steps

1. Check PostgreSQL Version

psql --version

2. Verify Database Connection

# Connect to PostgreSQL
sudo -u postgres psql

# Check connection info
\conninfo

# Exit
\q

Important Security Notes

1. Always use strong passwords
2. Restrict remote access to specific IP addresses when possible
3. Keep the system and PostgreSQL updated
4. Regular backup of database
5. Monitor logs for suspicious activities

Additional Configurations (Optional)

Performance Tuning Parameters

Edit /var/lib/pgsql/15/data/postgresql.conf:

# Memory Configuration
shared_buffers = 256MB                  # 25% of RAM for dedicated server
work_mem = 4MB                          # Depends on workload
maintenance_work_mem = 64MB             # For maintenance operations

# Checkpoint Configuration
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Query Planner Configuration
effective_cache_size = 768MB            # 75% of RAM for dedicated server

Logging Configuration

log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_min_duration_statement = 1000       # Log queries taking more than 1 second