www.rozumim.cz

Run your own PostgreSQL server using Vagrant and Puppet

Do you want to experiment with a PostgreSQL server? Do you wish to simply tell the computer: “I want an PostgreSQL server” without the need to find out, how exactly one have to install it? Because that’s what computers are for, right? Then keep on reading.

Vagrant, Puppet and PostgreSQL … what are they?

Vagrant    “Create and configure lightweight, reproducible, and portable development environments.”
     
Puppet    “Puppet is a configuration management system that allows you to define the state of your IT infrastructure, then automatically enforces the correct state.”
     
PostgreSQL    “The world’s most advanced open source database.”
     

Vagrant setup

First, install Vagrant. After that, create a default Vagrantfile for the configuration of your new virtual machine:

1
> vagrant init

Starting the virtual machine (vagrant up) will end with an error:

1
2
3
4
5
6
7
8
9
10
11
12
13
> vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'base' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Adding box 'base' (v0) for provider: virtualbox
    default: Downloading: base

An error occurred while downloading the remote file. The error
message, if any, is reproduced below. Please fix this error and try
again.

Couldn't open file /(...)/box

This means that in your Vagrantfile is defined a box (a base for new virtual machine) called box and vagrant doesn’t know, where to find it. Open Vagrantfile and change:

1
config.vm.box = "base"

to

1
config.vm.box = "ubuntu/trusty64"

Run vagrant up again and if you don’t have that box on your disc already, Vagrant will download it for you:

1
2
3
4
5
==> default: Loading metadata for box 'ubuntu/trusty64'
    default: URL: https://vagrantcloud.com/ubuntu/trusty64
==> default: Adding box 'ubuntu/trusty64' (v14.04) for provider: virtualbox
    default: Downloading: https://vagrantcloud.com/ubuntu/boxes/trusty64/versions/1/providers/virtualbox.box
    default: Progress: 2% (Rate: 1117k/s, Estimated time remaining: 0:10:20))

You can see all the boxes that your vagrant installation has downloaded with the command vagrant box list. You can find more boxes online and add another box with vagrant box add box/name.

Now we have the virtual machine running. How can we setup PostgreSQL in there? I recommend using a tool called Puppet, that allows you to specify, what software, services and settings you need (in declarative fashion) and it will install and configure them for you.

Puppet setup

Vagrant supports using Puppet for exactly this purpose, but u need to manually install it first:

Does vagrant automatically install puppet?

No, (at the moment) Vagrant doesn’t install it automatically. So you either need to use a basebox which already has it installed (Puppet Labs provides boxes too), or you need to install it yourself. Probably the easiest way to install is to use shell provisioner before the puppet provisioner(s).

[SO]

To install puppet (and PostgreSQL as well) in your virtual machine, add the following lines in your Vagrantfile:

1
2
3
4
5
6
7
8
9
10
  config.vm.provision "shell", :inline => <<-SHELL
    apt-get update
    apt-get install -y puppet
    puppet module install puppetlabs/postgresql 
  SHELL

  config.vm.provision "puppet" do |puppet|
    puppet.manifest_file  = "build.pp"
    puppet.module_path = "modules"
  end

This will tell Vagrant to use a shell script first to install puppet, then puppet PostgreSQL module and at last use puppet provisioner to set up the rest.

Now create a file manifests/build.pp and a folder modules in the same folder, where your Vagrantfile is.

Puppet will use base.pp as a source of your virtual machine configuration. Here is what belongs in there:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# global PostgreSQL settings
class { 'postgresql::globals':
  encoding => 'UTF8',
  # locale   => 'cs_CZ.UTF-8',  # see below, how to install locale
  # version  => '9.3',          # default
}

class { 'postgresql::server': 
  listen_addresses           => '*',
  postgres_password          => 'postgrespassword',
  
  # https://docs.puppetlabs.com/puppet/latest/reference/lang_relationships.html
  require => Class['postgresql::globals'],
}

# create db + user
postgresql::server::db { 'testdb':
  user     => 'testuser',
  password => postgresql_password('testuser', 'testpassword'),
}

# rule for remote connections
postgresql::server::pg_hba_rule { 'allow remote connections with password':
  type        => 'host',
  database    => 'all',
  user        => 'all',
  address     => 'all',
  auth_method => 'md5',
}

# PostgreSQL password
file {'.pgpass-vagrant':
  path    => '/home/vagrant/.pgpass',
  ensure  => present,
  mode    => 0600,
  content => "localhost:5432:testdb:testuser:testpassword",
  owner  => "vagrant",
  group  => "vagrant",
}

