RamNode logo
    Sign UpLogin
    Database Management

    Install PostgreSQL 18 on RamNode

    Set up PostgreSQL 18 on your RamNode VPS hosting. Deploy the world's most advanced open source database with enhanced performance, security, and modern features.

    Ubuntu 24.04 LTS+
    PostgreSQL 18
    ⏱️ 15-25 minutes

    Prerequisites

    Before starting, ensure you have:

    Server Requirements

    • • RamNode VPS (1GB+ RAM minimum)
    • • Ubuntu 24.04 LTS or higher
    • • 1+ CPU cores
    • • 10GB+ disk space available
    • • SSH access to your VPS
    → View Recommended Premium VPS Plans

    Access Requirements

    • • Root or sudo access
    • • Basic Linux command knowledge
    • • Understanding of database concepts
    • • Network firewall access
    2

    System Update

    Connect to your RamNode VPS and update the system:

    Connect via SSH
    ssh root@your-server-ip
    Update System Packages
    sudo apt update
sudo apt upgrade -y
    Install Required Dependencies
    sudo apt install -y wget ca-certificates software-properties-common apt-transport-https lsb-release

    💡 System Tip: Always ensure your system is up-to-date before installing database software to avoid compatibility issues.

    3

    Add PostgreSQL Repository

    PostgreSQL 18 requires the official PostgreSQL APT repository:

    Import PostgreSQL Signing Key
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    Add 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

    ✅ PostgreSQL official repository is now configured and ready for installation!

    4

    Install PostgreSQL 18

    Install PostgreSQL 18 and related packages:

    Install PostgreSQL 18
    sudo apt install -y postgresql-18 postgresql-contrib-18 postgresql-client-18
    Verify Installation
    sudo systemctl status postgresql
psql --version
    Enable and Start PostgreSQL Service
    sudo systemctl enable postgresql
sudo systemctl start postgresql

    What's New in PostgreSQL 18?

    PostgreSQL 18 brings significant performance improvements, enhanced JSON processing, improved parallel query execution, better memory management, and new security features. It's the most advanced version yet with optimizations for modern workloads.

    5

    Initial Configuration

    Configure PostgreSQL for your environment:

    Switch to PostgreSQL User
    sudo -i -u postgres
    Access PostgreSQL Prompt
    psql
    Set Postgres User Password
    -- Set a secure password for the postgres user
ALTER USER postgres PASSWORD 'your_secure_password_here';

-- Check PostgreSQL version
SELECT version();

-- Exit psql
\q
    Exit PostgreSQL User
    exit

    🔐 Security: Replace 'your_secure_password_here' with a strong, unique password. Store it securely!

    6

    Security Configuration

    Secure your PostgreSQL installation:

    Configure PostgreSQL Authentication
    sudo nano /etc/postgresql/18/main/pg_hba.conf

    Update the authentication methods (find and modify these lines):

    PostgreSQL Authentication Configuration
    # Database administrative login by Unix domain socket
local   all             postgres                                md5

# "local" is for Unix domain socket connections only
local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5
    Configure PostgreSQL Server Settings
    sudo nano /etc/postgresql/18/main/postgresql.conf

    Key settings to configure:

    Essential PostgreSQL Settings
    # Connection settings
listen_addresses = 'localhost'          # Change to '*' for remote access
port = 5432
max_connections = 100

# Memory settings (adjust based on your VPS RAM)
shared_buffers = 256MB                   # 25% of total RAM
effective_cache_size = 1GB               # 75% of total RAM
work_mem = 4MB
maintenance_work_mem = 64MB

# Logging
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_min_duration_statement = 1000       # Log slow queries (1 second+)

# Security
ssl = on
password_encryption = scram-sha-256
    Restart PostgreSQL
    sudo systemctl restart postgresql
    7

    Create Database and User

    Create a new database and user for your applications:

    Access PostgreSQL as Postgres User
    sudo -u postgres psql
    Create Database and User
    -- Create a new database
CREATE DATABASE myapp_db;

-- Create a new user with password
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'secure_user_password';

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

-- Grant schema privileges
\c myapp_db
GRANT ALL ON SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;

-- List databases
\l

-- List users
\du

-- Exit
\q
    Test New User Connection
    psql -h localhost -U myapp_user -d myapp_db

    ✅ Database and user created successfully! You can now connect your applications to PostgreSQL.

    8

    Configure Remote Access

    Enable remote connections to your PostgreSQL server:

    Update PostgreSQL Configuration for Remote Access
    sudo nano /etc/postgresql/18/main/postgresql.conf

    Change the listen_addresses setting:

    Enable Remote Connections
    # Allow connections from any IP address
listen_addresses = '*'

# Or specify specific IP addresses
# listen_addresses = 'localhost,192.168.1.100'
    Update Client Authentication
    sudo nano /etc/postgresql/18/main/pg_hba.conf

    Add remote connection rules (be specific with IP ranges for security):

    Remote Access Configuration
    # Allow connections from specific networks
host    all             all             192.168.1.0/24          md5
host    all             all             10.0.0.0/8              md5

# For development only - allow from anywhere (NOT recommended for production)
# host    all             all             0.0.0.0/0               md5
    Configure Firewall
    sudo ufw allow 5432/tcp
