upgrade-postgres-dokke.sh

#!/usr/bin/env bash
set -ex

SERVICE_NAME="resident-cabinet-back"
OLD_NAME="resident-cabinet-back"
NEW_NAME="resident-cabinet-back-new"
DATA_DIR="/var/lib/dokku/services/postgres/$NEW_NAME/data/"

POSTGRES_IMAGE="mdillon/postgis"  POSTGRES_IMAGE_VERSION="11" dokku postgres:create $NEW_NAME

# move data to big disk
dokku postgres:stop $NEW_NAME

# move postgres data to onother folder/disk
mv /var/lib/dokku/services/postgres/$NEW_NAME/data $DATA_DIR
ln -s $DATA_DIR /var/lib/dokku/services/postgres/$NEW_NAME/data

# fine tune postgres server and add replication settings
cat <<EOF >> $DATA_DIR/postgresql.conf
# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 46 GB
# CPUs num: 16
# Connections num: 1000
# Data Storage: ssd

max_connections = 1000
shared_buffers = 11776MB
effective_cache_size = 35328MB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1507kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

# Replication settings
wal_level = replica
archive_mode = on
archive_command = '/bin/true'
max_wal_senders = 8
wal_keep_segments = 64
EOF

cat <<EOF >> $DATA_DIR/pg_hba.conf
host replication all 0.0.0.0/0 md5
EOF

# start database
dokku postgres:start $NEW_NAME

# stop service to prevent getting new data
dokku ps:stop $SERVICE_NAME

# import data from old database instance
time dokku postgres:export $OLD_NAME | dokku postgres:import $NEW_NAME

# link new database instead of old one
dokku postgres:unlink $OLD_NAME $SERVICE_NAME
dokku postgres:link $NEW_NAME $SERVICE_NAME

dokku ps:start $SERVICE_NAME