PostgreSQL13.6 構築

お勉強用（備忘）

目的

・PostgreSQL13.6の構築
・PostgreSQL・Clientから接続できること

環境

VM:VirtualBox6.1
OS：Red Hat Enterprise Linux release 8.5 (Ootpa)
PosgreSQL(Server/Client): PostgreSQL 13.6

1.準備

#ユーザ作成
[root@posgr13]# 
useradd -m postgres

# ディレクトリ作成
mkdir -pm 755 /opt/pgsql/pgsql-13/{system,data,wal,arc}
chown -R postgres:  /opt/pgsql
ll -Rh /opt/pgsql

# ワークディレクトリ
mkdir -pm 777 /tmp/Installer/postgres
ll -h /tmp/Installer

2.PostgreSQL インストール

＃リポジトリRPMをインストール
[root@posgr13]# 
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

＃組み込みのPostgreSQLモジュールを無効
dnf -qy module disable postgresql

＃postgresql13-server ダウンロード
dnf install --downloadonly postgresql13-server --downloaddir=/tmp/installer/postgres/

ll -h /tmp/installer/postgres/
> 合計 7.5M
> -rw-r--r--. 1 root root 1.5M  5月  2 16:57 postgresql13-13.6-1PGDG.rhel8.x86_64.rpm
> -rw-r--r--. 1 root root 415K  5月  2 16:57 postgresql13-libs-13.6-1PGDG.rhel8.x86_64.rpm
> -rw-r--r--. 1 root root 5.6M  5月  2 16:57 postgresql13-server-13.6-1PGDG.rhel8.x86_64.rpm

