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';

amazon Linux 2023 設定

【タイムゾーンと日本語】

$ sudo timedatectl set-timezone Asia/Tokyo

$ sudo timedatectl

$ sudo localectl set-locale LANG=ja_JP.utf8

$ sudo localectl

【プロキシ設定】

$ sudo vi /etc/dnf/dnf.conf

proxy=http://192.168.13.101:3128

$ sudo vi /etc/profile.d/proxy.sh

export http_proxy=http://192.168.13.101:3128

export https_proxy=http://192.168.13.101:3128

【epelリポジトリの登録】
$ sudo amazon-linux-extras install epel ----> 廃止

【fedra36 リポジトリの登録】

epelの代わりに登録

# vi /etc/yum.repos.d/fedora.repo

[fedora]

name=Fedora 36 – $basearch

baseurl=https://archives.fedoraproject.org/pub/archive/fedora/linux/releases/36/Everything/$basearch/os/

metalink=https://mirrors.fedoraproject.org/metalink?repo=fedora-36&arch=$basearch

enabled=1

metadata_expire=7d

repo_gpgcheck=0

type=rpm

gpgcheck=1

gpgkey=https://getfedora.org/static/fedora.gpg

skip_if_unavailable=False

【未インストールパッケージ】

$ sudo dnf install rsyslog

$ sudo dnf install cronie

# systemctl start rsyslog

# systemctl start crond

【s3 Storage マウント】

$ sudo mkdir /aws

$ sudo vi /etc/fstab

192.168.210.101:/test-cxdnext-filegateway /aws nfs rw,hard,nolock 0 0

$ sudo mount -a

【httpd】

$ sudo dnf install httpd

$  httpd -V

Server version: Apache/2.4.56 (Amazon Linux)

$ sudo systemctl start httpd

$ sudo systemctl enable httpd

【zabbix agent2 】  → 保留（openssl1.1が必要な為
$ sudo dnf install https://repo.zabbix.com/zabbix/6.4/rhel/8/x86_64/zabbix-agent2-6.4.1-release1.el8.x86_64.rpm
Last metadata expiration check: 0:28:37 ago on Fri Apr 7 16:31:07 2023.
zabbix-agent2-6.4.1-release1.el8.x86_64.rpm 3.8 MB/s | 5.5 MB 00:01
Error:
Problem: conflicting requests
- nothing provides libcrypto.so.1.1()(64bit) needed by zabbix-agent2-6.4.1-release1.el8.x86_64
- nothing provides libcrypto.so.1.1(OPENSSL_1_1_0)(64bit) needed by zabbix-agent2-6.4.1-release1.el8.x86_64
- nothing provides libssl.so.1.1()(64bit) needed by zabbix-agent2-6.4.1-release1.el8.x86_64
- nothing provides libssl.so.1.1(OPENSSL_1_1_0)(64bit) needed by zabbix-agent2-6.4.1-release1.el8.x86_64
- nothing provides libssl.so.1.1(OPENSSL_1_1_1)(64bit) needed by zabbix-agent2-6.4.1-release1.el8.x86_64

(try to add '--skip-broken' to skip uninstallable packages)

→openssl3がインストールされている。

# dnf list installed |grep openssl

openssl.x86_64                        1:3.0.8-1.amzn2023.0.1             @System

openssl-libs.x86_64               1:3.0.8-1.amzn2023.0.1             @System

openssl-pkcs11.x86_64        0.4.12-3.amzn2023.0.1              @System

【PYTHON】

$ python3

Python 3.9.16 (main, Feb 23 2023, 00:00:00) 

[GCC 11.3.1 20221121 (Red Hat 11.3.1-4)] on linux

Type "help", "copyright", "credits" or "license" for more information.

>>> 

【JAVA】

$ sudo dnf install java-17-amazon-corretto java-17-amazon-corretto-devel

$ sudo update-alternatives --config java

# cat > /etc/profile.d/java.sh <<__EOF__

export JAVA_HOME=$(dirname $(dirname $(readlink $(readlink $(which java)))))

export PATH=$PATH:$JAVA_HOME/bin

__EOF__

$ source /etc/profile

$ java --version

openjdk 17.0.6 2023-01-17 LTS

OpenJDK Runtime Environment Corretto-17.0.6.10.1 (build 17.0.6+10-LTS)

OpenJDK 64-Bit Server VM Corretto-17.0.6.10.1 (build 17.0.6+10-LTS, mixed mode, sharing)

【TOMCAT】

# cd /opt

