Database Configuration

SolidPing supports multiple database backends. PostgreSQL is recommended for production, while SQLite is great for testing and small deployments.

Database Types

Type	Description	Use Case
postgres	External PostgreSQL	Production, multi-instance
sqlite	SQLite file database	Single instance, simple deployments
sqlite-memory	In-memory SQLite	Testing only
postgres-embedded	Embedded PostgreSQL	Testing only

PostgreSQL (Recommended)

PostgreSQL 15+ is recommended for production deployments.

Configuration

SP_DB_TYPE=postgres
SP_DB_URL=postgresql://user:password@host:port/database?sslmode=disable

Connection String Format

postgresql://[user]:[password]@[host]:[port]/[database]?[options]

Options:

• sslmode=disable - No SSL (development only)
• sslmode=require - Require SSL
• sslmode=verify-full - Verify SSL certificate

Examples

# Local development
SP_DB_URL=postgresql://solidping:solidping@localhost:5432/solidping?sslmode=disable

# Docker network
SP_DB_URL=postgresql://solidping:password@postgres:5432/solidping?sslmode=disable

# Cloud provider with SSL
SP_DB_URL=postgresql://user:password@db.example.com:5432/solidping?sslmode=require

# With connection pooling
SP_DB_URL=postgresql://user:password@pgbouncer:6432/solidping?sslmode=disable

PostgreSQL Setup

Create the database and user:

-- Create user
CREATE USER solidping WITH PASSWORD 'your-secure-password';

-- Create database
CREATE DATABASE solidping OWNER solidping;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE solidping TO solidping;

Recommended PostgreSQL Settings

For production, consider these PostgreSQL settings in postgresql.conf:

# Memory
shared_buffers = 256MB
effective_cache_size = 768MB
work_mem = 16MB

# Connections
max_connections = 100

# Write performance
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Query planning
random_page_cost = 1.1  # For SSDs

SQLite

SQLite is suitable for single-instance deployments or testing.

Configuration

SP_DB_TYPE=sqlite
SP_DB_DIR=/path/to/data/directory

The database file will be created at $SP_DB_DIR/solidping.db.

Examples

# Current directory
SP_DB_TYPE=sqlite
SP_DB_DIR=.

# Specific directory
SP_DB_TYPE=sqlite
SP_DB_DIR=/var/lib/solidping

# Docker volume
SP_DB_TYPE=sqlite
SP_DB_DIR=/data

SQLite Limitations

• Single writer at a time (concurrent reads are fine)
• Not suitable for distributed deployments
• Limited to ~1TB database size
• No built-in replication

When to Use SQLite

• Single-instance deployments
• Development and testing
• Low to medium check volume (under 1000 checks/minute)
• Simple infrastructure requirements

Migrations

Migrations run automatically on startup. You can also run them manually:

./solidping migrate

Migration Commands

# Run pending migrations
./solidping migrate up

# Rollback last migration
./solidping migrate down

# Check migration status
./solidping migrate status

Backup and Restore

PostgreSQL

# Backup
pg_dump -U solidping -h localhost solidping > backup.sql

# Restore
psql -U solidping -h localhost solidping < backup.sql

SQLite

# Backup (while running - uses SQLite backup API)
sqlite3 /data/solidping.db ".backup '/backup/solidping-$(date +%Y%m%d).db'"

# Simple copy (stop the server first)
cp /data/solidping.db /backup/solidping-backup.db

Troubleshooting

Connection Issues

# Test PostgreSQL connection
psql -U solidping -h localhost -d solidping -c "SELECT 1"

# Check if port is open
nc -zv localhost 5432

Permission Errors

For PostgreSQL:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO solidping;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO solidping;

For SQLite:

# Ensure the directory is writable
chmod 755 /data
chown solidping:solidping /data

Performance

If experiencing slow queries:

1. Check database size: SELECT pg_database_size('solidping');
2. Run VACUUM: VACUUM ANALYZE;
3. Check for missing indexes
4. Review connection pool settings