Posted 5 days ago by Vanessa

PostgreSQL 18 brings significant performance improvements and new features that make it an excellent choice for modern applications. This comprehensive guide will walk you through installing PostgreSQL 18 on a Ramnode VPS running Ubuntu 24.04 LTS or higher.

Prerequisites

Before beginning the installation, ensure you have:

  • A Ramnode VPS with Ubuntu 24.04 LTS or higher
  • Root or sudo access to your server
  • At least 1GB of RAM (2GB+ recommended for production)
  • Sufficient disk space (minimum 10GB free space recommended)

Update Your System

First, connect to your Ramnode VPS via SSH and update the system packages:

sudo apt update
sudo apt upgrade -y

Install Required Dependencies

Install the necessary packages and dependencies:

sudo apt install -y wget ca-certificates software-properties-common apt-transport-https lsb-release

Add the PostgreSQL Official APT Repository

PostgreSQL 18 may not be available in Ubuntu’s default repositories, so we’ll add the official PostgreSQL APT repository:

# Import the PostgreSQL signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Add the PostgreSQL APT repository
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# Update package list
sudo apt update

Install PostgreSQL 18

Now install PostgreSQL 18 and its contrib package:

sudo apt install -y postgresql-18 postgresql-contrib-18

The installation will automatically:

  • Create a postgres system user
  • Initialize a database cluster
  • Start the PostgreSQL service
  • Enable PostgreSQL to start automatically on boot

Verify the Installation

Check that PostgreSQL is running:

sudo systemctl status postgresql

You should see output indicating that PostgreSQL is active and running.

Verify the installed version:

sudo -u postgres psql -c "SELECT version();"

Configure PostgreSQL

Set a Password for the PostgreSQL User

By default, PostgreSQL creates a user called postgres with no password. Set a secure password:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_secure_password_here';"

Replace your_secure_password_here with a strong password.

Configure Connection Settings

Edit the PostgreSQL configuration file to allow connections:

sudo nano /etc/postgresql/18/main/postgresql.conf

Find and modify these settings:

# Listen on all addresses (change from 'localhost' if needed)
listen_addresses = 'localhost'

# Set the port (default is 5432)
port = 5432

# Increase max connections if needed
max_connections = 100

For remote connections, you may want to set listen_addresses = '*', but ensure you have proper firewall rules in place.

Configure Client Authentication

Edit the host-based authentication file:

sudo nano /etc/postgresql/18/main/pg_hba.conf

For local connections, the default configuration should work. For remote connections, you might add:

# Allow connections from your IP range (adjust as needed)
host    all             all             your.ip.range/24        md5

Restart PostgreSQL

After making configuration changes, restart PostgreSQL:

sudo systemctl restart postgresql

Configure Firewall (if applicable)

If you’re using UFW (Uncomplicated Firewall), allow PostgreSQL connections:

# For local connections only
sudo ufw allow from 127.0.0.1 to any port 5432

# For remote connections (adjust IP range as needed)
sudo ufw allow from your.ip.range/24 to any port 5432

Create a Database and User

Connect to PostgreSQL and create a new database and user:

sudo -u postgres psql

In the PostgreSQL prompt, run:

-- Create a new database
CREATE DATABASE myapp_db;

-- Create a new user
CREATE USER myapp_user WITH PASSWORD 'secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- Exit
\q

Test the Installation

Test connecting to your new database:

psql -h localhost -U myapp_user -d myapp_db

Performance Optimization for Ramnode VPS

Memory Configuration

Edit the PostgreSQL configuration to optimize for your VPS’s memory:

sudo nano /etc/postgresql/18/main/postgresql.conf

Adjust these settings based on your VPS specifications:

# For a 2GB RAM VPS
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
work_mem = 4MB

# For a 4GB RAM VPS
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
work_mem = 8MB

Enable Query Performance Monitoring

Add these lines to enable query logging and performance monitoring:

log_statement = 'all'
log_duration = on
log_min_duration_statement = 1000  # Log queries taking > 1 second

Security Best Practices

  1. Use strong passwords for all PostgreSQL users
  2. Limit network access using firewall rules and pg_hba.conf
  3. Regular backups: Set up automated backups using pg_dump
  4. Keep PostgreSQL updated with security patches
  5. Use SSL connections for remote access when possible

Backup and Maintenance

Create a Backup Script

Create a simple backup script:

sudo nano /usr/local/bin/pg_backup.sh

#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

# Backup all databases
sudo -u postgres pg_dumpall > $BACKUP_DIR/postgresql_backup_$DATE.sql

# Keep only last 7 days of backups
find $BACKUP_DIR -name "postgresql_backup_*.sql" -mtime +7 -delete

Make it executable and set up a cron job:

sudo chmod +x /usr/local/bin/pg_backup.sh
sudo crontab -e

Add this line for daily backups at 2 AM:

0 2 * * * /usr/local/bin/pg_backup.sh

Troubleshooting Common Issues

PostgreSQL Won’t Start

Check the logs:

sudo journalctl -u postgresql -f

Common issues include:

  • Port conflicts
  • Insufficient disk space
  • Configuration file syntax errors

Connection Refused

  • Verify PostgreSQL is running: sudo systemctl status postgresql
  • Check port configuration in postgresql.conf
  • Verify firewall rules
  • Check pg_hba.conf for authentication settings

Performance Issues

  • Monitor resource usage: htop or top
  • Check PostgreSQL logs for slow queries
  • Consider adjusting memory settings
  • Monitor disk I/O with iotop

Conclusion

You now have PostgreSQL 18 successfully installed and configured on your Ramnode VPS. This setup provides a solid foundation for hosting databases for web applications, APIs, or data analysis projects. Remember to regularly backup your databases, monitor performance, and keep your system updated for optimal security and performance.

For production environments, consider implementing additional monitoring tools, setting up replication for high availability, and fine-tuning configuration parameters based on your specific workload requirements.