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


作業ログ

2023/10/18

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

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

## Vagrantfile

```ruby

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

VAGRANTFILE_API_VERSION = "2"

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

      # download and install postgresql.
      whoami
      timedatectl set-timezone Asia/Tokyo
      date
      sed -i -e "s/^mirrorlist/#mirrorlist/g" -e "s@^#baseurl=http://mirror@baseurl=http://vault@g" /etc/yum.repos.d/CentOS-*repo ; echo "exit with : $?"
      echo "dnf update. This may take a few minutes... please wait..."
      dnf -qy update ; echo "exit with : $?"
      date
      sleep 5
      sed -i -e "s/^mirrorlist/#mirrorlist/g" -e "s@^#baseurl=http://mirror@baseurl=http://vault@g" /etc/yum.repos.d/CentOS-*repo ; echo "exit with : $?"
      sleep 5
      echo "repo edit end"
      dnf -qy install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm ; echo "exit with : $?"
      dnf -qy module disable postgresql ; echo "exit with : $?"
      dnf -qy install postgresql14-server ; echo "exit with : $?"
      systemctl enable postgresql-14
      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-14/bin:$PATH" >> ~/.bash_profile
      usermod -G wheel postgres

      # initialize postgresql server.
      whoami
      date
      /usr/pgsql-14/bin/postgresql-14-setup initdb
      sed -i 's/host.*all.*all.*127.0.0.1/#host    all             all             127.0.0.1/g' /var/lib/pgsql/14/data/pg_hba.conf
      echo "host    replication     postgres    192.168.55.102/32       trust" >> /var/lib/pgsql/14/data/pg_hba.conf
      echo "host    all             all         127.0.0.1/32            password" >> /var/lib/pgsql/14/data/pg_hba.conf
      echo "host    all             all         ::1/128                 password" >> /var/lib/pgsql/14/data/pg_hba.conf
      echo "host    all             all         192.168.55.0/24         trust" >> /var/lib/pgsql/14/data/pg_hba.conf
      echo "conf setting over."
      systemctl start postgresql-14.service
      sleep 5
      echo "restart over."
      /usr/pgsql-14/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-14/bin:$PATH" >> ~/.bash_profile
echo "postgres setting 1 export over."
EOF
      whoami
      systemctl restart postgresql-14.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/14/data/postgresql.conf
      echo "wal_level='replica'" >> /var/lib/pgsql/14/data/postgresql.conf
      echo "max_wal_senders='3'" >> /var/lib/pgsql/14/data/postgresql.conf
      echo "synchronous_standby_names='s1'" >> /var/lib/pgsql/14/data/postgresql.conf
      echo "synchronous_commit='on'" >> /var/lib/pgsql/14/data/postgresql.conf
      echo "archive_mode='on'" >> /var/lib/pgsql/14/data/postgresql.conf
      echo "archive_command='cp %p /var/wal/%f'" >> /var/lib/pgsql/14/data/postgresql.conf
      echo postgres | passwd --stdin postgres
      systemctl restart postgresql-14.service
      /usr/pgsql-14/bin/pg_isready
      echo 'centos8pg14a provision over.'
      date

SHELL1
  end

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

      # download and install postgresql.
      whoami
      timedatectl set-timezone Asia/Tokyo
      date
      sed -i -e "s/^mirrorlist/#mirrorlist/g" -e "s@^#baseurl=http://mirror@baseurl=http://vault@g" /etc/yum.repos.d/CentOS-*repo ; echo "exit with : $?"
      echo "dnf update. This may take a few minutes... please wait..."
      dnf -qy update ; echo "exit with : $?"
      date
      sleep 5
      sed -i -e "s/^mirrorlist/#mirrorlist/g" -e "s@^#baseurl=http://mirror@baseurl=http://vault@g" /etc/yum.repos.d/CentOS-*repo ; echo "exit with : $?"
      sleep 5
      echo "repo edit end"
      dnf -qy install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm ; echo "exit with : $?"
      dnf -qy module disable postgresql ; echo "exit with : $?"
      dnf -qy install postgresql14-server ; echo "exit with : $?"
      systemctl enable postgresql-14
      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-14/bin:$PATH" >> ~/.bash_profile
      usermod -G wheel postgres

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

      # setting postgresql server
      whoami
      date
      sudo -iu postgres /usr/pgsql-14/bin/pg_ctl stop
      sudo rm -rf /var/lib/pgsql/14/data
      /usr/pgsql-14/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/14/data/
      sed -i -e "s/'$/ application_name=s1'/g" /var/lib/pgsql/14/data/postgresql.auto.conf ; echo "exit with : $?"

      echo "conf setting over."
      systemctl start postgresql-14.service
      sleep 5
      echo "restart over."
      /usr/pgsql-14/bin/pg_isready
      sudo -iu postgres psql -l
      echo postgres | passwd --stdin postgres
      echo 'centos8pg14b 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_centos8_pg14
$ wmic CPU get Name
Name
Intel(R) Core(TM) m3-7Y30 CPU @ 1.00GHz

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

C:\var\dev\DB\rep_test_centos8_pg14
$ wmic diskdrive get Model
Model
FORESEE 256GB SSD

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

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

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

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

C:\var\dev\DB\rep_test_centos8_pg14
$ 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_centos8_pg14
$ vagrant status
Current machine states:

centos8pg14a              not created (virtualbox)
centos8pg14b              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_centos8_pg14
$ cmd /V:ON /C echo !DATE! !TIME! & vagrant up & cmd /V:ON /C echo !DATE! !TIME!
2023/10/19  0:42:20.20
Bringing machine 'centos8pg14a' up with 'virtualbox' provider...
Bringing machine 'centos8pg14b' up with 'virtualbox' provider...
==> centos8pg14a: Importing base box 'bento/centos-8.1'...
==> centos8pg14a: Matching MAC address for NAT networking...
==> centos8pg14a: Checking if box 'bento/centos-8.1' version '202005.21.0' is up to date...
==> centos8pg14a: Setting the name of the VM: rep_test_centos8_pg14_centos8pg14a_1697643761605_63745
==> centos8pg14a: Fixed port collision for 22 => 2222. Now on port 2201.
==> centos8pg14a: Clearing any previously set network interfaces...
==> centos8pg14a: Preparing network interfaces based on configuration...
    centos8pg14a: Adapter 1: nat
    centos8pg14a: Adapter 2: hostonly
==> centos8pg14a: Forwarding ports...
    centos8pg14a: 22 (guest) => 2201 (host) (adapter 1)
==> centos8pg14a: Booting VM...
==> centos8pg14a: Waiting for machine to boot. This may take a few minutes...
    centos8pg14a: SSH address: 127.0.0.1:2201
    centos8pg14a: SSH username: vagrant
    centos8pg14a: SSH auth method: private key
    centos8pg14a:
    centos8pg14a: Vagrant insecure key detected. Vagrant will automatically replace
    centos8pg14a: this with a newly generated keypair for better security.
    centos8pg14a:
    centos8pg14a: Inserting generated public key within guest...
    centos8pg14a: Removing insecure key from the guest if it's present...
    centos8pg14a: Key inserted! Disconnecting and reconnecting using new SSH key...
==> centos8pg14a: Machine booted and ready!
==> centos8pg14a: Checking for guest additions in VM...
==> centos8pg14a: Setting hostname...
==> centos8pg14a: Configuring and enabling network interfaces...
==> centos8pg14a: Mounting shared folders...
    centos8pg14a: /vagrant => C:/var/dev/DB/rep_test_centos8_pg14
==> centos8pg14a: Running provisioner: shell...
    centos8pg14a: Running: inline script
    centos8pg14a: root
    centos8pg14a: Thu Oct 19 00:44:01 JST 2023
    centos8pg14a: exit with : 0
    centos8pg14a: dnf update. This may take a few minutes... please wait...
    centos8pg14a: exit with : 0
    centos8pg14a: Thu Oct 19 00:54:49 JST 2023
    centos8pg14a: exit with : 0
    centos8pg14a: repo edit end
    centos8pg14a:
    centos8pg14a: Installed:
    centos8pg14a:   pgdg-redhat-repo-42.0-35PGDG.noarch
    centos8pg14a:
    centos8pg14a: exit with : 0
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a: exit with : 0
    centos8pg14a: Importing GPG key 0x442DF0F8:
    centos8pg14a:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14a:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14a:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14a:
    centos8pg14a: Installed:
    centos8pg14a:   lz4-1.8.3-3.el8_4.x86_64
    centos8pg14a:   postgresql14-14.9-2PGDG.rhel8.x86_64
    centos8pg14a:   postgresql14-libs-14.9-2PGDG.rhel8.x86_64
    centos8pg14a:   postgresql14-server-14.9-2PGDG.rhel8.x86_64
    centos8pg14a:
    centos8pg14a: exit with : 0
    centos8pg14a: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → / usr/lib/systemd/system/postgresql-14.service.
    centos8pg14a: root
    centos8pg14a: Thu Oct 19 00:56:17 JST 2023
    centos8pg14a: Initializing database ... OK
    centos8pg14a:
    centos8pg14a: conf setting over.
    centos8pg14a: restart over.
    centos8pg14a: /var/run/postgresql:5432 - accepting connections
    centos8pg14a:                                   List of databases
    centos8pg14a:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    centos8pg14a: -----------+----------+----------+-------------+-------------+-----------------------
    centos8pg14a:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    centos8pg14a:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14a:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14a:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14a:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14a: (3 rows)
    centos8pg14a:
    centos8pg14a: root
    centos8pg14a: Thu Oct 19 00:56:26 JST 2023
    centos8pg14a: postgres setting 1.
    centos8pg14a: CREATE ROLE
    centos8pg14a: ALTER ROLE
    centos8pg14a: postgres setting 1 alter db over.
    centos8pg14a:                                   List of databases
    centos8pg14a:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    centos8pg14a: -----------+----------+----------+-------------+-------------+-----------------------
    centos8pg14a:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    centos8pg14a:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14a:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14a:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14a:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14a: (3 rows)
    centos8pg14a:
    centos8pg14a: root
    centos8pg14a: Thu Oct 19 00:56:26 JST 2023
    centos8pg14a: postgres
    centos8pg14a: postgres setting 1 export over.
    centos8pg14a: root
    centos8pg14a: postgres setting 1 restart over.
    centos8pg14a: Last login: Thu Oct 19 00:56:26 JST 2023
    centos8pg14a: postgres
    centos8pg14a: postgres setting 2.
    centos8pg14a: postgres setting 2 createdb over.
    centos8pg14a: ALTER DATABASE
    centos8pg14a: CREATE TABLE
    centos8pg14a: INSERT 0 1
    centos8pg14a: root
    centos8pg14a: Changing password for user postgres.
    centos8pg14a: passwd: all authentication tokens updated successfully.
    centos8pg14a: /var/run/postgresql:5432 - accepting connections
    centos8pg14a: centos8pg14a provision over.
    centos8pg14a: Thu Oct 19 00:56:28 JST 2023
==> centos8pg14b: Importing base box 'bento/centos-8.1'...
==> centos8pg14b: Matching MAC address for NAT networking...
==> centos8pg14b: Checking if box 'bento/centos-8.1' version '202005.21.0' is up to date...
==> centos8pg14b: Setting the name of the VM: rep_test_centos8_pg14_centos8pg14b_1697644602261_98725
==> centos8pg14b: Fixed port collision for 22 => 2222. Now on port 2202.
==> centos8pg14b: Clearing any previously set network interfaces...
==> centos8pg14b: Preparing network interfaces based on configuration...
    centos8pg14b: Adapter 1: nat
    centos8pg14b: Adapter 2: hostonly
==> centos8pg14b: Forwarding ports...
    centos8pg14b: 22 (guest) => 2202 (host) (adapter 1)
==> centos8pg14b: Booting VM...
==> centos8pg14b: Waiting for machine to boot. This may take a few minutes...
    centos8pg14b: SSH address: 127.0.0.1:2202
    centos8pg14b: SSH username: vagrant
    centos8pg14b: SSH auth method: private key
    centos8pg14b:
    centos8pg14b: Vagrant insecure key detected. Vagrant will automatically replace
    centos8pg14b: this with a newly generated keypair for better security.
    centos8pg14b:
    centos8pg14b: Inserting generated public key within guest...
    centos8pg14b: Removing insecure key from the guest if it's present...
    centos8pg14b: Key inserted! Disconnecting and reconnecting using new SSH key...
==> centos8pg14b: Machine booted and ready!
==> centos8pg14b: Checking for guest additions in VM...
==> centos8pg14b: Setting hostname...
==> centos8pg14b: Configuring and enabling network interfaces...
==> centos8pg14b: Mounting shared folders...
    centos8pg14b: /vagrant => C:/var/dev/DB/rep_test_centos8_pg14
==> centos8pg14b: Running provisioner: shell...
    centos8pg14b: Running: inline script
    centos8pg14b: root
    centos8pg14b: Thu Oct 19 00:57:58 JST 2023
    centos8pg14b: exit with : 0
    centos8pg14b: dnf update. This may take a few minutes... please wait...
    centos8pg14b: exit with : 0
    centos8pg14b: Thu Oct 19 01:08:32 JST 2023
    centos8pg14b: exit with : 0
    centos8pg14b: repo edit end
    centos8pg14b:
    centos8pg14b: Installed:
    centos8pg14b:   pgdg-redhat-repo-42.0-35PGDG.noarch
    centos8pg14b:
    centos8pg14b: exit with : 0
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b: exit with : 0
    centos8pg14b: Importing GPG key 0x442DF0F8:
    centos8pg14b:  Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
    centos8pg14b:  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
    centos8pg14b:  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
    centos8pg14b:
    centos8pg14b: Installed:
    centos8pg14b:   lz4-1.8.3-3.el8_4.x86_64
    centos8pg14b:   postgresql14-14.9-2PGDG.rhel8.x86_64
    centos8pg14b:   postgresql14-libs-14.9-2PGDG.rhel8.x86_64
    centos8pg14b:   postgresql14-server-14.9-2PGDG.rhel8.x86_64
    centos8pg14b:
    centos8pg14b: exit with : 0
    centos8pg14b: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → / usr/lib/systemd/system/postgresql-14.service.
    centos8pg14b: root
    centos8pg14b: Thu Oct 19 01:10:01 JST 2023
    centos8pg14b: Initializing database ... OK
    centos8pg14b:
    centos8pg14b: restart over.
    centos8pg14b: /var/run/postgresql:5432 - accepting connections
    centos8pg14b:                                   List of databases
    centos8pg14b:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    centos8pg14b: -----------+----------+----------+-------------+-------------+-----------------------
    centos8pg14b:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    centos8pg14b:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14b:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14b:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14b:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14b: (3 rows)
    centos8pg14b:
    centos8pg14b: root
    centos8pg14b: Thu Oct 19 01:10:09 JST 2023
    centos8pg14b: waiting for server to shut down.... done
    centos8pg14b: server stopped
    centos8pg14b: 192.168.55.101:5432 - accepting connections
    centos8pg14b: exit with : 0
    centos8pg14b: conf setting over.
    centos8pg14b: restart over.
    centos8pg14b: /var/run/postgresql:5432 - accepting connections
    centos8pg14b:                                   List of databases
    centos8pg14b:    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    centos8pg14b: -----------+----------+----------+-------------+-------------+-----------------------
    centos8pg14b:  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    centos8pg14b:  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14b:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14b:  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
    centos8pg14b:            |          |          |             |             | postgres=CTc/postgres
    centos8pg14b:  test      | test     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    centos8pg14b: (4 rows)
    centos8pg14b:
    centos8pg14b: Changing password for user postgres.
    centos8pg14b: passwd: all authentication tokens updated successfully.
    centos8pg14b: centos8pg14b provision over.
    centos8pg14b:  pg_is_in_recovery
    centos8pg14b: -------------------
    centos8pg14b:  t
    centos8pg14b: (1 row)
    centos8pg14b:
    centos8pg14b: Thu Oct 19 01:10:20 JST 2023
2023/10/19  1:10:21.11

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

centos8pg14a              running (virtualbox)
centos8pg14b              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_centos8_pg14
$ vagrant ssh centos8pg14a

This system is built by the Bento project by Chef Software
More information can be found at https://github.com/chef/bento
[vagrant@centos8pg14a ~]$ pwd
/home/vagrant
[vagrant@centos8pg14a ~]$ uname -a
Linux centos8pg14a 4.18.0-147.8.1.el8_1.x86_64 #1 SMP Thu Apr 9 13:49:54 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
[vagrant@centos8pg14a ~]$ whoami
vagrant
[vagrant@centos8pg14a ~]$ hostname
centos8pg14a
[vagrant@centos8pg14a ~]$ ip a | grep eth | grep "inet "
    inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic noprefixroute eth0
    inet 192.168.55.101/24 brd 192.168.55.255 scope global noprefixroute eth1
[vagrant@centos8pg14a ~]$ ps fax | grep post
  15794 pts/0    S+     0:00              \_ grep --color=auto post
  11000 ?        Ss     0:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
  11002 ?        Ss     0:00  \_ postgres: logger
  11004 ?        Ss     0:00  \_ postgres: checkpointer
  11005 ?        Ss     0:00  \_ postgres: background writer
  11006 ?        Ss     0:00  \_ postgres: walwriter
  11007 ?        Ss     0:00  \_ postgres: autovacuum launcher
  11008 ?        Ss     0:00  \_ postgres: archiver last was 000000010000000000000002.00000060.backup
  11009 ?        Ss     0:00  \_ postgres: stats collector
  11010 ?        Ss     0:00  \_ postgres: logical replication launcher
  11270 ?        Ss     0:00  \_ postgres: walsender postgres 192.168.55.102(36472) streaming 0/3000148 [vagrant@centos8pg14a ~]$ psql --version
psql (PostgreSQL) 14.9
[vagrant@centos8pg14a ~]$ sudo -iu postgres pg_isready
/var/run/postgresql:5432 - accepting connections
[vagrant@centos8pg14a ~]$ sudo -iu postgres psql -c "select version()"
                                                 version

---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit(1 row)

[vagrant@centos8pg14a ~]$ 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@centos8pg14a ~]$ sudo -iu postgres psql test -c "\d"
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

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

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

[vagrant@centos8pg14a ~]$ sudo -iu postgres psql << EOF
> \x auto
> select * from pg_stat_replication
> EOF
Expanded display is used automatically.
-[ RECORD 1 ]----+------------------------------
pid              | 11270
usesysid         | 10
usename          | postgres
application_name | s1
client_addr      | 192.168.55.102
client_hostname  |
client_port      | 36472
backend_start    | 2023-10-19 01:10:15.296862+09
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000148
write_lsn        | 0/3000148
flush_lsn        | 0/3000148
replay_lsn       | 0/3000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | sync
reply_time       | 2023-10-19 05:38:57.910425+09

[vagrant@centos8pg14a ~]$ sudo -iu postgres psql test -c "insert into test (id, val) values (2, 'rep ch k');"
INSERT 0 1
[vagrant@centos8pg14a ~]$ exit
logout

C:\var\dev\DB\rep_test_centos8_pg14
$ vagrant ssh centos8pg14b

This system is built by the Bento project by Chef Software
More information can be found at https://github.com/chef/bento
[vagrant@centos8pg14b ~]$ ps fax | grep post
  15048 pts/0    S+     0:00              \_ grep --color=auto post
  10915 ?        Ss     0:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
  10917 ?        Ss     0:00  \_ postgres: logger
  10918 ?        Ss     0:00  \_ postgres: startup recovering 000000010000000000000003
  10922 ?        Ss     0:00  \_ postgres: checkpointer
  10928 ?        Ss     0:00  \_ postgres: background writer
  10929 ?        Ss     0:00  \_ postgres: stats collector
  10930 ?        Ss     0:15  \_ postgres: walreceiver streaming 0/3000338
[vagrant@centos8pg14b ~]$ sudo -iu postgres psql test -c "select * from test"
 id |   val
----+---------
  1 | test
  2 | rep chk
(2 rows)

[vagrant@centos8pg14b ~]$ exit
logout

C:\var\dev\DB\rep_test_centos8_pg14
$ vagrant halt
==> centos8pg14b: Attempting graceful shutdown of VM...
==> centos8pg14a: Attempting graceful shutdown of VM...

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

centos8pg14a              poweroff (virtualbox)
centos8pg14b              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_centos8_pg14
$ vagrant destroy -f
==> centos8pg14b: Destroying VM and associated drives...
==> centos8pg14a: Destroying VM and associated drives...

C:\var\dev\DB\rep_test_centos8_pg14
$


```

