カテゴリーアーカイブ:postgresql

CentOS7.8 setup

コメントを残す
update 
cat /etc/redhat-release
yum update
history 
shopt
vi /etc/profile.d/history.sh

HISTTIMEFORMAT='%F %T '
HISTSIZE=100000
HISTFILESIZE=100000
HISTIGNORE='history:pwd:ls:ls *:ll:w:top:df *'      # 保存しないコマンド
PROMPT_COMMAND='history -a; history -c; history -r' # 履歴のリアルタイム反映
timezone 
ll /etc/localtime
timedatectl
date

timedatectl set-timezone Asia/Tokyo
apache 
systemctl status apache
systemctl status httpd

yum install epel-release
yum install https://repo.ius.io/ius-release-el7.rpm

vi /etc/yum.repos.d/epel.repo
vi /etc/yum.repos.d/ius.repo
enabled=0

yum install centos-logos.noarch mailcap openldap-devel expat-devel libdb-devel openssl perl
yum --enablerepo=epel install nghttp2 brotli
yum install --disablerepo=base,extras,updates --enablerepo=ius httpd httpd-devel mod_ssl
yum list | grep httpd
systemctl start httpd
systemctl enable httpd.service

httpd -h
httpd -S
httpd -t -D DUMP_VHOSTS
httpd -t -D DUMP_RUN_CFG
httpd -v
httpd -V
httpd -l
httpd -t -D DUMP_MODULES
httpd -M
httpd -t -D DUMP_INCLUDES

ll /var/log/httpd/
less /etc/logrotate.conf
less /etc/logrotate.d/httpd

mkdir /var/www/html/php
chmod 777 /var/www/html/php/
ps aux | grep httpd
systemctl reload httpd
systemctl status httpd
systemctl restart httpd
systemctl status httpd
php5.4 
yum info php
rpm -q --changelog php | grep CVE | less

yum install php
php -v
php -r 'phpinfo();'  | less
php modules 
php -m | less
yum list --enablerepo=epel | grep php | less
yum install php-bcmath
php -m | less

vi bcmath.php
php bcmath.php
vi gettext.php
php gettext.php
vi gettext.php
php /var/www/html/php/phpinfo.php | less
yum list --enablerepo=epel | grep php | less
yum install php-pgsql
vi postgresql.php
php postgresql.php
mkdir php
vi *.php php/.
mv *.php php/.

php -m | less
yum install --enablerepo=epel php-pecl-redis
php -m | less
vi redis.php
php redis.php
php -m | less
postgresql13 
yum install postgresql
yum install centos-release-scl
yum repolist -q
yum repolist all | less
vi /etc/yum.repos.d/CentOS-SCLo-scl.repo
vi /etc/yum.repos.d/CentOS-SCLo-scl-rh.repo
↓
enabled=0

yum repolist all | less
yum repolist -q
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql13-contrib
yum install postgresql13-devel

yum repolist -q
yum install --enablerepo=epel postgresql13-devel
systemctl enable postgresql-13.service
ll /var/lib/pgsql/13/data/
PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-13/bin/postgresql-13-setup initdb
ll /var/lib/pgsql/13/data/
su - postgres

vi /var/lib/pgsql/.pgsql_profile
↓
PATH=/usr/pgsql-13/bin:$PATH
export PATH

which psql
psql -v
psql --version
/usr/pgsql-13/bin/psql --version
ls /bin/psql
ll /bin/psql
ll /etc/alternatives/pgsql-psql
pg_ctl
source .bash_profile

psql -l
psql -U postgres
createuser --login --pwprompt centos
createdb --owner=centos centos
systemctl start postgresql-13
ps aux | grep master
psql -U postgres
psql -l
redis6.2 
yum list | grep redis
yum list --enablerepo=epel | grep redis
rpm -ivh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm
vi /etc/yum.repos.d/remi.repo
vi /etc/yum.repos.d/remi-safe.repo
↓
enabled=0

