Host Your Own Postgres

elephant Get running with the last version of Postgres in minutes

Set Up Server

Spin up a new server with Ubuntu 16.04.

Firewall

sudo ufw allow ssh
sudo ufw enable

Automatic updates

sudo apt-get -y install unattended-upgrades
echo 'APT::Periodic::Unattended-Upgrade "1";' >> /etc/apt/apt.conf.d/10periodic

Time zone

sudo dpkg-reconfigure tzdata

and select None of the above, then UTC.

Install Postgres

Install PostgreSQL 10

echo "deb https://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 -
sudo apt-get update
sudo apt-get install -qq -y postgresql-10 postgresql-contrib

Configure

Edit /etc/postgresql/10/main/postgresql.conf.

# general
max_connections = 100

# logging
log_min_duration_statement = 100 # log queries over 100ms
log_temp_files = 0               # log all temp files

# stats
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000

Remote Connections

Enable remote connections if needed

echo "host all all 0.0.0.0/0 md5" >> /etc/postgresql/9.6/main/pg_hba.conf
echo "listen_addresses = '*'" >> /etc/postgresql/9.6/main/postgresql.conf
sudo service postgresql restart

And update the firewall

sudo ufw allow 5432/tcp # for all ips
sudo ufw allow from 127.0.0.1 to any port 5432 proto tcp # specific ip
sudo ufw enable

Provisioning

Create a new user and database for each of your apps

sudo su - postgres
psql

And run:

CREATE USER myapp WITH PASSWORD 'mypassword';
ALTER USER myapp WITH CONNECTION LIMIT 20;
CREATE DATABASE myapp_production OWNER myapp;

Generate a random password with:

cat /dev/urandom | LC_CTYPE=C tr -dc 'a-zA-Z0-9' | fold -w 32 | head -n 1

Backups

Daily

Store backups on S3

TODO: better instructions

Continuous

Rollback to a specific point in time with WAL-E.

Opbeat has a great tutorial.

Logging

Papertrail is great and has a free plan.

Install remote syslog

cd /tmp
wget https://github.com/papertrail/remote_syslog2/releases/download/v0.13/remote_syslog_linux_amd64.tar.gz
tar xzf ./remote_syslog*.tar.gz
cd remote_syslog
sudo cp ./remote_syslog /usr/local/bin

Create /etc/log_files.yml with:

files:
  - /var/log/postgresql/*.log
destination:
  host: logs.papertrailapp.com
  port: 12345
  protocol: tls

Archive

Archive logs to S3

sudo apt-get install logrotate s3cmd
s3cmd --configure

Add to /etc/logrotate.d/postgresql-common:

sharedscripts
postrotate
  s3cmd sync /var/log/postgresql/*.gz s3://mybucket/logs/
endscript

Test with:

logrotate -fv /etc/logrotate.d/postgresql-common

TODO

  pghost bootstrap
  pghost allow all
  pghost allow 127.0.0.1
  pghost backup:all
  pghost backup myapp
  pghost restore myapp
  pghost provision myapp
  pghost logs:syslog logs.papertrailapp.com 12345
  pghost logs:archive mybucket/logs

Resources

Published May 31, 2015


You might also enjoy

Rails on Heroku

Adding CSP to Rails

Anonymizing IPs in Ruby


All code examples are public domain.
Use them however you’d like (licensed under CC0).