# initialize the content of your new database
exec { "populate_postgresql":
  command => "/usr/bin/psql -d testdb -U testuser -h localhost -p 5432 --no-password < /vagrant/psql-db/psql-dump.sql",
  path    => "/usr/vagrant/", # there is .pgpass  file
  user    => 'vagrant',
  logoutput => true,
  
  
  require => [ File['.pgpass-vagrant'], 
                Postgresql::Server::Db['testdb'], 
                Postgresql::Server::Pg_hba_rule['allow remote connections with password'] ]
}

To access the database from outside of your virtual machine, you need to share the 5432 port in Vagrantfile:

1
config.vm.network "forwarded_port", guest: 5432, host: 15432  

When you run vagrant provision now, you should get a virtual machine with a PostgreSQL running. Test it at localhot:15432, database testdb, user testuser and using password testpassword.

For more information about Puppet, take a look at:

Problems with older version of Puppet

Error: Unknown Puppet subcommand ‘module’

Originally I started with a box hashicorp/precise64, which means Ubuntu 12.04. I quickly found, that the command puppet module is not available there. You have to install it as a separate Ruby gem, in your Vagrantfile:

1
2
3
4
5
6
7
8
9
10
  #...
  
  config.vm.provision "shell", :inline => <<-SHELL
    apt-get update            
    apt-get install -y puppet
    gem install puppet-module   # <--- install puppet module as a gem
  SHELL
  
	#...

My box with Ubuntu 12.04 used Puppet 2.7.11, Ubuntu 14.04 has Puppet 3.4.3.

Could not find declared class postgresql::globals

Puppet::Parser::AST::Resource failed with error ArgumentError: Could not find declared class postgresql::globals at /vagrant/manifests/default.pp:13 on node xyz

Here Puppet can’t find a module, because it is installed in the directory /home/vagrant/postgresql/, but Puppet expects it somewhere else. I solved this by upgrading to ubuntu/trusty64 box that has Puppet version 3.4.3. After that the downloaded module was in correct directory /etc/puppet/modules/postgresql/.

Other issues

Resource failed with error ArgumentError: Invalid resource type concat

Error: Puppet::Parser::AST::Resource failed with error ArgumentError: Invalid resource type concat at /etc/puppet/modules/postgresql/manifests/server/config.pp:27 on node vagrant-ubuntu-trusty-64.xyz

and

Error: Unknown function pick at /etc/puppet/modules/postgresql/manifests/globals.pp:92 on node vagrant-ubuntu-trusty-64.xyz

You are probably forcing Puppet to install a module, but Puppet will not install module’s dependencies in that case. See SO.

Or maybe something bad happen last time you try to create the virtual machine. Start over. Run vagrant destroy and then vagrant up.

database creation failed: ERROR: invalid locale name: “cs_CZ.UTF-8”

It’s often useful to test puppet script right inside of your virtual machine. Instead of running vagrant provision you can log vagrant ssh into your machine and then provision the machine with puppet from inside: sudo puppet apply /vagrant/manifests/build.pp. You can still edit puppet file from outside and using this technique, you will see more detailed error messages. For example, when running vagrant up, you may encounter this error:

Error: /Stage[main]/Main/Postgresql::Server::Db[testdb]/Postgresql::Server::Database[testdb]/Exec[/usr/lib/postgresql/9.3/bin/createdb –port=’5432’ –owner=’postgres’ –template=template0 –encoding ‘UTF8’ –locale=cs_CZ.UTF-8 ‘testdb’]:Failed to call refresh: /usr/lib/postgresql/9.3/bin/createdb –port=’5432’ –owner=’postgres’ –template=template0 –encoding ‘UTF8’ –locale=cs_CZ.UTF-8 ‘testdb’ returned 1 instead of one of [0]

Notice: /Stage[main]/Main/Postgresql::Server::Db[testdb]/Postgresql::Server::Database[testdb]/Exec[/usr/lib/postgresql/9.3/bin/createdb –port=’5432’ –owner=’postgres’ –template=template0 –encoding ‘UTF8’ –locale=cs_CZ.UTF-8 ‘testdb’]/returns: createdb: database creation failed: ERROR: invalid locale name: “cs_CZ.UTF-8”

Here you are trying to create a database with locale that is not present on your machine. To see all locales, run locale -a. To install locale, use this in your Vagrantfile:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  #...
  
  config.vm.provision "shell", :inline => <<-SHELL
    
    #...
    
    sudo locale-gen cs_CZ
    sudo locale-gen cs_CZ.UTF-8
    
    #...
    
  SHELL
  
	#...

Conclusion

I believe this approach to server configuration is quite convenient and powerful.

You can find all the files over at GitHub.

21. 10. 2014, kategorie: it
comments powered by Disqus