## 調べた点
- centos8のdnfがデフォルトでは失敗する。ミラーサイトの設定変更。
- selinuxの止め方は、公式の推奨はPermissiveだが、簡単な検証なのでdisabled。
- sudoとpsqlのcオプションを利用して、psqlをsuせずにワンライナー操作。
- ヒアドキュメントを利用した複数行の設定。
- postgresの12以降は"recovery.conf"を利用しなくなっている。

```
2023-10-18 14:20:24.307 JST [11599] FATAL:  using recovery command file "recovery.conf" is not supported

```

## 所要時間
無線のノートパソコンでも、有線のデスクトップでも、
プロビジョニングにかかる時間は30分程度だった。

```

C:\var\dev\DB\rep_test_centos8_pg14
$ wmic CPU get Name
Name
Intel(R) Xeon(R) CPU E3-1270 v3 @ 3.50GHz

C:\var\dev\DB\rep_test_centos8_pg14
$ wmic memorychip get Capacity
Capacity
8589934592
8589934592

C:\var\dev\DB\rep_test_centos8_pg14
$ wmic diskdrive get Model
Model
SPCC Solid State Disk
WDC WD10EADS-00L5B1

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

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

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

C:\var\dev\DB\rep_test_centos8_pg14
$ vagrant plugin list
vagrant-vbguest (0.21.0, global)
  - Version Constraint: 0.21
vagrant-vyos (1.1.10, global)
  - Version Constraint: > 0

C:\var\dev\DB\rep_test_centos8_pg14
$ vagrant box list
bento/centos-8   (virtualbox, 202112.19.0)
bento/centos-8.1 (virtualbox, 202005.21.0)
centos/7         (virtualbox, 2004.01)
coent7pg11base   (virtualbox, 0)
opensuse153      (virtualbox, 0)
ubuntu2004       (virtualbox, 0)


C:\var\dev\DB\rep_test_centos8_pg14
$
C:\var\dev\DB\rep_test_centos8_pg14
$ cmd /V:ON /C echo !DATE! !TIME! & vagrant up & cmd /V:ON /C echo !DATE! !TIME!
2023/10/19  0:43:40.94

略

2023/10/19  1:16:50.06

```


以上