sudo ufw reload
    Restart PostgreSQL
    sudo systemctl restart postgresql

    🔒 Security Warning: Only allow remote access from trusted networks. Never use 0.0.0.0/0 in production environments.

    9

    Performance Tuning

    Optimize PostgreSQL for your RamNode VPS:

    Memory Configuration

    Optimal Memory Settings
    # For 2GB RAM VPS
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 8MB
maintenance_work_mem = 128MB

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

# For 8GB RAM VPS
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 32MB
maintenance_work_mem = 512MB

    Connection and Query Optimization

    Connection Pool Settings
    # Connection settings
max_connections = 100
superuser_reserved_connections = 3

# Query planner settings
random_page_cost = 1.1                   # For SSD storage
effective_io_concurrency = 200           # For SSD storage
default_statistics_target = 100

# Checkpoint settings
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
wal_buffers = 16MB
wal_writer_delay = 200ms
    Apply Configuration Changes
    sudo systemctl reload postgresql
# Or restart for major changes
sudo systemctl restart postgresql
    Monitor Performance
    # Check active connections
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

# Check database sizes
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

# View current settings
sudo -u postgres psql -c "SHOW shared_buffers;"
sudo -u postgres psql -c "SHOW effective_cache_size;"

    ⚡ Performance Tip: Monitor your database performance with pg_stat_statements extension to identify slow queries and optimize accordingly.

    10

    Backup Configuration

    Set up automated backups for your PostgreSQL databases:

    Create Backup Directory
    sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
    Create Backup Script
    sudo nano /usr/local/bin/pg_backup.sh
    PostgreSQL Backup Script
    #!/bin/bash

# PostgreSQL backup script
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DATABASES="myapp_db"  # Add your database names here

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Backup each database
for DATABASE in $DATABASES; do
    echo "Backing up database: $DATABASE"
    sudo -u postgres pg_dump $DATABASE | gzip > $BACKUP_DIR/${DATABASE}_backup_$DATE.sql.gz
done

# Backup all databases (alternative)
sudo -u postgres pg_dumpall | gzip > $BACKUP_DIR/all_databases_backup_$DATE.sql.gz

# Clean up old backups (keep 7 days)
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed: $DATE"
    Make Script Executable
    sudo chmod +x /usr/local/bin/pg_backup.sh
    Test Backup Script
    sudo /usr/local/bin/pg_backup.sh
    Schedule Daily Backups
    sudo crontab -e
# Add this line for daily backups at 2 AM
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

    Backup Restoration

    Restore Database from Backup
    # Restore a specific database
gunzip -c /var/backups/postgresql/myapp_db_backup_YYYYMMDD_HHMMSS.sql.gz | sudo -u postgres psql myapp_db

# Restore all databases
gunzip -c /var/backups/postgresql/all_databases_backup_YYYYMMDD_HHMMSS.sql.gz | sudo -u postgres psql postgres

    💾 Backup Tip: Test your backup restoration process regularly to ensure your backups are working correctly and your data is protected.

    11

    Monitoring and Maintenance

    Monitor your PostgreSQL installation:

    Enable Query Statistics
    sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
    Useful Monitoring Queries
    -- Check database connections
SELECT datname, numbackends FROM pg_stat_database;

-- Monitor slow queries
SELECT query, calls, total_time, mean_time 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) as size 
FROM pg_database 
ORDER BY pg_database_size(datname) DESC;

-- Monitor locks
SELECT locktype, database, relation, mode, granted 
FROM pg_locks 
WHERE NOT granted;

-- Check table statistics
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del 
FROM pg_stat_user_tables 
ORDER BY n_tup_ins DESC;
    Create Monitoring Script
    nano /usr/local/bin/pg_monitor.sh
    PostgreSQL Monitoring Script
    #!/bin/bash

echo "=== PostgreSQL Status ==="
sudo systemctl status postgresql --no-pager -l

echo -e "\n=== Active Connections ==="
sudo -u postgres psql -c "SELECT count(*) as active_connections FROM pg_stat_activity WHERE state = 'active';"

echo -e "\n=== Database Sizes ==="
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database WHERE datistemplate = false;"

echo -e "\n=== Cache Hit Ratio ==="
sudo -u postgres psql -c "SELECT datname, blks_read, blks_hit, round((blks_hit::float/(blks_read+blks_hit)*100)::numeric, 2) as cache_hit_ratio FROM pg_stat_database WHERE datname NOT IN ('template0','template1');"

echo -e "\n=== Long Running Queries ==="
sudo -u postgres psql -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes' AND state = 'active';"

echo -e "\n=== Disk Usage ==="
df -h | grep -E '(Filesystem|/$|/var)'
    Make Monitoring Script Executable
    chmod +x /usr/local/bin/pg_monitor.sh
./usr/local/bin/pg_monitor.sh

    📊 Monitoring Tip: Set up log rotation and consider using tools like pgAdmin, Grafana, or custom dashboards for comprehensive monitoring.

    12

    Troubleshooting

    Common issues and solutions:

    🛠️ Support: For additional help, check PostgreSQL documentation, community forums, or contact RamNode support for VPS-specific issues.

    🎉 PostgreSQL 18 Successfully Installed!

    Your PostgreSQL 18 database server is now running on RamNode VPS with enhanced performance, security, and modern features. You're ready to deploy production applications with confidence.

    Production Ready
    Secure Setup
    Performance Optimized