Managing the inside of your Postgres with Puppet

In this playground, we will show you how you can use Puppet to manage your Postgres database setup.

The playground system

On the playground system, you will find an Postgres database. We have created it using the pg_profile module. The playground will guide you in your customisations.

Working in the playground

Under this text, you see the working area. You can inspect the system and issue any command you like in the terminal. In the editor window, you can see the Puppet production environment. You can edit anything you wish. The documentation tab shows the documentation for the pg_config module.


This system will self destruct in about one hour. So please don’t use it to build or create anything you wish to keep!

Subjects in this playground

The playground contains the following sub paragraphs:
  • Using Puppet for introspection
  • Puppet knows about your Postgres databases too
  • Creating a database with Puppet from the command line
  • Modifying a Postgres database with Puppet from the command line
Happy exploring!!

Using Puppet for introspection

In this section, we’ll show you how you can use Puppet to introspect your Postgres server. In general in Puppet to introspect any resource, you use the command:

$ puppet resource <puppet_type> [<resource_name>]

Looking at your Postgres database with puppet

Standard, however, Puppet doesn’t know about Postgres database. There is a set of pg_ types extending puppet, so it now knows about tablespaces and what is inside. First off, we will take a look at the list of types created for Postgres management.

So to see what types are provided to manage your databases, enter the next command.

puppet resource --types | grep ^pg_

The command will list all the types existing in puppet resource and filters the output for those starting with pg prefix.

Puppet knows about your Postgres databases too

In the last section, we showed you what types are provided in our Puppet modules to introspect your databases. In this section, we will get a close look at the pg_database type.

TEASER: introspection is the prequel to creating and managing Postgres databases with the Puppet.

Introspect available Postgres databases

Remember the general command for introspecting?

puppet resource <puppet_type> [<resource_name>]

For Postgres databases the Puppet type is pg_database. So the command becomes.

puppet resource pg_database

The output of this command can be used as a starting point for defining new Postgres databases.

Creating a database with Puppet from the command line

We have been using Puppet from the command line for introspecting. But you can also use puppet on the command line for creating and changing Postgres resources. To do this we again use the puppet resource command.

The general command for making sure a resource exists is:

puppet resource <puppet_type> [<resource_name>] ensure=present

“Making sure a resource exists” means that when it doesn’t exist, Puppet will create it for us.

Let’s use this to create a new database with the name testdb@localhost.

Create the Postgres database

The command to make this happen is:

puppet resource  pg_database testdb@localhost ensure=present

Now check the output:

Notice: /Pg_database[testdb@localhost]/ensure: created
pg_database { 'testdb@localhost':
  ensure   => 'present',
  provider => 'simple',

If we do the command again, notice that we don’t get the message Notice: /Pg_database[testdb@localhost]/ensure: created. That is because the database is already created and Puppet doesn’t have to do anything.

Modifying a Postgres database with Puppet from the command line

The database testdb@localhost exists now, but it has the default properties. With the same command as the command to create it, you can also set other properties of the Postgres database.

Setting the connection_limit

The connection_limit of the database specifies the number of connections it allows. The default is -1, meaning unlimited. We want to set it to 100.

We can use the same puppet resource command and append the change.

puppet resource  pg_database testdb@localhost connection_limit=100

Here is the output Puppet gives us:

Notice: /Pg_database[testdb@localhost]/connection_limit: connection_limit changed -1 to 100
pg_database { 'testdb@localhost':
  ensure           => 'present',
  connection_limit => -1,
  provider         => 'simple',

Let’s run Puppet again and see what happens. Notice that the message Notice: /Pg_database[testdb@localhost]/connection_limit: connection_limit changed -1 to 100 does not appear again. This is because it is already set correctly, and Puppet doesn’t need to do anything.

More information about properties managed by Puppet can be found on Enterprise Modules documentation website.## You like it?

Do you like what you see here and want to test this on your own infrastructure? No problem. You can sign up for a free trial.

If you have any questions, don’t hesitate to contact us.