Rocky Linux9 に postgres インストール

$ cat /etc/redhat-release
Rocky Linux release 9.1 (Blue Onyx)

【ロケーション設定】

timedatectl set-timezone Asia/Tokyo

localectl set-locale LANG=ja_JP.utf8

timedatectl

localectl

【ネットワーク設定】

nmcli d

nmcli c

#nmcli c delete vlan100

nmcli general hostname postgres

nmcli c delete internet

nmcli c add type ethernet ifname enp1s0 con-name "internet" ethernet.mtu 1500

nmcli c mod internet ipv4.method auto

nmcli c mod internet connection.autoconnect yes

nmcli c up internet

VLAN=vlan_monitor

VLAN_IP=192.168.254.211/24

nmcli c delete $VLAN

nmcli c add type ethernet ifname enp7s0 con-name "$VLAN"

nmcli c mod $VLAN ipv4.addresses $VLAN_IP

nmcli c mod $VLAN ipv4.method manual

nmcli c mod $VLAN connection.autoconnect yes

nmcli c mod $VLAN ipv4.dns 8.8.8.8

nmcli c mod $VLAN ipv4.gateway 192.168.254.254

nmcli c up $VLAN

VLAN=vlan_database

VLAN_IP=192.168.200.111/24

nmcli c delete $VLAN

nmcli c add type ethernet ifname enp8s0 con-name "$VLAN"

nmcli c mod $VLAN ipv4.addresses $VLAN_IP

nmcli c mod $VLAN ipv4.method manual

nmcli c mod $VLAN connection.autoconnect yes

nmcli c mod $VLAN +ipv4.routes "192.168.210.0/24 192.168.200.2"

nmcli c mod $VLAN +ipv4.routes "192.168.12.0/24  192.168.200.2"

nmcli c mod $VLAN +ipv4.routes "192.168.22.0/24  192.168.200.2"

nmcli c mod $VLAN +ipv4.routes "192.168.32.0/24  192.168.200.2"

nmcli c up $VLAN

# systemctl restart NetworkManager

【postgres15】

# dnf update --refresh

# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# dnf  -y install postgresql15-server

# /usr/pgsql-15/bin/postgresql-15-setup initdb

# systemctl start postgresql-15

# systemctl enable postgresql-15

# systemctl status postgresql-15

# passwd postgres

# su - postgres

$ psql -V

psql (PostgreSQL) 15.5

$ psql

alter user postgres with password 'password';

exit

$  vi /var/lib/pgsql/15/data/pg_hba.conf 

#local   all             all                                     peer

#host    all             all             127.0.0.1/32            scram-sha-256

local     all             all                                     trust

host      all             all             10.0.0.0/16             password

$ vi /var/lib/pgsql/15/data/postgresql.conf 

listen_addresses = '*'          

port = 5432 

max_connections = 100 

log_timezone = 'Asia/Tokyo'

timezone = 'Asia/Tokyo'

lc_messages = 'ja_JP.utf8'                    # locale for system error message

lc_monetary = 'ja_JP.utf8'                    # locale for monetary formatting

lc_numeric = 'ja_JP.utf8'                       # locale for number formatting

lc_time = 'ja_JP.utf8'

# systemctl restart postgresql-15

# firewall-cmd --add-port=5432/tcp --zone=public --permanent

# firewall-cmd --reload

【データディレクトリの変更】

$ psql

show config_file ;

show data_directory;

# systemctl stop postgresql-15.service

# mkdir -p /pg_data/data

# chown postgres:postgres /pg_data

# chown postgres:postgres /pg_data/data

# chmod -R 700  /pg_data/data

# rsync -av /var/lib/pgsql/15/data/  /pg_data/data

# vi /var/lib/pgsql/15/data/postgresql.conf

data_directory = '/pg_data/data'

# vi /lib/systemd/system/postgresql-15.service

#Environment=PGDATA=/var/lib/pgsql/15/data/

Environment=PGDATA=/pg_data/data/

