Automating PostgreSQL Installation & Configuration with Chef Cookbook

Purpose

The postgresql Chef cookbook automates installation and configuration of PostgreSQL as either a client or a server on supported Linux platforms (Debian 7+, Ubuntu 12.04+, RHEL/CentOS 6+, Fedora, SLES 12+, openSUSE 13+).

Attributes

All configurable settings are defined in attributes/default.rb. The source repository is https://github.com/sous-chefs/postgresql. The cookbook generates postgresql.conf and pg_hba.conf from the node['postgresql']['config'] and node['postgresql']['pg_hba'] hashes.

Example line generated for postgresql.conf: listen_addresses = 'localhost' Boolean values must be literal true or false; strings are quoted; nil omits the line. Note that the parameter unix_socket_directory was renamed to unix_socket_directories in PostgreSQL 9.3.

Client Installation

The packages listed in node['postgresql']['client']['packages'] are installed on the node.

Ruby Support

The pg gem is installed so Ruby applications can connect to PostgreSQL. The cookbook also installs build-essential and the client packages to compile the native extension.

Server Installation

Server‑specific packages ( server_debian or server_redhat) are installed and the cookbook manages:

Generation of a strong random password for the postgres system user (via OpenSSL).

Setting the postgres password.

Management of postgresql.conf.

Management of pg_hba.conf.

config_initdb

Derives locale and timezone from the host system and populates node.default['postgresql']['config'] accordingly. It also mirrors the default initdb logging settings, allowing custom log rotation (e.g., keep logs for 7 days).

config_pgtune

Provides performance‑tuning defaults based on the intended workload. The attribute node['postgresql']['config_pgtune']['db_type'] selects a profile (web, oltp, dw, mixed, desktop). Additional optional parameters: max_connections – default varies by db_type (web = 200, oltp = 300, dw = 20, mixed = 80, desktop = 5). total_memory – total system memory in kB; if omitted, Ohai supplies the value.

The cookbook respects these values when generating the configuration; directly overriding node['postgresql']['config']['max_connections'] is ignored in favor of the config_pgtune calculation.

contrib Packages and Extensions

Optional PostgreSQL contrib packages listed in node['postgresql']['contrib']['packages'] (e.g., pgbench, pg_buffercache) are installed. Extensions defined in node['postgresql']['contrib']['extensions'] are created with CREATE EXTENSION in the default template1 database. The pg_stat_statements view requires the shared library to be loaded; this can be enabled via the appropriate node attribute.

PGDG Repository Support

Two attributes enable the official PostgreSQL Global Development Group repositories: node['postgresql']['enable_pgdg_apt'] – when true, adds the APT repository, sets client and server package lists, and defines node['postgresql']['version'] (e.g., “9.2”). node['postgresql']['enable_pgdg_yum'] – when true, adds the YUM repository. The attribute node['postgresql']['pgdg']['repo_rpm_url'] can be overridden to point to a specific RPM.

Usage

To install a client, add the postgresql attribute or include the postgresql::client recipe in the node’s run list. To set up a server, include postgresql::server. The cookbook generates a random password for the postgres user; on Chef Server the password is saved automatically, while Chef Solo requires the password to be set in the node JSON.

On Debian/Ubuntu the package enables SSL by default. After any configuration change the PostgreSQL service restarts; a reload can be forced by setting the appropriate attribute.

Passwords are stored on the Chef Server as encrypted data. To reuse an existing MD5‑hashed password, copy the hash from an existing database and set it via the node attribute, or run the SQL command shown below to set the password directly:

ALTER USER postgres WITH ENCRYPTED PASSWORD 'md5<hash>';