Re: [GENERAL] Cookbook for doing installation and configuration ofPostgreSQL on Redhat - Mailing list pgsql-general

> I am new to chef concept, can anyone provide me a cookbook for doing
> installation and configuration of PostgreSQL on Redhat.

Hi Pawan,

I have always used the standard "postgresql" cookbook, which has recipes
to install a server. In a Berksfile you just say:

     cookbook "postgresql"

The repo is here with usage information:
https://github.com/sous-chefs/postgresql

One nice thing is it lets you control postgresql.conf values with chef
node attributes.

You can either add its recipe directly to your runlist, or include it
from an app recipe you write yourself. Here is an example of the latter
(a snippet from one of my own recipes). Of course you should change all
this to match your own situation (e.g. probably not `*` for
`listen_addresses`).

# These attributes control the postgresql.conf file:
node.default[:postgresql][:config][:data_directory] =
node[:postgresql][:data_dir]
node.default[:postgresql][:config][:port] = 5432
node.default[:postgresql][:config][:max_connections] = 100
node.default[:postgresql][:config][:unix_socket_directories] =
'/var/run/postgresql'
node.default[:postgresql][:config][:ssl] = true
node.default[:postgresql][:config][:ssl_cert_file] =
'/etc/ssl/certs/ssl-cert-snakeoil.pem'
node.default[:postgresql][:config][:ssl_key_file] =
'/etc/ssl/private/ssl-cert-snakeoil.key'
node.default[:postgresql][:config][:shared_buffers] = '2GB'
node.default[:postgresql][:config][:effective_cache_size] = '3GB'
# node.default[:postgresql][:config][:wal_level] = 'hot_standby'
# node.default[:postgresql][:config][:max_wal_senders] = 3
node.default[:postgresql][:config][:checkpoint_completion_target] = '0.9'
node.default[:postgresql][:config][:work_mem] = '32MB'
node.default[:postgresql][:config][:synchronous_commit] = 'off'
node.default[:postgresql][:config][:wal_keep_segments] = 8
node.default[:postgresql][:config][:log_line_prefix] = '%t '
node.default[:postgresql][:config][:log_timezone] = 'UTC'
node.default[:postgresql][:config][:log_min_duration_statement] = 500
node.default[:postgresql][:config][:log_checkpoints] = 'on'
node.default[:postgresql][:config][:datestyle] = 'iso, mdy'
node.default[:postgresql][:config][:timezone] = 'UTC'
node.default[:postgresql][:config][:lc_messages] = 'en_US.UTF-8'
node.default[:postgresql][:config][:lc_monetary] = 'en_US.UTF-8'
node.default[:postgresql][:config][:lc_numeric] = 'en_US.UTF-8'
node.default[:postgresql][:config][:lc_time] = 'en_US.UTF-8'
node.default[:postgresql][:config][:default_text_search_config] =
'pg_catalog.english'
node.default[:postgresql][:config][:listen_addresses] = '*'
node.default[:postgresql][:pg_hba] << {
   'type' => 'hostssl',
   'db' => 'all',
   'user' => 'all',
   'addr' => "0.0.0.0/0",
   'method' => 'md5'
}

include_recipe 'postgresql::server'
include_recipe 'postgresql::client'
package "postgresql-#{node['postgresql']['version']}-postgis-2.2"

# used for archiving WAL files
directory "#{node[:postgresql][:data_dir]}/archive" do
   owner "postgres"
   group "postgres"
   mode "0700"
end

...

You could also set these things via a role/environment/node file. Chef
has a lot of ways to set attributes. But setting them in your own recipe
is direct and lets you version the settings with the recipe. It may not
be appropriate for deployments with many variations though.

But if you are just learning chef, it can be easier to start out writing
one big recipe that is essentially just a long script. The most
degenerate use of chef would be this:

bash "do everything" do
   code <<-EOF
     echo Hello World!
     apt-get install postgresql-9.6
     etc
     etc
   EOF
end

(I don't actually recommend you do that, except pedagogically....)

A step better would be to break that into individual resources, e.g.

     package 'postgresql-9.6'
     ...

And then a step above that would be to use third-party cookbooks where
available, and include their recipes in your runlist.

I also think it is easier to use chef-solo when you are just starting
out, if possible. That gives you a model more like Ansible: "push these
commands from my local development machine to the target and run them."
It combines well with Vagrant so you can quickly try things out and
start from fresh.

This is all really more about Chef than Postgres though, so you might
have more success asking that community. Or send me a note and I can
probably help solve specific Chef problems. The try-test-fix cycle can
be pretty slow!

Good luck!

Paul