yum info --enablerepo=epel,remi redis
yum install --enablerepo=epel,remi redis
less /usr/lib/systemd/system/redis.service
systemctl status redis
systemctl enable redis
systemctl status redis
ps aux | grep redis
systemctl start redis
ps aux | grep redis
redis-cli ping
systemctl status redis

less /etc/logrotate.d/redis
pure-ftpd1.0.43 
yum list | grep pure | less
yum list --enablerepo=epel | grep pure | less
cat /etc/redhat-release
yum update centos-release
→アップデート無し

yum repolist all | less
yum info --enablerepo=C7.0.1406-base pure-ftpd
yum search --enablerepo=C7.0.1406-base ftp
yum search ftp | less
yum install pure-ftpd
yum repolist all | less
yum list --enablerepo=epel | grep pure

cd /usr/local/src/
yum install wget
wget https://download.pureftpd.org/pub/pure-ftpd/releases/obsolete/pure-ftpd-1.0.43.tar.gz
tar xvzf pure-ftpd-1.0.43.tar.gz
cd pure-ftpd-1.0.43
yum install gcc
yum install openssl-devel
./configure --with-everything --with-paranoidmsg --without-capabilities --with-virtualchroot --with-uploadscript --with-tls
make
make install

https://gist.githubusercontent.com/zrong/c9588a17ccb15e75fdf7/raw/a24c563bfe0cdc238952ce24f560a8de42631c0f/pure-ftpd.sh

systemctl status pure-ftpd
cp configuration-file/pure-ftpd.conf /etc/.
cp configuration-file/pure-config.py /usr/local/bin/.
vi /etc/init.d/pure-ftpd
chmod 755 /etc/init.d/pure-ftpd
chmod 755 /usr/local/bin/pure-config.py
/etc/init.d/pure-ftpd start
ps aux | grep pure
yum install ftp
ftp localhost
java1.8.0 
yum list | grep java | less
yum install java-1.8.0-openjdk
java -version
node.js14 
yum list --enablerepo=epel | grep node | less
curl -fsSL https://rpm.nodesource.com/setup_14.x | bash -
vi /etc/yum.repos.d/nodesource-el7.repo
yum list | grep node
yum list | grep node | less
yum install nodejs
node -v
npm install forever -g
vi index.js
node index.js
forever start index.js
ll
vi node.js
mkdir node.js
mv index.js node.js/.

postgresql case

コメントを残す

–update t_datanum set reg_dt = now() where reg_dt < now() – interval ‘1 hour’;
update
t_datanum
set
— reg_dt = (case when reg_dt < now() – interval ‘1 hour’ then date_trunc(‘hour’, now()) else reg_dt end),
reg_dt = (case when reg_dt < now() – interval ‘1 hour’ then now() else reg_dt end),
count = (case when reg_dt < now() – interval ‘1 hour’ then 0 else count + 1 end)
;

–update t_datanum set count = count + 1 where reg_dt >= now() – interval ‘1 hour’;
select * from t_datanum ;

file or database

コメントを残す

＃ファイルとDatabaseの書き込みの差

 

#filesystem read→write

10000ファイル

1.24ms

0.00012ms/req

 

#filesystem append

10000ファイル

0.26852 ms
3.0E-5ms/req

 

#psql insert

1000 insert

0.52306 ms
0.00052ms/req

※ ×10レコードでも大差無い

 

#psql insert

10000 insert

4.29751 ms
0.00043ms/req

pgpool 性能

コメントを残す

※localhost指定だと遅くなるのでハマっていた・・。

＃同一サーバ localhost pgpool経由 select

1000req

0.0401ms/req

 

＃同一サーバ自IPアドレス指定 pgpool経由 select

1000req

0.0002ms/req

 

＃同一サーバ localhost psql直 select

1000req

0.00012ms/req

 

＃同一サーバ自IPアドレス指定 psql直 select

10000req

0.00012ms/req

 

＃インターネット経由 pgpool経由 select

1000req

0.00276ms/req

 

＃インターネット経由 psql直 select

1000req

0.00237ms/req

pgbench

コメントを残す

※事前にテーブル作成

 

