PostgreSQL kolimine Pilviole

Selles juhendis kolime olemasoleva PostgreSQL andmebaasi Pilvio VM-ile, seadistame automaatse varundamise ja tulemüüri.

Mida ehitame

PostgreSQL 16 server Pilvio VM-il
Andmete migreerimine olemasolevalt serverilt
Automaatne varundamine Pilvio StorageVault'i (S3)
Tulemüürireeglid andmebaasi kaitsmiseks

Eeldused

Pilvio konto ja API token (vaata ülevaadet)
Ligipääs olemasolevale PostgreSQL serverile (kui kolid andmeid)
pg_dump ja psql tööriistad

1. samm: VM loomine andmebaasile

Andmebaasi jaoks soovitame eraldi VM-i koos piisava kettaruumiga:

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"

Lisakettad andmete jaoks (valikuline, soovituslik)

Eralda andmebaasi andmed süsteemikettalt:

# Loo eraldi ketas andmete jaoks
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"

Serveris kettamount:

ssh deploy@SINU_FLOATING_IP

# Leia uue ketta seadme nimi (tüüpiliselt /dev/vdb)
lsblk

# Formaadi ja monteeri
sudo mkfs.ext4 /dev/vdb
sudo mkdir -p /mnt/pgdata
sudo mount /dev/vdb /mnt/pgdata

# Lisa fstab-i püsivaks monteerimiseks
echo '/dev/vdb /mnt/pgdata ext4 defaults 0 2' | sudo tee -a /etc/fstab

2. samm: Tulemüüri seadistamine

Luba PostgreSQL port ainult teatud IP-delt (sinu rakendusserveritelt):

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"]
      }
    ]
  }'

Turvalisus: Ära ava PostgreSQL porti kogu internetile. Kasuta Pilvio privaatvõrku VM-ide vaheliseks suhtluseks.

3. samm: PostgreSQL paigaldamine ja seadistamine

ssh deploy@SINU_FLOATING_IP

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

# Peata teenus seadistamiseks
sudo systemctl stop postgresql

Andmete kataloog eraldi kettale (kui lõid 1. sammus lisaketta)

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

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

PostgreSQL seadistamine

# Luba väliseid ühendusi
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" \
  /etc/postgresql/16/main/postgresql.conf

# Jõudluse optimeerimine (4 GB RAM VM jaoks)
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

# Autentimine — luba ühendused sinu võrgust
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

# Käivita
sudo systemctl start postgresql

Kasutaja ja andmebaasi loomine

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

4. samm: Andmete migreerimine

Väike andmebaas (< 1 GB): pg_dump/pg_restore

# Vanalt serverilt: ekspordi
pg_dump -h vana-server.ee -U vana_kasutaja -d vana_andmebaas \
  -Fc -f /tmp/dump.backup

# Kopeeri Pilvio serverisse
scp /tmp/dump.backup deploy@SINU_FLOATING_IP:/tmp/

# Pilvio serveris: impordi
ssh deploy@SINU_FLOATING_IP
sudo -u postgres pg_restore -d myapp /tmp/dump.backup

Suur andmebaas (> 1 GB): striimimine

# Otse vanalt serverilt Pilvio serverisse
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"

Ainult skeem (ilma andmeteta)

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"

5. samm: Automaatne varundamine StorageVault'i (S3)

Loo fail /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: iga päev kell 3:00
(crontab -l 2>/dev/null; echo "0 3 * * * /home/deploy/backup-pg.sh >> /var/log/pg-backup.log 2>&1") | crontab -

Pilvio automaatne VM backup (lisaks)

# Lülita sisse Pilvio VM-i automaatne varundamine
curl "https://api.pilvio.com/v1/user-resource/vm/backup" \
  -H "apikey: SINU_PILVIO_TOKEN" \
  -X POST \
  -d "uuid=SINU_VM_UUID"

6. samm: Taastamine varukoopiast

# Lae varukoopia alla StorageVault'ist
aws s3 cp s3://minu-pg-backups/postgresql/20250211-030000.sql.gz /tmp/ \
  --endpoint-url https://s3.pilvio.com:8080

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

Ühendamine rakendusega

# Ühendustring (kasuta privaatset IP-d kui sama võrgus)
postgresql://app_user:tugev-parool@10.x.x.x:5432/myapp

# Või Floating IP kaudu (aeglasem, tulemüür peab lubama)
postgresql://app_user:tugev-parool@SINU_FLOATING_IP:5432/myapp

Järgmised sammud: Ühenda PostgreSQL oma Node.js või FastAPI backendiga.