vagrant、centos7でpostgres11のレプリケーション設定をプロビジョニング


作業ログ

2023/10/16

# vagrant、centos7でpostgres11のレプリケーション設定をプロビジョニング

## 目的
- コマンド一つでストリーミングレプリケーション設定したい。
- vagrantのプロビジョニングを利用してできることを確認する。

## Vagrantfile

```ruby

# -*- mode: ruby -*-
# vi: set ft=ruby :

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.define :coent7pg11a do | coent7 |
    if Vagrant.has_plugin?("vagrant-vbguest")
      config.vbguest.no_remote = true
      config.vbguest.auto_update = false
    end
    coent7.vm.box = "centos/7"
    coent7.vm.hostname = "coent7pg11a"
    coent7.vm.network "private_network", ip: "192.168.55.101", :netmask => "255.255.255.0"
    coent7.vm.provision "shell", inline: <<-SHELL1

      # download and install postgresql.
      whoami
      timedatectl set-timezone Asia/Tokyo
      date
      yum -y install wget > /dev/null
      yum -y install epel-release centos-release-scl > /dev/null
      wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm > /dev/null
      rpm -Uvh pgdg-redhat-repo-latest.noarch.rpm > /dev/null
      yum -y install postgresql11-server postgresql11-devel postgresql11-contrib > /dev/null 2&>1
      systemctl enable postgresql-11
      systemctl disable firewalld
      sudo sed -i -e "s/^SELINUX=enforcing$/SELINUX=disabled/g" /etc/selinux/config
      mkdir -m 777 -p /var/wal
      echo "export PATH=/usr/pgsql-11/bin:$PATH" >> ~/.bash_profile
      usermod -G wheel postgres

      # initialize postgresql server.
      whoami
      date
      /usr/pgsql-11/bin/postgresql-11-setup initdb
      sed -i 's/host.*all.*all.*127.0.0.1/#host    all             all             127.0.0.1/g' /var/lib/pgsql/11/data/pg_hba.conf
      echo "host    replication     postgres    192.168.55.102/32       trust" >> /var/lib/pgsql/11/data/pg_hba.conf
      echo "host    all             all         127.0.0.1/32            password" >> /var/lib/pgsql/11/data/pg_hba.conf
      echo "host    all             all         ::1/128                 password" >> /var/lib/pgsql/11/data/pg_hba.conf
      echo "host    all             all         192.168.55.0/24         trust" >> /var/lib/pgsql/11/data/pg_hba.conf
      echo "conf setting over."
      systemctl start postgresql-11.service
      sleep 5
      echo "restart over."
      /usr/pgsql-11/bin/pg_isready
      sudo -iu postgres psql -l

      # create users and databases.
      whoami
      date
      echo "postgres setting 1."
      sudo -iu postgres psql -U postgres -c "create user test with password 'test';"
      sudo -iu postgres psql -U postgres -c "alter user postgres with password 'postgres';"
      echo "postgres setting 1 alter db over."
      sudo -iu postgres psql -l

      # create users and databases.
      whoami
      date
su - postgres <<-EOF
whoami
echo "export PATH=/usr/pgsql-11/bin:$PATH" >> ~/.bash_profile
echo "postgres setting 1 export over."
EOF
      whoami
      systemctl restart postgresql-11.service
      echo "postgres setting 1 restart over."
su - postgres <<-EOF
whoami
echo "postgres setting 2."
createdb test
echo "postgres setting 2 createdb over."
psql -c "alter database test owner to test;"
psql test -c "create table test (id int, val text);"
psql test -c "insert into test (id, val) values (1, 'test');"
EOF

      whoami
      echo "listen_addresses='*'" >> /var/lib/pgsql/11/data/postgresql.conf
      echo "wal_level='replica'" >> /var/lib/pgsql/11/data/postgresql.conf
      echo "max_wal_senders='3'" >> /var/lib/pgsql/11/data/postgresql.conf
      echo "synchronous_standby_names='s1'" >> /var/lib/pgsql/11/data/postgresql.conf
      echo "synchronous_commit='on'" >> /var/lib/pgsql/11/data/postgresql.conf
      echo "archive_mode='on'" >> /var/lib/pgsql/11/data/postgresql.conf
      echo "archive_command='cp %p /var/wal/%f'" >> /var/lib/pgsql/11/data/postgresql.conf
      echo postgres | passwd --stdin postgres
      systemctl restart postgresql-11.service
      /usr/pgsql-11/bin/pg_isready
      echo 'coent7pg11a provision over.'
      date

SHELL1
  end

  config.vm.define :coent7pg11b do | coent7 |
    if Vagrant.has_plugin?("vagrant-vbguest")
      config.vbguest.no_remote = true
      config.vbguest.auto_update = false
    end
    coent7.vm.box = "centos/7"
    coent7.vm.hostname = "coent7pg11b"
    coent7.vm.network "private_network", ip: "192.168.55.102", :netmask => "255.255.255.0"
    coent7.vm.provision "shell", inline: <<-SHELL2

      # download and install postgresql.
      whoami
      timedatectl set-timezone Asia/Tokyo
      date
      yum -y install wget > /dev/null
      yum -y install epel-release centos-release-scl > /dev/null
      wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm > /dev/null
      rpm -Uvh pgdg-redhat-repo-latest.noarch.rpm > /dev/null
      yum -y install postgresql11-server postgresql11-devel postgresql11-contrib > /dev/null 2&>1
      systemctl enable postgresql-11
      systemctl disable firewalld
      sudo sed -i -e "s/^SELINUX=enforcing$/SELINUX=disabled/g" /etc/selinux/config
      mkdir -m 777 -p /var/wal
      echo "export PATH=/usr/pgsql-11/bin:$PATH" >> ~/.bash_profile
      usermod -G wheel postgres

      # initialize postgresql server
      whoami
      date
      /usr/pgsql-11/bin/postgresql-11-setup initdb
      systemctl start postgresql-11.service
      sleep 5
      echo "restart over."
      /usr/pgsql-11/bin/pg_isready
      sudo -iu postgres psql -l
      sudo -iu postgres echo "export PATH=/usr/pgsql-11/bin:$PATH" >> ~/.bash_profile

      # setting postgresql server
      whoami
      date
      sudo -iu postgres /usr/pgsql-11/bin/pg_ctl stop
      sudo rm -rf /var/lib/pgsql/11/data
      /usr/pgsql-11/bin/pg_isready -h 192.168.55.101
      sudo -iu postgres pg_basebackup -h 192.168.55.101 -U postgres -R -D /var/lib/pgsql/11/data/

      sudo -iu postgres echo "standby_mode='on'" >> /var/lib/pgsql/11/data/recovery.conf
      sudo -iu postgres echo "primary_conninfo='host=192.168.55.101 port=5432 user=postgres password=postgres application_name=s1'" >> /var/lib/pgsql/11/data/recovery.conf
      sudo -iu postgres echo "restore_command='scp 192.168.55.101:/var/wal/%f %p'" >> /var/lib/pgsql/11/data/recovery.conf

      echo "conf setting over."
      systemctl start postgresql-11.service
      sleep 5
      echo "restart over."
      /usr/pgsql-11/bin/pg_isready
      sudo -iu postgres psql -l
      echo postgres | passwd --stdin postgres
      echo 'coent7pg11b provision over.'
      sudo -iu postgres psql -U postgres -c "select * from pg_is_in_recovery();"
      date

SHELL2
  end
end

```

