LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kubo4ka

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

Last updated at Posted at 2024-09-16

作業ログ

2024/09/17

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

目的

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

vagrantの説明が必要な場合は以下をご確認ください。

Vagrantfile

2台をストリーミングレプリケーション設定するVagrantfile 

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

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.provider "virtualbox" do |vb|
    vb.customize ["modifyvm", :id, "--vrde", "off"]
  end
  config.vm.define :centosstr9pg16a 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-stream-9"
    centos8.vm.hostname = "centosstr9pg16a"
    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
      pwd
      timedatectl set-timezone Asia/Tokyo
      date
      echo "dnf update. This may take a few minutes... please wait..."
      dnf -qy update ; echo "dnf -qy update exit with : $?"
      date
      sleep 5
      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 "dnf -qy module disable postgresql exit with : $?"
      dnf -qy install postgresql16-server ; echo "dnf -qy install postgresql16-server exit with : $?"
      systemctl enable postgresql-16
      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-16/bin:$PATH" >> ~/.bash_profile
      usermod -G wheel postgres

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

SHELL1
  end







  config.vm.define :centosstr9pg16b do | centos8 |
    config.vm.provider "virtualbox" do |vb|
      vb.customize ["modifyvm", :id, "--vrde", "off"]
    end
    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-stream-9"
    centos8.vm.hostname = "centosstr9pg16b"
    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
      pwd
      timedatectl set-timezone Asia/Tokyo
      date
      echo "dnf update. This may take a few minutes... please wait..."
      dnf -qy update ; echo "dnf -qy update exit with : $?"
      date
      sleep 5
      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 "dnf -qy module disable postgresql exit with : $?"
      dnf -qy install postgresql16-server ; echo "dnf -qy install postgresql16-server exit with : $?"
      systemctl enable postgresql-16
      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-16/bin:$PATH" >> ~/.bash_profile
      usermod -G wheel postgres

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

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

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


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


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


C:\var\dev\DB\rep_test_centosstr9_pg16
$ ver

Microsoft Windows [Version 10.0.19045.4894]

C:\var\dev\DB\rep_test_centosstr9_pg16
$ VBoxManage -v
7.0.12r159484

C:\var\dev\DB\rep_test_centosstr9_pg16
$ vagrant -v
Vagrant 2.4.0

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

C:\var\dev\DB\rep_test_centosstr9_pg16
$ vagrant box list
bento/centos-8.1      (virtualbox, 202005.21.0)
bento/centos-stream-9 (virtualbox, 202304.28.0)
bento/ubuntu-24.04    (virtualbox, 0)
centos/7              (virtualbox, 2004.01)
centos/8              (virtualbox, 2011.0)
hashicorp/bionic64    (virtualbox, 1.0.282)

起動ログ

vagrant upした際のプロビジョニングログ 

C:\var\dev\DB\rep_test_centosstr9_pg16
$ vagrant up
Bringing machine 'centosstr9pg16a' up with 'virtualbox' provider...
Bringing machine 'centosstr9pg16b' up with 'virtualbox' provider...
==> centosstr9pg16a: Importing base box 'bento/centos-stream-9'...
==> centosstr9pg16a: Matching MAC address for NAT networking...
==> centosstr9pg16a: Checking if box 'bento/centos-stream-9' version '202304.28.0' is up to date...
==> centosstr9pg16a: Setting the name of the VM: rep_test_centosstr9_pg16_centosstr9pg16a_1726507008076_17552
==> centosstr9pg16a: Clearing any previously set network interfaces...
==> centosstr9pg16a: Preparing network interfaces based on configuration...
    centosstr9pg16a: Adapter 1: nat
    centosstr9pg16a: Adapter 2: hostonly
==> centosstr9pg16a: Forwarding ports...
    centosstr9pg16a: 22 (guest) => 2222 (host) (adapter 1)
==> centosstr9pg16a: Running 'pre-boot' VM customizations...
==> centosstr9pg16a: Booting VM...
==> centosstr9pg16a: Waiting for machine to boot. This may take a few minutes...
    centosstr9pg16a: SSH address: 127.0.0.1:2222
    centosstr9pg16a: SSH username: vagrant
    centosstr9pg16a: SSH auth method: private key
    centosstr9pg16a:
    centosstr9pg16a: Vagrant insecure key detected. Vagrant will automatically replace
    centosstr9pg16a: this with a newly generated keypair for better security.
    centosstr9pg16a:
    centosstr9pg16a: Inserting generated public key within guest...
    centosstr9pg16a: Removing insecure key from the guest if it's present...
    centosstr9pg16a: Key inserted! Disconnecting and reconnecting using new SSH key...
