Migrating PostgreSQL to Pilvio

In this tutorial, we will migrate an existing PostgreSQL database to a Pilvio VM, set up automatic backups, and configure a firewall.

What we will build

PostgreSQL 16 server on a Pilvio VM
Data migration from an existing server
Automatic backups to Pilvio StorageVault (S3)
Firewall rules to protect the database

Prerequisites

Pilvio account and API token (see overview)
Access to an existing PostgreSQL server (if migrating data)
pg_dump and psql tools

Step 1: Creating a VM for the database

We recommend a dedicated VM with sufficient disk space for the database:

curl "https://api.pilvio.com/v1/user-resource/vm" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -X POST \
  -d "name=postgresql-server" \
  -d "os_name=ubuntu" \
  -d "os_version=24.04" \
  -d "vcpu=2" \
  -d "ram=4096" \
  -d "disks=50" \
  -d "username=deploy" \
  -d "password=TurvalineParool123!" \
  -d "public_key=ssh-ed25519 AAAA... sinu@arvuti"

Additional disks for data (optional, recommended)

Separate database data from the system disk:

# Create a separate disk for data
curl "https://api.pilvio.com/v1/user-resource/vm/storage" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -X POST \
  -d "uuid=SINU_VM_UUID" \
  -d "size_gb=100"

Mount the disk on the server:

ssh deploy@SINU_FLOATING_IP

# Find the new disk device name (typically /dev/vdb)
lsblk

# Format and mount
sudo mkfs.ext4 /dev/vdb
sudo mkdir -p /mnt/pgdata
sudo mount /dev/vdb /mnt/pgdata

# Add to fstab for persistent mounting
echo '/dev/vdb /mnt/pgdata ext4 defaults 0 2' | sudo tee -a /etc/fstab

Step 2: Configuring the firewall

Allow the PostgreSQL port only from specific IPs (your application servers):

curl "https://api.pilvio.com/v1/network/firewall" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -H "Content-Type: application/json" \
  -X POST \
  --data '{
    "name": "postgresql-fw",
    "rules": [
      {
        "protocol": "tcp",
        "direction": "inbound",
        "port_start": 22,
        "port_end": 22,
        "endpoint_spec_type": "any"
      },
      {
        "protocol": "tcp",
        "direction": "inbound",
        "port_start": 5432,
        "port_end": 5432,
        "endpoint_spec_type": "ip_prefixes",
        "endpoint_spec": ["SINU_APP_SERVERI_IP/32", "10.0.0.0/8"]
      }
    ]
  }'

Security: Do not open the PostgreSQL port to the entire internet. Use the Pilvio private network for communication between VMs.

Step 3: Installing and configuring PostgreSQL

ssh deploy@SINU_FLOATING_IP

# Install PostgreSQL 16
sudo apt-get update
sudo apt-get install -y postgresql-16 postgresql-client-16

# Stop the service for configuration
sudo systemctl stop postgresql

Data directory on a separate disk (if you created an additional disk in Step 1)

sudo chown postgres:postgres /mnt/pgdata
sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /mnt/pgdata/16/main

# Update configuration
sudo sed -i "s|data_directory = '.*'|data_directory = '/mnt/pgdata/16/main'|" \
  /etc/postgresql/16/main/postgresql.conf

Configuring PostgreSQL

# Allow external connections
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" \
  /etc/postgresql/16/main/postgresql.conf

# Performance tuning (for a 4 GB RAM VM)
sudo tee -a /etc/postgresql/16/main/postgresql.conf <<'EOF'

# Pilvio jõudluse seadistused
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
work_mem = 16MB
wal_buffers = 16MB
max_connections = 100
random_page_cost = 1.1
effective_io_concurrency = 200
EOF

# Authentication — allow connections from your network
echo "host all app_user 10.0.0.0/8 scram-sha-256" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
echo "host all app_user SINU_APP_SERVERI_IP/32 scram-sha-256" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf

# Start
sudo systemctl start postgresql

Creating user and database