# systemctl daemon-reload

# systemctl start postgresql-15

【Postgres14】

# dnf update --refresh

# dnf install http://apt.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# dnf  -y install postgresql14-server postgresql14-docs 

# dnf -y install postgresql14-libs postgresql14-plperl postgresql14-plpython3 postgresql14-pltcl postgresql14-tcl postgresql14-contrib postgresql14-llvmjit

/usr/pgsql-14/bin/postgresql-14-setup initdb

# vi /var/lib/pgsql/14/data/pg_hba.conf  << __EOF__

#local   all             all                                        peer

local     all             all                                          trust

#host    all           all       127.0.0.1/32         scram-sha-256      

host      all           all        192.168.0.0/16   password

__EOF__

# vi /var/lib/pgsql/14/data/postgresql.conf  <<__EOF__

listen_addresses = '*'          

port = 5432 

max_connections = 100 

log_timezone = 'Asia/Tokyo'

timezone = 'Asia/Tokyo'

lc_messages = 'ja_JP.utf8'                    # locale for system error message

lc_monetary = 'ja_JP.utf8'                    # locale for monetary formatting

lc_numeric = 'ja_JP.utf8'                       # locale for number formatting

lc_time = 'ja_JP.utf8'

__EOF__

※ 青字確認。AWS EC2では、en_USxxxxになっていた。

# firewall-cmd --add-port=5432/tcp --zone=public --permanent

# firewall-cmd --reload

# systemctl enable postgresql-14

# systemctl start postgresql-14 

# psql --version

psql (PostgreSQL) 14.7

# ls -lZ /var/lib/pgsql

合計 0

drwx------. 4 postgres postgres system_u:object_r:postgresql_db_t:s0 51  4月 24 11:50 14

# passwd postgres

# su - postgres

$ psql -U postgres

alter user postgres with password  'newpassword';

create user admin with password 'xxxxxxxx' superuser;

select * from pg_user;

select * from pg_shadow;

\g

ALTER USER admin WITH PASSWORD 'xxxxxxxx'

create database ARCSDBMS owner admin;

【psql パスワード自動入力】

$ vi ~/.pgpass <<__EOF__

192.168.254.211:5432:workbase:admin:PASSWORD

__EOF__

又は、

$ export PGPASSWORD=password  （非推奨）

【postgres 文字コード】

(1） vi /var/lib/pgsql/14/data/postgresql.conf

       timezone = 'Asia/Tokyo'

       log_timezone = 'Asia/Tokyo'

       lc_messages = 'ja_JP.utf8'                      # locale for system error message

       lc_monetary = 'ja_JP.utf8'                      # locale for monetary formatting

       lc_numeric    = 'ja_JP.utf8'                      # locale for number formatting

       lc_time           = 'ja_JP.utf8'                      # locale for time formatting

(2) pg_settingsを更新。

    select name,setting,context from pg_settings where name like 'lc%';

    update pg_settings set setting ='ja_JP.UTF-8' where name = 'lc_messages'; set_config

(3) role を変更

　alter role office_role set lc_monetary = 'ja_JP.UTF-8';

　alter role office_role set lc_numeric = 'ja_JP.UTF-8';

　alter role office_role set lc_time = 'ja_JP.UTF-8';

(4) データベース作成時に設定 

     create database japan with encoding  'utf8'  lc_collate='ja_JP.utf8' lc_ctype='ja_JP.utf8' template=template0;

(5) テーブルのカラムにロケールを個別に指定。

ALTER TABLE items ALTER COLUMN title TYPE VARCHAR COLLATE "ja_JP.utf8";

(6) templateデータベースを更新。

1) template1データベースをテンプレートから外す

   UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

2) template1データベースを削除

   DROP DATABASE template1;

3) template1データベースを作成

    CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 文字コード 

    LC_COLLATE = 'ja_JP.utf-8' LC_CTYPE = 'ja_JP.utf8'

4) template1データベースをテンプレートへ戻す

   UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';