pg exec

Overview

This type allows you run a specific SQL statement or an sql file on a specified instance db.

pg_exec{"drop table application_users/db@instance":
  role     => 'app_user',
  password => 'password,'
}

This statement will execute the sql statement drop table application_users on the database named db in the instance named instance.

You can use the unless parameter to only execute the statement in certain states. If the query specified in the unless parameter returns one or more records, the main statement is skipped.

pg_exec{ "create synonym ${user}.${synonym} for USER.${synonym}":
  unless  => "select * from all_synonyms where owner='${user}' and synonym_name='${synonym}'",
}

You can also execute a script.

pg_exec{"@/tmp/do_some_stuff.sql/db@instance":
  role      => 'app_user',
  password  => 'password,'
  logoutput => on_failure,  # can be true, false or on_failure
}

This statement will run the sqlscript /tmp/do_some_stuff.sql on the db named db. Use the unless parameter to just execute the script in certain situations.

When you don’t specify the rolw and the password, the type will connect as sysdba.

Attributes

cwd

The default directory from where the scripts will be run. If not specified, this will be /tmp.

  pg_exec {...:
    ...
    cwd => '/opt/my_scripts'
  }

This parameter is convenient when the script you run, expects a default directory. For example when running other scripts, without a specified directory:

@execute.sql

Back to overview of pg_exec

db

database to connect to.

Back to overview of pg_exec

disable_corrective_change

Disable the modification of a resource when Puppet decides it is a corrective change.

(requires easy_type V2.11.0 or higher)

When using a Puppet Server, Puppet knows about adaptive and corrective changes. A corrective change is when Puppet notices that the resource has changed, but the catalog has not changed. This can occur for example, when a user, by accident or willingly, changed something on the system that Puppet is managing. The normal Puppet process then repairs this and puts the resource back in the state as defined in the catalog. This process is precisely what you want most of the time, but not always. This can sometimes also occur when a hardware or network error occurs. Then Puppet cannot correctly determine the current state of the system and thinks the resource is changed, while in fact, it is not. Letting Puppet recreate remove or change the resource in these cases, is NOT wat you want.

Using the disable_corrective_change parameter, you can disable corrective changes on the current resource.

Here is an example of this:

crucial_resource {'be_carefull':
  ...
  disable_corrective_change => true,
  ...
}

When a corrective ensure does happen on the resource Puppet will not modify the resource and signal an error:

    Error: Corrective change present requested by catalog, but disabled by parameter disable_corrective_change
    Error: /Stage[main]/Main/Crucial_resource[be_carefull]/parameter: change from '10' to '20' failed: Corrective change present requested by catalog, but disabled by parameter disable_corrective_change. (corrective)

Back to overview of pg_exec

disable_corrective_ensure

Disable the creation or removal of a resource when Puppet decides is a corrective change.

(requires easy_type V2.11.0 or higher)

When using a Puppet Server, Puppet knows about adaptive and corrective changes. A corrective change is when Puppet notices that the resource has changed, but the catalog has not changed. This can occur for example, when a user, by accident or willingly, changed something on the system that Puppet is managing. The normal Puppet process then repairs this and puts the resource back in the state as defined in the catalog. This process is precisely what you want most of the time, but not always. This can sometimes also occur when a hardware or network error occurs. Then Puppet cannot correctly determine the current state of the system and thinks the resource is changed, while in fact, it is not. Letting Puppet recreate remove or change the resource in these cases, is NOT wat you want.

Using the disable_corrective_ensure parameter, you can disable corrective ensure present or ensure absent actions on the current resource.

Here is an example of this:

crucial_resource {'be_carefull':
  ensure                    => 'present',
  ...
  disable_corrective_ensure => true,
  ...
}

When a corrective ensure does happen on the resource Puppet will not create or remove the resource and signal an error:

    Error: Corrective ensure present requested by catalog, but disabled by parameter disable_corrective_ensure.
    Error: /Stage[main]/Main/Crucial_resource[be_carefull]/ensure: change from 'absent' to 'present' failed: Corrective ensure present requested by catalog, but disabled by parameter disable_corrective_ensure. (corrective)

Back to overview of pg_exec

instance

Postgres instance to connect to.

All types have a name like resource@instance.

Back to overview of pg_exec

logoutput

Whether to log command output in addition to logging the exit code. Defaults to on_failure, which only logs the output when the command has an exit code that does not match any value specified by the returns attribute. As with any resource type, the log level can be controlled with the loglevel metaparameter.