pgbench -i

※DB名省略

 

$ pgbench -p 9999 -S
Password:
starting vacuum…end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10

 

$ pgbench -p 5432 -S
Password:
starting vacuum…end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 1378.549766 (including connections establishing)
tps = 3500.175009 (excluding connections establishing)

postgresql pgpool

コメントを残す

vsftpd posgres pam_pgsql

コメントを残す

postreqsql のインストール

yum install postgresql-server

# pam_pgsql をbuildするため

yum install postgresql-client

yum install postgresql-contrib

yum install postgresql-devel

 

pam_pgsqlのインストール

cd /usr/local/src/

wget https://github.com/pam-pgsql/pam-pgsql/archive/release-0.7.3.2.zip

unzip release-0.7.3.2.zip

cd pam-pgsql-release-0.7.3.2/

./autogen.sh

./configure –libdir=/lib64

#pamのlibが入っている

make

make install

# /usr/local/lib/security/pam_pgsql.so

 

vsftpd on postgresql

# su – postgres
-bash-4.1$ createuser vsftpd
新しいロールをスーパーユーザとしますか? (y/n)y
パスワード:

-bash-4.1$ psql -U postgres
ユーザ postgres のパスワード:

postgres=# alter user vsftpd with password ‘vsftpd’;
ALTER ROLE

-bash-4.1$ createdb vsftpd -U vsftpd
パスワード:

-bash-4.1$ psql -U vsftpd
ユーザ vsftpd のパスワード:

vsftpd=# CREATE TABLE “accounts”(
vsftpd(# “i_id” SERIAL,
vsftpd(# “s_username” VARCHAR(30) NOT NULL,
vsftpd(# “s_password” VARCHAR(50),
vsftpd(# “b_expired” BOOLEAN DEFAULT false,
vsftpd(# CONSTRAINT “accounts_s_username_key” UNIQUE(“s_username”)
vsftpd(# );
NOTICE: CREATE TABLEはシリアル列”accounts.i_id”用に暗黙的なシーケンス”accounts_i_id_seq”を作成します。
NOTICE: CREATE TABLE / UNIQUEはテーブル”accounts”に暗黙的なインデックス”accounts_s_username_key”を作成します
CREATE TABLE

vsftpd=# insert into accounts(s_username, s_password) values(‘xxxx’,md5(‘yyyyy’));

 

vsftpd設定

vi /etc/vsftpd/vsftpd.conf

pam_service_name=vsftpd

anonymous_enable=NO

virtual_use_local_privs=YES
guest_enable=YES
local_root=/home/nilesflow/vsftpd_root/$USER
hide_ids=YES
guest_username=nilesflow
user_sub_token=$USER

chroot_local_user=YES

dual_log_enable=YES
vsftpd_log_file=/var/log/vsftpd.log
log_ftp_protocol=YES

 

vsftpd pam設定

vi /etc/pam.d/vsftpd

auth required pam_pgsql.so config_file=/etc/pam_pgsql_vsftpd.conf
account required pam_pgsql.so config_file=/etc/pam_pgsql_vsftpd.conf

 

vsftpd pam postgresql 設定

vi /etc/pam_pgsql_vsftpd.conf

debug
pw_type = md5
connect = hostaddr=127.0.0.1 port=5432 dbname=xxxxx　user=xxxxx password=xxxxx connect_timeout=15
auth_query = select s_password from accounts where s_username = %u
acct_query = select b_expired as acc_expired, 0 as acc_new_pwreq, (s_password ISNULL OR s_password = ”) as user_password from accounts where s_username = %u

 

ディレクトリ作成

mkdir /home/nilesflow/vsftpd_root/nilesflow

 

local_rootでディレクトリ自動作成できないのが。。。

 

ディレクトリ作成できないのは、直接 secutil.c をいじればできるが（以下適当）

retval = str_mkdir(p_ext_dir_str, 0775);
chmod(str_getbuf(p_ext_dir_str), 0775);
retval = chown(str_getbuf(p_ext_dir_str), 0, 0);