sudo -u postgres psql <<'SQL'
CREATE USER app_user WITH PASSWORD 'tugev-parool-siia';
CREATE DATABASE myapp OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;
SQL

Step 4: Migrating data

Small database (< 1 GB): pg_dump/pg_restore

# From the old server: export
pg_dump -h vana-server.ee -U vana_kasutaja -d vana_andmebaas \
  -Fc -f /tmp/dump.backup

# Copy to Pilvio server
scp /tmp/dump.backup deploy@SINU_FLOATING_IP:/tmp/

# On Pilvio server: import
ssh deploy@SINU_FLOATING_IP
sudo -u postgres pg_restore -d myapp /tmp/dump.backup

Large database (> 1 GB): streaming

# Directly from old server to Pilvio server
pg_dump -h vana-server.ee -U vana_kasutaja -d vana_andmebaas -Fc | \
  ssh deploy@SINU_FLOATING_IP "sudo -u postgres pg_restore -d myapp"

Schema only (without data)

pg_dump -h vana-server.ee -U vana_kasutaja -d vana_andmebaas \
  --schema-only -f schema.sql
scp schema.sql deploy@SINU_FLOATING_IP:/tmp/
ssh deploy@SINU_FLOATING_IP "sudo -u postgres psql myapp < /tmp/schema.sql"

Step 5: Automatic backups to StorageVault (S3)

Create the file /home/deploy/backup-pg.sh:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/tmp/pg-backups"
BUCKET="minu-pg-backups"
S3_ENDPOINT="https://s3.pilvio.com:8080"
DATE=$(date +%Y%m%d-%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/myapp-${DATE}.sql.gz"

mkdir -p "$BACKUP_DIR"

# Dump ja tihendamine
sudo -u postgres pg_dump myapp | gzip > "$BACKUP_FILE"

# Üleslaadimine StorageVault'i
aws s3 cp "$BACKUP_FILE" "s3://${BUCKET}/postgresql/${DATE}.sql.gz" \
  --endpoint-url "$S3_ENDPOINT"

# Kohalike failide puhastamine (hoia viimased 3)
ls -t ${BACKUP_DIR}/myapp-*.sql.gz | tail -n +4 | xargs rm -f

# Kustuta S3-st vanemad kui 30 päeva
aws s3 ls "s3://${BUCKET}/postgresql/" --endpoint-url "$S3_ENDPOINT" | \
  awk '{print $4}' | while read -r file; do
    file_date=$(echo "$file" | grep -oP '\d{8}')
    if [[ $(date -d "$file_date" +%s) -lt $(date -d '30 days ago' +%s) ]]; then
      aws s3 rm "s3://${BUCKET}/postgresql/$file" --endpoint-url "$S3_ENDPOINT"
    fi
  done

echo "Varundamine lõpetatud: $BACKUP_FILE"

chmod +x /home/deploy/backup-pg.sh

# Cron: every day at 3:00
(crontab -l 2>/dev/null; echo "0 3 * * * /home/deploy/backup-pg.sh >> /var/log/pg-backup.log 2>&1") | crontab -

Pilvio automatic VM backup (additionally)

# Enable Pilvio VM automatic backups
curl "https://api.pilvio.com/v1/user-resource/vm/backup" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -X POST \
  -d "uuid=SINU_VM_UUID"

Step 6: Restoring from a backup

# Download backup from StorageVault
aws s3 cp s3://minu-pg-backups/postgresql/20250211-030000.sql.gz /tmp/ \
  --endpoint-url https://s3.pilvio.com:8080

# Restore
gunzip -c /tmp/20250211-030000.sql.gz | sudo -u postgres psql myapp

Connecting to the application

# Connection string (use private IP if on the same network)
postgresql://app_user:tugev-parool@10.x.x.x:5432/myapp

# Or via Floating IP (slower, firewall must allow it)
postgresql://app_user:tugev-parool@SINU_FLOATING_IP:5432/myapp

Next steps: Connect PostgreSQL to your Node.js or FastAPI backend.