==> centosstr9pg16a: Machine booted and ready!
==> centosstr9pg16a: Checking for guest additions in VM...
==> centosstr9pg16a: Setting hostname...
==> centosstr9pg16a: Configuring and enabling network interfaces...
==> centosstr9pg16a: Mounting shared folders...
    centosstr9pg16a: /vagrant => C:/var/dev/DB/rep_test_centosstr9_pg16
==> centosstr9pg16a: Running provisioner: shell...
    centosstr9pg16a: Running: inline script
    centosstr9pg16a: root
    centosstr9pg16a: /home/vagrant
    centosstr9pg16a: Tue Sep 17 02:17:59 AM JST 2024
    centosstr9pg16a: dnf update. This may take a few minutes... please wait...
    centosstr9pg16a:
    centosstr9pg16a: dnf -qy update exit with : 0
    centosstr9pg16a: Tue Sep 17 02:24:25 AM JST 2024
    centosstr9pg16a:
    centosstr9pg16a: Installed:
    centosstr9pg16a:   pgdg-redhat-repo-42.0-44PGDG.noarch
    centosstr9pg16a:
    centosstr9pg16a: exit with : 0
    centosstr9pg16a:
    centosstr9pg16a: Installed:
    centosstr9pg16a:   libicu-67.1-9.el9.x86_64
    centosstr9pg16a:   postgresql16-16.4-1PGDG.rhel9.x86_64
    centosstr9pg16a:   postgresql16-libs-16.4-1PGDG.rhel9.x86_64
    centosstr9pg16a:   postgresql16-server-16.4-1PGDG.rhel9.x86_64
    centosstr9pg16a:
    centosstr9pg16a: dnf -qy install postgresql16-server exit with : 0
    centosstr9pg16a: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-16.service →  /usr/lib/systemd/system/postgresql-16.service.
    centosstr9pg16a: root
    centosstr9pg16a: Tue Sep 17 02:25:31 AM JST 2024
    centosstr9pg16a: Initializing database ... OK
    centosstr9pg16a:
    centosstr9pg16a: conf setting over.
    centosstr9pg16a: restart over.
    centosstr9pg16a: /run/postgresql:5432 - accepting connections
    centosstr9pg16a:                                                        List of databases
    centosstr9pg16a:    Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
    centosstr9pg16a: -----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
    centosstr9pg16a:  postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
    centosstr9pg16a:  template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16a:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16a:  template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16a:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16a: (3 rows)
    centosstr9pg16a:
    centosstr9pg16a: root
    centosstr9pg16a: Tue Sep 17 02:25:39 AM JST 2024
    centosstr9pg16a: postgres setting 1.
    centosstr9pg16a: CREATE ROLE
    centosstr9pg16a: ALTER ROLE
    centosstr9pg16a: postgres setting 1 alter db over.
    centosstr9pg16a:                                                        List of databases
    centosstr9pg16a:    Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
    centosstr9pg16a: -----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
    centosstr9pg16a:  postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
    centosstr9pg16a:  template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16a:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16a:  template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16a:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16a: (3 rows)
    centosstr9pg16a:
    centosstr9pg16a: root
    centosstr9pg16a: Tue Sep 17 02:25:40 AM JST 2024
    centosstr9pg16a: postgres
    centosstr9pg16a: postgres setting 1 export over.
    centosstr9pg16a: root
    centosstr9pg16a: postgres setting 1 restart over.
    centosstr9pg16a: Last login: Tue Sep 17 02:25:40 JST 2024
    centosstr9pg16a: postgres
    centosstr9pg16a: postgres setting 2.
    centosstr9pg16a: postgres setting 2 createdb over.
    centosstr9pg16a: ALTER DATABASE
    centosstr9pg16a: CREATE TABLE
    centosstr9pg16a: INSERT 0 1
    centosstr9pg16a: root
    centosstr9pg16a: Changing password for user postgres.
    centosstr9pg16a: passwd: all authentication tokens updated successfully.
    centosstr9pg16a: /run/postgresql:5432 - accepting connections
    centosstr9pg16a: centosstr9pg16a provision over.
    centosstr9pg16a: Tue Sep 17 02:25:41 AM JST 2024
==> centosstr9pg16b: Importing base box 'bento/centos-stream-9'...
==> centosstr9pg16b: Matching MAC address for NAT networking...
==> centosstr9pg16b: Checking if box 'bento/centos-stream-9' version '202304.28.0' is up to date...
==> centosstr9pg16b: Setting the name of the VM: rep_test_centosstr9_pg16_centosstr9pg16b_1726507550030_22788
==> centosstr9pg16b: Fixed port collision for 22 => 2222. Now on port 2200.
==> centosstr9pg16b: Clearing any previously set network interfaces...
==> centosstr9pg16b: Preparing network interfaces based on configuration...
    centosstr9pg16b: Adapter 1: nat
    centosstr9pg16b: Adapter 2: hostonly
