Ensure databases

After you have installed your software and created your primary database, you may need to add some specific databases. You can do this by adding some data to your hiera data.

In the editor tab, go to the directory hierdata\nodes and open the file pg14.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Databases definition
#
pg_profile::database::db_definition::list:
  my_db_1@localhost: 
    ensure:   present
    owner:    postgres
  my_db_2@localhost: 
    ensure:   present
    owner:    postgres

This data tells Puppet to ensure that the databases my_db_1 and my_db_2 needs to be available with the specified owner. Make sure that you safe the changes before continuing. See the documentation what kind of properties you can use.

First Puppet run

Puppet runs will read this data and make sure the database is available with the specified properties. Puppet will detect that the database is unknown and create it since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure Postgres database(s) my_db_1@localhost,my_db_2@localhost

This is the information Puppet provides you about the databases it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Pg_profile::Database::Db_definition/Pg_database[my_db_1@localhost]/ensure: created
Notice: /Stage[main]/Pg_profile::Database::Db_definition/Pg_database[my_db_2@localhost]/ensure: created

So Puppet created the database.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the database already exists with the specified properties and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the database, but we no longer have the creation message, just as we expected.

Manage database roles

Now let’s add some database roles. Again you only need to add some yaml settings to your hiera data.

For managing database users, we can use the hiera key pg_profile::database::db_roles::list.

In the editor tab, go to the directory hierdata\nodes and open the file pg14.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Database roles
#
pg_profile::database::db_roles::list:
  my_role_1@localhost: 
    ensure:   present
    password: secretstuff
  my_role_2@localhost: 
    ensure: present

First Puppet run

Puppet runs will read this data and make sure the database user is available with the specified properties. Puppet will detect that the user is unknown and create it since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure Postgres role(s) my_role_1@localhost,my_role_2@localhost

This is the information Puppet provides you about the users it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Pg_profile::Database::Db_roles/Pg_role[my_role_1@localhost]/ensure: created
Notice: /Stage[main]/Pg_profile::Database::Db_roles/Pg_role[my_role_2@localhost]/ensure: created

So Puppet created the database user.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the database user already exists with the specified properties and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the user, but we no longer have the creation message, just as we expected.

More information

See the documentation what kind of user properties you can manage with Puppet use.

Manage database parameters

You can also manage your database parameters with Puppet. Let’s change the log_connections value for this database from off to on.

For managing database users, we can use the hiera key pg_profile::database::db_parameters.

In the editor tab, go to the directory hierdata\nodes and open the file pg14.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Database parameters
#
pg_profile::database::db_parameters::list:
  log_connections@localhost:
    value:  'on'

First Puppet run

Puppet runs will read this data and make sure the parameters are available and have the correct value. Puppet will detect that the parameters have a different value since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure Postgres parameter(s) log_connections@localhost

This is the information Puppet provides you about the parameters it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Pg_secured::Controls::Log_connections_is_enabled[localhost]/Pg_secured::Internal::Set_parameter[log_connections@localhost]/Pg_parameter[log_connections@localhost]/value: value changed 'off' to 'on'

So Puppet ensured that both parameters are present and set to the correct value.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the database parameters are already set with the correct values and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the user, but we no longer have the creation message, just as we expected.

More information

See the documentation what kind of user properties you can manage with Puppet use.

Ensure tablespaces

After you have installed created your primary roles, and databases, most of the time, you need to have some specific tablespaces. You can do this by adding some data to your hiera data.

In the editor tab, go to the directory hierdata\nodes and open the file pg14.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Database tablespaces
#
pg_profile::database::db_init::data_dirs: 
  - /u01
  - /u01/tablespaces
pg_profile::database::db_tablespaces::list:
  my_ts_1@localhost:
    ensure:   present
    owner:    my_role_2
    location: /u01/tablespaces

This data tells Puppet to ensure that path /u01/tablespaces the tablespace my_ts_1@localhost needs to be available with the specified location. Make sure that you safe the changes before continuing. See the documentation what kind of properties you can use.

First Puppet run

Puppet runs will read this data and make sure the tablespace is available with the specified properties. Puppet will detect that the tablespace is unknown and create it since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure Postgres database(s) my_ts_1@localhost

This is the information Puppet provides you about the tablespaces it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Pg_profile::Database::Db_init/File[/u01]/ensure: created
Notice: /Stage[main]/Pg_profile::Database::Db_init/File[/u01/tablespaces]/ensure: created
Notice: /Stage[main]/Pg_profile::Database::Db_tablespaces/Pg_tablespace[my_ts_1@localhost]/ensure: created

So Puppet created the tablespace.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the tablespace already exists with the specified properties and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the tablespace, but we no longer have the creation message, just as we expected.

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.