pg_exec {...:
  ...
  logoutput => true,
}

The default value is on_failure

Valid values are true, false, on_failure.

Back to overview of pg_exec

mark_as_error

Additional error strings or regexes.

To decide whether an SQL action was successful or not, Puppet scan’s the output for specific strings indicating an error. Sometimes you want full control over what is an error and what is not.

Using this parameter, you can do just that. When the string or regular expression you specify here, is found, pg_exec will signal an error.

Here is an example:

  pg_exec{'@/tmp/my_script.sql':
    ...
    mark_as_error => /no such user/,
  }

when your output contains the string no such user, an error will be raised.

WARNING Using this parameter, all normal checks are discarded. So use this parameter with care

Back to overview of pg_exec

name

The full sql statement including a SID.

pg_exec { 'select * from tab@db':
  ...
}

The db is optional. When you don’t specify a database, We recommend you always use a full qualified name (e.g. a name including the database name).

Back to overview of pg_exec

onlyif

A query to determine if the pg_exec must execute or not.

If the query returns something, either one or more rows, the pg_exec is executed. If the query returns no rows, the specified pg_exec statement will NOT be executed.

it is the oposite of the unless parameter.

The onlyif clause must be a valid query. An error in the query will result in a failure of the apply statement.

If you have specified a username and a password, the onlyif statement will be executed in that context. E.g. logged in as the specified user with the specified password.

The default value is empty. Meaning no onlyif` statement is executed and the statement or script specified in the title, will always be executed.

pg_exec{ "create synonym ${user}.${synonym} for PRES.${synonym}":
  onlyif  => "select * from all_synonyms where owner='${user}' and synonym_name='${synonym}'",
}

Back to overview of pg_exec

password

The user’s password.

Back to overview of pg_exec

provider

The specific backend to use for this pg_exec resource. You will seldom need to specify this — Puppet will usually discover the appropriate provider for your platform.Available providers are:

psql

Back to overview of pg_exec

refreshonly

do the exec only when notfied.

The command should only be run as a refresh mechanism for when a dependent object is changed. It only makes sense to use this option when this command depends on some other object; it is useful for triggering an action:

Note that only subscribe and notify can trigger actions, not require, so it only makes sense to use refreshonly with subscribe or notify.

pg_exec {...:
  ...
  refreshonly => true,
}

The default value is false, meaning the SQL statement is executed as a normal part of the Puppet catalog.

Valid values are true, false.

Back to overview of pg_exec

report_errors

Report any errors in the SQL scripts.

When you set this value to true, the type will report any errors that occur in the SQL statements or scripts and stop processing. When you set it to false, it will ignore any SQL errors and just continue processing. The default value is true, so it will stop processing when an SQL error is generated.

Here is an example:

pg_exec{'delete from user_config':
  ...
  report_errors => true,

}

Valid values are true, false.

Back to overview of pg_exec

role

The user name.

Back to overview of pg_exec

statement

The sql command to execute.

This is a required string value. The value must either contain a valid SQL statement:

pg_exec { 'select * from tab/db@instance':
}

or a valid script name.

pg_exec { '@/tmp/valid_sql_script.sql/db@instance':
}

when you don’t specify a directory, Puppet will use the default directory specified in the cwd parameter.

pg_exec { '@valid_sql_script.sql/db@instance':
  ...
  cwd => '/tmp',
}

Back to overview of pg_exec

timeout

Timeout for applying a resource in seconds.

To be sure no Puppet operation, hangs a Puppet run, all operations have a timeout. When this timeout expires, Puppet will abort the current operation and signal an error in the Puppet run.

With this parameter, you can specify the length of the timeout. The value is specified in seconds. In this example, the timeout is set to 600 seconds.

pg_exec{ ...:
  ...
  timeout => 600,
}

The default value for timeout is 300 seconds.

Back to overview of pg_exec

unless

A query to determine if the pg_exec must execute or not.

If the query returns something, either one or more rows, the pg_exec is NOT executed. If the query returns no rows, the specified pg_exec statement IS executed.

The unless clause must be a valid query. An error in the query will result in a failure of the apply statement.

If you have specified a username and a password, the unless statement will be executed in that context. E.g. logged in as the specified user with the specified password.

The default value is empty. Meaning no unless statement is executed and the statement or script specified in the title, will always be executed.

pg_exec { "create synonym ${user}.${synonym} for PRES.${synonym}":
  unless  => "select * from all_synonyms where owner='${user}' and synonym_name='${synonym}'",
}

Back to overview of pg_exec