PostgreSQL on a MicroVM

PostgreSQL is lighter than MySQL out of the box and has more advanced features. It is well suited to MicroVMs with limited resources.

Preparing the Image

# Mount the image
sudo mount -o loop rootfs.img /mnt/microvm

# Debian/Ubuntu
sudo chroot /mnt/microvm bash -c '
  apt update && apt install -y postgresql postgresql-client
'

# Alpine
sudo chroot /mnt/microvm bash -c '
  apk add postgresql postgresql-client
  mkdir -p /var/lib/postgresql/data
  chown postgres:postgres /var/lib/postgresql/data
  su - postgres -c "initdb -D /var/lib/postgresql/data"
'

sudo umount /mnt/microvm

Optimal Configuration

sudo mount -o loop rootfs.img /mnt/microvm

sudo tee /mnt/microvm/etc/postgresql/15/main/postgresql.conf << 'PGCONF'
# Memory
shared_buffers = 64MB
effective_cache_size = 128MB
work_mem = 2MB
maintenance_work_mem = 32MB

# Write Ahead Log
wal_buffers = 4MB
checkpoint_completion_target = 0.9
max_wal_size = 256MB
min_wal_size = 64MB

# Connections
max_connections = 50
superuser_reserved_connections = 3

# Query Planner
random_page_cost = 1.1
effective_io_concurrency = 200

# Logging
logging_collector = off
log_min_duration_statement = 1000

# Disable expensive features
ssl = off
PGCONF

sudo tee /mnt/microvm/etc/postgresql/15/main/pg_hba.conf << 'PGHBA'
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             0.0.0.0/0               md5
host    all             all             ::1/128                 md5
PGHBA

sudo umount /mnt/microvm

Running the MicroVM

qemu-system-x86_64 \
  -M microvm -enable-kvm -cpu host -m 512m -smp 2 \
  -kernel vmlinuz \
  -append "console=ttyS0 root=/dev/vda rw" \
  -drive id=rootfs,file=rootfs.img,format=raw,if=none,cache=none,aio=linux \
  -device virtio-blk-device,drive=rootfs \
  -netdev user,id=net0,hostfwd=tcp::5432-:5432 \
  -device virtio-net-device,netdev=net0 \
  -nographic -serial stdio

Setting Up the Guest

# Start PostgreSQL (Debian/Ubuntu)
service postgresql start

# Or Alpine
su - postgres -c "pg_ctl -D /var/lib/postgresql/data start"

# Create a user and a database
su - postgres -c "psql -c \"CREATE USER app_user WITH PASSWORD 'secure_password';\""
su - postgres -c "psql -c \"CREATE DATABASE app_db OWNER app_user;\""
su - postgres -c "psql -c \"GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;\""

# Test the connection
psql -h localhost -U app_user -d app_db

Auto Start (init.d Alpine)

sudo tee /mnt/microvm/etc/init.d/postgresql << 'PGINIT'
#!/sbin/openrc-run

PGDATA=/var/lib/postgresql/data
PGUSER=postgres

depend() {
    need net
    keyword timeout 300
}

start() {
    ebegin "Starting PostgreSQL"
    su - $PGUSER -c "pg_ctl -D $PGDATA -l /var/log/postgresql.log start"
    eend $?
}

stop() {
    ebegin "Stopping PostgreSQL"
    su - $PGUSER -c "pg_ctl -D $PGDATA stop -m fast"
    eend $?
}
PGINIT

sudo chmod +x /mnt/microvm/etc/init.d/postgresql
sudo chroot /mnt/microvm rc-update add postgresql default

Backup

# Logical backup
pg_dump -h localhost -U app_user app_db > backup.sql

# All databases
pg_dumpall -h localhost -U postgres > all_backup.sql

Performance Tuning

# Check connection statistics
psql -c "SELECT * FROM pg_stat_activity;"

# Check database sizes
psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"

# Check index usage
psql -c "SELECT schemaname, tablename, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan DESC;"

Popular Extensions

psql -d app_db -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
psql -d app_db -c "CREATE EXTENSION IF NOT EXISTS uuid-ossp;"
psql -d app_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Troubleshooting

Cannot connect

# Check that PostgreSQL is running
ps aux | grep postgres

# Check the port
ss -tlnp | grep 5432

# Check pg_hba.conf
cat /etc/postgresql/15/main/pg_hba.conf

Out of memory

# Reduce shared_buffers
shared_buffers = 32MB
effective_cache_size = 64MB
work_mem = 1MB
maintenance_work_mem = 16MB

# Disable statistics collector
track_activities = off
track_counts = off