# wget https://dlcdn.apache.org/tomcat/tomcat-10/v10.1.7/bin/apache-tomcat-10.1.7.tar.gz

#  tar xvzf apache-tomcat-10.1.7.tar.gz

# vi /usr/lib/systemd/system/tomcat10.service<<__EOF__

[Unit]

Description=Apache Tomcat 10

After=network.target

[Service]

Type=oneshot

ExecStart=/opt/tomcat/bin/startup.sh

ExecStop=/opt/tomcat/bin/shutdown.sh

RemainAfterExit=yes

User=tomcat

Group=tomcat

[Install]

WantedBy=multi-user.target

__EOF__

# cd /opt

# ln -s /opt/apache-tomcat-10.1.7 tomcat

# useradd -M -d /opt/tomcat tomcat

# chown -R tomcat:tomcat  /opt/tomcat /opt/tomcat/*

# firewall-cmd --add-port=8080/tcp --permanent

# firewall-cmd --reload

# mkdir ~/Selinux

# cd ~/Selinux

# vi catalinash.te <<__EOF__

module catalinash 1.0;

require {

        type init_t;

        type admin_home_t;

        class file { append execute execute_no_trans ioctl open read };

}

#============= init_t ==============

allow init_t admin_home_t:file { append execute execute_no_trans ioctl open read };

__EOF__

# checkmodule -m -M -o catalinash.mod catalinash.te

# semodule_package --outfile catalinash.pp --module catalinash.mod

# semodule -i catalinash.pp

# systemctl restart tomcat10

# systemctl enable tomcat10

URL localhost:8080で確認

# /opt/tomcat/bin/version.sh

Using CATALINA_BASE:   /opt/tomcat

Using CATALINA_HOME:   /opt/tomcat

Using CATALINA_TMPDIR: /opt/tomcat/temp

Using JRE_HOME:        /usr

Using CLASSPATH:       /opt/tomcat/bin/bootstrap.jar:/opt/tomcat/bin/tomcat-juli.jar

Using CATALINA_OPTS:   

Server version: Apache Tomcat/10.1.7

Server built:   Feb 27 2023 20:25:27 UTC

Server number:  10.1.7.0

OS Name:        Linux

OS Version:     6.1.21-1.45.amzn2023.x86_64

Architecture:   amd64

JVM Version:    17.0.6+10-LTS

JVM Vendor:     Amazon.com Inc.

■apache連携（ apache 2.4.56  /  tomcat 10.1.7 )

# cd /etc/httpd/conf.modules.d

# grep -e mod_proxy_ajp -e  mod_proxy.so *

00-proxy.conf:LoadModule proxy_module modules/mod_proxy.so

00-proxy.conf:LoadModule proxy_ajp_module modules/mod_proxy_ajp.so

# vi /etc/httpd/conf.d/tomcat .conf  <<__EOF__

<Location /test2023/guacamole >

ProxyPass                  ajp://localhost:8009/guacamole

ProxyPassReverse  ajp://localhost:8009/guacamole

</Location>

<Location  /test2023/tomcat >

ProxyPass                   ajp://localhost:8009

ProxyPassReverse  ajp://localhost:8009

</Location>

<Location  /test2023/docs >

ProxyPass                  ajp://localhost:8009/docs

ProxyPassReverse  ajp://localhost:8009/docs

</Location>

<Location  /test2023/examples >

ProxyPass                   ajp://localhost:8009/examples

ProxyPassReverse  ajp://localhost:8009/examples

</Location>

<Location /test2023 /host-manager >

ProxyPass                  ajp://localhost:8009/host-manager

ProxyPassReverse  ajp://localhost:8009/host-manager

</Location>

<Location  /test2023/manager >

ProxyPass                   ajp://localhost:8009/manager

ProxyPassReverse  ajp://localhost:8009/manager

</Location>

__EOF__

$ sudo vi /opt/tomcat/conf/server.xml << __EOF__

<!-- del by takahab

    <Connector port="8080" protocol="HTTP/1.1"

               connectionTimeout="20000"

               redirectPort="8443" />

    -->

  <!-- add by takahab from -->

   <Connector port="8009" protocol="AJP/1.3"

        address="127.0.0.1"

        proxyName="portal.cxdnext.co.jp"

        proxyPort="443"

        scheme="https"

        secure="true"

        redirectPort="8443"

        rejectIllegalHeader="true"

        secretRequired="false" />

    <!-- to add by takahab -->

__EOF__

【Django環境設定】

(1) Django 仮想環境作成

# mkdir /var/www/wsgi

# cd /var/www/wsgi

# vi get << __EOF__

