postgresql-database-exists

Example:

# Install PostgreSQL, create the db 'my_database' and user 'freckles'.
- postgresql-database-exists:
    postgresql_listen_addresses:
    - '*'
    postgresql_pg_hba:
    - method: md5
    db_name: my_database
    db_user: freckles
    db_user_password: md5aee63ef475154b1b0461fc508db22950

Description

Installs postgresql service on a host, and ensures a database with the provided name is present.

If db_user is provided, its value will be used as the owner of the database.

If PostgreSQL is already installed on a particular host, it's better to use the frecklet::postgresql-service frecklet before this, configure the service, and use the no_setup_postgresql flag set to true. Otherwise PostgreSQL configuration might be overwritten by this.

When creating a user and providing a password, that password needs to be passed in encrypted (md5-hased) form.

On Linux, you can do that via:

echo "md5$(echo -n "${PASSWORD}${USERNAME}" | md5sum | cut -c -32)"

# for example:

echo "md5$(echo -n "mysecretpasswordfreckles" | md5sum | cut -c -32)"

And on Mac OS X you use the md5 executable instead:

echo "md5$(echo -n "${PASSWORD}${USERNAME}" | md5 | cut -c -32)"

If a database dump file is provided, the database will be imported from it. Otherwise an empty table will be created.

Resources

Variables

Name Type Default Description

db_name

string --

The name of the database to use from the dump. Required

db_encoding

string UTF-8

The encoding of the db (default: 'UTF-8').

db_import

boolean False

Whether to use a sql dump file to create the database.

db_lc_collate

string --

Collation order (LC_COLLATE) to use in the database. Must match collation order of template database unless template0 is used as template.

db_lc_ctype

string --

Character classification (LC_CTYPE) to use in the database (e.g. lower, upper, ...) Must match LC_CTYPE of template database unless template0 is used as template.

db_template

string --

The template used to create the database.

db_user

string --

The name of the database user ('role' in postgres).

db_user_password

string --

The password needs to be passed in hashed form, please check the [Postgresql documentation](

no_setup_postgresql

boolean True

If that option is enabled, any potential existing PostgreSQL server configuration is overwritten by this frecklet, independent of whether PostgreSQL was installed already or not.

postgresql_group_id

integer --

The (optional) custom PostgreSQL group gid (when installing PostgreSQL).

postgresql_listen_addresses

list --

The IPs the PostgreSQL server is listening on. If a PostgreSQL server is already installed, the existing settings will be overwritten.

postgresql_pg_hba

list --

A list of hosts to allow connections from, apart from the defaul:

local  all  postgres    trust
# "local" is for Unix domain socket connections only
local  all  all    trust
# IPv4 local connections:
host  all  all  127.0.0.1/32  trust
# IPv6 local connections:
host  all  all  ::1/128  trust
# Local root Unix user, passwordless access
local  all  postgres    peer map=root_as_postgres

postgresql_port

integer --

The port the PostgreSQL server is listening on.

If a PostgreSQL server is already installed, the existing setting will be overwritten.

postgresql_user_id

integer --

The (optional) custom PostgreSQL user uid (when installing PostgreSQL).

postgresql_version

string --

The version of postgresql

Examples

Example 1

Install PostgreSQL, create the db 'my_database' and user 'freckles'.

Code
- postgresql-database-exists:
    postgresql_listen_addresses:
    - '*'
    postgresql_pg_hba:
    - method: md5
    db_name: my_database
    db_user: freckles
    db_user_password: md5aee63ef475154b1b0461fc508db22950
Description

This configures PostgreSQL to listen on all network interfaces, and be accessible from all network locations (0.0.0.0/0) when using md5-auth.

Command-line

frecklecute postgresql-database-exists --help

Usage: frecklecute postgresql-database-exists [OPTIONS]

  Installs postgresql service on a host, and ensures a database with the
  provided name is present.

  If ``db_user`` is provided, its value will be used as the owner of the
  database.

  If PostgreSQL is already installed on a particular host, it's better to
  use the frecklet::postgresql-service frecklet before this, configure the
  service, and use the ``no_setup_postgresql`` flag set to ``true``.
  Otherwise PostgreSQL configuration might be overwritten by this.

  When creating a user and providing a password, that password needs to be
  passed in encrypted (md5-hased) form.

  On Linux, you can do that via:

  ```

  echo "md5$(echo -n "${PASSWORD}${USERNAME}" | md5sum | cut -c -32)"

  # for example:

  echo "md5$(echo -n "mysecretpasswordfreckles" | md5sum | cut -c -32)"

  ```

  And on Mac OS X you use the ``md5`` executable instead:

  ```

  echo "md5$(echo -n "${PASSWORD}${USERNAME}" | md5 | cut -c -32)"

  ```

  If a database dump file is provided, the database will be imported from
  it. Otherwise an empty table will be created.

Options:
  --db-name DB_NAME               The name of the database to use from the
                                  dump.  [required]
  --db-encoding ENCODING          The encoding of the db (default: 'UTF-8').
  --db-import / --no-db-import    Whether to use a sql dump file to create the
                                  database.
  --db-lc-collate LC_COLLATE      The collation order to use in the database.
  --db-lc-ctype LC_CTYPE          Character classification (LC_CTYPE) to use
                                  in the database.
  --db-template DB_TEMPLATE       The template used to create the database.
  --db-user DB_USER               The name of the database user ('role' in
                                  postgres).
  --db-user-password DB_USER_PASSWORD
                                  The (hashed) password for the database user
                                  ('role' in PostgreSQL).
  --no-setup-postgresql / --no-no-setup-postgresql
                                  Don't attempt to install PostgreSQL service.
  --postgresql-group-id POSTGRESQL_GROUP_ID
                                  The (optional) custom PostgreSQL group gid
                                  (when installing PostgreSQL).
  --listen-address POSTGRESQL_LISTEN_ADDRESSES
                                  The IPs the PostgreSQL server is listening
                                  on.
  --postgresql-port POSTGRESQL_PORT
                                  The port the PostgreSQL server is listening
                                  on.
  --postgresql-user-id POSTGRESQL_USER_ID
                                  The (optional) custom PostgreSQL user uid
                                  (when installing PostgreSQL).
  --postgresql-version POSTGRESQL_VERSION
                                  The version of postgresql
  --help                          Show this message and exit.