<commit_before># postgresql

![PRs Welcome](https://img.shields.io/badge/PRs-welcome-brightgreen.svg)

**NTTCom-MS/eyp-postgresql**: [![Build Status](https://travis-ci.org/NTTCom-MS/eyp-postgresql.png?branch=master)](https://travis-ci.org/NTTCom-MS/eyp-postgresql)

#### Table of Contents

1. [Overview](#overview)
2. [Module Description](#module-description)
3. [Setup](#setup)
    * [What postgresql affects](#what-postgresql-affects)
    * [Setup requirements](#setup-requirements)
    * [Beginning with postgresql](#beginning-with-postgresql)
4. [Usage](#usage)
5. [Reference](#reference)
5. [Limitations](#limitations)
6. [Development](#development)
    * [TODO](#todo)
    * [Contributing](#contributing)

## Overview

manages postgresql:
* standalone
* streaming replication

## Module Description

Installs and configures PostgreSQL on CentOS 6

## Setup

### What postgresql affects

* Installs PostgreSQL:
* configures:
  * postgres.conf
  * pg_hba
  * pg_stat_statements
* it can manage the following DB objects:
  * roles
  * schemas
* if eyp-sysctl is present:
  * overcommit_memory = 2 - total virtual address space on the system is limited to *(SWAP + RAM ·( /proc/sys/vm/overcommit_ratio /100))*
  * shmmax: maximum size of shared memory segment (default: ceiling(sprintf('%f', $::memorysize_mb)·786432))
  * shmall: total amount of shared memory available (default: ceiling(ceiling(sprintf('%f',$::memorysize_mb)·786432)/$::eyp_postgresql_pagesize))

### Setup Requirements

This module requires pluginsync enabled and **optionally** *eyp/sysctl* module
installed. Mountpoints **must** be already in place (datadir, archive_dir...)

### Beginning with postgresql

Currently, it only supports PostgreSQL 9.2 (check TODO list)

## Usage

streaming replication setup:

```puppet
node 'pgm'
{
	#.29

	class { 'sysctl': }

	class { 'postgresql':
		wal_level => 'hot_standby',
		max_wal_senders => '3',
		checkpoint_segments => '8',
		wal_keep_segments => '8',
	}

	postgresql::hba_rule { 'test':
		user => 'replicator',
		database => 'replication',
		address => '192.168.56.0/24',
	}

	postgresql::role { 'replicator':
		replication => true,
		password => '<PASSWORD>',
	}

	postgresql::schema { 'jordi':
		owner => 'replicator',
	}

}

node 'pgs'
{
	#.30

	class { 'sysctl': }

	class { 'postgresql':
		wal_level => 'hot_standby',
		max_wal_senders => '3',
		checkpoint_segments => '8',
		wal_keep_segments => '8' ,
		hot_standby => true,
		initdb => false,
	}

	class { 'postgresql::streaming_replication':
		masterhost     => '192.168.56.29',
		masterusername => 'replicator',
		masterpassword => '<PASSWORD>',
	}
}
```

backup configurtion:

```puppet
postgresql::pgdumpbackup { "backup logic":
  destination => '/backup',
  mailto => '<EMAIL>',
  idhost => 'postgresmaster01',
}
```

## Reference

### classes

#### postgresql

It uses the following (private) classes to install, configure and manage PostgreSQL:

* **postgresql::install**: Installation and initdb
* **postgresql::config**: Modifies configuration files
* **postgresql::service**: Manages postgres service

Options:
* **version**: version to install (default: 9.2)
* **datadir**: datadir to use (default: /var/lib/pgsql/9.2/data)
* **initdb**: boolean, true to create datadir's directies. In a standby server with streaming replication you want to set it to false (default: true)
* **manage_service**: boolean, true to manage PostgreSQL's service (default: true)
* **archive_command_custom**: custom archive command
* **archive_dir**: archive dir, if archive_command_custom is undef, it will be: *test ! -f ${archive_dir}/%f && cp %p ${archive_dir}/%f*
* **overcommit_memory**: modes available:
  * undef: do not change it
  * 0: heuristic overcommit (this is the default)
  * 1: always overcommit, never check
  * 2: always check, never overcommit(default: 2)',
* **shmmax**: maximum size of shared memory segment (default: ceiling(sprintf('%f', $::memorysize_mb)·786432)) you can set it to undef to disable
* **shmall**: total amount of shared memory available (default: ceiling(ceiling(sprintf('%f',$::memorysize_mb)·786432)/$::eyp_postgresql_pagesize)) you can set it to undef to disable
* for directly mapped variables (lc_messages, listen, port...) check postgres documentation:
  * port
  * listen
  * port
  * max_connections
  * wal_level
  * max_wal_senders
  * checkpoint_segments
  * wal_keep_segments
  * hot_standby
  * pidfile
  * log_directory
  * log_filename
  * track_activities
  * track_counts
  * autovacuum
  * autovacuum_vacuum_scale_factor
  * autovacuum_vacuum_threshold
  * autovacuum_analyze_scale_factor
  * autovacuum_analyze_threshold
  * timezone
  * log_timezone
  * superuser_reserved_connections
  * archive_mode
  * archive_timeout
  * archived_wals_retention
  * archived_wals_hour
  * archived_wals_minute
  * archived_wals_month
  * archived_wals_monthday
  * archived_wals_weekday
  * maintenance_work_mem
  * wal_buffers
  * work_mem
  * shared_buffers (autocalculated to 1/4 main RAM)
  * lc_messages
  * lc_monetary
  * lc_numeric
  * lc_time
  * default_text_search_config
  * shared_preload_libraries

usage example:

```puppet
class { 'postgresql': }
```

#### postgresql::streaming_replication

* **masterhost**: required, postgres master
* **masterusername**: required, replication username
* **masterpassword**: required, replication password
* **masterport** (default: port_default)
* **datadir** (default: datadir_default)

It requires to have **pg_basebackup** and the defined username already created on
the master DB

usage example:

```puppet
class { 'postgresql::streaming_replication':
  masterhost     => '192.168.56.29',<commit_msg>més examples<commit_after># postgresql

![PRs Welcome](https://img.shields.io/badge/PRs-welcome-brightgreen.svg)

**NTTCom-MS/eyp-postgresql**: [![Build Status](https://travis-ci.org/NTTCom-MS/eyp-postgresql.png?branch=master)](https://travis-ci.org/NTTCom-MS/eyp-postgresql)

#### Table of Contents

1. [Overview](#overview)
2. [Module Description](#module-description)
3. [Setup](#setup)
    * [What postgresql affects](#what-postgresql-affects)
    * [Setup requirements](#setup-requirements)
    * [Beginning with postgresql](#beginning-with-postgresql)
4. [Usage](#usage)
5. [Reference](#reference)
5. [Limitations](#limitations)
6. [Development](#development)
    * [TODO](#todo)
    * [Contributing](#contributing)

## Overview

manages postgresql:
* standalone
* streaming replication

## Module Description

Installs and configures PostgreSQL on CentOS 6

## Setup

### What postgresql affects

* Installs PostgreSQL:
* configures:
  * postgres.conf
  * pg_hba
  * pg_stat_statements
* it can manage the following DB objects:
  * roles
  * schemas
* if eyp-sysctl is present:
  * overcommit_memory = 2 - total virtual address space on the system is limited to *(SWAP + RAM ·( /proc/sys/vm/overcommit_ratio /100))*
  * shmmax: maximum size of shared memory segment (default: ceiling(sprintf('%f', $::memorysize_mb)·786432))
  * shmall: total amount of shared memory available (default: ceiling(ceiling(sprintf('%f',$::memorysize_mb)·786432)/$::eyp_postgresql_pagesize))

### Setup Requirements

This module requires pluginsync enabled and **optionally** *eyp/sysctl* module
installed. Mountpoints **must** be already in place (datadir, archive_dir...)

### Beginning with postgresql

Currently, it only supports PostgreSQL 9.2 (check TODO list)

## Usage

streaming replication setup:

```puppet
node 'pgm'
{
	#.29

	class { 'sysctl': }

	class { 'postgresql':
		wal_level => 'hot_standby',
		max_wal_senders => '3',
		checkpoint_segments => '8',
		wal_keep_segments => '8',
	}

	postgresql::hba_rule { 'test':
		user => 'replicator',
		database => 'replication',
		address => '192.168.56.0/24',
	}

	postgresql::role { 'replicator':
		replication => true,
		password => '<PASSWORD>',
	}

	postgresql::schema { 'jordi':
		owner => 'replicator',
	}

}

node 'pgs'
{
	#.30

	class { 'sysctl': }

	class { 'postgresql':
		wal_level => 'hot_standby',
		max_wal_senders => '3',
		checkpoint_segments => '8',
		wal_keep_segments => '8' ,
		hot_standby => true,
		initdb => false,
	}

	class { 'postgresql::streaming_replication':
		masterhost     => '192.168.56.29',
		masterusername => 'replicator',
		masterpassword => '<PASSWORD>',
	}
}
```

backup configurtion:

```puppet
postgresql::pgdumpbackup { "backup logic":
  destination => '/backup',
  mailto => '<EMAIL>',
  idhost => 'postgresmaster01',
}
```

postgresmaster using hiera:

```hiera
---
classes:
  - postgresql
  - postgresql::pgstatsstatements
postgresql::port: 60901
postgresql::wal_level: hot_standby
postgresql::max_wal_senders: 3
postgresql::checkpoint_segments: 16
postgresql::wal_keep_segments: 8
postgresql::archive_mode: true
postgresql::max_connections: 200
postgresql::archive_mode: true
postgresql::archive_command_custom: 'rsync --exclude lost+found -a %p barman@192.168.52.21:/var/lib/barman/pgm/incoming/%f'
postgresql::shared_preload_libraries:
  - pg_stat_statements
postgresroles:
  extension:
    password: '<PASSWORD>'
    port: 60901
  extension_ro:
    password: '<PASSWORD>'
    port: 60901
  extension_rw:
    password: '<PASSWORD>'
    port: 60901
  replicator:
    port: 60901
    password: '<PASSWORD>'
    replication: true
postgresschemas:
  extension:
    owner: extension
    port: 60901
```

postgres slave using hiera:

```hiera
---
classes:
  - postgresql
  - postgresql::pgstatsstatements
  - postgresql::streaming_replication
postgresql::port: 60901
postgresql::wal_level: hot_standby
postgresql::max_wal_senders: 3
postgresql::checkpoint_segments: 16
postgresql::wal_keep_segments: 8
postgresql::archive_mode: true
postgresql::max_connections: 200
postgresql::initdb: false
postgresql::hot_standby: true
postgresql::shared_preload_libraries:
  - pg_stat_statements
postgresql::streaming_replication::masterhost: 192.168.52.20
postgresql::streaming_replication::masterport: 60901
postgresql::streaming_replication::masterusername: replicator
postgresql::streaming_replication::masterpassword: <PASSWORD>
```

hba rules using hiera:

```hiera
---
hbarules:
  barman:
    user: postgres
    database: all
    address: '192.168.52.21/32'
    auth_method: trust
  replicator:
    user: replicator
    database: replication
    address: '192.168.52.0/24'
  nagios:
    user: nagios
    database: nagios
    address: '1.2.3.4/32'
  nagiospre:
    user: nagios
    database: nagios
    address: '1.2.3.4/32'
  extension:
    user: extension
    database: extension
    address: '192.168.52.0/24'
  extensionRO:
    user: extension_ro
    database: extension
    address: '192.168.52.0/24'
  extensionRW:
    user: extension_rw
    database: extension
    address: '192.168.52.0/24'
```


## Reference

### classes

#### postgresql

It uses the following (private) classes to install, configure and manage PostgreSQL:

* **postgresql::install**: Installation and initdb
* **postgresql::config**: Modifies configuration files
* **postgresql::service**: Manages postgres service

Options:
* **version**: version to install (default: 9.2)
* **datadir**: datadir to use (default: /var/lib/pgsql/9.2/data)
* **initdb**: boolean, true to create datadir's directies. In a standby server with streaming replication you want to set it to false (default: true)
* **manage_service**: boolean, true to manage PostgreSQL's service (default: true)
* **archive_command_custom**: custom archive command
* **archive_dir**: archive dir, if archive_command_custom is undef, it will be: *test ! -f ${archive_dir}/%f && cp %p ${archive_dir}/%f*
* **overcommit_memory**: modes available:
  * undef: do not change it
  * 0: heuristic overcommit (this is the default)
  * 1: always overcommit, never check
  * 2: always check, never overcommit(default: 2)',
* **shmmax**: maximum size of shared memory segment (default: ceiling(sprintf('%f', $::memorysize_mb)·786432)) you can set it to undef to disable
* **shmall**: total amount of shared memory available (default: ceiling(ceiling(sprintf('%f',$::memorysize_mb)·786432)/$::eyp_postgresql_pagesize)) you can set it to undef to disable
* for directly mapped variables (lc_messages, listen, port...) check postgres documentation:
  * port (default: 5432)
  * listen (default: \*)
  * max_connections (default: 100)
  * wal_level (default: hot_standby)
  * max_wal_senders (default: 0)
  * checkpoint_segments (default: 16)
  * wal_keep_segments (default: 0)
  * hot_standby
  * pidfile
  * log_directory
  * log_filename
  * track_activities
  * track_counts
  * autovacuum
  * autovacuum_vacuum_scale_factor
  * autovacuum_vacuum_threshold
  * autovacuum_analyze_scale_factor
  * autovacuum_analyze_threshold
  * timezone
  * log_timezone
  * superuser_reserved_connections
  * archive_mode
  * archive_timeout
  * archived_wals_retention
  * archived_wals_hour
  * archived_wals_minute
  * archived_wals_month
  * archived_wals_monthday
  * archived_wals_weekday
  * maintenance_work_mem
  * wal_buffers
  * work_mem
  * shared_buffers (autocalculated to 1/4 main RAM)
  * lc_messages (default: C)
  * lc_monetary (default: en_US.UTF-8)
  * lc_numeric (default: en_US.UTF-8)
  * lc_time (default: en_US.UTF-8)
  * default_text_search_config (default: pg_catalog.english)
  * shared_preload_libraries (default: undef)

usage example:

```puppet
class { 'postgresql': }
```

#### postgresql::streaming_replication

* **masterhost**: required, postgres master
* **masterusername**: required, replication username
* **masterpassword**: required, replication password
* **masterport** (default: port_default)
* **datadir** (default: datadir_default)

It requires to have **pg_basebackup** and the defined username already created on
the master DB

usage example:

```puppet
class { 'postgresql::streaming_replication':
  masterhost     => '192.168.56.29',
{'fraction_non_alphanumeric': 0.13302520401531018, 'fraction_numerical': 0.024409619412147036, 'mean_word_length': 4.958691910499139, 'pattern_counts': {'":': 2, '<': 14, '<?xml version=': 0, '>': 60, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 15, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2158808', 'n_tokens_mistral': 5072, 'n_tokens_neox': 4715, 'n_words': 1123}