作業ログ
2023/10/16
vagrant、centos7でpostgres11のレプリケーション設定をプロビジョニング
目的
- コマンド一つでストリーミングレプリケーション設定したい。
- vagrantのプロビジョニングを利用してできることを確認する。
Vagrantfile
# -*- 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せずにワンライナー操作。
- ヒアドキュメントを利用した複数行の設定。
以上