Benchmark postgres servers

postgres-benchmark.sh

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install 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