==> centosstr9pg16b: Forwarding ports...
    centosstr9pg16b: 22 (guest) => 2200 (host) (adapter 1)
==> centosstr9pg16b: Running 'pre-boot' VM customizations...
==> centosstr9pg16b: Booting VM...
==> centosstr9pg16b: Waiting for machine to boot. This may take a few minutes...
    centosstr9pg16b: SSH address: 127.0.0.1:2200
    centosstr9pg16b: SSH username: vagrant
    centosstr9pg16b: SSH auth method: private key
    centosstr9pg16b:
    centosstr9pg16b: Vagrant insecure key detected. Vagrant will automatically replace
    centosstr9pg16b: this with a newly generated keypair for better security.
    centosstr9pg16b:
    centosstr9pg16b: Inserting generated public key within guest...
    centosstr9pg16b: Removing insecure key from the guest if it's present...
    centosstr9pg16b: Key inserted! Disconnecting and reconnecting using new SSH key...
==> centosstr9pg16b: Machine booted and ready!
==> centosstr9pg16b: Checking for guest additions in VM...
==> centosstr9pg16b: Setting hostname...
==> centosstr9pg16b: Configuring and enabling network interfaces...
==> centosstr9pg16b: Mounting shared folders...
    centosstr9pg16b: /vagrant => C:/var/dev/DB/rep_test_centosstr9_pg16
==> centosstr9pg16b: Running provisioner: shell...
    centosstr9pg16b: Running: inline script
    centosstr9pg16b: root
    centosstr9pg16b: /home/vagrant
    centosstr9pg16b: Tue Sep 17 02:27:15 AM JST 2024
    centosstr9pg16b: dnf update. This may take a few minutes... please wait...
    centosstr9pg16b:
    centosstr9pg16b: dnf -qy update exit with : 0
    centosstr9pg16b: Tue Sep 17 02:33:12 AM JST 2024
    centosstr9pg16b:
    centosstr9pg16b: Installed:
    centosstr9pg16b:   pgdg-redhat-repo-42.0-44PGDG.noarch
    centosstr9pg16b:
    centosstr9pg16b: exit with : 0
    centosstr9pg16b: Installed:
    centosstr9pg16b:   libicu-67.1-9.el9.x86_64
    centosstr9pg16b:   postgresql16-16.4-1PGDG.rhel9.x86_64
    centosstr9pg16b:   postgresql16-libs-16.4-1PGDG.rhel9.x86_64
    centosstr9pg16b:   postgresql16-server-16.4-1PGDG.rhel9.x86_64
    centosstr9pg16b:
    centosstr9pg16b: dnf -qy install postgresql16-server exit with : 0
    centosstr9pg16b: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-16.service →  /usr/lib/systemd/system/postgresql-16.service.
    centosstr9pg16b: root
    centosstr9pg16b: Tue Sep 17 02:34:19 AM JST 2024
    centosstr9pg16b: Initializing database ... OK
    centosstr9pg16b:
    centosstr9pg16b: restart over.
    centosstr9pg16b: /run/postgresql:5432 - accepting connections
    centosstr9pg16b:                                                        List of databases
    centosstr9pg16b:    Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
    centosstr9pg16b: -----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
    centosstr9pg16b:  postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
    centosstr9pg16b:  template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16b:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16b:  template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16b:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16b: (3 rows)
    centosstr9pg16b:
    centosstr9pg16b: root
    centosstr9pg16b: Tue Sep 17 02:34:29 AM JST 2024
    centosstr9pg16b: waiting for server to shut down.... done
    centosstr9pg16b: server stopped
    centosstr9pg16b: 192.168.55.101:5432 - accepting connections
    centosstr9pg16b: exit with : 0
    centosstr9pg16b: conf setting over.
    centosstr9pg16b: restart over.
    centosstr9pg16b: /run/postgresql:5432 - accepting connections
    centosstr9pg16b:                                                        List of databases
    centosstr9pg16b:    Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
    centosstr9pg16b: -----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
    centosstr9pg16b:  postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
    centosstr9pg16b:  template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16b:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16b:  template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
    centosstr9pg16b:            |          |          |                 |             |             |        |           | postgres=CTc/postgres
    centosstr9pg16b:  test      | test     | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
    centosstr9pg16b: (4 rows)
    centosstr9pg16b:
    centosstr9pg16b: Changing password for user postgres.
    centosstr9pg16b: passwd: all authentication tokens updated successfully.
    centosstr9pg16b: centosstr9pg16b provision over.
    centosstr9pg16b:  pg_is_in_recovery
    centosstr9pg16b: -------------------
    centosstr9pg16b:  t
    centosstr9pg16b: (1 row)
    centosstr9pg16b:
    centosstr9pg16b: Tue Sep 17 02:34:43 AM JST 2024

