PuppetでのPostgreSQLインストール方法

訳あってPuppetでPostgreSQLをインストールする方法を探していて、[PuppetLabsが提供しているモジュール](https://github.com/puppetlabs/puppetlabs-postgresql)が使えそうだったので触った手順をメモしておく。

環境は
* CentOS 7.2
* Puppet 3.8.7

Puppetはスタンドアローンモード、自サーバ適用で試した。


# Puppetのインストール

デフォルトではyumでは入らないのでPuppetLabsが提供しているレポジトリをインストールする。

```
[root@localhost ~]# rpm -ivh http://yum.puppetlabs.com/puppetlabs-release-el-7.noarch.rpm
http://yum.puppetlabs.com/puppetlabs-release-el-7.noarch.rpm を取得中
警告: /var/tmp/rpm-tmp.d38UNf: ヘッダー V4 RSA/SHA1 Signature、鍵 ID 4bd6ec30: NOKEY
準備しています...              ################################# [100%]
更新中 / インストール中...
   1:puppetlabs-release-7-12          ################################# [100%]
```

puppetをyumでインストール。

```
[root@localhost ~]# yum install puppet
```

```
[root@localhost ~]# puppet --version
3.8.7
```

manifest置き場のディレクトリを作っておく。なぜインストール時に作ってくれないのか。。。

```
[root@localhost ~]# mkdir -p /etc/puppet/manifests
```

# PuppetlabsのPostgreSQLモジュールのインストール

`puppet module`コマンドでインストールできる。

```
[root@localhost ~]# puppet module install puppetlabs-postgresql
Notice: Preparing to install into /etc/puppet/modules ...
Notice: Downloading from https://forgeapi.puppetlabs.com ...
Notice: Installing -- do not interrupt ...
/etc/puppet/modules
└─┬ puppetlabs-postgresql (v4.7.1)
  ├── puppetlabs-apt (v2.2.2)
  ├── puppetlabs-concat (v2.1.0)
  └── puppetlabs-stdlib (v4.12.0)
```

ちなみに`puppet module`コマンドは検索もできる。

```
[root@localhost postgresql]# puppet module search postgresql
Notice: Searching https://forgeapi.puppetlabs.com ...
NAME                                     DESCRIPTION                   AUTHOR          KEYWORDS     
puppetlabs-postgresql                    Offers support for basic ...  @puppetlabs     postgresql   
example42-postgresql                     Puppet module for postgresql  @example42      postgresql   
akumria-postgresql                       Install and configure the...  @akumria        postgresql   
godp1301-postgresql                      PostgreSQL defined resour...  @godp1301                    
herculesteam-augeasproviders_postgresql  Augeas-based postgresql t...  @herculesteam   postgresql   
npwalker-pe_external_postgresql          A module for installing a...  @npwalker                    
inkling-postgresql                       PostgreSQL defined resour...  @inkling        postgresql   
icinga-icingaweb2                        Module to install and mai...  @icinga         postgresql   
stschulte-postgres                       Manage postgres roles and...  @stschulte      postgresql   
ULHPC-backupninja                        Install and configure bac...  @ULHPC          postgresql   
rhoml-pgbouncer                          Installs and configure pg...  @rhoml          postgresql   
chrisjohntapp-repmgr                     Module to install and con...  @chrisjohntapp  postgresql   
cpitman-database_schema                  Manages database schema c...  @cpitman        postgresql   
it2ndq-barman                            Barman (Backup and Recove...  @it2ndq         postgresql   
leoc-phppgadmin                          Deprecated! Use velaluqa/...  @leoc           postgresql   
mwhahaha-pgpool                          A puppet module to config...  @mwhahaha       postgresql   
jlcox-postgresplus                       Installation and confgiur...  @jlcox          postgresql   
Envek-pgtune                             Configures PostgreSQL ins...  @Envek          postgresql   
domcleal-augeasproviders                 Alternative Augeas-based ...  @domcleal       postgresql   
jhoblitt-pureftpd                        Manages the pure-ftpd pac...  @jhoblitt       postgresql   
knowshan-phppgadmin                      Install and configure php...  @knowshan       postgresql   
jdowning-wal_e                           Setup wal_e                   @jdowning       postgresql   
velaluqa-phppgadmin                      Puppet PhpPgAdmin Module      @velaluqa       postgresql   
SchnWalter-happydev                      Setup a Development Envir...  @SchnWalter     postgresql   
mjhas-mailserver                         This Module provides a ma...  @mjhas          postgresql   
echoes-postgres_xc                       Postgres-XC Module            @echoes         postgresql
```

## Proxy環境下でのモジュールインストール

以下のパラメータを`/etc/puppet/puppet.conf`に設定する。

* http_proxy_host
* http_proxy_port
* http_proxy_user
* http_proxy_password

https://docs.puppet.com/puppet/latest/reference/configuration.html#httpproxyhost

# PostgreSQLのインストール＆起動

[READMEのUsage](https://github.com/puppetlabs/puppetlabs-postgresql#usage)を見ると、postgresql::serverクラスをmanifest内で使えばよいらしい。

とりあえず、自サーバにインストールするようなmanifestを書いてみる。

```/etc/puppet/manifests/site.pp 

class {'postgresql::server': 
    listen_addresses  => '*',
    postgres_password => 'postgres',
}

```

適用してみる。

```
[root@localhost manifests]# puppet apply /etc/puppet/manifests/site.pp 
Warning: Config file /etc/puppet/hiera.yaml not found, using Hiera defaults
Warning: Scope(Concat[/var/lib/pgsql/data/pg_ident.conf]): The $force parameter to concat is deprecated and has no effect.
Notice: Compiled catalog for localhost in environment production in 0.87 seconds
Notice: /Stage[main]/Postgresql::Client/Package[postgresql-client]/ensure: created
Notice: /Stage[main]/Postgresql::Client/File[/usr/local/bin/validate_postgresql_connection.sh]/ensure: defined content as '{md5}20301932819f035492a30880f5bf335a'
Notice: /Stage[main]/Postgresql::Server::Install/Package[postgresql-server]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Initdb/Exec[postgresql_initdb]/returns: executed successfully
Notice: /Stage[main]/Postgresql::Server::Config/File[systemd-override]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Postgresql::Server::Config_entry[data_directory]/Postgresql_conf[data_directory]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/data/pg_ident.conf]/File[/var/lib/pgsql/data/pg_ident.conf]/content: content changed '{md5}f11c8332d3f444148c0b8ee83ec5fc6d' to '{md5}9300ac105fe777787ac9e793b8df8d25'
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/data/pg_ident.conf]/File[/var/lib/pgsql/data/pg_ident.conf]/mode: mode changed '0600' to '0640'
Notice: /Stage[main]/Postgresql::Server::Config/Exec[restart-systemd]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Config/Postgresql::Server::Config_entry[listen_addresses]/Postgresql_conf[listen_addresses]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Postgresql::Server::Config_entry[port]/Postgresql_conf[port]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/data/pg_hba.conf]/File[/var/lib/pgsql/data/pg_hba.conf]/content: content changed '{md5}9784211d3c0e7ad5a539b9dcb964b9c3' to '{md5}c31ee6f98ede22591533e8056938c902'
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/data/pg_hba.conf]/File[/var/lib/pgsql/data/pg_hba.conf]/mode: mode changed '0600' to '0640'
Notice: /Stage[main]/Postgresql::Server::Service/Anchor[postgresql::server::service::begin]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Service/Service[postgresqld]/ensure: ensure changed 'stopped' to 'running'
Notice: /Stage[main]/Postgresql::Server::Service/Postgresql::Validate_db_connection[validate_service_is_running]/Exec[validate postgres connection for @:5432/postgres]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Service/Anchor[postgresql::server::service::end]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Reload/Exec[postgresql_reload]: Triggered 'refresh' from 1 events
Notice: Finished catalog run in 45.50 seconds
[root@localhost manifests]# pgrep -a postgres
29258 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
29259 postgres: logger process                        
29261 postgres: checkpointer process                  
29262 postgres: writer process                        
29263 postgres: wal writer process                    
29264 postgres: autovacuum launcher process           
29265 postgres: stats collector process
[root@localhost manifests]# rpm -qa | grep postgres
postgresql-9.2.15-1.el7_2.x86_64
postgresql-libs-9.2.15-1.el7_2.x86_64
postgresql-server-9.2.15-1.el7_2.x86_64
```

ふむ。OSデフォルトで用意しているPostgreSQLが入るようだ。

# インストールするPostgreSQLのバージョンを変える

ドキュメントを読んでみたところ、インストールするPostgreSQLのバージョンは指定できるらしい。指定した場合PostgreSQLオフィシャルのパッケージが利用されるとある。こっちの方が良いのでmanifestを修正してみる。

```/etc/puppet/mafiests/site.pp 
class { 'postgresql::globals':
    manage_package_repo => true,
    version             => '9.5',
}

class {'postgresql::server': 
    listen_addresses  => '*',
    postgres_password => 'postgres',
}

```

`manage_package_repo`を`true`にして、`version`で入れたいバージョンを指定すればOK。
適用してみる。


```
[root@localhost manifests]# puppet apply /etc/puppet/manifests/site.pp 
Warning: Config file /etc/puppet/hiera.yaml not found, using Hiera defaults
Warning: Scope(Concat[/var/lib/pgsql/9.5/data/pg_ident.conf]): The $force parameter to concat is deprecated and has no effect.
Notice: Compiled catalog for localhost in environment production in 0.99 seconds
Notice: /Stage[main]/Postgresql::Repo::Yum_postgresql_org/File[/etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-95]/ensure: defined content as '{md5}78b5db170d33f80ad5a47863a7476b22'
Notice: /Stage[main]/Postgresql::Repo::Yum_postgresql_org/Yumrepo[yum.postgresql.org]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Install/Package[postgresql-server]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Initdb/Exec[postgresql_initdb]/returns: executed successfully
Notice: /Stage[main]/Postgresql::Server::Config/File[systemd-override]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Postgresql::Server::Config_entry[data_directory]/Postgresql_conf[data_directory]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/9.5/data/pg_ident.conf]/File[/var/lib/pgsql/9.5/data/pg_ident.conf]/content: content changed '{md5}f11c8332d3f444148c0b8ee83ec5fc6d' to '{md5}9300ac105fe777787ac9e793b8df8d25'
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/9.5/data/pg_ident.conf]/File[/var/lib/pgsql/9.5/data/pg_ident.conf]/mode: mode changed '0600' to '0640'
Notice: /Stage[main]/Postgresql::Server::Config/Exec[restart-systemd]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Config/Postgresql::Server::Config_entry[listen_addresses]/Postgresql_conf[listen_addresses]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Postgresql::Server::Config_entry[port]/Postgresql_conf[port]/ensure: created
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/9.5/data/pg_hba.conf]/File[/var/lib/pgsql/9.5/data/pg_hba.conf]/content: content changed '{md5}16401f4bb2f7a0cbeac3ecb5133e2904' to '{md5}c31ee6f98ede22591533e8056938c902'
Notice: /Stage[main]/Postgresql::Server::Config/Concat[/var/lib/pgsql/9.5/data/pg_hba.conf]/File[/var/lib/pgsql/9.5/data/pg_hba.conf]/mode: mode changed '0600' to '0640'
Notice: /Stage[main]/Postgresql::Server::Service/Anchor[postgresql::server::service::begin]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Service/Service[postgresqld]/ensure: ensure changed 'stopped' to 'running'
Notice: /Stage[main]/Postgresql::Server::Service/Postgresql::Validate_db_connection[validate_service_is_running]/Exec[validate postgres connection for @:5432/postgres]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Service/Anchor[postgresql::server::service::end]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Reload/Exec[postgresql_reload]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Postgresql::Server::Passwd/Exec[set_postgres_postgrespw]/returns: ALTER ROLE
Notice: /Stage[main]/Postgresql::Server::Passwd/Exec[set_postgres_postgrespw]/returns: executed successfully
Notice: Finished catalog run in 51.49 seconds
[root@localhost manifests]# pgrep -a postgres
30567 /usr/pgsql-9.5/bin/postgres -D /var/lib/pgsql/9.5/data
30568 postgres: logger process                              
30570 postgres: checkpointer process                        
30571 postgres: writer process                              
30572 postgres: wal writer process                          
30573 postgres: autovacuum launcher process                 
30574 postgres: stats collector process                     
[root@localhost manifests]# rpm -qa | grep postgres
postgresql95-server-9.5.3-2PGDG.rhel7.x86_64
postgresql95-9.5.3-2PGDG.rhel7.x86_64
postgresql95-libs-9.5.3-2PGDG.rhel7.x86_64
```

いいですね。

# データベースとユーザを作成してみる

機能があるというので試してみる。

```/etc/puppet/manifests/site.pp 
class { 'postgresql::globals':
    manage_package_repo => true,
    version             => '9.5',
}

class {'postgresql::server': 
    listen_addresses  => '*',
    postgres_password => 'postgres',
}

postgresql::server::db { 'test': 
    user      => 'testuser',
    password  => 'password',
    grant     => 'all',
    require   => Class['postgresql::server'],
}

```

`postgresql::server::db`はclassではないので若干きもい。（真面目にmanifestを構造化していないのも悪い気がするが）

```
[root@localhost manifests]# puppet apply /etc/puppet/manifests/site.pp 
Warning: Config file /etc/puppet/hiera.yaml not found, using Hiera defaults
Warning: Scope(Concat[/var/lib/pgsql/9.5/data/pg_ident.conf]): The $force parameter to concat is deprecated and has no effect.
Notice: Compiled catalog for localhost in environment production in 1.16 seconds
Notice: /Stage[main]/Postgresql::Server::Service/Service[postgresqld]/ensure: ensure changed 'stopped' to 'running'
Notice: /Stage[main]/Main/Postgresql::Server::Db[test]/Postgresql::Server::Role[testuser]/Postgresql_psql[CREATE ROLE testuser ENCRYPTED PASSWORD ****]/command: command changed 'notrun' to 'CREATE ROLE "testuser" ENCRYPTED PASSWORD '$NEWPGPASSWD' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER  CONNECTION LIMIT -1'
Notice: /Stage[main]/Main/Postgresql::Server::Db[test]/Postgresql::Server::Database[test]/Postgresql_psql[Create db 'test']/command: command changed 'notrun' to 'CREATE DATABASE "test" WITH OWNER="postgres" TEMPLATE="template0"   '
Notice: /Stage[main]/Main/Postgresql::Server::Db[test]/Postgresql::Server::Database[test]/Postgresql_psql[REVOKE CONNECT ON DATABASE "test" FROM public]: Triggered 'refresh' from 1 events
Notice: /Stage[main]/Main/Postgresql::Server::Db[test]/Postgresql::Server::Database_grant[GRANT testuser - all - test]/Postgresql::Server::Grant[database:GRANT testuser - all - test]/Postgresql_psql[grant:database:GRANT testuser - all - test]/command: command changed 'notrun' to 'GRANT ALL ON DATABASE "test" TO
      "testuser"'
Notice: Finished catalog run in 1.96 seconds
[root@localhost manifests]# pgrep -a postgres
31428 /usr/pgsql-9.5/bin/postgres -D /var/lib/pgsql/9.5/data
31430 postgres: logger process                              
31432 postgres: checkpointer process                        
31433 postgres: writer process                              
31434 postgres: wal writer process                          
31435 postgres: autovacuum launcher process                 
31436 postgres: stats collector process                     
[root@localhost manifests]# sudo -u postgres psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |      アクセス権       
-----------+----------+------------------+-------------+-------------------+-----------------------
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 test      | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =T/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres+
           |          |                  |             |                   | testuser=CTc/postgres
(4 行)

[postgres@localhost ~]$ psql -h localhost -Utestuser -d test
ユーザ testuser のパスワード: 
psql (9.5.3)
"help" でヘルプを表示します.

localhost 31589 test=> \d
リレーションがありません。

```

うーん。このあたりは自前でやったほうが制御が楽な気がする。

以上！

# 参考

* [How To Install Puppet In Standalone Mode on CentOS 7](https://www.digitalocean.com/community/tutorials/how-to-install-puppet-in-standalone-mode-on-centos-7)
* [Puppetlabs-Postgresql: A Puppet Enterprise Supported Module](https://puppet.com/blog/puppetlabs-postgresql-a-puppet-enterprise-supported-module)
* [puppetlabs-postgresqlモジュールはDB内の管理までできる](http://www.aware.jp/diary/?date=20130220)