Postgres
SQL
Vagrant

Creating a simple Postgres VM for development

Posted on Nov 12, 2016 by Alexej Bondarenko

This guide will lead you through the steps on how to easily set up a Postgres database for development with Vagrant. We will cover the basic VM setup, adding repositories and setting up a database for development.

Versions used in this tutorial:

  • Ununtu 14.04 LTS
  • Vagrant 1.8.1
  • Postgres 9.6

Step1 - Setting up a VM

First of all we will set up and prepare our VM. We will use the LTS version of Ubuntu (Version 14.04). (If you would like to see any guides for other distributions, like Fedora or Debian, just drop a request in the commenting section). We will set the box to:

# Every Vagrant development environment requires a box. You can search for
# boxes at https://atlas.hashicorp.com/search.
config.vm.box = "ubuntu/trusty64"

Since we are up to use Postgres, we need to forward the port to our host system. At Ersocon we usually use a different port for each project. So lets choose port 5436 for our development database like this:

# Create a forwarded port mapping which allows access to a specific port
# within the machine from a port on the host machine. In the example below,
# accessing "localhost:8080" will access port 80 on the guest machine.
config.vm.network "forwarded_port", guest: 5432, host: 5436

We are going to use the simplest way for provisioning (simplest in terms of installation). Hence, we will define two provisioning steps. First one for a basic VM configuration and the second one for Postgres setup:

config.vm.provision :shell, path: "provision_bootstrap.sh"
config.vm.provision :shell, path: "provision_postgresql.sh"

That should be enough to get us going.

Step2 - Bootstrapping the VM

This part is not too complicated. We will just call default apt-get commands and set up locales, so we will just post the following code to our provision_bootstrap.sh file.

sudo sed -i -e 's,    SendEnv LANG LC_*,#   SendEnv LANG LC_*,g' /etc/ssh/ssh_config
sudo service ssh restart

sudo apt-get update
sudo apt-get install -y language-pack-en
sudo update-locale LC_ALL=en_US.UTF-8
sudo update-locale LANGUAGE=en_US.UTF-8

sudo update-rc.d chef-client disable
sudo update-rc.d puppet disable

Step3 - Setting up Postgres

(The following steps are all placed into provision_postgres.sh)

To be able to install Postgres, we need to add the according repository first. Take care that we are adding the 9.6 version of our Postgres installation.

# Install the postgres key
echo "Importing PostgreSQL key and installing software"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 9.6" >> /etc/apt/sources.list.d/pgdg.list

Now that we have told Ubuntu where to fetch Postgres we can easily install our database on the VM:

sudo apt-get update
sudo apt-get -y install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6

Sidenote: The -y option will auto comfirm the installation.

The previous step will install Postgres with the default user postgres. But you won't be able to log in, since by default the user has no password set (only local access is granted). If you would like to change this (it's optional) you can add the following line to the provisioning script:

sudo -u postgres psql postgres -c "ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres'"

Usually you don't want to use the default postgres user as your application user. Let's create a new user and the develpment database:

sudo -u postgres psql postgres -c "CREATE USER myproject_dev WITH ENCRYPTED PASSWORD 'myproject_dev'"
sudo -u postgres psql postgres -c "CREATE DATABASE myproject_dev OWNER 'myproject_dev'"

That's it. Now we have the following set up:

  • Username: myproject_dev
  • Database: myproject_dev
  • Password: myproject_dev

Those are the credentials you could use in your project now. (Of course, change names to your needs)

What we need to do now is grant username/password as an authentication option and accept the connection from all hosts:

# Allow access from outside and enable logging
sudo echo "listen_addresses = '*'" >> /etc/postgresql/9.6/main/postgresql.conf
sudo echo "logging_collector = on" >> /etc/postgresql/9.6/main/postgresql.conf

# Edit to allow socket access, not just local unix access
echo "Patching pg_hba to change -> socket access"
sudo echo "host all all all md5" >> /etc/postgresql/9.6/main/pg_hba.conf

# Restart Postgres to apply all change we made
sudo service postgresql restart

Sidenote: We used default Postgres commands to create a new user and a database. You can enable special packages by using this method as well.

I hope this tutorial helped you to set up your own Postgres DEV VM with vagrant. If you find any issues or have any questions regarding configuration, just leave a comment in the section below.