Install PostgreSQL 18 on RamNode

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

Access Requirements

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

ssh root@your-server-ip

sudo apt update
sudo apt upgrade -y

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

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

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

sudo apt update

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

sudo systemctl status postgresql
psql --version

sudo systemctl enable postgresql
sudo systemctl start postgresql

sudo -i -u postgres

psql

-- 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

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

# 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

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

# 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

sudo systemctl restart postgresql

sudo -u postgres psql

-- 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

psql -h localhost -U myapp_user -d myapp_db

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

# Allow connections from any IP address
listen_addresses = '*'
# Or specify specific IP addresses
# listen_addresses = 'localhost,192.168.1.100'

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

# 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

sudo ufw allow 5432/tcp
sudo ufw reload

sudo systemctl restart postgresql

# 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 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

sudo systemctl reload postgresql
# Or restart for major changes
sudo systemctl restart postgresql

# 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;"

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql

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

#!/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"

sudo chmod +x /usr/local/bin/pg_backup.sh

sudo /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 >> /var/log/pg_backup.log 2>&1

# 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

sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

-- 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;

nano /usr/local/bin/pg_monitor.sh

#!/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)'

chmod +x /usr/local/bin/pg_monitor.sh
./usr/local/bin/pg_monitor.sh

Cannot Connect to PostgreSQL

Slow Performance or High Memory Usage

Running Out of Disk Space

Authentication and Permission Issues