# dnf -y install flatpak
# flatpak remote-add --if-not-exists flathub https://flathub.org/repo/flathub.flatpakrepo
# flatpak -y install flathub org.openshot.OpenShot
$ flatpak run org.openshot.OpenShot
2023年7月27日木曜日
2023年7月3日月曜日
Rocky Linux 8.8 設定
#---------------------------------------
# TIME ZONE
#---------------------------------------
# timedatectl set-timezone Asia/Tokyo
# timedatectl# localectl set-locale LANG=ja_JP.utf8
# localectl
#------------------------------
# dns
#-------------------------------
#-------------------------------
# vi /etc/resolv.conf
#nameserver 10.14.4.7nameserver 8.8.8.8
# systemctl restart NetworkManager
# dig www.example.org
# vi /etc/sysconfig/tomcat <<_EOF__
CATALINA_HOME=/opt/tomcat
$ openssl genrsa -aes256 2048 > bms.cxdnext.co.jp.key
# dnf module list php
# dnf module enable php:remi-8.0
# dnf install php
# php --version# dnf repolist all
#------------------------------
# chrony
#-------------------------------
● chronyd.service - NTP client/server
Loaded: loaded (/usr/lib/systemd/system/chronyd.service; enabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:chronyd(8)
man:chrony.conf(5)
# systemctl stop ntpd
# systemctl disable ntpd
# systemctl enable chronyd
# systemctl start chronyd
# chronyc sources
MS Name/IP address Stratum Poll Reach LastRx Last sample
======================================================================
^+ x.ns.gin.ntt.net 2 6 17 2 -2262us[-1053us] +/- 87ms
^* time.cloudflare.com 3 6 17 2 +1543us[+2752us] +/- 72ms
^+ 122x215x240x51.ap122.ftt> 2 6 17 2 +4556us[+5765us] +/- 49ms
^+ gotoky.hojmark.net 2 6 17 2 -3532us[-2323us] +/- 40ms
#-------------------------------------
# net-tools
#---------------------------------------
# user作成
#---------------------------------------
# chrony
#-------------------------------
# dnf install chrony
# systemctl status chronyd● chronyd.service - NTP client/server
Loaded: loaded (/usr/lib/systemd/system/chronyd.service; enabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:chronyd(8)
man:chrony.conf(5)
# systemctl stop ntpd
# systemctl disable ntpd
# systemctl enable chronyd
# systemctl start chronyd
# chronyc sources
MS Name/IP address Stratum Poll Reach LastRx Last sample
======================================================================
^+ x.ns.gin.ntt.net 2 6 17 2 -2262us[-1053us] +/- 87ms
^* time.cloudflare.com 3 6 17 2 +1543us[+2752us] +/- 72ms
^+ 122x215x240x51.ap122.ftt> 2 6 17 2 +4556us[+5765us] +/- 49ms
^+ gotoky.hojmark.net 2 6 17 2 -3532us[-2323us] +/- 40ms
#-------------------------------------
# net-tools
#------------------------------------
# dnf install net-tools
# dnf install net-tools
# user作成
#---------------------------------------
useradd administrator
passwd administrator<<__EOF__
WEB-server-%4266%
WEB-server-%4266%
__EOF__
useradd sysadm
passwd sysadm<<__EOF__
WEB-server-\$9166\$
WEB-server-\$9166\$
__EOF__
useradd sysuser
passwd sysuser<<__EOF__
WEB-server-<0308<
WEB-server-<0308<
__EOF__
useradd appuser
passwd appuser<<__EOF__
WEB-server-!4795!
WEB-server-!4795!
__EOF__
# ------------------------------------
# JAVA
#-------------------------------------
# dnf install java-17-openjdk
# update-alternatives --config java# dirname $(readlink $(readlink $(which java)))
# vi /etc/profile.d/java.shexport JAVA_HOME=$(dirname $(dirname $(readlink $(readlink $(which java)))))
export PATH=$PATH:$JAVA_HOME/bin
#----------------------------------------
# tomcat
#----------------------------------------
# dnf install wget
# dnf install wget
# cd /opt
# wget https://dlcdn.apache.org/tomcat/tomcat-10/v10.1.10/bin/apache-tomcat-10.1.10.tar.gz
# tar xzvf apache-tomcat-10.1.10.tar.gz
# ln -s apache-tomcat-10.1.10 tomcat
# vi /usr/lib/systemd/system/tomcat.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
EnvironmentFile=/etc/sysconfig/tomcat
RemainAfterExit=yes
User=tomcat
Group=tomcat
[Install]
WantedBy=multi-user.target
__EOF__
CATALINA_HOME=/opt/tomcat
__EOF__
# cd /opt
# ln -s /opt/apache-tomcat-10.1.10 tomcat
# useradd -M -d /opt/tomcat tomcat
# chown -R tomcat:tomcat /opt/tomcat /opt/tomcat/*
# vi /etc/httpd/conf.d/tomcat.conf <<__EOF__
<Location /tomcat/ >
ProxyPass ajp://localhost:8009/
ProxyPassReverse ajp://localhost:8009/tomcat/
</Location>
<Location /docs/ >
ProxyPass ajp://localhost:8009/docs/
ProxyPassReverse ajp://localhost:8009/docs/
</Location>
<Location /examples/ >
ProxyPass ajp://localhost:8009/examples/
ProxyPassReverse ajp://localhost:8009/examples/
</Location>
<Location /host-manager/ >
ProxyPass ajp://localhost:8009/host-manager/
ProxyPassReverse ajp://localhost:8009/host-manager/
</Location>
<Location /manager/ >
ProxyPass ajp://localhost:8009/manager/
ProxyPassReverse ajp://localhost:8009/manager/
</Location>
__EOF__
■ postgres jdbcドライバ
URL： https://jdbc.postgresql.org/download/
# mv postgresql-42.6.0.jar /opt/tomca/lib/
# chown tomcat:tomcat postgresql-42.6.0.jar
# chmod 640 postgresql-42.6.0.jar
# vi /opt/tomcat/conf/context.xml
<Context>
<Resource name="jdbc/PostgreSQL"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
initialSize="2" maxActive="4" minIdle="1" maxIdle="2"
username="service_admin" password="casio00"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/servicebase"
validationQuery="SELECT 1" />
</Context>
■ MariaDB jdbc ドライバ
java 8+ connector
3.1.4-GA
Platform Independent
# mv mariadb-java-client-3.1.4.jar /opt/tomcat/lib/
# chown tomcat:tomcat /opt/tomcat/lib/mariadb-java-client-3.1.4.jar
# systemctl start tomcat
# systemctl enable tomcat
# systemctl restart httpd
# ------------------------------------
# httpd
#-------------------------------------
# dnf install https://pkgs.dyn.su/el8/base/x86_64/raven-release-1.0-3.el8.noarch.rpm
# dnf -y install --disablerepo=AppStream --enablerepo=raven httpd httpd-devel mod_ssl
# vi /etc/httpd/conf/httpd.conf << __EOF__
# add by takahab
ServerName shqap0392:80
# del by takahab
# Listen 80
# mod by takahab
#Options Indexes FollowSymLinks
Options FollowSymLinks
# add by takahab
ServerTokens ProductOnly
ServerSignature Off
Timeout 300
TraceEnable off
Header append X-FRAME-OPTIONS "SAMEORIGIN"
__EOF__
# vi /etc/httpd/conf.d/ssl.conf
ServerName bms.cxdnext.co.jp:443
# httpd -t
AH00526: Syntax error on line 101 of /etc/httpd/conf.d/ssl.conf:
SSLCertificateFile: file '/etc/pki/tls/certs/localhost.crt' does not exist or is empty
# firewall-cmd --add-service=http --zone=public --permanent
# firewall-cmd --add-service=https --zone=public --permanent
# firewall-cmd --reload
# systemctl restart httpd
Enter TLS private key passphrase for bms.cxdnext.co.jp:443 (RSA) : *******
#------------------------------------------------------
# 仮証明書
#------------------------------------------------------
URL: https://www.digicert.com/help/
# vi /etc/httpd/conf.d/ssl.conf
SSLCertificateFile /var/local/certs/bms.cxdnext.co.jp.crt
SSLCertificateKeyFile /var/local/certs/bms.cxdnext.co.jp.key
#SSLCertificateChainFile /var/local/certs/bms.cxdnext.co.jp-chain.crt
$ openssl req -new -key bms.cxdnext.co.jp.key > bms.cxdnext.co.jp.csr
$ openssl x509 -in bms.cxdnext.co.jp.csr -days 36500 -req -signkey bms.cxdnext.co.jp.key > bms.cxdnext.co.jp.crt
# ls -l /var/local/certs
-rw-r--r--. 1 root root 1224 7月 6 11:38 bms.cxdnext.co.jp.crt
-rw-r--r--. 1 root root 1041 7月 6 11:37 bms.cxdnext.co.jp.csr
-rw-r--r--. 1 root root 1766 7月 6 11:33 bms.cxdnext.co.jp.key
#----------------------------------------------------------------------------------
# 本番サーバ証明書発行 （stts.cxdnext.co.jp)
#----------------------------------------------------------------------------------
# nmcli general hostname www.exsample.co.jp
# systemctl restart NetworkManager.service
# vi /etc/httpd/conf.d/ssl.conf <<__EOF__
ServerName www.example.co.jp:443
__EOF__
# openssl genrsa -aes256 2048 > www.example.co.jp.key
# openssl req -new -key www.example.co.jp.key > www.example.co.jp.csr
# openssl x509 -in www.example.co.jp.csr -days 3650 -req -signkey www.example.co.jp.key > www.example.co.jp.crt
# openssl genrsa -aes256 2048 > www.example.co.jp.key
Generating RSA private key, 2048 bit long modulus (2 primes)
.......+++++
.......................................+++++
e is 65537 (0x010001)
Enter pass phrase:example
Verifying - Enter pass phrase:example
sh-4.4# openssl req -new -key www.example.co.jp.key > www.example.co.jp.csr
Enter pass phrase for www.example.co.jp.key:example
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:JP
State or Province Name (full name) []:Tokyo
Locality Name (eg, city) [Default City]:Shibuya-ku
Organization Name (eg, company) [Default Company Ltd]:EXAMPLE CO., LTD.
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:www.example.co.jp
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# ls -l /var/local/certs/
-rw-r--r--. 1 root root 1553 7 月 19 13:58 www.example.co.jp-chain.crt ← 中間証明書
-rw-r--r--. 1 root root 2333 7 月 19 13:57 www.example.co.jp.crt ← サーバ証明書
-rw-r--r--. 1 root root 1009 7 月 19 11:43 www.example.co.jp.csr
-rw-r--r--. 1 root root 1766 7 月 19 11:41 www.example.co.jp.key
#-------------------------------------------
# Postgres
#-------------------------------------------
# dnf install postgresql14-server
# passwd postgres
postgres_password
※ semanage: コマンドが見つかりません
# dnf provides /usr/sbin/semanage
# dnf install policycoreutils-python-utils
# semanage fcontext -a -t postgresql_db_t "/data/postgres(/.*)?"
# PGSETUP_INITDB_OPTIONS='--encoding=UTF-8 --no-locale'
# /usr/pgsql-14/bin/postgresql-14-setup initdb
# vi /var/lib/pgsql/14/data/pg_hba.conf
#local all all peer
local all all trust
#host all all 127.0.0.1/32 ident
host all all 192.168.0.0/16 password
# vi /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '*'
port = 5432
# firewall-cmd --add-port=5432/tcp --zone=public --permanent
# firewall-cmd --reload
# mkdir -p /data/postgres/data
# chown -R postgres:postgres /data/postgres/data
# semanage fcontext -a -t postgresql_db_t "/data/postgres(/.*)?"
# grep -i postgresql /etc/selinux/targeted/contexts/files/file_contexts.local
# restorecon -R -v /data/postgres
# ls -lZR /data/postgres
（確認）
# ls -l /var/lib/pgsql/14/data
# vi /usr/lib/systemd/system/postgresql-14.service
# grep -v -E "^#|^$" /var/lib/pgsql/14/data/pg_hba.conf
# grep listen_addresses /var/lib/pgsql/14/data/postgresql.conf
# ls -lZ /var/lib/pgsql
drwx------. 4 postgres postgres system_u:object_r:postgresql_db_t:s0 51 4月 20 14:26 14
# systemctl start postgresql-14
# systemctl enable postgresql-14
$ su - postgres
$ psql -U postgres
ALTER USER postgres PASSWORD 'password';
$ psql
create user admin with password 'password' superuser;
create database workbase owner admin;
grant all privileges on database workbase to admin;
create user service_admin with password 'password' superuser;
create database servicebase owner service_admin;
grant all privileges on database servicebase to service_admin;
Rocky linux9
mkdir /data/tablespace_admin
chown postgres:postgres /data/tablespace_admin
chmod 700 /data/tablespace_admin
mkdir /data/tablespace_office
chown postgres:postgres /data/tablespace_office
chmod 700 /data/tablespace_office
mkdir /data/tablespace_sales
chown postgres:postgres /data/tablespace_sales
chmod 700 /data/tablespace_sales
mkdir /data/tablespace_shop
chown postgres:postgres /data/tablespace_shop
chmod 700 /data/tablespace_shop
mkdir /data/tablespace_calendars
chown postgres:postgres /data/tablespace_calendars
chmod 700 /data/tablespace_calendars
#-----------------------------------------------
# MariaDB client
#-----------------------------------------------
# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# dnf remove mysql*
# dnf remove mysql*
# dnf remove Mariadb*
# dnf install MariaDB-client MariaDB-devel
※ pip install mysqlclientでエラー
Exception: Can not find valid pkg-config name.
6/22 リリースのmysqlclient==2.2.0 でエラー発生､
→ mysqlclient==2.1.0にバージョンダウンで回避。
# dnf install postgresql14-devel
- perl(IPC::Run) が提供されません postgresql14-devel-14.8-2PGDG.rhel8.x86_64 に必要です
→ dnf install perl-CPAN
perl -MCPAN -e shell
cpan> install IPC::Run
→ 解決できない！！！
※ Error: pg_config executable not found.
→ PATH確認
# find / -print |grep pg_config
# PATH=/usr/pgsql-14/bin:$PATH;export PATH
※ libpq-fe.h: そのようなファイルやディレクトリはありません
postgresql14-develがインストールできない為、やむおえず、/usr/pgpsql-14/include/*
をコピー。
#------------------------------------
# Oracle client
#-------------------------------------
■ 11gクライアントインストール
URL: https://www.oracle.com/jp/database/technologies/instant-client/linux-x86-64-downloads.html
https://www.oracle.com/jp/database/technologies/instant-client/linux-x86-64-downloads.html#license-lightbox
# dnf localinstall oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
# vi /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/11.2/client64/lib
# vi /etc/profile.d/oracle.sh
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/:$LD_LIBRARY_PATH
# vi /etc/sysconfig/httpd
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/:$LD_LIBRARY_PATH
ERROR: django.db.utils.NotSupportedError: Oracle 19 or later is required (found 11.2.0.4.0).
■ 19Cクライアントインストール
# dnf localinstall oracle-instantclient19.18-basic-19.18.0.0.0-2.x86_64.rpm --allowerasing
# vi /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/19.18/client64/lib
# vi /etc/profile.d/oracle.sh
export LD_LIBRARY_PATH=/usr/lib/oracle/19.18/client64/lib/:$LD_LIBRARY_PATH
# vi /etc/sysconfig/httpd
LD_LIBRARY_PATH=/usr/lib/oracle/19.18/client64/lib/:$LD_LIBRARY_PATH
→ 同じエラー発生
ERROR: django.db.utils.NotSupportedError: Oracle 19 or later is required (found 11.2.0.4.0).
※ やむおえず、チェックしているソースをNOPにしたら動作した。
vi /var/www/wsgi/office/.venv/lib64/python3.9/site-packages/django/db/backends/base/base.py
if (
self.features.minimum_database_version is not None
and self.get_database_version() < self.features.minimum_database_version
):
db_version = ".".join(map(str, self.get_database_version()))
min_db_version = ".".join(map(str, self.features.minimum_database_version))
#raise NotSupportedError(
# f"{self.display_name} {min_db_version} or later is required "
# f"(found {db_version})."
#)
#---------------------------------------------------
# PHP 8.0
#---------------------------------------------------
# dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
# dnf install https://rpms.remirepo.net/enterprise/remi-release-8.rpm
# dnf module reset php# dnf module list php
# dnf module enable php:remi-8.0
# dnf install php
# php --version
# dnf config-manager --disable epel epel-modular remi-modular remi-safe
# dnf config-manager --enable epel epel-modular
#------------------------------------------------
# python3.9
#------------------------------------------------
# dnf install python39
# update-alternatives --display python3
python3 -ステータスは自動です。
リンクは現在 /usr/bin/python3.6 を指しています。
/usr/bin/python3.6 - 優先度 1000000
スレーブ easy_install-3: /usr/bin/easy_install-3.6
スレーブ pip-3: /usr/bin/pip-3.6
スレーブ pip3: /usr/bin/pip3.6
スレーブ pydoc-3: /usr/bin/pydoc3.6
スレーブ pydoc3: /usr/bin/pydoc3.6
スレーブ pyvenv-3: /usr/bin/pyvenv-3.6
スレーブ python3-man: /usr/share/man/man1/python3.6.1.gz
/usr/bin/python3.9 - 優先度 3900
スレーブ easy_install-3: /usr/bin/easy_install-3.9
スレーブ pip-3: /usr/bin/pip-3.9
スレーブ pip3: /usr/bin/pip3.9
スレーブ pydoc-3: /usr/bin/pydoc3.9
スレーブ pydoc3: /usr/bin/pydoc3.9
スレーブ pyvenv-3: (null)
スレーブ python3-man: /usr/share/man/man1/python3.9.1.gz
現在の「最適」バージョンは /usr/bin/python3.6 です。
sh-4.4# update-alternatives --config python3
2 プログラムがあり 'python3' を提供します。
選択 コマンド
-----------------------------------------------
*+ 1 /usr/bin/python3.6
2 /usr/bin/python3.9
Enter を押して現在の選択 [+] を保持するか、選択番号を入力します:2
#-------------------------------------------------
# Django
#-------------------------------------------------
# dnf install gcc rpm-build python39-devel make zbar
# pip3.9 install mod-wsgi
# find / -print|grep mod_wsgi-py39.cpython
/usr/local/lib64/python3.9/site-packages/mod_wsgi/server/mod_wsgi-py39.cpython-39-x86_64-linux-gnu.so
# vi /etc/httpd/conf.modules.d/20-wsgi.conf
LoadModule wsgi_module /usr/local/lib64/python3.9/site-packages/mod_wsgi/server/mod_wsgi-py39.cpython-39-x86_64-linux-gnu.so
# vi /etc/httpd/conf.d/wsgi.conf << __EOF__
#
# Timeout: The number of seconds before receives and sends time out.
#Timeout 1000
WSGIApplicationGroup %{GLOBAL}
#
# Timeout: The number of seconds before receives and sends time out.
#Timeout 1000
WSGIApplicationGroup %{GLOBAL}
WSGISocketPrefix /var/run/wsgi
WSGIDaemonProcess office user=apache group=apache processes=1 threads=100 maximum-requests=10000 \
home=/var/www/wsgi/office \
python-home=/var/www/wsgi/office/.venv \
python-path=/var/www/wsgi/office:/var/www/wsgi/office/.venv/lib/python3.9/site-packages \
lang=ja_JP.utf8
WSGIScriptAlias /office /var/www/wsgi/office/Config/wsgi.py process-group=office
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 /office>
WSGIProcessGroup office
</Location>
WSGIDaemonProcess office user=apache group=apache processes=1 threads=100 maximum-requests=10000 \
home=/var/www/wsgi/office \
python-home=/var/www/wsgi/office/.venv \
python-path=/var/www/wsgi/office:/var/www/wsgi/office/.venv/lib/python3.9/site-packages \
lang=ja_JP.utf8
WSGIScriptAlias /office /var/www/wsgi/office/Config/wsgi.py process-group=office
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 /office>
WSGIProcessGroup office
</Location>
__EOF__
#-------------------------------------------
# ZABBIX
#-------------------------------------------
# rpm -Uvh https://repo.zabbix.com/zabbix/6.4/rhel/8/x86_64/zabbix-release-6.4-1.el8.noarch.rpm
# dnf clean all
# dnf module switch-to php:7.4
# dnf install zabbix-server-pgsql zabbix-web-pgsql zabbix-apache-conf zabbix-sql-scripts zabbix-selinux-policy zabbix-agent
# sudo -u postgres createuser --pwprompt zabbix
# sudo -u postgres createdb -O zabbix zabbix
# zcat /usr/share/zabbix-sql-scripts/postgresql/server.sql.gz | sudo -u zabbix psql zabbix
DBPassword=password
# su - postgres
# psql
> alter role zabbix with password 'password';
# systemctl restart zabbix-server zabbix-agent httpd php-fpm
# systemctl enable zabbix-server zabbix-agent httpd php-fpm
#-------------------------------------------------
# clam
#-------------------------------------------------
# dnf --enablerepo=epel install clamav clamav-scanner-systemd clamav-update
# ln -s /etc/clamd.d/scan.conf /etc/clamd.conf
# vi /etc/clamd.conf
#Example
LogFile /var/log/clamd.scan
LogFileMaxSize 2M
LogTime yes
LogRotate yes
LocalSocket /var/run/clamd.scan/clamd.sock
FixStaleSocket yes
ExcludePath ^/proc/
ExcludePath ^/sys/
ExcludePath ^/dev/pts/ptmx
ExcludePath ^/etc/gshadow
ExcludePath ^/etc/gshadow-
ExcludePath ^/etc/shadow
ExcludePath ^/etc/shadow-
ExcludePath ^/etc/audit
ExcludePath ^/etc/selinux
ExcludePath ^/etc/security
ExcludePath ^/run/systemd/inaccessible
ExcludePath ^/run/user
ExcludePath ^/var/log/audit
ExcludePath ^/var/lib/selinux/targeted
# vi /etc/freshclam.conf
#Example
DatabaseDirectory /var/lib/clamav
UpdateLogFile /var/log/freshclam.log/
LogFileMaxSize 2M
LogTime yes
LogRotate yes
DatabaseOwner root
# vi /usr/lib/systemd/system/clamd@.service <<__EOF__
[Unit]
Description = clamd scanner (%i) daemon
Documentation=man:clamd(8) man:clamd.conf(5) https://www.clamav.net/documents/
After = syslog.target nss-lookup.target network.target
[Service]
Type = forking
ExecStart = /usr/sbin/clamd -c /etc/clamd.d/%i.conf
# Reload the database
ExecReload=/bin/kill -USR2 $MAINPID
Restart = on-failure
TimeoutStartSec=420
MemoryLimit=256M
CPUQuota=20%
Nice = 19
[Install]
WantedBy = multi-user.target
__EOF__
SElinux
# setsebool -P antivirus_can_scan_system 1
# setsebool -P antivirus_use_jit 1
# freshclam
# systemctl enable clamd@scan
# systemctl start clamd@scan
# vi /etc/cron.daily/clamscan.sh <<__EOF__
#!/bin/bash
PATH=/usr/bin:/bin
dnf - -y update clamav-server clamav-data clamav-update clamav-filesystem clamav clamav-scanner clamav-scanner-systemd clamav-devel clamav-lib clamav-server-systemd > /dev/null 2>&1
freshclam > /dev/null
CLAMSCANTMP=`mktemp`
clamdscan /boot /dev /etc /home /media /mnt /opt /proc /root /run /srv /sys /tmp /usr /var --remove > $CLAMSCANTMP 2>&1
[ ! -z "$(grep FOUND$ $CLAMSCANTMP)" ] && \
grep FOUND$ $CLAMSCANTMP | mail -s "Virus Found in `hostname`" root
[ -z "$(grep FOUND$ $CLAMSCANTMP)" ] && \
echo "clamdscan normal end" | mail -s "Virus Not Found in `hostname`" root
rm -f $CLAMSCANTMP
__EOF__
# chown root:root /etc/cron.daily/clamscan.sh
# chmod 755 /etc/cron.daily/clamscan.sh
→ systemctl start clamd@scanでタイムアウト発生。 保留
#------------------------------------------
# その他課題
#------------------------------------------
1） 7月 06 13:40:53 shqap0392 sshd[683709]: error: kex_exchange_identification: read: Connection reset by peer 多発
2）clamd 起動でタイムアウト
2023年5月9日火曜日
Postgres への移行
tinyint → int
longint → bigint
decimal → numeric( p, s )
engine → カット
comment → カット
char(32) → uuid
longint → bigint
decimal → numeric( p, s )
engine → カット
comment → カット
char(32) → uuid
decimal(13.0) →
longtext → text
datetime → timestamp
longtext → text
datetime → timestamp
【自動時刻更新】
create table xxxxx {
date_update timestamp not null default current_timestamp,
xxxx
}create or replace function trigger_set_timestamp() returns trigger as $$
begin
new.updated_at = noW();
return new; end;
$$ language plpgsql;
【insert or update】
INSERT INTO {tabale} ( {colmuns} ) VALUES ({values})
ON DUPLICATE KEY UPDATE {updates}
↓
INSERT INTO {table} VALUES ({values})
ON CONFLICT ON CONSTRAINT {table}_primary
DO UPDATE SET {updates};
【大文字テーブル名】
大文字の入ったテーブル名は、“”で囲む必要がある。（囲まない小文字としてみなすよう）
【distinct 】
()が必要。
【カレンダ出力】
#DEBUG
if DB_SYSTEM == 'postgres':
WITH_RECURSIVE_DATE_TABALE = f"""
with recursive date_table (date_value) AS (
select
(select date_trunc( 'month', current_date + interval '-{str(before)} month' + '1 Day'))
union all select date_value + interval '1 Day'
from date_table
where date_value < (select date_trunc('month', current_date) + '{str(before + 1)} month' +'-1 Day')
)"""
TO_CHAR_DATE_VALUE = """to_char(date_value, 'YYYYmmdd')"""
else:
WITH_RECURSIVE_DATE_TABALE = f"""
with recursive date_table(date_value) as (
select
(select date_format(adddate(curdate(), interval -{str(before)} month), '%Y-%m-01'))
union all
select
date_add(date_value, interval 1 day)
from date_table
where
date_value < (select last_day(adddate(curdate(), interval -{str(before)} month)))
)"""
TO_CHAR_DATE_VALUE = """date_format(date_value, '%Y%m%d')"""
【date_format】
TO_CHAR_DATE_VALUE = """to_char(date_value, 'YYYYmmdd')"""
↓
TO_CHAR_DATE_VALUE = """date_format(date_value, '%Y%m%d')"""
【ifnull】
ifnull(gross_amt,0)
↓
COALESCE(gross_amt,0)
【uuid】
program sql DBフィールド型
mysql UUID UUID.hex char(32)
postgres UUID str( UUID) uuid
uuidをhexに変換してSQLに埋め込む。
↓
uuid1をstrに新刊してSQLに埋め込む。
2023年4月24日月曜日
Rocky Linux9 に postgres インストール
$ cat /etc/redhat-release
Rocky Linux release 9.1 (Blue Onyx)
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';
2023年4月7日金曜日
amazon Linux 2023 設定
【タイムゾーンと日本語】
$ sudo timedatectl set-timezone Asia/Tokyo
$ sudo timedatectl
$ sudo localectl set-locale LANG=ja_JP.utf8
$ sudo localectl
【プロキシ設定】
$ sudo amazon-linux-extras install epel ----> 廃止
$ 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
$ 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 マウント】
【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
$ 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
【PYTHON】
【JAVA】
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
$ 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.
>>>
$ 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と異る。
2023年3月23日木曜日
EC2 linux 2にora2pg インストール
【オラクルクライアントインストール】
# rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
# vi /etc/profile.d/oracle.sh <<__EOF__
export ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export C_INCLUDE_PATH=/usr/include/oracle/11.2/client64
sudo dnf -y install postgresql15-private-devel # シンボリックリンクのみで実体なし。
__EOF__
# source /etc/profile
【参考】20241025 追記 amazon linux2023
sudo timedatectl set-timezone Asia/Tokyo
sudo localectl set-locale LANG=ja_JP.utf8
sudo dnf -y install libnsl
cd /tmp
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-basic-19.20.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-devel-19.20.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-sqlplus-19.20.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-jdbc-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-basic-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-devel-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-sqlplus-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-jdbc-19.20.0.0.0-1.x86_64.rpm
sudo sh -c 'cat > /etc/profile.d/oracle.sh <<__EOF__
export ORACLE_HOME=/usr/lib/oracle/19.20/client64/lib
export LD_LIBRARY_PATH=/usr/lib/oracle/19.20/client64/lib
export C_INCLUDE_PATH=/usr/include/oracle/19.20/client64
__EOF__'
source /etc/profile
sudo sh -c 'cat > /etc/ld.so.conf.d/oracle.conf <<__EOF__
/usr/lib/oracle/19.20/client64/lib
__EOF__'
sudo ldconfig
sqlplus user/password@host:1521/service_name(sid) # SIDではつながらなかった。
#sudo dnf provides */pg_config
: # /usr/bin/pg_config -> pg_server_config
sudo dnf -y install postgresql15-server-devel # これで実体がインストールされた。
sudo dnf -y install postgresql15-server-devel # これで実体がインストールされた。
sudo dnf install -y perl-CPAN
echo "export PERL_MM_USE_DEFAULT=1" >> ~/.bashrcecho "export PERL_INSTALL_ROOT=/usr/lib64/perl5" >> ~/.bashrc
source ~/.bashrc
cpanm --quiet DBD::Pg is up to date (3.18.0).
【DBD::Oracle ドライバインストール】
$ sudo yum -y install perl-CPAN
$ sudo yum -y install libyaml-devel
$ sudo yum -y install gcc
$ git clone https://github.com/darold/ora2pg.git
sudo dnf -y install perl-DBI perl-YAML
source ~/.bashrc
#-------------------対面-----------------------------------
cpan
cpan[1]> look Test::More
perl Makefile.PL
sudo make
sudo make install
exit
exit
cpan
cpan[1]> look DBD::Oracle
perl Makefile.PL
sudo make
sudo make install
exit
exit
#-------------------対面-----------------------------------
#-------------------非対面-----------------------------------
export PERL_MM_USE_DEFAULT=1
cpan Test::More <<__EOF__
look Test::More
__EOF__
cd $(find ~/.cpan/build -type d -name "Test-More*" | tail -n 1)
perl Makefile.PL
sudo make
sudo make install
exit
export PERL_MM_USE_DEFAULT=1
cpan -T -i DBD::Oracle <<__EOF__
look DBD::Oracle
__EOF__
cd $(find ~/.cpan/build -type d -name "DBD-Oracle*" | tail -n 1)
perl Makefile.PL
sudo make
sudo make install
exit
#-------------------非対面-----------------------------------
cd ~
mkdir ora2pg
curl -OL https://github.com/darold/ora2pg/archive/refs/tags/v24.3.tar.gz
tar xvzf v24.3.tar.gz
cd ora2pg-24.3
sudo perl Makefile.PL
sudo sudo make
sudo make install
source ~/.bashrc
ora2pg help
#--------------------------------Ora2PG install 終了-------------------------------------
sudo dnf -y install perl-CPAN
sudo dnf -y install libyaml-devel
sudo dnf -y install gcc # allready installed
sudo dnf -y install perl-ExtUtils-MakeMaker # allready installed
sudo dnf -y install perl-Test-Simple # allready installed
sudo dnf -y install perl-Env # allready installed
sudo dnf -y install postgresql15
sudo dnf -y install perl-DBI perl-DBD-Pg
$ cd /home/ec2-user
$ cd /home/ec2-user
※先にポストグレスとオラクルドライバをインストール
sudo cpan App::cpanminus
sudo cpan
sudo cpan
cpan 1> o conf init
Would you like to configure as much as possible automatically? [yes] yes
upgrade
What approach do you want? (Choose 'local::lib', 'sudo' or 'manual')
Would you like me to append that to /home/ec2-user/.bashrc now? [yes]
network traffice external site?( Y/n) return
Path to pg_config? return
Check for a new version of the Public Suffix List? [N ]
Import PAUSE and author keys to GnuPG? [y]
Do you want to run external tests?
These tests *will* *fail* if you do not have network connectivity. [n]
network traffic to external sites. [Y/n]
sudo cpanm --quiet YAML
sudo cpanm --quiet DBI
sudo cpanm --quiet Test
sudo cpanm --quiet Test::NoWarnings
sudo cpanm --quiet DBD::Oracle
sudo cpanm --quiet DBD::Pg
【ora2pg インストール】
$ sudo dnf install git
$ mkdir Make
$ cd Make
$ git clone https://github.com/darold/ora2pg.git
$ git clone https://github.com/darold/ora2pg.git
$ cd ./ora2pg
$ perl Makefile.PL
$ gmake
$ sudo gmake install
#---------------------------------------------------------------------------
【参考】 20240123 追記 Rocky linux9.3
# dnf install perl-CPAN
# dnf install libyaml-devel
# dnf install gcc
# dnf install perl-ExtUtils-MakeMaker
# dnf install perl-Test-Simple
# dnf install perl-Env
# dnf install postgresql15-devel
# dnf install perl-DBI perl-DBD-Pg
POSTGRES_HOME=/usr/pgsql-15 # for DBD::Pg
$ cpan
o conf init
upgrade
takahab
noxxxxx
install YAML
install Test
install Test::NoWarnings # 下記エラー発生の為追加。
install DBI
install DBD::Oracle
install DBD::Pg
DBD::Pg is up to date (3.18.0).
$ sudo yum -y install perl-CPAN
$ sudo yum -y install libyaml-devel
$ sudo yum -y install gcc
$ sudo yum install perl-ExtUtils-MakeMaker
$ sudo yum install perl-Test-Simple
$ sudo yum install perl-Test-Simple
$ sudo yum install perl-Env
$ sudo yum install postgresql-devel # for DBD::Pg
$ cpan
o conf init
$ export NLS_LANG=Japanese_Japan.JA16SJIS
【ora2pg インストール】
upgrade
install YAML
install Test
install Test::NoWarnings # 下記エラー発生の為追加。
install DBI
install DBD::Oracle
install DBD::Pg # for type=COPY
$ export ORACLE_SID=ARCSDBMS2
$ export ORACLE_USERID="arcsdbcb/password"
$ export ORACLE_DSN="dbi:Oracle:SID=arcsdbms2;host=192.168.10.201;port=1521"$ export NLS_LANG=Japanese_Japan.JA16SJIS
or
export NLS_LANG=Japanese_Japan.AL32UTF8
$ sqlplus64 arcsdbcb/passwd@10.0.19.2:1521/arcsdbms.world
# vi /etc/ld.so.conf.d/oracle.conf <<__EOF__
/usr/lib/oracle/11.2/client64/lib
__EOF__
$ cpan
install DBI
install DBD::Oracle
install DBD::Pg # for type=COPY
ERROR Test::NoWarnings is not installed (any version required)
install Test::NoWarnings
# vi /etc/profile.d/proxy.sh <<__EOF__
PROXY="http://10.0.19.2:3128"
export http_proxy=$PROXY
export HTTP_PROXY=$PROXY
export https_proxy=$PROXY
export HTTPS_PROXY=$PROXY
__EOF__
# cpan DBI
# cpan DBD::Oracle
又は、
$ perl -MCPAN -e shell #全てEnter
$ perl -MCPAN -e 'install DBI'
$ perl -MCPAN -e 'install DBD::Oracle'
$ git clone https://github.com/darold/ora2pg.git
$ cd ./ora2pg
$ perl Makefile.PL
$ gmake
$ sudo gmake install
【初期設定】
$ mkdir migration$ ora2pg --project_base ./migration --init_project test_project
$ vi ./migration/test_project/config/ora2pg.conf __EOF__
ORACLE_DSN dbi:Oracle:host=192.168.10.201;sid=xxxxxxxx;port=1521
ORACLE_USER xxxxxxxxx
ORACLE_PWD xxxxxx
# Oracle schema/owner to use
SCHEMA xxxxxxx
__EOF__
$ cd ./migration/test_project
ora2pg -t SHOW_VERSION -c config/ora2pg.conf
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
※ 設定ファイルのひな型 : /etc/ora2pg/ora2pg.conf.dist
$ cp /etc/ora2pg/ora2pg.conf.dist ora2pg.conf
【Oracle から Postgresへ変換】
$ ssh ora2pg
$ mkdir Database
$ cd Database
$ mkdir arcsdbcb
$ cd arcsdbcb
$ cp /etc/ora2pg/ora2pg.conf.dist ora2pg.conf
$ vi ora2pg.conf << __EOF__
ORACLE_DSN dbi:Oracle:host=10.0.19.2;sid=xxxxxxxx;port=1521
ORACLE_USER xxxxxxxxx
ORACLE_PWD xxxxxx
__EOF__
【on the fly 】
$ vi ora2pg.conf << __EOF__
PG_DSN dbi:Pg:dbname=ARCSDBMS;host=10.0.18.101;port=5432
PG_USER xxxxxxcb
PG_PWD xxxxx00
__EOF__
【表領域出力＆パーティション】
$ vi ora2pg.conf << __EOF__
DATA_LIMIT 1000 # for out of memory
USE_TABLESPACE 1
KEEP_PKEY_NAMES 0 # 表領域出力 USE_TABLESPACE=1にしているので、内部で強制的に１になる。
PREFIX_PARTITION 0 # RENAME_PARTITION と同じ動き？ → 0指定であとで手動で修正
PG_NUMERIC_TYPE 0 # numbe(n,m) → real → decimal(n,m)
PG_INTEGER_TYPE 0 # number(n) → bigint → numeric( n)
STOP_ON_ERROR 0 # for debug
__EOF__
【修正】
ora2pg_TABLE.sql
1) 1,$s/TABLESPACE ARCSDB/TABLESPACE TABLESPACE_ARCSDB/g
2) 1,$s/_primary/_pkey/
3) rb_xxxx →table_pkey に修正
$ cat *TAB*sql |grep rb_
ALTER TABLE cca002 ADD CONSTRAINT rb_4f3840d101 PRIMARY KEY (acode,gcode,ccode,scode);
ALTER TABLE cca003 ADD CONSTRAINT rb_48edd00701 PRIMARY KEY (acode,gcode,ccode,rcode);
ALTER TABLE cca004 ADD CONSTRAINT rb_48edd02701 PRIMARY KEY (acode,gcode,ccode,tcode);
ALTER TABLE ccc002 ADD CONSTRAINT rb_4f3840ec03 PRIMARY KEY (gcode,ccode,scode);
ALTER TABLE ccc003 ADD CONSTRAINT rb_48edd04105 PRIMARY KEY (gcode,ccode,rcode);
ALTER TABLE ccc004 ADD CONSTRAINT rb_48edd05a04 PRIMARY KEY (gcode,ccode,tcode);
4) サブパーティション名変更
mv ora2pg_PARTITION.sql ora2pg_PARTITION.sql.org
cat ora2pg_PARTITION.sql.org |./convert.py >ora2pg_PARTITION.sql
5) csb008 Primary KEY名不正 (ora2pg_TABLE.sql )
csb008 -> csb008_pkey
ysb008 -> ysb008_pkey
sss016_pkey2 -> sss016_pkey
TABLESPACE_ARCSDBPA_TBL -> TABLESPACE_ARCSDBSS_TBL (sst999 )
5) ERROR: テーブル空間 tablespace_arcsdbss_tbl へのアクセスが拒否されました。
arcsdbss -> arcsdbxx (一部 検索要）
arcsdbss -> arcsdbtm
$ mkdir -P ~/Database/arcsdbcb
$ cd Database/arcsdbcb
$ cp /etc/ora2pg/ora2pg.conf.dist ora2pg_cb.conf
$ time ora2pg -c ora2pg_cb.conf -t TABLE -b ~/Database/arcsdbcb/ -o ora2pg_TABLE.sql
[2024-01-10 19:52:19] [========================>] 82/82 tables (100.0%) end of scanning.
[2024-01-10 19:53:05] [========================>] 82/82 tables (100.0%) end of table export.
real 6m21.216s
user 0m0.367s
sys 0m0.093s
$ time ora2pg -c ora2pg_cb.conf -t PARTITION -N arcsdbcb -b ~/Database/arcsdbcb/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 15:26:27] [========> ] 3564/9900 partitions (36.0%) end of output.
real 7m16.630s
user 0m0.623s
sys 0m0.231s
$ time ora2pg -c ora2pg_cb.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 15:47:53] [========================>] 82/82 tables (100.0%) end of scanning.
[2024-01-22 15:48:10] [========================>] 10/10 objects types (100.0%) end of objects auditing.
real 5m8.331s
user 0m0.155s
sys 0m0.067s
$ time ora2pg -c ora2pg_cb.conf -t GRANT -N arcsdcb -b ~/Database/arcsdbcb/ -o ora2pg_GRANT.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
real 1m52.493s
user 0m0.106s
sys 0m0.010s
$ time ora2pg -c ora2pg_ss.conf -t TABLE -b ~/Database/arcsdbss/ -o ora2pg_TABLE.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 19:05:12] [========================>] 375/375 tables (100.0%) end of scanning.
[2024-01-22 19:10:02] [========================>] 375/375 tables (100.0%) end of table export.
real 19m48.498s
user 0m1.752s
sys 0m0.362s
$ time ora2pg -c ora2pg_ss.conf -t PARTITION -N arcsdbcb -b ~/Database/arcsdbss/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 19:34:16] [========> ] 28727/79783 partitions (36.0%) end of output.
real 22m30.711s
user 0m4.047s
sys 0m1.588s
$ time ora2pg -c ora2pg_ss.conf -t PARTITION -N arcsdbss -b ~/Database/arcsdbss/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 22:27:11] [========> ] 28727/79783 partitions (36.0%) end of output.
real 23m33.264s
user 0m4.306s
sys 0m1.546s
USAGE ./CreDB.sh create_tablespace_directory # 表領域の作成
./CreDB.sh dropdatabases_arcsdbms # ARCSDBMSデータベースの削除(databae,schema,role,table)
./CreDB.sh initdatabases_arcsdbms # ARCSDBMS全スキーマ・ROLE作成
./CreDB.sh arcsdbms_gettable # オラクルからテーブル情報取得
./CreDB.sh arcsdbms_createtable # ARCSDVMS内全テーブルの作成
./CreDB.sh arcsdbms_copy # オラクル環境からの全データのコピー
ERROR: 共有メモリが足りません
HINT: max_locks_per_transactionを増やす必要があるかもしれません
# vi /var/lib/pgsql/15/data/postgresql.conf
shared_buffers =128MB -> 256MB
max_locks_per_transaction = "" ->512
max_connections = 100 -> 200
# systemctl restart postgresql-15
→解消されず。
# vi /pg_data/data/postgresql.conf
max_locks_per_transaction = "" ->1024
→ 解消された。
TABLESPACEを指定しないCREATEテーブル文がある。
→
SET default_tablespace = TABLESPACE TABLESPACE_ARCSDBSS_TBL; 追加。
FATAL: ERROR: リレーション"ccm201"の列"norcode"は存在しません.
→ psql -h 10.0.18.101 -U arcsdbcb -d arcsdbms
ALTER TABLE arcsdbcb.ccm201 ADD norcode varchar(1) NULL DEFAULT '0';
psql -h 10.0.18.101 -U vrcsdbcb -d arcsdbms
ALTER TABLE vrcsdbcb.ccm201 ADD norcode varchar(1) NULL DEFAULT '0';
削除するときは
ALTER TABLE arcsdbcb.ccm201 DROP COLUMN norcode;
※ CXD擬似のxrcsdbcbは、norcodeはなかった。
FATAL: ERROR: リレーション"ssn003"の列"authoritymasterprod"は存在しません
→
psql -h 10.0.18.101 -U arcsdbss -d arcsdbms
alter table arcsdbss.ssn003 add authoritymasterprod varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymasterdept varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymasterprodgroup varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymastercust varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymasteremployee varchar(1) NULL DEFAULT '2';
FATAL: ERROR: リレーション"ssz103"の列"month"は存在しません
→
psql -h 10.0.18.101 -U arcsdbss -d arcsdbms
alter table arcsdbss.ssz103 add month numeric(14,0);
alter table arcsdbss.ssz103 add whcode numeric(14,0);
alter table arcsdbss.ssz103 add actqty numeric(14,0);
alter table arcsdbss.ssz103 add alcqty numeric(14,0);
alter table arcsdbss.ssz103 add resqty numeric(14,0);
alter table arcsdbss.ssz103 add movqty numeric(14,0);
alter table arcsdbss.ssz103 add ordqty numeric(14,0);
alter table arcsdbss.ssz103 add rcvqty numeric(14,0);
alter table arcsdbss.ssz103 add pmonthqty numeric(14,0);
alter table arcsdbss.ssz103 add whinqty numeric(14,0);
alter table arcsdbss.ssz103 add whoutqty numeric(14,0);
alter table arcsdbss.ssz103 add whadjqty numeric(14,0);
alter table arcsdbss.ssz103 add qtypurchaseret numeric(14,0);
alter table arcsdbss.ssz103 add qtysalesret numeric(14,0);
alter table arcsdbss.ssz103 add qtymoveout numeric(14,0);
alter table arcsdbss.ssz103 add qtymovein numeric(14,0);
alter table arcsdbss.ssz103 add qtyinventoryout numeric(14,0);
alter table arcsdbss.ssz103 add qtyinventoryin numeric(14,0);
alter table arcsdbss.ssz103 add qtyrepairout numeric(14,0);
alter table arcsdbss.ssz103 add qtyrepairin numeric(14,0);
alter table arcsdbss.ssz103 add qtyproductout numeric(14,0);
alter table arcsdbss.ssz103 add qtyproductin numeric(14,0);
alter table arcsdbss.ssz103 add qtydismantleout numeric(14,0);
alter table arcsdbss.ssz103 add qtydismantlein numeric(14,0);
alter table arcsdbss.ssz103 add qtyhousebonout numeric(14,0);
※ データもNULLなので、本来なら擬似環境から削除する方が望ましい。
擬似環境ORACLE DLL
"MONTH" NUMBER(*,0),
"WHCODE" NUMBER(*,0),
"ACTQTY" NUMBER(*,0),
"ALCQTY" NUMBER(*,0),
"RESQTY" NUMBER(*,0),
"MOVQTY" NUMBER(*,0),
"ORDQTY" NUMBER(*,0),
"RCVQTY" NUMBER(*,0),
"PMONTHQTY" NUMBER(*,0),
"WHINQTY" NUMBER(*,0),
"WHOUTQTY" NUMBER(*,0),
"WHADJQTY" NUMBER(*,0),
"QTYPURCHASERET" NUMBER(*,0),
"QTYSALESRET" NUMBER(*,0),
"QTYMOVEOUT" NUMBER(*,0),
"QTYMOVEIN" NUMBER(*,0),
"QTYINVENTORYOUT" NUMBER(*,0),
"QTYINVENTORYIN" NUMBER(*,0),
"QTYREPAIROUT" NUMBER(*,0),
"QTYREPAIRIN" NUMBER(*,0),
"QTYPRODUCTOUT" NUMBER(*,0),
"QTYPRODUCTIN" NUMBER(*,0),
"QTYDISMANTLEOUT" NUMBER(*,0),
"QTYDISMANTLEIN" NUMBER(*,0),
"QTYHOUSEBONOUT" NUMBER(*,0),
FATAL: ERROR: 行に対応するパーティションがリレーション"ssz103"に見つかりません SSZ103 (1000 recs/sec)
DETAIL: 失敗した行のパーティションキーは(zdate) = (20520120)を含みます。
CONTEXT: ssz103のCOPY、行 79: "301 1093 0001 7716650000361 20520120 1000000000000004 20220120165711 905 0 -1 0 0 1 905 0 0 00 0 0 ..."
FATAL: ERROR: 行に対応するパーティションがリレーション"ssz104"に見つかりません
DETAIL: 失敗した行のパーティションキーは(month) = (205201)を含みます。
CONTEXT: ssz104のCOPY、行 64: "301 1093 0001 7716650000361 205201 1000000000000004 20220120165711 905 0 -1 0 0 1 905 0 0 0 00 0 0 ..."
→デフォルトパーティション作成
CREATE TABLE ssz103_default PARTITION OF ssz103 DEFAULT;
CREATE TABLE ssz104_default PARTITION OF ssz104 DEFAULT;
CREATE TABLE ssz111_default PARTITION OF ssz111 DEFAULT;
CREATE TABLE ssz112_default PARTITION OF ssz112 DEFAULT;
CREATE TABLE ssz113_default PARTITION OF ssz113 DEFAULT;
CREATE TABLE ssz118_default PARTITION OF ssz118 DEFAULT;
NOTICE: リレーション"batchstatuslog_seq"は存在しません、スキップします
→ vi run.sh
time ora2pg -c ora2pg_pa.conf -t SEQUENCE -N arcsdbpa -b ~/Database/arcsdbpa/ -o ora2pg_SEQUENCE.sql
psql:ora2pg_VIEW.sql:149: ERROR: 列"BUSINESSTYPE"は存在しません
行 3: select "BUSINESSTYPE","PROCESSTYPE","REVERSALTYPE","DEALINGM...
※ ""カット（大文字->小文字）
→129,149s/"//g
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)
※ CXD擬似には、arcsdbtp, darcsdbcb ,trcsdbcb は存在しない。
【参考】
psql コマンド 例
psql -h host -d postgres -U postgres
\l データベース一覧表示
\c arcsdbms データベースを切り替える
\c arcsdbms arcsdbcb データベースとユーザを切り替える
\c - arcsdbss ユーザのみを切り替える
\dn スキーマ一覧
\d テーブル一覧表示(viewやsequence含む)
\dt テーブル一覧 （ユーザ切り替え後に実行）
\d
\d arcsdbcb.cca001 列定義一覧
\z arcsdbcb.cca001 テーブルのアクセス権限表示
\du ユーザー(Role)の一覧を表示
\dp アクセス権限の表示
\z
select current_database();select current_user; 現在のユーザ
select * from pg_user; ユーザ一覧
#----------------------------------------
# 定義
#----------------------------------------
STAGE=test_
POSTGRES_HOST=192.168.210.201
SUPERUSER_NAME=${STAGE}admin
DATABASE_NAME=${STAGE}arcsdbms
#SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs arcsdbco arcsdbtm arcsdbtp vrcsdbcb xrcsdbcb"
#SCHEMA_LISTS="xrcsdbcb vrcsdbcb"
#SCHEMA_LISTS="arcsdbcb"
SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs vrcsdbcb xrcsdbcb"
TABLE_LISTS="ssa000 ssa001"
#----------------------------------------
# Main ルーチン
#----------------------------------------
main(){
case $1 in
new )
new
;;
drop )
drop
;;
all )
clean
init
getdll
report
;;
init )
init
;;
getdll )
getdll
;;
putdll )
putdll
;;
report )
report
;;
copy )
copy
;;
copytable )
if [ -p /dev/stdin ]; then
cat -
else
echo $@
fi | copy_by_table
;;
save )
save
;;
clean )
clean
;;
diff )
diff
;;
show )
show
;;
showtable )
showtable
;;
psql )
psql
;;
*)
echo "usage: ./run clean # スキーマワークディレクトリの削除"
echo "usage: ./run drop # 表領域＆データベース＆スキーマ＆ユーザの削除"
echo "usage: ./run new # 表領域＆データベース＆スキーマ＆ユーザの作成"
echo "usage: ./run init # ora2pg.conf ファイルの初期化とスキーマワークディレクトリの作成"
echo "usage: ./run getdll # OracleDBからDLL取得"
echo "usage: ./run putdll # PostgresDBにDLLi登録"
echo "usage: ./run report # 変換レポートの作成"
echo "usage: ./run copy # oracleDBらデータをexportしてposgreDBにimport"
echo "usage: ./run all (clean/init/getdll/report) # /clean/init/getdll/reportを一括実行"
;;
esac
}
new(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
/* --------------------------------------------------+
| create ROLE |
+-------------------------------------------------- */
create user ${SUPERUSER_NAME} with password 'casio00' superuser;
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data
--\! sudo chown postgres:postgres /data
--\! sudo chmod 700 /data
--\! sudo mkdir -p /data/${DATABASE_NAME}_dat
--\! sudo chown postgres:postgres /data/${DATABASE_NAME}_dat
--\! sudo chmod 700 /data/${DATABASE_NAME}_dat
create tablespace ${DATABASE_NAME}_dat owner ${SUPERUSER_NAME} location '/data/${DATABASE_NAME}_dat';
/* --------------------------------------------------+
| create DATABASE |
+-------------------------------------------------- */
create database ${DATABASE_NAME} owner ${SUPERUSER_NAME} tablespace ${DATABASE_NAME}_dat ;
__EOF__
echo DONE CREATE DATABASE database=${DATABASE_NAME} owner=${SUPERUSER_NAME} tablespace=${DATABASE_NAME}_dat ;
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U ${SUPERUSER_NAME} -d ${DATABASE_NAME} << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data/${STAGE}${sc}_tbl
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_tbl
--\! sudo chmod 700 /data/${STAGE}${sc}_tbl
--\! sudo mkdir -p /data/${STAGE}${sc}_idx
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_idx
--\! sudo chmod 700 /data/${STAGE}${sc}_idx
create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_tbl';
create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_idx';
/* --------------------------------------------------+
| create ROLE & SCHEMA |
+-------------------------------------------------- */
create user ${STAGE}${sc} with password 'casio00' ;
create schema ${sc} authorization ${STAGE}${sc};
__EOF__
echo "DONE USER=${STAGE}${sc}"
echo create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_tbl";
echo create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_idx";
done
show_postgres
}
drop(){
echo "データベース（${DATABASE_NAME})を全て削除します。"
echo "他（dbeaver等)で接続している場合は、切断してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
drop schema ${sc};
drop user ${STAGE}${sc};
drop tablespace ${STAGE}${sc}_tbl;
drop tablespace ${STAGE}${sc}_idx;
__EOF__
done
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c postgres postgres
drop database ${DATABASE_NAME};
drop tablespace ${DATABASE_NAME}_dat;
drop user ${SUPERUSER_NAME};
__EOF__
}
show_postgres(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\echo データベース一覧
select * from pg_database;
\echo 表領域一覧
select * from pg_tablespace;
\echo ユーザ一覧
select * from pg_user;
__EOF__
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
showtable(){
sc=arcsdbss
echo "psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c '\dt'"
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c "\dt"
}
psql(){
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
show(){
sc=arcsdbcb
cd $sc
cdir=`pwd`
echo POSTGRES_HOST=192.168.210.201
echo SUPERUSER_NAME=${STAGE}admin
echo DATABASE_NAME=${STAGE}arcsdbms
echo time ora2pg -c ora2pg.conf -t TABLE -N ${STAGE}${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
}
ALL(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
echo time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
echo time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
init(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${STAGE}${sc}/" | sed -e "s/ARCS_PG_DBNAME/${DATABASE_NAME}/"> ora2pg.conf
/usr/bin/diff ../ora2pg.conf.org ora2pg.conf
#time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
#time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
getdll(){
for sc in ${SCHEMA_LISTS}; do
echo start collect table DLL $sc
cd $sc
grep ORACLE_DSN ora2pg.conf | grep -v '#ORACLE_DSN'
cdir=`pwd`
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql -e 'TABLE[smp_*]'
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cp -p ora2pg_TABLE.sql ora2pg_TABLE_org.sql
cat ora2pg_TABLE_org.sql | convert > ora2pg_TABLE.sql
cd ..
done
}
convert(){
sed -e "s/) ;/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_TBL\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_IDX\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_TBL\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_IDX\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\));/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\));/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\)) TABLESPACE \([^;]*\);/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/ALTER TABLE csb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE csb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/ALTER TABLE ysb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE ysb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/verbose numeric/x_verbose numeric/"
}
report(){
for sc in ${SCHEMA_LISTS}; do
echo start create report $sc
cd $sc
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cd ..
done
}
putdll(){
echo "インポートしたDLLでテーブルを作成します。"
echo "一時的にスーパユーザになる必要があります。dbeaver等で${STAGE}userをスーパユーザに設定してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
echo start create $sc
cd $sc
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
copy(){
for sc in ${SCHEMA_LISTS}; do
echo start copy data to $sc
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
cd ..
done
}
copy_by_table(){
sc=arcsdbss
for ta in $(cat -); do
echo start copy data to $ta
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
cd ..
done
}
clean(){
for sc in ${SCHEMA_LISTS}; do
rm -rf ${sc}
done
echo "ディクトリを削除しました（${SCHEMA_LISTS})"
}
diff(){
/usr/bin/diff -l /etc/ora2pg/ora2pg.conf.dist ora2pg.conf.org
}
save(){
tar cvzf arcsdbms.tar ${SCHEMA_LISTS}
}
#----------------------------------------
# 汎用関数
#----------------------------------------
yes_no(){
echo -n "よろしいですか？ [y/N]:"
if tty -s; then
read ANS
else
echo TTY端末からの起動で無いため、強制的に「y」を入力します。
ANS=Yes
fi
case $ANS in
[Yy]* )
#echo "Yes"
return 0
;;
* )
#echo "No"
return 1
;;
esac
}
main $@
select current_database();select current_user; 現在のユーザ
select * from pg_user; ユーザ一覧
psql -h 10.0.18.101 -d arcsdbms -U arcsdbcb -f ora2pg_TABLE.sql
psql -h 10.0.18.101 -d arcsdbms -U arcsdbcb -f ora2pg_PARTITION.sql
$ time ora2pg -c ora2pg.conf -t TABLE -b ~/arcsdbcb/ -o ora2pg_TABLE.sql
$ time ora2pg -c ora2pg.conf -t PARTITION -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_PARTITION.sql
$ time ora2pg -c ora2pg.conf -t TABLESPACE -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_TABLESPACE.sql
$ time ora2pg -c ora2pg.conf -t PROCEDURE -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_PROCEDURE.sql
$ time ora2pg -c ora2pg.conf -t INSERT -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_INSERT.sql
$ time ora2pg -c ora2pg.conf -t GRANT -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_GRANT.sql
$ time ora2pg -c ora2pg.conf -t VIEW -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_VIEW.sql
$ time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -o ora2pg_COPY.sql
※ 表領域設定の為、一時的にスーパユーザにする。
付与する ALTER ROLE ARCSDBCB WITH SUPERUSER;
剥奪する ALTER ROLE ARCSDBCB WITH NOSUPERUSER;
psql -h 192.168.210.201 -p 5432 -U arcsdbcb -d ARCSDBMS -f ora2pg_TABLE.sql
psql -h 192.168.210.201 -p 5432 -U arcsdbcb -d ARCSDBMS -f ora2pg_PARTITION.sql
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -a 'table[ccd002]' -e 'PARTITION[sys_*]'
※ 正規表現＊が動作しない。
※ for パスワード自動入力
$ vi ~/.pgpass <<__EOF__
192.168.210.201:5432:postgres:postgres:Noxxxxx77%
192.168.210.201:5432:ARCSDBMS:admin:caxxx00
__EOF__
$ chmod 0600 ~/.pgpass
SCHEMA_LISTS="ARCSDBCB ARCSDBCO ARCSDBCS ARCSDBPA ARCSDBSS ARCSDBTM ARCSDBTP DRCSDBCB TRCSDBCB VRCSDBCB XRCSDBCB SGNGDBMC SGNGDBMS"
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_TABLE.sql
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_PARTITION.sql
cd ..
done
【例】
$ time ora2pg -c ora2pg.conf -t TABLE -N arcsdbss -b ~/arcsdbss/ -o ora2pg_TABLE.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 as USE_TABLESPACE is enabled.
[========================>] 373/373 tables (100.0%) end of scanning.
[========================>] 373/373 tables (100.0%) end of table export.
real 13m3.017s
user 0m3.534s
sys 0m0.556s
$ time ora2pg -c ora2pg.conf -t PARTITION -N arscdbss -b ~/arcsdbss/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 as USE_TABLESPACE is enabled.
[========> ] 25667/71283 partitions (36.0%) end of output.
real 14m20.962s
user 0m8.570s
sys 0m1.826s
※ ora2pg.conf 内の
$ time psql -h 192.168.210.201 -p 5432 -U admin -d ARCSDBMS -f ora2pg_TABLE.sql
$ time psql -h 192.168.210.201 -p 5432 -U admin -d ARCSDBMS -f ora2pg_PARTITION.sql
FATAL: ERROR: new row for relation "ccd002_part100_subpart1" violates partition constraint
DETAIL: Failing row contains (201512, 101, 1360, 0002, 0003, 10001000003000000100, 売上集計管理サービス, 1, 5400, 5400, 400, 201512, 20160106125511, 0, null, null, null, 7716610000552, 002001).
CONTEXT: COPY ccd002_part100_subpart1, line 1: "201512 101 1360 0002 0003 10001000003000000100 売上集計管理サービス 1 5400 5400 400 201512..."
DBI::db=HASH(0x3b50558)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /home/takahab/perl5/lib/perl5/Ora2Pg.pm line 14637.
Aborting export...
→メインテーブルコピー後に、重複してサブパーティションをコピーした為、
（サブパーティションは外す）
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -a 'table[ccp001]' -e 'table[sys_subp*]'
→正規表現が効かない！！
psql -h 192.168.210.201 -p 5432 -U arcsdbcb -d ARCSDBMS
\COPY test_table (id, food_name) FROM '/xxxxx/test_table.csv' WITH CSV HEADER;
--------------------------------------------------------------------------------------------------
【実行結果例】
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -a 'table[ccp020]'
WARNING: Enforcing KEEP_PKEY_NAMES to 1 as USE_TABLESPACE is enabled.
[========================>] 1/1 tables (100.0%) end of scanning.
SET client_encoding TO 'UTF8';
SET synchronous_commit TO off;
[========================>] 7584704/7584704 rows (100.0%) Table CCP020 (2785 recs/sec)
[========================>] 7584704/7584704 total rows (100.0%) - (2723 sec., avg: 2785 recs/sec).
[========================>] 7584704/7584704 rows (100.0%) on total estimated data (2723 sec., avg: 2785 recs/sec)
real 46m11.963s
user 16m58.208s
sys 0m10.782s
---------------------------------------------------------------------------------------------------
スキーマ定義のexport
$ cd migration/test_project
$ ./export_schema
$ psql -h 192.168.210.201 -U postgres
----------------------------------------------------------------------------------------------------
【移行オブジェクト】
| 移行対象オブジェクト | 指定するキーワード | 説明 | ||||||
|---|---|---|---|---|---|---|---|---|
| テーブル | TABLE | Export tables, constraints, indexes, ... | ||||||
| パッケージ | PACKAGE | Export packages | ||||||
| データ | INSERT | Export data from table as INSERT statement | $ time ora2pg -c ora2pg_cb.conf -t PARTITION -N arcsdbcb -b ~/Database/arcsdbcb/ -o ora2pg_PARTITION.sql | [2024-01-10 20:12:17] [========> ] 3546/9850 partitions (36.0%) end of output. | real 6m49.824s | user 0m0.601s | sys 0m0.246s | |
| データ | COPY | Export data from table as COPY statement | ||||||
| ビュー | VIEW | Export views | ||||||
| 権限 | GRANT | Export grants | ||||||
| シーケンス | SEQUENCE | Export sequences | ||||||
| トリガ | TRIGGER | Export triggers | ||||||
| ユーザ関数 | FUNCTION | Export functions | ||||||
| プロシージャ | PROCEDURE | Export procedures | ||||||
| 表領域 | TABLESPACE | Export tablespace (PostgreSQL >= 8 only) | ||||||
| データ型 | TYPE | Export user defined Oracle types | ||||||
| パーティション | PARTITION | Export range or list partition (PostgreSQL >= v8.4) | ||||||
| 外部表 | FDW | Export table as foreign data wrapper tables | ||||||
| マテリアライズドビュー | MVIEW | Export materialized view as snapshot refresh view | ||||||
| SQL文 | QUERY | Convert Oracle SQL queries from a file. | ||||||
| XMLテンプレート | KETTLE | Generate XML ktr template files to be used by Kettle. | ||||||
| シノニム | SYNONYM | (説明なし) | ||||||
| データベースリンク | DBLINK | (説明なし) |
【データの変換】
| 属性 | Oracleのデータ型 | ora2pgでの変換後のデータ型 | AWS-SCTでの変換後のデータ型 | 備考 |
|---|---|---|---|---|
| 文字列 | CHAR(n) | char(n) | character(n) | PostgreSQLのnは文字数 |
| NCHAR(n) | char(n) | character(n) | ||
| VARCHAR2(n) | varchar(n) | character varying(n) | PostgreSQLのnは文字数 | |
| NVARCHAR2(n) | varchar(n) | character varying(n) | ||
| CLOB | text | text | ||
| LONG | text | text | ||
| 数値 | NUMBER | bigint | double | |
| NUMBER(n) | smallint | numeric(n,0) | n=1～4 | |
| NUMBER(n) | integer | numeric(n,0) | n=5～9 | |
| NUMBER(n) | bigint | numeric(n,0) | n=10～19 | |
| NUMBER(n) | decimal | numeric(n,0) | n=20～38 | |
| NUMBER(n,m) | real | numeric(n,m) | n=2～6 | |
| NUMBER(n,m) | double precision | numeric(n,m) | n=7～15 | |
| NUMBER(n,m) | decimal | numeric(n,m) | n=16～38 | |
| 日付 | DATE | timestamp | timestamp | |
| TIMESTAMP | timestamp | timestamp | ||
| バイナリ | BLOB | bytea | bytea | |
| RAW | bytea | bytea | ||
| その他 | ROWID | oid | character(255) |
# 定義
#----------------------------------------
STAGE=test_
POSTGRES_HOST=192.168.210.201
SUPERUSER_NAME=${STAGE}admin
DATABASE_NAME=${STAGE}arcsdbms
#SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs arcsdbco arcsdbtm arcsdbtp vrcsdbcb xrcsdbcb"
#SCHEMA_LISTS="xrcsdbcb vrcsdbcb"
#SCHEMA_LISTS="arcsdbcb"
SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs vrcsdbcb xrcsdbcb"
TABLE_LISTS="ssa000 ssa001"
#----------------------------------------
# Main ルーチン
#----------------------------------------
main(){
case $1 in
new )
new
;;
drop )
drop
;;
all )
clean
init
getdll
report
;;
init )
init
;;
getdll )
getdll
;;
putdll )
putdll
;;
report )
report
;;
copy )
copy
;;
copytable )
if [ -p /dev/stdin ]; then
cat -
else
echo $@
fi | copy_by_table
;;
save )
save
;;
clean )
clean
;;
diff )
diff
;;
show )
show
;;
showtable )
showtable
;;
psql )
psql
;;
*)
echo "usage: ./run clean # スキーマワークディレクトリの削除"
echo "usage: ./run drop # 表領域＆データベース＆スキーマ＆ユーザの削除"
echo "usage: ./run new # 表領域＆データベース＆スキーマ＆ユーザの作成"
echo "usage: ./run init # ora2pg.conf ファイルの初期化とスキーマワークディレクトリの作成"
echo "usage: ./run getdll # OracleDBからDLL取得"
echo "usage: ./run putdll # PostgresDBにDLLi登録"
echo "usage: ./run report # 変換レポートの作成"
echo "usage: ./run copy # oracleDBらデータをexportしてposgreDBにimport"
echo "usage: ./run all (clean/init/getdll/report) # /clean/init/getdll/reportを一括実行"
;;
esac
}
new(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
/* --------------------------------------------------+
| create ROLE |
+-------------------------------------------------- */
create user ${SUPERUSER_NAME} with password 'casio00' superuser;
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data
--\! sudo chown postgres:postgres /data
--\! sudo chmod 700 /data
--\! sudo mkdir -p /data/${DATABASE_NAME}_dat
--\! sudo chown postgres:postgres /data/${DATABASE_NAME}_dat
--\! sudo chmod 700 /data/${DATABASE_NAME}_dat
create tablespace ${DATABASE_NAME}_dat owner ${SUPERUSER_NAME} location '/data/${DATABASE_NAME}_dat';
/* --------------------------------------------------+
| create DATABASE |
+-------------------------------------------------- */
create database ${DATABASE_NAME} owner ${SUPERUSER_NAME} tablespace ${DATABASE_NAME}_dat ;
__EOF__
echo DONE CREATE DATABASE database=${DATABASE_NAME} owner=${SUPERUSER_NAME} tablespace=${DATABASE_NAME}_dat ;
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U ${SUPERUSER_NAME} -d ${DATABASE_NAME} << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data/${STAGE}${sc}_tbl
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_tbl
--\! sudo chmod 700 /data/${STAGE}${sc}_tbl
--\! sudo mkdir -p /data/${STAGE}${sc}_idx
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_idx
--\! sudo chmod 700 /data/${STAGE}${sc}_idx
create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_tbl';
create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_idx';
/* --------------------------------------------------+
| create ROLE & SCHEMA |
+-------------------------------------------------- */
create user ${STAGE}${sc} with password 'casio00' ;
create schema ${sc} authorization ${STAGE}${sc};
__EOF__
echo "DONE USER=${STAGE}${sc}"
echo create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_tbl";
echo create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_idx";
done
show_postgres
}
drop(){
echo "データベース（${DATABASE_NAME})を全て削除します。"
echo "他（dbeaver等)で接続している場合は、切断してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
drop schema ${sc};
drop user ${STAGE}${sc};
drop tablespace ${STAGE}${sc}_tbl;
drop tablespace ${STAGE}${sc}_idx;
__EOF__
done
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c postgres postgres
drop database ${DATABASE_NAME};
drop tablespace ${DATABASE_NAME}_dat;
drop user ${SUPERUSER_NAME};
__EOF__
}
show_postgres(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\echo データベース一覧
select * from pg_database;
\echo 表領域一覧
select * from pg_tablespace;
\echo ユーザ一覧
select * from pg_user;
__EOF__
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
showtable(){
sc=arcsdbss
echo "psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c '\dt'"
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c "\dt"
}
psql(){
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
show(){
sc=arcsdbcb
cd $sc
cdir=`pwd`
echo POSTGRES_HOST=192.168.210.201
echo SUPERUSER_NAME=${STAGE}admin
echo DATABASE_NAME=${STAGE}arcsdbms
echo time ora2pg -c ora2pg.conf -t TABLE -N ${STAGE}${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
}
ALL(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
echo time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
echo time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
init(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${STAGE}${sc}/" | sed -e "s/ARCS_PG_DBNAME/${DATABASE_NAME}/"> ora2pg.conf
/usr/bin/diff ../ora2pg.conf.org ora2pg.conf
#time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
#time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
getdll(){
for sc in ${SCHEMA_LISTS}; do
echo start collect table DLL $sc
cd $sc
grep ORACLE_DSN ora2pg.conf | grep -v '#ORACLE_DSN'
cdir=`pwd`
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql -e 'TABLE[smp_*]'
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cp -p ora2pg_TABLE.sql ora2pg_TABLE_org.sql
cat ora2pg_TABLE_org.sql | convert > ora2pg_TABLE.sql
cd ..
done
}
convert(){
sed -e "s/) ;/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_TBL\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_IDX\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_TBL\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_IDX\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\));/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\));/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\)) TABLESPACE \([^;]*\);/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/ALTER TABLE csb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE csb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/ALTER TABLE ysb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE ysb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/verbose numeric/x_verbose numeric/"
}
report(){
for sc in ${SCHEMA_LISTS}; do
echo start create report $sc
cd $sc
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cd ..
done
}
putdll(){
echo "インポートしたDLLでテーブルを作成します。"
echo "一時的にスーパユーザになる必要があります。dbeaver等で${STAGE}userをスーパユーザに設定してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
echo start create $sc
cd $sc
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
copy(){
for sc in ${SCHEMA_LISTS}; do
echo start copy data to $sc
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
cd ..
done
}
copy_by_table(){
sc=arcsdbss
for ta in $(cat -); do
echo start copy data to $ta
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
cd ..
done
}
clean(){
for sc in ${SCHEMA_LISTS}; do
rm -rf ${sc}
done
echo "ディクトリを削除しました（${SCHEMA_LISTS})"
}
diff(){
/usr/bin/diff -l /etc/ora2pg/ora2pg.conf.dist ora2pg.conf.org
}
save(){
tar cvzf arcsdbms.tar ${SCHEMA_LISTS}
}
#----------------------------------------
# 汎用関数
#----------------------------------------
yes_no(){
echo -n "よろしいですか？ [y/N]:"
if tty -s; then
read ANS
else
echo TTY端末からの起動で無いため、強制的に「y」を入力します。
ANS=Yes
fi
case $ANS in
[Yy]* )
#echo "Yes"
return 0
;;
* )
#echo "No"
return 1
;;
esac
}
main $@
登録:
投稿 (Atom)