Postgres & PgBouncer
# DB Version: 15
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 2 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd
max_connections = 100
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2621kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2creating the pgbouncer admin user:
DB_PASSWORD=$(< /dev/urandom tr -dc A-Za-z0-9 | head -c32; echo)
echo "pgbouncer password (save this securely): $DB_PASSWORD"
echo "CREATE USER pgbouncer WITH PASSWORD '$DB_PASSWORD' CREATEDB" | sudo -u postgres psql -f -Mastodon db:migrates should be pointed directly at Postgres (default port: 5432), not through PgBouncer, by overriding DB_PORT env variable.
RAILS_ENV=production DB_PORT=5432 bundle exec rails db:migrateDB_HOST=localhost
DB_USER=mastodon
DB_NAME=mastodon_production
DB_PASS=
# change from postgres port (default: 5432) to pgbouncer (default: 6432)
DB_PORT=6432
# add this:
PREPARED_STATEMENTS=false[databases]
mastodon_production = host=127.0.0.1 port=5432 dbname=mastodon_production user=mastodon password=
[pgbouncer]
listen_addr = localhost
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20generate md5 hash of postgres "password + username" with echo -n "passwordusername" | md5sum
"mastodon" "md5xxxxxxxx"
"pgbouncer" "md5xxxxxxxx"
Connect directly to Postgres (default port: 5432), not via PgBouncer!
listen_addresses = '*'# TYPE DATABASE USER ADDRESS METHOD
# tailscale
host all all 100.64.0.0/10 md5