switch のディレクトリ変更

# ./get deploy

      (./env create)

#  vi  /etc/httpd/conf.d/wsgi.conf <<__EOF__

#

# Timeout: The number of seconds before receives and sends time out.

#

Timeout 600

WSGIApplicationGroup %{GLOBAL}

WSGISocketPrefix /var/run/wsgi

WSGIDaemonProcess test2023 user=apache group=apache processes=1 threads=100 maximum-requests=10000 \

   home=/var/www/wsgi/test2023 \

   python-home=/var/www/wsgi/test2023/.venv \

   python-path=/var/www/wsgi/test2023:/var/www/wsgi/test2023/.venv/lib/python3.9/site-packages \

   lang=ja_JP.utf8

WSGIScriptAlias /test2023 /var/www/wsgi/test2023/Config/wsgi.py process-group=test2023

Alias /assets/ /aws/efs/assets/

Alias /media/  /aws/efs/media/

<Directory /aws/efs/assets>

   Require all granted

</Directory>

<Directory /aws/efs/media>

   Require all granted

</Directory>

WSGIPassAuthorization on

<Location /test2023>

  WSGIProcessGroup test2023

</Location>

__EOF__

# vi /etc/httpd/conf.modules.d/20-wsgi.conf << __EOF__

LoadModule wsgi_module /var/www/wsgi/test2023/.venv/lib64/python3.9/site-packages/mod_wsgi/server/mod_wsgi-py39.cpython-39-x86_64-linux-gnu.so

__EOF__

#------------------------------------------参考-------------------------------------------------

Collecting mod-wsgi

  Using cached mod_wsgi-4.9.4.tar.gz (497 kB)

  Preparing metadata (setup.py) ... error

  error: subprocess-exited-with-error

  

  × python setup.py egg_info did not run successfully.

  │ exit code: 1

  ╰─> [6 lines of output]

      Traceback (most recent call last):

        File "<string>", line 2, in <module>

        File "<pip-setuptools-caller>", line 34, in <module>

        File "/tmp/pip-install-t1qa20dz/mod-wsgi_1a6f23a5f3fb4d4eba390f4e7d024c38/setup.py", line 88, in <module>

          raise RuntimeError('The %r command appears not to be installed or '

      RuntimeError: The 'apxs' command appears not to be installed or is not executable. Please check the list of prerequisites in the documentation for this package and install any missing Apache httpd server packages.

      [end of output]

  

  note: This error originates from a subprocess, and is likely not a problem with pip.

error: metadata-generation-failed

× Encountered error while generating package metadata.

╰─> See above for output.

 →

# dnf install  python3-devel  rpm-build-libs  httpd-devel

Collecting mysqlclient

  Using cached mysqlclient-2.1.1.tar.gz (88 kB)

      /bin/sh: 行 1: mysql_config: コマンドが見つかりません

      /bin/sh: 行 1: mariadb_config: コマンドが見つかりません

      /bin/sh: 行 1: mysql_config: コマンドが見つかりません

→

# dnf install mariadb-connector-c-devel

【Django】

pip3 install -r requirements.txt でエラー

→ requirements.txt 内のバージョン番号を全て削除して最新版をインストールする。

ImportError: cannot import name 'ugettext_lazy' from 'django.utils.translation'

→from django.utils.translation import ugettext_lazy as _

    ugettext_lazy  --> getttext_lazy

ImportError: Unable to find zbar shared library

dnf install zbar zbar-libs

epelからインストールできない。 --> 保留

DEPRECATION: starkbank-ecdsa is being installed using the legacy 'setup.py install' method, because it does not have a 'pyproject.toml' and the 'wheel' package is not installed. pip 23.1 will enforce this behaviour change. A possible replacement is to enable the '--use-pep517' option. Discussion can be found at https://github.com/pypa/pip/issues/8559

同様に、mod-wsgi-httpd/ mod-wsgi/jaconv/mysqlclient

→requirement.txtの内容をインストールする前にwhellをインストールしておく。

pip3 install wheel 

ImportError: Module "debug_toolbar.panels.profiling" does not define a "ProfilingPanel" attribute/class

【課題】

(1) rsyslogが入っていない。

(2） cronieがはいっていない。

(3） guacamoleでsshログインできない。

            exe="/usr/sbin/sshd" hostname=? addr=? terminal=? res=success'   認証？

(4)      zabbix clientがインストールできない。（openssl のバージョン）

(5)    epelリポジトリが使えない。

(7)    AWS ロードバランサは、pathでバランスされる為、URL ROOTがサーバのROOTと異る。