作業ログ
2023/10/18
vagrant、centos8でpostgres14のレプリケーション設定をプロビジョニング
目的
- コマンド一つでストリーミングレプリケーション設定したい。
- vagrantのプロビジョニングを利用してできることを確認する。
- 前回と違うバージョンでの設定を確認する。
Vagrantfile
# -*- 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
以上