C:\var\dev\DB\rep_test_centosstr9_pg16
$

動作確認ログ

レプリケーションの確認ログ 

C:\var\dev\DB\rep_test_centosstr9_pg16
$ vagrant ssh centosstr9pg16a

This system is built by the Bento project by Chef Software
More information can be found at https://github.com/chef/bento
[vagrant@centosstr9pg16a ~]$ pwd
/home/vagrant
[vagrant@centosstr9pg16a ~]$ uname -a
Linux centosstr9pg16a 5.14.0-285.el9.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 7 17:32:48 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
[vagrant@centosstr9pg16a ~]$ whoami
vagrant
[vagrant@centosstr9pg16a ~]$ hostname
centosstr9pg16a
[vagrant@centosstr9pg16a ~]$ ip a | grep "inet "
    inet 127.0.0.1/8 scope host lo
    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@centosstr9pg16a ~]$ ps fax | grep post
  34020 pts/0    S+     0:00              \_ grep --color=auto post
  33627 ?        Ss     0:00 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
  33628 ?        Ss     0:00  \_ postgres: logger
  33629 ?        Ss     0:00  \_ postgres: checkpointer
  33630 ?        Ss     0:00  \_ postgres: background writer
  33632 ?        Ss     0:00  \_ postgres: walwriter
  33633 ?        Ss     0:00  \_ postgres: autovacuum launcher
  33634 ?        Ss     0:00  \_ postgres: archiver last was 000000010000000000000002.00000028.backup
  33635 ?        Ss     0:00  \_ postgres: logical replication launcher
  33784 ?        Ss     0:00  \_ postgres: walsender postgres 192.168.55.102(55476) streaming 0/3000148
[vagrant@centosstr9pg16a ~]$ sudo -iu postgres pg_isready
/run/postgresql:5432 - accepting connections
[vagrant@centosstr9pg16a ~]$ sudo -iu postgres psql -c "select version()"
                                                 version

----------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit(1 row)

[vagrant@centosstr9pg16a ~]$ sudo -iu postgres psql -c "\l"
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |
   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |

 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 test      | test     | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |

(4 rows)

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

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

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

[vagrant@centosstr9pg16a ~]$ sudo -iu postgres psql << EOF
> \x auto
> select * from pg_stat_replication
> EOF
Expanded display is used automatically.
-[ RECORD 1 ]----+------------------------------
pid              | 33784
usesysid         | 10
usename          | postgres
application_name | s1
client_addr      | 192.168.55.102
client_hostname  |
client_port      | 55476
backend_start    | 2024-09-17 02:34:36.939421+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       | 2024-09-17 02:49:11.09962+09

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

C:\var\dev\DB\rep_test_centosstr9_pg16
$ vagrant ssh centosstr9pg16b

This system is built by the Bento project by Chef Software
More information can be found at https://github.com/chef/bento
[vagrant@centosstr9pg16b ~]$ ps fax | grep post
  33840 pts/0    S+     0:00              \_ grep --color=auto post
  33528 ?        Ss     0:00 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
  33529 ?        Ss     0:00  \_ postgres: logger
  33530 ?        Ss     0:00  \_ postgres: checkpointer
  33531 ?        Ss     0:00  \_ postgres: background writer
  33532 ?        Ss     0:00  \_ postgres: startup recovering 000000010000000000000003
  33533 ?        Ss     0:00  \_ postgres: walreceiver streaming 0/3000250
[vagrant@centosstr9pg16b ~]$ sudo -iu postgres psql test -c "select * from test"
 id |   val
----+----------
  1 | test
  2 | rep ch k
(2 rows)

[vagrant@centosstr9pg16b ~]$ exit
logout
Connection to 127.0.0.1 closed.

C:\var\dev\DB\rep_test_centosstr9_pg16
$ vagrant halt
==> centosstr9pg16b: Attempting graceful shutdown of VM...
==> centosstr9pg16a: Attempting graceful shutdown of VM...

C:\var\dev\DB\rep_test_centosstr9_pg16
$

作業してみて

  • centos8とpostgres14の設定からコンフィグは変化なく実施できた。

以上

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?