＃postgresql13-server インストール
cd /tmp/installer/postgres;pwd
dnf install ./*
　　：
> インストール済み:
>  postgresql13-13.6-1PGDG.rhel8.x86_64
>  postgresql13-libs-13.6-1PGDG.rhel8.x86_64
>  postgresql13-server-13.6-1PGDG.rhel8.x86_64

> 完了しました!

3.PostgreSQL用・環境変数プロファイル作成

#.pgsql_profile作成
[root@posgr13]# 
cat <<EOF>/home/postgres/.pgsql_profile
export PGPATH=/usr/pgsql-13
export PGHOME=/opt/pgsql/pgsql-13
export PGDATA=\${PGHOME}/system
export PGWAL=\${PGHOME}/wal
export PGARC=\${PGHOME}/arc
export PATH=\${PATH}:\${PGPATH}/bin
#export PS1="[\u@\h \W]$ "
EOF

cat /home/postgres/.pgsql_profile

# .bash_profileに追記
echo "source /home/postgres/.pgsql_profile" >> /home/postgres/.bash_profile
cat /home/postgres/.bash_profile

#bash_profile再読み込み
exec .bash_profile
env | grep PG
> PGPATH=/usr/pgsql-13
> PGARC=/opt/pgsql/pgsql-13/arc
> PGHOME=/opt/pgsql/pgsql-13
> PGDATA=/opt/pgsql/pgsql-13/system
> PGWAL=/opt/pgsql/pgsql-13/wal

4.DBクラスタ作成

#postgresユーザにスイッチ
[root@posgr13]# 
su - postgres
id

#DBクラスタ作成
[postgres@posgr13]$
initdb \
  -E UTF-8 --locale=C --lc-ctype=ja_JP.UTF-8 \
  -D ${PGDATA} \
  -X ${PGWAL} \
  --lc-time=ja_JP.UTF-8

5.ユーザデータベース作成

# サービス起動
systemctl enable postgresql-13
systemctl start postgresql-13
systemctl status postgresql-13

> ● postgresql-13.service - PostgreSQL 13 database server
>    Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled)
>    Active: active (running) since Mon 2022-05-02 21:06:07 JST; 20h ago
>      Docs: https://www.postgresql.org/docs/13/static/
>   Process: 6428 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
>  Main PID: 6433 (postmaster)
>     Tasks: 15 (limit: 10474)
>    Memory: 249.3M

#ユーザデータベース作成
psql

postgres=> CREATE DATABASE testdb LC_COLLATE 'C' LC_CTYPE 'ja_JP.UTF-8' ENCODING UTF8 TEMPLATE template0 ;createdb \
postgres=> SELECT OID,DATNAME,DATDBA,ENCODING,DATCOLLATE,DATCTYPE FROM PG_DATABASE;

>   oid  |  datname  | datdba | encoding | datcollate  |  datctype
> -------+-----------+--------+----------+-------------+-------------
>  13438 | postgres  |     10 |        6 | C           | ja_JP.UTF-8
>  16384 | testdb    |     10 |        6 | C           | ja_JP.UTF-8
>      1 | template1 |     10 |        6 | C           | ja_JP.UTF-8
>  13437 | template0 |     10 |        6 | C           | ja_JP.UTF-8

postgres=> \q
exit

6.サービス定義編集

[root@posgr13]# 
#バックアップ
cp -p /usr/lib/systemd/system/postgresql-13.service{,_`date +%Y%m%d`}
ll -h /usr/lib/systemd/system/postgresql-13.service*

#不要な記述を削除
sed -i '/^#/d' /usr/lib/systemd/system/postgresql-13.service
sed -i  '/^$/d' /usr/lib/systemd/system/postgresql-13.service
sed -i 's/\/var\/lib\/pgsql\/13\/data/\/opt\/pgsql\/pgsql-13\/system/g' /usr/lib/systemd/system/postgresql-13.service

cat /usr/lib/systemd/system/postgresql-13.service

systemctl status postgresql-13.service
systemctl start postgresql-13.service
systemctl status postgresql-13.service

7.postgresql.conf編集

#postgresユーザにスイッチ
[root@posgr13]# 
su - postgres
id

#postgresql.confバックアップ
[postgres@posgr13]$
cp -p /opt/pgsql/pgsql-13/system/postgresql.conf{,_`date +%Y%m%d`}
ll -h /opt/pgsql/pgsql-13/system/postgresql.conf*

cat /opt/pgsql/pgsql-13/system/postgresql.conf | grep -v "^#\|^\s*$\|^\s*#"
vi  /opt/pgsql/pgsql-13/system/postgresql.conf
#以下内容を編集
～～～～～～～～～～～～～～～～～～～～～～～
listen_addresses = '*'
port = 5432
～～～～～～～～～～～～～～～～～～～～～～～

#不要な記述を削除
sed -i '/^#/d' /opt/pgsql/pgsql-13/system/postgresql.conf
sed -i '/^\s*#/d' /opt/pgsql/pgsql-13/system/postgresql.conf
sed -i '/^$/d' /opt/pgsql/pgsql-13/system/postgresql.conf
cat /opt/pgsql/pgsql-13/system/postgresql.conf

8.pg_hba.conf編集

他のサーバからDBに接続できるよう編集

[postgres@posgr13]$
cp -p /opt/pgsql/pgsql-13/system/pg_hba.conf{,_`date +%Y%m%d`}
ll /opt/pgsql/pgsql-13/system/pg_hba.conf*

# コメント行ををすべて削除
sed -i '/^#/d' /opt/pgsql/pgsql-13/system/pg_hba.conf
cat /opt/pgsql/pgsql-13/system/pg_hba.conf
vi /opt/pgsql/pgsql-13/system/pg_hba.conf
#以下内容を編集
～～～～～～～～～～～～～～～～～～～～～～～
# TYPE	DATABASE	USER	ADDRESS				METHOD
host	all			 all	192.168.0.0/16		password

※ADDRESS：対象サブネットのホストからを接続許可
～～～～～～～～～～～～～～～～～～～～～～～

9.testdb オブジェクト作成

[postgres@posgr13]$
psql

#postgresユーザ パスワード変更
postgres=# ALTER ROLE postgres WITH PASSWORD 'password';
> ALTER ROLE

#ユーザ作成
postgres=# CREATE ROLE test_user01 LOGIN PASSWORD 'password';
> CREATE ROLE

#権限付与
postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO test_user01;
> GRANT

#testdbにオブジェクトを作成
postgres=# \c testdb test_user01
testdb=> create table test_t1(id int,data varchar,dt timestamp DEFAULT now() );
> CREATE TABLE

testdb=> CREATE SEQUENCE test_seq1 START WITH 1 INCREMENT BY 1;
> CREATE SEQUENCE

testdb=> create index on test_t1(id);
> create index on test_t1(id,data);
> CREATE INDEX

#テーブルのレコードを追加
testdb=> INSERT INTO test_t1(id, data ) SELECT nextval('test_seq1'),md5(clock_timestamp()::TEXT) AS val FROM generate_series(1,1000000) AS id;
> INSERT 0 1000000

testdb=> SELECT * FROM test_t1 order by id desc limit 5 ;
>    id    |               data               |             dt
> ---------+----------------------------------+----------------------------
>  1000000 | b1a5fe8389c1cb74f24a022bb5b6d68c | 2022-05-03 01:34:25.152956
>   999999 | 55b0890e40228c67c9872fa1472e0e7e | 2022-05-03 01:34:25.152956
>   999998 | f30aa2ae807c60130acce3336879f1d0 | 2022-05-03 01:34:25.152956
>   999997 | 73bf0995f5a1d3974c50fc953bb6f339 | 2022-05-03 01:34:25.152956
>   999996 | 53d17922c148f1cadea1f3ad2212c01c | 2022-05-03 01:34:25.152956

10.PostgreSQL・clientのインストール

アプリサーバ(Tomcat)にPostgreSQL・clientをインストール

#ユーザ作成
[root@tomcat]# 
useradd -m postgres

#作業フォルダ作成
mkdir -m 777 /tmp/Installer/postgres

# Install the repository RPM:
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 既存postgresql無効
dnf -qy module disable postgresql

# Install PostgreSQL:
dnf install --downloadonly postgresql13 --downloaddir=/tmp/Installer/postgres
ll -h /tmp/Installer/postgres

cd /tmp/Installer/postgres;pwd
dnf localinstall ./*

インストール済み:
  postgresql13-13.6-1PGDG.rhel8.x86_64                        postgresql13-libs-13.6-1PGDG.rhel8.x86_64

完了しました!

11.アプリサーバから接続

#postgresユーザにスイッチ
[root@tomcat]# 
su - postgres
id

#testdbに接続
[postgres@tomcat]$
psql -h <DB・IPアドレス> -d testdb -U test_user01 
> ユーザ test_user01 のパスワード:password
> psql (13.6)
> "help"でヘルプを表示します。


testdb=> select current_user,current_schema(),current_database();
>  current_user | current_schema | current_database
>　--------------+----------------+------------------
> test_user01  | public         | testdb
>　(1 行)

testdb=> \q

参考リンク

PostgreSQL を CentOS にインストールするには
CentOS 8にPostgreSQL 13をインストールする