## 環境

```

C:\var\dev\DB\rep_test_centos7_pg11
$ wmic CPU get Name
Name
Intel(R) Core(TM) m3-7Y30 CPU @ 1.00GHz

C:\var\dev\DB\rep_test_centos7_pg11
$ wmic memorychip get Capacity
Capacity
4294967296
4294967296

C:\var\dev\DB\rep_test_centos7_pg11
$ wmic diskdrive get Model, Size
Model              Size
FORESEE 256GB SSD  256052966400

C:\var\dev\DB\rep_test_centos7_pg11
$ ver
Microsoft Windows [Version 10.0.19045.3570]

C:\var\dev\DB\rep_test_centos7_pg11
$ VBoxManage -v
6.1.34r150636

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant -v
Vagrant 2.2.19

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant plugin list
vagrant-vbguest (0.30.0, global)

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant box list
bento/centos-8.1 (virtualbox, 202005.21.0)
centos/7         (virtualbox, 2004.01)
higebu/vyos      (virtualbox, 20220521.02.17)
opensuse153      (virtualbox, 0)
ubuntu-20.04     (virtualbox, 0)

```

## 起動ログ

```
C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant status
Current machine states:

coent7pg11a               not created (virtualbox)
coent7pg11b               not created (virtualbox)

This environment represents multiple VMs. The VMs are all listed
above with their current state. For more information about a specific
VM, run `vagrant status NAME`.

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant up
Bringing machine 'coent7pg11a' up with 'virtualbox' provider...
Bringing machine 'coent7pg11b' up with 'virtualbox' provider...
==> coent7pg11a: Importing base box 'centos/7'...
==> coent7pg11a: Matching MAC address for NAT networking...
==> coent7pg11a: Checking if box 'centos/7' version '2004.01' is up to date...
==> coent7pg11a: Setting the name of the VM: rep_test_centos7_pg11_coent7pg11a_1697465087177_43514
==> coent7pg11a: Fixed port collision for 22 => 2222. Now on port 2201.
==> coent7pg11a: Clearing any previously set network interfaces...
==> coent7pg11a: Preparing network interfaces based on configuration...
    coent7pg11a: Adapter 1: nat
    coent7pg11a: Adapter 2: hostonly
==> coent7pg11a: Forwarding ports...
    coent7pg11a: 22 (guest) => 2201 (host) (adapter 1)
==> coent7pg11a: Booting VM...
==> coent7pg11a: Waiting for machine to boot. This may take a few minutes...
    coent7pg11a: SSH address: 127.0.0.1:2201
    coent7pg11a: SSH username: vagrant
    coent7pg11a: SSH auth method: private key
    coent7pg11a: Warning: Connection reset. Retrying...
    coent7pg11a: Warning: Connection aborted. Retrying...
    coent7pg11a: Warning: Remote connection disconnect. Retrying...
    coent7pg11a: Warning: Connection reset. Retrying...
    coent7pg11a: Warning: Connection aborted. Retrying...
    coent7pg11a: Warning: Connection reset. Retrying...
    coent7pg11a:
    coent7pg11a: Vagrant insecure key detected. Vagrant will automatically replace
    coent7pg11a: this with a newly generated keypair for better security.
    coent7pg11a:
    coent7pg11a: Inserting generated public key within guest...
    coent7pg11a: Removing insecure key from the guest if it's present...
    coent7pg11a: Key inserted! Disconnecting and reconnecting using new SSH key...
==> coent7pg11a: Machine booted and ready!
==> coent7pg11a: Checking for guest additions in VM...
    coent7pg11a: No guest additions were detected on the base box for this VM! Guest
    coent7pg11a: additions are required for forwarded ports, shared folders, host only
    coent7pg11a: networking, and more. If SSH fails on this machine, please install
    coent7pg11a: the guest additions and repackage the box to continue.
    coent7pg11a:
    coent7pg11a: This is not an error message; everything may continue to work properly,
    coent7pg11a: in which case you may ignore this message.
==> coent7pg11a: Setting hostname...
==> coent7pg11a: Configuring and enabling network interfaces...
==> coent7pg11a: Rsyncing folder: /cygdrive/c/var/dev/DB/rep_test_centos7_pg11/ => /vagrant
==> coent7pg11a: Running provisioner: shell...
    coent7pg11a: Running: inline script
    coent7pg11a: root
    coent7pg11a: Mon Oct 16 23:06:13 JST 2023
    coent7pg11a: warning: /var/cache/yum/x86_64/7/base/packages/wget-1.14-18.el7_6.1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
    coent7pg11a: Importing GPG key 0xF4A80EB5:
    coent7pg11a:  Userid     : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
    coent7pg11a:  Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
    coent7pg11a:  Package    : centos-release-7-8.2003.0.el7.centos.x86_64 (@anaconda)
    coent7pg11a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
    coent7pg11a: --2023-10-16 23:06:39--  https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    coent7pg11a: Resolving download.postgresql.org (download.postgresql.org)... 72.32.157.246, 87.238.57.227, 217.196.149.55, ...
    coent7pg11a: Connecting to download.postgresql.org (download.postgresql.org)|72.32.157.246|:443... connected.
    coent7pg11a: WARNING: cannot verify download.postgresql.org's certificate, issued by ‘/C=US/O=Let 's Encrypt/CN=R3’:
    coent7pg11a:   Issued certificate has expired.
    coent7pg11a: HTTP request sent, awaiting response... 200 OK
    coent7pg11a: Length: 8780 (8.6K) [application/x-redhat-package-manager]
    coent7pg11a: Saving to: ‘pgdg-redhat-repo-latest.noarch.rpm’
    coent7pg11a:
    coent7pg11a:      0K ........                                              100%  158M=0s
    coent7pg11a:
    coent7pg11a: 2023-10-16 23:06:42 (158 MB/s) - ‘pgdg-redhat-repo-latest.noarch.rpm’ saved [8780/8780]
    coent7pg11a:
    coent7pg11a: warning: pgdg-redhat-repo-latest.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
    coent7pg11a: Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.
    coent7pg11a: root
    coent7pg11a: Mon Oct 16 23:09:04 JST 2023
    coent7pg11a: Initializing database ... OK
    coent7pg11a:
    coent7pg11a: conf setting over.
    coent7pg11a: restart over.
    coent7pg11a: /var/run/postgresql:5432 - accepting connections
    coent7pg11a:                                   List of databases
    coent7pg11a:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    coent7pg11a: -----------+----------+----------+-------------+-------------+-----------------------
    coent7pg11a:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    coent7pg11a:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11a:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11a:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11a:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11a: (3 rows)
    coent7pg11a:
    coent7pg11a: root
    coent7pg11a: Mon Oct 16 23:09:13 JST 2023
    coent7pg11a: postgres setting 1.
    coent7pg11a: CREATE ROLE
    coent7pg11a: ALTER ROLE
    coent7pg11a: postgres setting 1 alter db over.
    coent7pg11a:                                   List of databases
    coent7pg11a:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    coent7pg11a: -----------+----------+----------+-------------+-------------+-----------------------
    coent7pg11a:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    coent7pg11a:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11a:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11a:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11a:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11a: (3 rows)
    coent7pg11a:
    coent7pg11a: root
    coent7pg11a: Mon Oct 16 23:09:14 JST 2023
    coent7pg11a: postgres
    coent7pg11a: postgres setting 1 export over.
    coent7pg11a: root
    coent7pg11a: postgres setting 1 restart over.
    coent7pg11a: postgres
    coent7pg11a: postgres setting 2.
    coent7pg11a: postgres setting 2 createdb over.
    coent7pg11a: ALTER DATABASE
    coent7pg11a: CREATE TABLE
    coent7pg11a: INSERT 0 1
    coent7pg11a: Last login: Mon Oct 16 23:09:14 JST 2023
    coent7pg11a: root
    coent7pg11a: Changing password for user postgres.
    coent7pg11a: passwd: all authentication tokens updated successfully.
    coent7pg11a: /var/run/postgresql:5432 - accepting connections
    coent7pg11a: coent7pg11a provision over.
    coent7pg11a: Mon Oct 16 23:09:15 JST 2023
==> coent7pg11b: Importing base box 'centos/7'...
==> coent7pg11b: Matching MAC address for NAT networking...
==> coent7pg11b: Checking if box 'centos/7' version '2004.01' is up to date...
==> coent7pg11b: Setting the name of the VM: rep_test_centos7_pg11_coent7pg11b_1697465370907_72545
==> coent7pg11b: Fixed port collision for 22 => 2222. Now on port 2202.
==> coent7pg11b: Clearing any previously set network interfaces...
==> coent7pg11b: Preparing network interfaces based on configuration...
    coent7pg11b: Adapter 1: nat
    coent7pg11b: Adapter 2: hostonly
==> coent7pg11b: Forwarding ports...
    coent7pg11b: 22 (guest) => 2202 (host) (adapter 1)
==> coent7pg11b: Booting VM...
==> coent7pg11b: Waiting for machine to boot. This may take a few minutes...
    coent7pg11b: SSH address: 127.0.0.1:2202
    coent7pg11b: SSH username: vagrant
    coent7pg11b: SSH auth method: private key
    coent7pg11b: Warning: Connection reset. Retrying...
    coent7pg11b: Warning: Connection aborted. Retrying...
    coent7pg11b: Warning: Connection reset. Retrying...
    coent7pg11b: Warning: Connection aborted. Retrying...
    coent7pg11b: Warning: Connection reset. Retrying...
    coent7pg11b:
    coent7pg11b: Vagrant insecure key detected. Vagrant will automatically replace
    coent7pg11b: this with a newly generated keypair for better security.
    coent7pg11b:
    coent7pg11b: Inserting generated public key within guest...
    coent7pg11b: Removing insecure key from the guest if it's present...
    coent7pg11b: Key inserted! Disconnecting and reconnecting using new SSH key...
==> coent7pg11b: Machine booted and ready!
==> coent7pg11b: Checking for guest additions in VM...
    coent7pg11b: No guest additions were detected on the base box for this VM! Guest
    coent7pg11b: additions are required for forwarded ports, shared folders, host only
    coent7pg11b: networking, and more. If SSH fails on this machine, please install
    coent7pg11b: the guest additions and repackage the box to continue.
    coent7pg11b:
    coent7pg11b: This is not an error message; everything may continue to work properly,
    coent7pg11b: in which case you may ignore this message.
==> coent7pg11b: Setting hostname...
==> coent7pg11b: Configuring and enabling network interfaces...
==> coent7pg11b: Rsyncing folder: /cygdrive/c/var/dev/DB/rep_test_centos7_pg11/ => /vagrant
==> coent7pg11b: Running provisioner: shell...
    coent7pg11b: Running: inline script
    coent7pg11b: root
    coent7pg11b: Mon Oct 16 23:10:56 JST 2023
    coent7pg11b: warning: /var/cache/yum/x86_64/7/base/packages/wget-1.14-18.el7_6.1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
    coent7pg11b: Importing GPG key 0xF4A80EB5:
    coent7pg11b:  Userid     : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
    coent7pg11b:  Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
    coent7pg11b:  Package    : centos-release-7-8.2003.0.el7.centos.x86_64 (@anaconda)
    coent7pg11b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
    coent7pg11b: --2023-10-16 23:11:21--  https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    coent7pg11b: Resolving download.postgresql.org (download.postgresql.org)... 72.32.157.246, 147.75.85.69, 217.196.149.55, ...
    coent7pg11b: Connecting to download.postgresql.org (download.postgresql.org)|72.32.157.246|:443... connected.
    coent7pg11b: WARNING: cannot verify download.postgresql.org's certificate, issued by ‘/C=US/O=Let 's Encrypt/CN=R3’:
    coent7pg11b:   Issued certificate has expired.
    coent7pg11b: HTTP request sent, awaiting response... 200 OK
    coent7pg11b: Length: 8780 (8.6K) [application/x-redhat-package-manager]
    coent7pg11b: Saving to: ‘pgdg-redhat-repo-latest.noarch.rpm’
    coent7pg11b:
    coent7pg11b:      0K ........                                              100%  117M=0s
    coent7pg11b:
    coent7pg11b: 2023-10-16 23:11:22 (117 MB/s) - ‘pgdg-redhat-repo-latest.noarch.rpm’ saved [8780/8780]
    coent7pg11b:
    coent7pg11b: warning: pgdg-redhat-repo-latest.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
    coent7pg11b: Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.
    coent7pg11b: root
    coent7pg11b: Mon Oct 16 23:14:34 JST 2023
    coent7pg11b: Initializing database ... OK
    coent7pg11b:
    coent7pg11b: restart over.
    coent7pg11b: /var/run/postgresql:5432 - accepting connections
    coent7pg11b:                                   List of databases
    coent7pg11b:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    coent7pg11b: -----------+----------+----------+-------------+-------------+-----------------------
    coent7pg11b:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    coent7pg11b:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11b:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11b:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11b:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11b: (3 rows)
    coent7pg11b:
    coent7pg11b: root
    coent7pg11b: Mon Oct 16 23:14:45 JST 2023
    coent7pg11b: waiting for server to shut down.... done
    coent7pg11b: server stopped
    coent7pg11b: 192.168.55.101:5432 - accepting connections
    coent7pg11b: conf setting over.
    coent7pg11b: restart over.
    coent7pg11b: /var/run/postgresql:5432 - accepting connections
    coent7pg11b:                                   List of databases
    coent7pg11b:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    coent7pg11b: -----------+----------+----------+-------------+-------------+-----------------------
    coent7pg11b:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    coent7pg11b:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11b:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11b:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    coent7pg11b:            |          |          |             |             | postgres=CTc/postgres
    coent7pg11b:  test      | test     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    coent7pg11b: (4 rows)
    coent7pg11b:
    coent7pg11b: Changing password for user postgres.
    coent7pg11b: passwd: all authentication tokens updated successfully.
    coent7pg11b: coent7pg11b provision over.
    coent7pg11b:  pg_is_in_recovery
    coent7pg11b: -------------------
    coent7pg11b:  t
    coent7pg11b: (1 row)
    coent7pg11b:
    coent7pg11b: Mon Oct 16 23:14:58 JST 2023

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant status
Current machine states:

coent7pg11a               running (virtualbox)
coent7pg11b               running (virtualbox)

This environment represents multiple VMs. The VMs are all listed
above with their current state. For more information about a specific
VM, run `vagrant status NAME`.

```

