Benchmark postgres servers

postgres-benchmark.sh

touch /etc/apt/sources.list.d/pgdg.list
echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update
apt-get install -y postgresql-10




# now go to pgtune for config
# https://pgtune.leopard.in.ua/#/

# Stop the service
service postgresql stop

# Only on GCP
# - Mount the SSD
sudo mkfs.ext4 -F /dev/sdb
sudo mkdir -p /mnt/disks/sdb
sudo mount /dev/sdb /mnt/disks/sdb
sudo chmod a+w /mnt/disks/sdb
# - Move postgres data folder
mv /var/lib/postgresql /mnt/disks/sdb/
mkdir -p /var/lib/postgresql


vim /etc/postgresql/10/main/postgresql.conf
# Paste the settings
# If you're on GCP also change data directory
# data_directory = '/mnt/disks/sdb/postgresql/10/main'
# Also change work_mem to 24MB

# Start the service
service postgresql start

# Now run pgbench
# - Create pgbench db
sudo su postgres
psql -c "CREATE DATABASE pgbench"
pgbench -i pgbench
pgbench -c 100 -j 2 -t 1000 pgbench