## 動作確認ログ

```

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant ssh coent7pg11a
[vagrant@coent7pg11a ~]$ pwd
/home/vagrant
[vagrant@coent7pg11a ~]$ uname -a
Linux coent7pg11a 3.10.0-1127.el7.x86_64 #1 SMP Tue Mar 31 23:36:51 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
[vagrant@coent7pg11a ~]$ cat /etc/system-release
CentOS Linux release 7.8.2003 (Core)
[vagrant@coent7pg11a ~]$ whoami
vagrant
[vagrant@coent7pg11a ~]$ hostname
coent7pg11a
[vagrant@coent7pg11a ~]$ ip a | grep eth | grep "inet "
    inet 10.0.2.15/24 brd 10.0.2.255 scope global noprefixroute dynamic eth0
    inet 192.168.55.101/24 brd 192.168.55.255 scope global noprefixroute eth1
[vagrant@coent7pg11a ~]$ ps fax | grep post
 3951 pts/0    S+     0:00              \_ grep --color=auto post
  850 ?        Ss     0:00 /usr/libexec/postfix/master -w
 3740 ?        Ss     0:00 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
 3742 ?        Ss     0:00  \_ postgres: logger
 3744 ?        Ss     0:00  \_ postgres: checkpointer
 3745 ?        Ss     0:00  \_ postgres: background writer
 3746 ?        Ss     0:00  \_ postgres: walwriter
 3747 ?        Ss     0:00  \_ postgres: autovacuum launcher
 3748 ?        Ss     0:00  \_ postgres: archiver   last was 000000010000000000000002.00000060.backup  3749 ?        Ss     0:00  \_ postgres: stats collector
 3750 ?        Ss     0:00  \_ postgres: logical replication launcher
 3776 ?        Ss     0:00  \_ postgres: walsender postgres 192.168.55.102(38864) streaming 0/3000140 [vagrant@coent7pg11a ~]$ psql --version
psql (PostgreSQL) 11.21
[vagrant@coent7pg11a ~]$ sudo -iu postgres pg_isready
/var/run/postgresql:5432 - accepting connections
[vagrant@coent7pg11a ~]$ sudo -iu postgres psql -c "select version()"
                                                 version

----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

[vagrant@coent7pg11a ~]$ sudo -iu postgres psql -c "\l"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | test     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

[vagrant@coent7pg11a ~]$ sudo -iu postgres psql test -c "\d"
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

[vagrant@coent7pg11a ~]$ sudo -iu postgres psql test -c "\d test"
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 val    | text    |           |          |

[vagrant@coent7pg11a ~]$ sudo -iu postgres psql test -c "select * from test"
 id | val
----+------
  1 | test
(1 row)

[vagrant@coent7pg11a ~]$ sudo -iu postgres psql << EOF
> \x auto
> select * from pg_stat_replication
> EOF
Expanded display is used automatically.
-[ RECORD 1 ]----+-----------------------------
pid              | 3776
usesysid         | 10
usename          | postgres
application_name | s1
client_addr      | 192.168.55.102
client_hostname  |
client_port      | 38864
backend_start    | 2023-10-16 23:14:53.01264+09
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000140
write_lsn        | 0/3000140
flush_lsn        | 0/3000140
replay_lsn       | 0/3000140
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | sync

[vagrant@coent7pg11a ~]$ exit
logout

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant halt
==> coent7pg11b: Attempting graceful shutdown of VM...
==> coent7pg11a: Attempting graceful shutdown of VM...

C:\var\dev\DB\rep_test_centos7_pg11
$ vagrant status
Current machine states:

coent7pg11a               poweroff (virtualbox)
coent7pg11b               poweroff (virtualbox)

This environment represents multiple VMs. The VMs are all listed
above with their current state. For more information about a specific
VM, run `vagrant status NAME`.

C:\var\dev\DB\rep_test_centos7_pg11
$


```

## 調べた点
- 現在ではcentos7のyumがデフォルトでは失敗する。サポート終了後の現在も利用可能なCentOS Software Collectionsを導入。
- selinuxの止め方は、公式の推奨はPermissiveだが、簡単な検証なのでdisabled。
- sudoとpsqlのcオプションを利用して、psqlをsuせずにワンライナー操作。
- ヒアドキュメントを利用した複数行の設定。

以上