PostgreSQL主从同步配置，切换步骤

10月 25,2018 留下评论 Linux, PostgreSQL

零说明

本文档详细记录如何一步一步在两台Linux服务器上，搭建PostgreSQL数据库主从模式的高可用数据库环境，以及如何手工切换主备库，及其注意事项。

为了实现数据库对于应用层的透明，配合浮动IP来实现，即使数据库执行主备切换，应用层依然通过浮动IP来访问数据库。即：后端数据库执行主备切换前后，应用系统无需做任何更改。

搭建环境选择在CentOS 7+PostgreSQL 9.4.10上。

基本思路和流程大致如下：

1 主库真实IP为172.16.11.148，配置浮动IP为172.16.11.121，主机名为pgprimary，在其上安装并初始化PostgreSQL数据库；

2 备库真实IP为172.16.11.149，配置浮动IP为172.16.11.121，主机名为pgstandby，在其上只安装PostgreSQL数据库软件；

3 通常情况下，浮动IP 172.16.11.121运行在主库上，当主库出故障时，手工执行数据库的主备切换，备库成为新主库，然后新主库上启用浮动IP；

4 修复并重配原主库，使其成为新主库的备库；

一环境

两台数据库服务器的主机名、文件系统、OS level、网络配置分别如下：

1主库：

[root@pgprimary ~]# hostname
pgprimary
[root@pgprimary ~]# df -Th
文件系统                类型      容量  已用  可用 已用% 挂载点
/dev/mapper/centos-root xfs        47G  1.1G   46G    3% /
devtmpfs                devtmpfs  908M     0  908M    0% /dev
tmpfs                   tmpfs     920M     0  920M    0% /dev/shm
tmpfs                   tmpfs     920M  8.8M  911M    1% /run
tmpfs                   tmpfs     920M     0  920M    0% /sys/fs/cgroup
/dev/sda1               xfs      1014M  142M  873M   14% /boot
tmpfs                   tmpfs     184M     0  184M    0% /run/user/0
[root@pgprimary ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 
[root@pgprimary ~]# uname -rm
3.10.0-862.el7.x86_64 x86_64
[root@pgprimary ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.148  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::58b2:50cd:9d7c:8b23  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)
        RX packets 21338  bytes 4481980 (4.2 MiB)
        RX errors 0  dropped 2378  overruns 0  frame 0
        TX packets 270  bytes 31662 (30.9 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgprimary ~]#

2 备库：

[root@pgstandby ~]# hostname
pgstandby
[root@pgstandby ~]# df -Th
文件系统                类型      容量  已用  可用 已用% 挂载点
/dev/mapper/centos-root xfs        47G  1.1G   46G    3% /
devtmpfs                devtmpfs  908M     0  908M    0% /dev
tmpfs                   tmpfs     920M     0  920M    0% /dev/shm
tmpfs                   tmpfs     920M  8.8M  911M    1% /run
tmpfs                   tmpfs     920M     0  920M    0% /sys/fs/cgroup
/dev/sda1               xfs      1014M  142M  873M   14% /boot
tmpfs                   tmpfs     184M     0  184M    0% /run/user/0
[root@pgstandby ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 
[root@pgstandby ~]# uname -rm
3.10.0-862.el7.x86_64 x86_64
[root@pgstandby ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.149  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::486a:f6d4:7976:4c9a  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:5f:53  txqueuelen 1000  (Ethernet)
        RX packets 22927  bytes 4822730 (4.5 MiB)
        RX errors 0  dropped 2560  overruns 0  frame 0
        TX packets 262  bytes 28988 (28.3 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgstandby ~]#

二配置浮动IP

步骤3中的监控并启动浮动IP是可选项配置，不适用于本文的数据库主备切换。数据库主备切换还是推荐手工在主备库上启停浮动IP。

1 主库配置浮动IP：

a 拷贝网络配置文件，并修改：

[root@pgprimary ~]# cp /etc/sysconfig/network-scripts/ifcfg-ens160 /etc/sysconfig/network-scripts/ifcfg-ens160:1
[root@pgprimary ~]#

b 修改DEVICE为ens160:1，添加NM_CONTROLLED设置为no，修改IPADDR为浮动IP地址172.16.11.121，并且去掉网关信息，修改完如下:

[root@pgprimary ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens160:1
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens160
UUID=be70afb7-a578-4dc0-b694-af530586a76e
DEVICE=ens160:1
ONBOOT=yes
IPADDR=172.16.11.121
NETMASK=255.255.255.0
NM_CONTROLLED=no
[root@pgprimary ~]#

c 启用ens160:1网络设备，并测试：

[root@pgprimary ~]# ifup ens160:1
[root@pgprimary ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:80:64:27 brd ff:ff:ff:ff:ff:ff
    inet 172.16.11.148/24 brd 172.16.11.255 scope global noprefixroute ens160
       valid_lft forever preferred_lft forever
    inet 172.16.11.121/24 brd 172.16.11.255 scope global secondary ens160:1
       valid_lft forever preferred_lft forever
    inet6 fe80::58b2:50cd:9d7c:8b23/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@pgprimary ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.148  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::58b2:50cd:9d7c:8b23  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)
        RX packets 172902  bytes 59418679 (56.6 MiB)
        RX errors 0  dropped 15626  overruns 0  frame 0
        TX packets 15516  bytes 1443358 (1.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.121  netmask 255.255.255.0  broadcast 172.16.11.255
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgprimary ~]#

d 客户端去ping floating IP

$ ping 172.16.11.121
PING 172.16.11.121 (172.16.11.121): 56 data bytes
64 bytes from 172.16.11.121: icmp_seq=0 ttl=63 time=0.653 ms
64 bytes from 172.16.11.121: icmp_seq=1 ttl=63 time=0.766 ms
^C
--- 172.16.11.121 ping statistics ---
2 packets transmitted, 2 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 0.653/0.710/0.766/0.057 ms

asher at MacBook-Air-3 in ~
$

2 备库配置浮动IP

具体操作步骤，同上述主库配置浮动IP a,b,c步骤。

测试的时候，需要先在主库停止网络设备ens160:1，然后再在备库启动网络设备ens160:1。

主库停ens160:1

[root@pgprimary ~]# ifdown ens160:1
[root@pgprimary ~]#

备库启动ens160:1

[root@pgstandby ~]# ifup ens160:1
[root@pgstandby ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.149  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::486a:f6d4:7976:4c9a  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:5f:53  txqueuelen 1000  (Ethernet)
        RX packets 182284  bytes 61612908 (58.7 MiB)
        RX errors 0  dropped 16880  overruns 0  frame 0
        TX packets 15365  bytes 1381833 (1.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.121  netmask 255.255.255.0  broadcast 172.16.11.255
        ether 00:50:56:80:5f:53  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgstandby ~]#

至此，完成了对主备库的浮动IP配置。

3 监控并启停浮动IP【可选项】

a 主库监控并启动浮动IP

监控脚本为：

[root@pgprimary ~]# cat monitor_floating_ip.sh 
#!/bin/bash
#This script is used for monitoring the floating IP(172.16.11.121),if it is unreachable,
#then ifup the ens160:1 device on this postgres PRIMARY server.
#Created by 黄伟, whuang@onlyou.com,20181010.

FLOATING_IP=172.16.11.121

c=$(/usr/bin/ping -$FLOATING_IP -c 1|grep Unreachable|wc -l)
if [ $c -gt 0 ]
then 
   /usr/sbin/ifup ens160:1
fi
[root@pgprimary ~]#

crontab定时任务为：

[root@pgprimary ~]# crontab -l
* * * * * /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 10; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 20; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 30; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 40; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 50; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
[root@pgprimary ~]#

说明，脚本用于ping浮动IP，如果ping不通，则在主库上启动浮动IP。定时任务表示每隔10秒调用一次监控浮动IP脚本。

b 备库监控并启动浮动IP

监控脚本为：

[root@pgstandby ~]# cat monitor_floating_ip.sh 
#!/bin/bash
#This script is used for monitoring the floating IP(172.16.11.121),if it is unreachable,and also the PRIMARY
#IP(172.16.11.148) unreachable,then ifup the ens160:1 device on this postgres STANDBY server.
#Created by 黄伟, whuang@onlyou.com,20181010.

PRIMARY_IP=172.16.11.148
FLOATING_IP=172.16.11.121
c1=$(/usr/bin/ping $PRIMARY_IP -c 1|grep Unreachable|wc -l)
c2=$(/usr/bin/ping $FLOATING_IP -c 1|grep Unreachable|wc -l)
c3=$(/usr/sbin/ip addr|grep ens160:1)
if [ $c1 -gt 0 -a $c2 -gt 0 ]
then
   /usr/sbin/ifup ens160:1
elif [ $c1 -eq 0 -a $c2 -eq 0 -a "$c3" ]
then
   /usr/sbin/ifdown ens160:1
fi
[root@pgstandby ~]#

crontab定时任务为：

[root@pgstandby ~]# crontab -l
* * * * * /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 10; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 20; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 30; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 40; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
* * * * * sleep 50; /root/monitor_floating_ip.sh>>/root/monitor_floating_ip.log
[root@pgstandby ~]#

说明，脚本用于ping浮动IP，如果ping不通，且同时ping不通主库实际IP地址，那么意味着主库服务器已经宕机或者整个网络异常，则在备库上启动浮动IP。如果ping主、备库IP正常，且备库上浮动IP正常，则意味着主库服务器恢复正常或网络恢复正常，则在备库上停止浮动IP，把浮动IP交给主库托管。定时任务表示每隔10秒调用一次监控浮动IP脚本。

三主库安装、配置数据库

1 创建用户、路径

[root@pgprimary ~]# groupadd postgres
[root@pgprimary ~]# useradd -g postgres postgres
[root@pgprimary ~]# passwd postgres
更改用户 postgres 的密码 。
新的 密码：
无效的密码： 密码包含用户名在某些地方
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
[root@pgprimary ~]# mkdir -p /postgres/9.4.10
[root@pgprimary ~]# chown -R postgres:postgres /postgres/
[root@pgprimary ~]#

2 操作系统安装软件包gcc,zlib,readline,tar

[root@pgprimary ~]# yum install gcc*
具体略
[root@pgprimary ~]# yum install -y zlib*
具体略
[root@pgprimary ~]# yum install -y readline*
具体略
[root@pgprimary ~]# yum install -y tar*
具体略

3 下载软件，postgres用户执行：

[postgres@pgprimary ~]$ wget https://ftp.postgresql.org/pub/source/v9.4.10/postgresql-9.4.10.tar.gz
--2018-09-30 17:19:51--  https://ftp.postgresql.org/pub/source/v9.4.10/postgresql-9.4.10.tar.gz
正在解析主机 ftp.postgresql.org (ftp.postgresql.org)... 204.145.124.244, 217.196.149.55, 174.143.35.246, ...
正在连接 ftp.postgresql.org (ftp.postgresql.org)|204.145.124.244|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：23333040 (22M) [application/x-gzip]
正在保存至: “postgresql-9.4.10.tar.gz”

100%[====================================================================================================================================================================================>] 23,333,040  1.32MB/s 用时 20s    

2018-09-30 17:20:14 (1.10 MB/s) - 已保存 “postgresql-9.4.10.tar.gz” [23333040/23333040])

[postgres@pgprimary ~]$ ll
总用量 22788
-rw-rw-r--. 1 postgres postgres 23333040 10月 25 2016 postgresql-9.4.10.tar.gz
[postgres@pgprimary ~]$

4 解压软件，configure,postgres用户执行：

[postgres@pgprimary ~]$ tar -zxvf postgresql-9.4.10.tar.gz 
...
postgresql-9.4.10/configure.in
postgresql-9.4.10/INSTALL
[postgres@pgprimary ~]$ cd postgresql-9.4.10 && ll
总用量 620
-rw-r--r--.  1 postgres postgres    385 10月 25 2016 aclocal.m4
drwxrwxr-x.  2 postgres postgres   4096 10月 25 2016 config
-rwxr-xr-x.  1 postgres postgres 448579 10月 25 2016 configure
-rw-r--r--.  1 postgres postgres  69119 10月 25 2016 configure.in
drwxrwxr-x. 59 postgres postgres   4096 10月 25 2016 contrib
-rw-r--r--.  1 postgres postgres   1192 10月 25 2016 COPYRIGHT
drwxrwxr-x.  3 postgres postgres    107 10月 25 2016 doc
-rw-r--r--.  1 postgres postgres   3620 10月 25 2016 GNUmakefile.in
-rw-r--r--.  1 postgres postgres    283 10月 25 2016 HISTORY
-rw-r--r--.  1 postgres postgres  75420 10月 25 2016 INSTALL
-rw-r--r--.  1 postgres postgres   1489 10月 25 2016 Makefile
-rw-r--r--.  1 postgres postgres   1209 10月 25 2016 README
drwxrwxr-x. 15 postgres postgres   4096 10月 25 2016 src
[postgres@pgprimary postgresql-9.4.10]$ ./configure --prefix=/postgres/9.4.10/
...
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[postgres@pgprimary postgresql-9.4.10]$

5 gmake world,postgres用户执行：

[postgres@pgprimary postgresql-9.4.10]$ gmake world
..
gmake[1]: 离开目录“/home/postgres/postgresql-9.4.10/contrib”
PostgreSQL, contrib, and documentation successfully made. Ready to install.
[postgres@pgprimary postgresql-9.4.10]$

6 gmake install-world,postgres用户执行：

[postgres@pgprimary postgresql-9.4.10]$ gmake install-world
...
PostgreSQL, contrib, and documentation installation complete.
[postgres@pgprimary postgresql-9.4.10]$

7 初始化数据库，postgres用户执行：

[postgres@pgprimary ~]$ /postgres/9.4.10/bin/initdb -D /postgres/9.4.10/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory /postgres/9.4.10/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /postgres/9.4.10/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /postgres/9.4.10/bin/postgres -D /postgres/9.4.10/data
or
    /postgres/9.4.10/bin/pg_ctl -D /postgres/9.4.10/data -l logfile start

[postgres@pgprimary ~]$

8 修改postgres用户环境变量

[postgres@pgprimary ~]$ cat ~/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/postgres/9.4.10/bin
PGDATA=/postgres/9.4.10/data
export PATH
export PGDATA
[postgres@pgprimary ~]$

postgres用户的/home/.bash_profile修改之后的信息如下：

9 修改数据库参数

修改/postgres/9.4.10/data/postgresql.conf文件，使其：

listen_addresses = '*'
port = 5432

其它参数暂时不动。
修改/postgres/9.4.10/data/pg_hba.conf，在其中添加下述；

host    all             all             0.0.0.0/0               md5

其它保持不变。

10 启动数据库

[postgres@pgprimary ~]$ pg_ctl start
server starting
[postgres@pgprimary ~]$ LOG:  database system was shut down at 2018-10-10 09:32:14 CST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[postgres@pgprimary ~]$

11 测试数据库

a 备库停用浮动IP

[root@pgstandby ~]# ifdown ens160:1
[root@pgstandby ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.149  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::486a:f6d4:7976:4c9a  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:5f:53  txqueuelen 1000  (Ethernet)
        RX packets 14738337  bytes 3468772296 (3.2 GiB)
        RX errors 0  dropped 1833331  overruns 0  frame 0
        TX packets 149019  bytes 13182295 (12.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 1  bytes 104 (104.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 1  bytes 104 (104.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgstandby ~]#

b 主库启用浮动IP

[root@pgprimary ~]# ifup  ens160:1
[root@pgprimary ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.148  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::58b2:50cd:9d7c:8b23  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)
        RX packets 14551224  bytes 3431186752 (3.1 GiB)
        RX errors 0  dropped 1817255  overruns 0  frame 0
        TX packets 108201  bytes 9678459 (9.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.121  netmask 255.255.255.0  broadcast 172.16.11.255
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 12  bytes 1089 (1.0 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 12  bytes 1089 (1.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgprimary ~]#

c 连接主库原来IP

MacBook-Air-3:~ postgres$ psql -h 172.16.11.148 -p 5432 -d postgres -U postgres
Password for user postgres: 
psql (9.5.2, server 9.4.10)
Type "help" for help.

postgres=# \c
psql (9.5.2, server 9.4.10)
You are now connected to database "postgres" as user "postgres".
postgres=#

d 连接主库浮动IP

MacBook-Air-3:~ postgres$ psql -h 172.16.11.121 -p 5432 -d postgres -U postgres
Password for user postgres: 
psql (9.5.2, server 9.4.10)
Type "help" for help.

postgres=# \c
psql (9.5.2, server 9.4.10)
You are now connected to database "postgres" as user "postgres".
postgres=#

从上，可以看到，当主库启用浮动IP之后，客户端既可以通过原IP访问主库，也可以通过浮动IP访问主库。

四备库安装数据库软件

具体步骤参照步骤三的1-6和步骤8。其它不需要做更多的配置，需要注意的是，该备库只需要安装数据库软件即可，并不需要初始化数据库。

五配置主备库同步

1 主库添加同步用户

[root@pgprimary ~]# su - postgres
上一次登录：三 10月 10 10:08:01 CST 2018pts/0 上
[postgres@pgprimary ~]$ psql
psql (9.4.10)
Type "help" for help.

postgres=# CREATE ROLE replica login replication encrypted password 'replica';
CREATE ROLE
postgres=# \q
[postgres@pgprimary ~]$

2 主库修改配置文件pg_hba.conf和postgresql.conf

修改/postgres/9.4.10/data/pg_hba.conf，添加下述行：

host    replication     replica     172.16.11.149/32           md5

修改/postgres/9.4.10/data/postgresql.conf,修改下述行：

wal_level = hot_standby  # 这个是设置主为wal的主机
max_wal_senders = 4 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，主库的该参数要根据实际情况设置。从库的max_connections必须要大于主库的该参数

3 主库重启

[postgres@pgprimary ~]$ pg_ctl restart -m fast
waiting for server to shut down...LOG:  received fast shutdown request
.LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
FATAL:  terminating connection due to administrator command
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped
server starting
[postgres@pgprimary ~]$ LOG:  database system was shut down at 2018-10-10 16:35:19 CST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[postgres@pgprimary ~]$

4 备库，执行主库的基础备份

[root@pgstandby ~]# su - postgres
上一次登录：三 10月 10 09:43:15 CST 2018pts/0 上
[postgres@pgstandby ~]$ ll /postgres/9.4.10/
总用量 16
drwxrwxr-x. 2 postgres postgres 4096 9月  30 17:30 bin
drwxrwxr-x. 6 postgres postgres 4096 9月  30 17:30 include
drwxrwxr-x. 4 postgres postgres 4096 9月  30 17:30 lib
drwxrwxr-x. 8 postgres postgres 4096 9月  30 17:30 share
[postgres@pgstandby ~]$ which pg_basebackup 
/postgres/9.4.10/bin/pg_basebackup
[postgres@pgstandby ~]$ pg_basebackup -F p --progress -D /postgres/9.4.10/data -h 172.16.11.121 -p 5432 -U replica --password -X stream
Password: 
21122/21122 kB (100%), 1/1 tablespace
[postgres@pgstandby ~]$ ll /postgres/9.4.10/
总用量 20
drwxrwxr-x.  2 postgres postgres 4096 9月  30 17:30 bin
drwx------. 18 postgres postgres 4096 10月 10 16:38 data
drwxrwxr-x.  6 postgres postgres 4096 9月  30 17:30 include
drwxrwxr-x.  4 postgres postgres 4096 9月  30 17:30 lib
drwxrwxr-x.  8 postgres postgres 4096 9月  30 17:30 share
[postgres@pgstandby ~]$ du -sh /postgres/9.4.10/data/
37M     /postgres/9.4.10/data/
[postgres@pgstandby ~]$

可以看到，备库已经生成/postgres/9.4.10/data/。

5 备库，配置recovery.conf文件

从安装文件里cp过来。然后编辑，更改为下面3个参数，其它保持不变。

[postgres@pgstandby ~]$ cp /home/postgres/postgresql-9.4.10/src/backend/access/transam/recovery.conf.sample /postgres/9.4.10/data/recovery.conf
[postgres@pgstandby ~]$ vi /postgres/9.4.10/data/recovery.conf 
...
standby_mode = on
primary_conninfo = 'host=172.16.11.148 port=5432 user=replica password=replica'         # e.g. 'host=localhost port=5432'
recovery_target_timeline = 'latest'
...

6 备库，修改配置文件postgresql.conf

修改下列4个参数即可：

[postgres@pgstandby ~]$ vi /postgres/9.4.10/data/postgresql.conf 
...
hot_standby = on  ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 1s  # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
...

7 备库，启动

[postgres@pgstandby ~]$ pg_ctl status
pg_ctl: no server running
[postgres@pgstandby ~]$ pg_ctl start
server starting
[postgres@pgstandby ~]$ LOG:  database system was interrupted; last known up at 2018-10-10 16:38:47 CST
LOG:  entering standby mode
LOG:  redo starts at 0/2000028
LOG:  consistent recovery state reached at 0/2000128
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

[postgres@pgstandby ~]$ pg_ctl status
pg_ctl: server is running (PID: 28027)
/postgres/9.4.10/bin/postgres
[postgres@pgstandby ~]$ ps -ef|grep postgres
root     27400 25585  0 16:36 pts/0    00:00:00 su - postgres
postgres 27401 27400  0 16:36 pts/0    00:00:00 -bash
postgres 28027     1  0 16:48 pts/0    00:00:00 /postgres/9.4.10/bin/postgres
postgres 28028 28027  0 16:48 ?        00:00:00 postgres: startup process   recovering 000000010000000000000003
postgres 28029 28027  0 16:48 ?        00:00:00 postgres: checkpointer process  
postgres 28030 28027  0 16:48 ?        00:00:00 postgres: writer process   
postgres 28031 28027  0 16:48 ?        00:00:00 postgres: stats collector process  
postgres 28032 28027  0 16:48 ?        00:00:00 postgres: wal receiver process   streaming 0/3000210
postgres 28073 27401  0 16:49 pts/0    00:00:00 ps -ef
postgres 28074 27401  0 16:49 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgstandby ~]$

从上，可以看到，备库的receiver进程，在接收主库的日志信息。

8 验证主备库同步

主库执行：

[postgres@pgprimary ~]$ ps -ef|grep postgres
root     20280 17302  0 16:30 pts/0    00:00:00 su - postgres
postgres 20281 20280  0 16:30 pts/0    00:00:00 -bash
postgres 20677     1  0 16:35 pts/0    00:00:00 /postgres/9.4.10/bin/postgres
postgres 20679 20677  0 16:35 ?        00:00:00 postgres: checkpointer process  
postgres 20680 20677  0 16:35 ?        00:00:00 postgres: writer process   
postgres 20681 20677  0 16:35 ?        00:00:00 postgres: wal writer process  
postgres 20682 20677  0 16:35 ?        00:00:00 postgres: autovacuum launcher process  
postgres 20683 20677  0 16:35 ?        00:00:00 postgres: stats collector process  
postgres 21656 20677  0 16:48 ?        00:00:00 postgres: wal sender process replica 172.16.11.149(51868) streaming 0/3000210
postgres 21939 20281  0 16:52 pts/0    00:00:00 ps -ef
postgres 21940 20281  0 16:52 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgprimary ~]$ psql
psql (9.4.10)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 21656
usesysid         | 16385
usename          | replica
application_name | walreceiver
client_addr      | 172.16.11.149
client_hostname  | 
client_port      | 51868
backend_start    | 2018-10-10 16:48:44.010195+08
backend_xmin     | 1893
state            | streaming
sent_location    | 0/3000210
write_location   | 0/3000210
flush_location   | 0/3000210
replay_location  | 0/3000210
sync_priority    | 0
sync_state       | async

postgres=#

从上，看到主库有sender进程，发送同步信息给备库。同时，登录主库也可以查看到同步信息。

六主备库切换

这里，模拟主库出现故障，先做一次switchover测试，即先切换主备库角色，主备互换。

然后，重建原主库或者直接将原主库当作新主库的备库。

最后，再执行一次switchover，主备互换，即恢复到之前的最初状态，主库还是原主库，备库还是原备库。

1 执行第一次主备切换

当原主库库服务器或者数据库故障时，我们可以先停止主库，然后启动备库。

a 停止主库

[postgres@pgprimary data]$ pg_ctl stop -m fast -l /home/postgres/pg.log 
waiting for server to shut down.... done
server stopped
[postgres@pgprimary data]$

b 启动备库

 [postgres@pgstandby data]$ pwd
/postgres/9.4.10/data
[postgres@pgstandby data]$ ll 
总用量 72
-rw-------. 1 postgres postgres   206 10月 10 16:38 backup_label.old
drwx------. 5 postgres postgres    41 10月 10 16:38 base
drwx------. 2 postgres postgres  4096 10月 26 15:57 global
drwx------. 2 postgres postgres    18 10月 10 16:38 pg_clog
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_dynshmem
-rw-------. 1 postgres postgres  4605 10月 16 16:35 pg_hba.conf
-rw-------. 1 postgres postgres  1636 10月 10 16:38 pg_ident.conf
drwx------. 4 postgres postgres    39 10月 10 16:38 pg_logical
drwx------. 4 postgres postgres    36 10月 10 16:38 pg_multixact
drwx------. 2 postgres postgres    18 10月 26 15:57 pg_notify
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_replslot
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_serial
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_snapshots
drwx------. 2 postgres postgres     6 10月 26 15:57 pg_stat
drwx------. 2 postgres postgres     6 10月 26 09:29 pg_stat_tmp
drwx------. 2 postgres postgres    18 10月 10 16:38 pg_subtrans
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_tblspc
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_twophase
-rw-------. 1 postgres postgres     4 10月 10 16:38 PG_VERSION
drwx------. 3 postgres postgres  4096 10月 26 15:57 pg_xlog
-rw-------. 1 postgres postgres    88 10月 10 16:38 postgresql.auto.conf
-rw-------. 1 postgres postgres 20810 10月 10 16:45 postgresql.conf
-rw-------. 1 postgres postgres    30 10月 26 15:57 postmaster.opts
-rw-------. 1 postgres postgres    71 10月 26 15:57 postmaster.pid
-rw-r--r--. 1 postgres postgres  5641 10月 16 16:54 recovery.conf
[postgres@pgstandby data]$ pg_ctl promote -l /home/postgres/pg.log 
server promoting
[postgres@pgstandby data]$ ll
总用量 72
-rw-------. 1 postgres postgres   206 10月 10 16:38 backup_label.old
drwx------. 5 postgres postgres    41 10月 10 16:38 base
drwx------. 2 postgres postgres  4096 10月 26 15:57 global
drwx------. 2 postgres postgres    18 10月 10 16:38 pg_clog
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_dynshmem
-rw-------. 1 postgres postgres  4605 10月 16 16:35 pg_hba.conf
-rw-------. 1 postgres postgres  1636 10月 10 16:38 pg_ident.conf
drwx------. 4 postgres postgres    39 10月 10 16:38 pg_logical
drwx------. 4 postgres postgres    36 10月 10 16:38 pg_multixact
drwx------. 2 postgres postgres    18 10月 26 15:57 pg_notify
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_replslot
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_serial
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_snapshots
drwx------. 2 postgres postgres     6 10月 26 15:57 pg_stat
drwx------. 2 postgres postgres    42 10月 26 16:10 pg_stat_tmp
drwx------. 2 postgres postgres    18 10月 10 16:38 pg_subtrans
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_tblspc
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_twophase
-rw-------. 1 postgres postgres     4 10月 10 16:38 PG_VERSION
drwx------. 3 postgres postgres  4096 10月 26 16:10 pg_xlog
-rw-------. 1 postgres postgres    88 10月 10 16:38 postgresql.auto.conf
-rw-------. 1 postgres postgres 20810 10月 10 16:45 postgresql.conf
-rw-------. 1 postgres postgres    30 10月 26 15:57 postmaster.opts
-rw-------. 1 postgres postgres    71 10月 26 15:57 postmaster.pid
-rw-r--r--. 1 postgres postgres  5641 10月 16 16:54 recovery.done
[postgres@pgstandby data]$

这里，需要注意的是，启动备库的命令是pg_ctl promote，之前备库是出在接受主库同步的状态，需要通过这个命令来将备库提升到primary状态。同时，备库切换为主库之后，/postgres/9.4.10/data/recovery.conf文件自动变为/postgres/9.4.10/data/recovery.done。我个人猜想，主要是为了下次重启该数据库时，直接进入“主库模式”，如果还是recovery.conf的话，意味着该库需要执行recovery操作。

c 备库启动浮动IP

先在主库上停止浮动IP

 [root@pgprimary ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.148  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::58b2:50cd:9d7c:8b23  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)
        RX packets 37310308  bytes 8976173370 (8.3 GiB)
        RX errors 0  dropped 4868601  overruns 0  frame 0
        TX packets 1426780  bytes 127364686 (121.4 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.121  netmask 255.255.255.0  broadcast 172.16.11.255
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 134634  bytes 25999628 (24.7 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 134634  bytes 25999628 (24.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

您在 /var/spool/mail/root 中有邮件
[root@pgprimary ~]# ifdown ens160:1
[root@pgprimary ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.148  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::58b2:50cd:9d7c:8b23  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:64:27  txqueuelen 1000  (Ethernet)
        RX packets 37310444  bytes 8976218410 (8.3 GiB)
        RX errors 0  dropped 4868628  overruns 0  frame 0
        TX packets 1426794  bytes 127368186 (121.4 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 134634  bytes 25999628 (24.7 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 134634  bytes 25999628 (24.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgprimary ~]#

然后，备库启动浮动IP

 [root@pgstandby ~]# ifup ens160:1
您在 /var/spool/mail/root 中有邮件
[root@pgstandby ~]# ifconfig 
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.149  netmask 255.255.255.0  broadcast 172.16.11.255
        inet6 fe80::486a:f6d4:7976:4c9a  prefixlen 64  scopeid 0x20
        ether 00:50:56:80:5f:53  txqueuelen 1000  (Ethernet)
        RX packets 37445369  bytes 8975382297 (8.3 GiB)
        RX errors 0  dropped 4884823  overruns 0  frame 0
        TX packets 1509890  bytes 162194380 (154.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.11.121  netmask 255.255.255.0  broadcast 172.16.11.255
        ether 00:50:56:80:5f:53  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 195774  bytes 35184330 (33.5 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 195774  bytes 35184330 (33.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@pgstandby ~]#

d 测试新主库是否可用

MacBook-Air-3:~ postgres$ psql -h 172.16.11.121 -p 5432 -d postgres -U postgres
Password for user postgres: 
psql (9.5.2, server 9.4.10)
Type "help" for help.

postgres=# select * from t;
 id 
----
  1
(1 row)

postgres=# delete from t;
DELETE 1
postgres=# select * from t;
 id 
----
(0 rows)

postgres=#

客户端通过浮动IP访问数据库，并删除t表的数据。说明，新主库可用正常使用。同时，记录下，这里是从新主库里删除的数据，接下来，我们重建新备库之后，看看这条被删除的数据，是否也从备库里查询不到？

2 重建主库

这里，为了快速验证和测试主备互换的流程，只是假设主库出现故障，所以，在这里并不需要重建原主库。真实情况下，如果主库因出现故障而执行切换的话，就需要重建。重建的流程和步骤可以参考初始搭建备库的流程来完成。

当然，这里需要将原主库配置为新备库，同样需要参照搭建备库流程五配置主备库同步中的5，6，7三个步骤。

a 新备库创建配置recovery.conf文件

[postgres@pgprimary ~]$ cp /home/postgres/postgresql-9.4.10/src/backend/access/transam/recovery.conf.sample /postgres/9.4.10/data/recovery.conf
[postgres@pgprimary ~]$ vi /postgres/9.4.10/data/recovery.conf 
...
standby_mode = on
primary_conninfo = 'host=172.16.11.149 port=5432 user=replica password=replica'         # e.g. 'host=localhost port=5432'
recovery_target_timeline = 'latest'
...

需要注意的是，这里主库的信息，需要指向新主库的IP，即原备库的IP 172.16.11.149。

b 新备库修改配置文件postgresql.conf

修改下列4个参数即可：

[postgres@pgprimary ~]$ vi /postgres/9.4.10/data/postgresql.conf 
...
hot_standby = on  ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 1s  # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
...

c 启动新备库

[postgres@pgprimary data]$ pg_ctl start -l /home/postgres/pg.log 
server starting
[postgres@pgprimary data]$ ps -ef|grep postgres
root     13481 13458  0 09:22 pts/1    00:00:00 su - postgres
postgres 13482 13481  0 09:22 pts/1    00:00:00 -bash
postgres 14425     1  1 16:55 pts/1    00:00:00 /postgres/9.4.10/bin/postgres
postgres 14426 14425  0 16:55 ?        00:00:00 postgres: startup process   recovering 000000060000000000000005
postgres 14427 14425  0 16:55 ?        00:00:00 postgres: checkpointer process  
postgres 14428 14425  0 16:55 ?        00:00:00 postgres: writer process   
postgres 14429 14425  0 16:55 ?        00:00:00 postgres: stats collector process  
postgres 14430 14425  1 16:55 ?        00:00:00 postgres: wal receiver process   streaming 0/508DDD0
postgres 14431 13482  0 16:55 pts/1    00:00:00 ps -ef
postgres 14432 13482  0 16:55 pts/1    00:00:00 grep --color=auto postgres
[postgres@pgprimary data]$

启动之后，发现新备库上开始有wal receiver process 。

同时，登录新备库，查看t表的数据有没有同步：

[postgres@pgprimary data]$ psql
psql (9.4.10)
Type "help" for help.

postgres=# select * from t;
 id 
----
(0 rows)

postgres=#

显然，之前从新主库里删除的那条数据，在这个新备库里也看不到了。说明，新备库是OK的了。

3 再次执行主备切换

a 停掉新主库（原备库）

 [postgres@pgstandby data]$ ps -ef|grep postgres
postgres 16230     1  0 15:57 pts/1    00:00:00 /postgres/9.4.10/bin/postgres
postgres 16232 16230  0 15:57 ?        00:00:00 postgres: checkpointer process  
postgres 16233 16230  0 15:57 ?        00:00:00 postgres: writer process   
postgres 16234 16230  0 15:57 ?        00:00:00 postgres: stats collector process  
postgres 16998 16230  0 16:10 ?        00:00:00 postgres: wal writer process  
postgres 16999 16230  0 16:10 ?        00:00:00 postgres: autovacuum launcher process  
postgres 17546 16230  0 16:20 ?        00:00:00 postgres: postgres postgres 172.16.135.72(54471) idle
postgres 19262 16230  0 16:55 ?        00:00:00 postgres: wal sender process replica 172.16.11.148(53298) streaming 0/508DEA8
postgres 19520 29607  0 17:00 pts/1    00:00:00 ps -ef
postgres 19521 29607  0 17:00 pts/1    00:00:00 grep --color=auto postgres
root     29606 29605  0 09:23 pts/1    00:00:00 su - postgres
postgres 29607 29606  0 09:23 pts/1    00:00:00 -bash
[postgres@pgstandby data]$ pg_ctl stop -m fast -l /home/postgres/pg.log 
waiting for server to shut down.... done
server stopped
[postgres@pgstandby data]$ ll
总用量 68
-rw-------. 1 postgres postgres   206 10月 10 16:38 backup_label.old
drwx------. 5 postgres postgres    41 10月 10 16:38 base
drwx------. 2 postgres postgres  4096 10月 26 15:57 global
drwx------. 2 postgres postgres    18 10月 10 16:38 pg_clog
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_dynshmem
-rw-------. 1 postgres postgres  4605 10月 16 16:35 pg_hba.conf
-rw-------. 1 postgres postgres  1636 10月 10 16:38 pg_ident.conf
drwx------. 4 postgres postgres    39 10月 10 16:38 pg_logical
drwx------. 4 postgres postgres    36 10月 10 16:38 pg_multixact
drwx------. 2 postgres postgres    18 10月 26 15:57 pg_notify
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_replslot
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_serial
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_snapshots
drwx------. 2 postgres postgres    63 10月 26 17:00 pg_stat
drwx------. 2 postgres postgres     6 10月 26 17:00 pg_stat_tmp
drwx------. 2 postgres postgres    18 10月 10 16:38 pg_subtrans
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_tblspc
drwx------. 2 postgres postgres     6 10月 10 16:38 pg_twophase
-rw-------. 1 postgres postgres     4 10月 10 16:38 PG_VERSION
drwx------. 3 postgres postgres  4096 10月 26 16:10 pg_xlog
-rw-------. 1 postgres postgres    88 10月 10 16:38 postgresql.auto.conf
-rw-------. 1 postgres postgres 20810 10月 10 16:45 postgresql.conf
-rw-------. 1 postgres postgres    30 10月 26 15:57 postmaster.opts
-rw-r--r--. 1 postgres postgres  5641 10月 16 16:54 recovery.done
[postgres@pgstandby data]$

需要注意的是，这里的新主库停止之后，系统同样自动的将/postgres/9.4.10/data/recovery.conf文件变为/postgres/9.4.10/data/recovery.done。在我们重新启动这个备库的时候，需要把recovery.done改名为recovery.conf,使其在重新启动时，自己担任的是“备库角色”，自动同主库进行同步。

b 启动新备库（原主库）

[postgres@pgprimary data]$ ps -ef|grep postgres
root     13481 13458  0 09:22 pts/1    00:00:00 su - postgres
postgres 13482 13481  0 09:22 pts/1    00:00:00 -bash
postgres 14425     1  0 16:55 pts/1    00:00:00 /postgres/9.4.10/bin/postgres
postgres 14426 14425  0 16:55 ?        00:00:00 postgres: startup process   recovering 000000060000000000000005
postgres 14427 14425  0 16:55 ?        00:00:00 postgres: checkpointer process  
postgres 14428 14425  0 16:55 ?        00:00:00 postgres: writer process   
postgres 14429 14425  0 16:55 ?        00:00:00 postgres: stats collector process  
postgres 14894 13482  0 17:01 pts/1    00:00:00 ps -ef
postgres 14895 13482  0 17:01 pts/1    00:00:00 grep --color=auto postgres
[postgres@pgprimary data]$ pg_ctl promote -l /home/postgres/
server promoting
[postgres@pgprimary data]$ ps -ef|grep postgres
root     13481 13458  0 09:22 pts/1    00:00:00 su - postgres
postgres 13482 13481  0 09:22 pts/1    00:00:00 -bash
postgres 14425     1  0 16:55 pts/1    00:00:00 /postgres/9.4.10/bin/postgres
postgres 14427 14425  0 16:55 ?        00:00:00 postgres: checkpointer process  
postgres 14428 14425  0 16:55 ?        00:00:00 postgres: writer process   
postgres 14429 14425  0 16:55 ?        00:00:00 postgres: stats collector process  
postgres 14916 14425  0 17:01 ?        00:00:00 postgres: wal writer process  
postgres 14917 14425  0 17:01 ?        00:00:00 postgres: autovacuum launcher process  
postgres 14918 13482  0 17:01 pts/1    00:00:00 ps -ef
postgres 14919 13482  0 17:01 pts/1    00:00:00 grep --color=auto postgres
[postgres@pgprimary data]$

此时，这个库又恢复到最初的状态，成为主库。

c 启动原备库

在启动之前，修改recovery.done改名为recovery.conf。

 [postgres@pgstandby data]$ pwd
/postgres/9.4.10/data
[postgres@pgstandby data]$ mv recovery.done recovery.conf
[postgres@pgstandby data]$

启动备库：

 [postgres@pgstandby data]$ pg_ctl start -l /home/postgres/pg.log 
server starting
[postgres@pgstandby data]$ ps -ef|grep postgres
postgres 19871     1  0 17:07 pts/1    00:00:00 /postgres/9.4.10/bin/postgres
postgres 19872 19871  0 17:07 ?        00:00:00 postgres: startup process   recovering 000000070000000000000005
postgres 19873 19871  0 17:07 ?        00:00:00 postgres: checkpointer process  
postgres 19874 19871  0 17:07 ?        00:00:00 postgres: writer process   
postgres 19875 19871  0 17:07 ?        00:00:00 postgres: stats collector process  
postgres 19876 19871  0 17:07 ?        00:00:00 postgres: wal receiver process   streaming 0/508E108
postgres 19881 29607  0 17:07 pts/1    00:00:00 ps -ef
postgres 19882 29607  0 17:07 pts/1    00:00:00 grep --color=auto postgres
root     29606 29605  0 09:23 pts/1    00:00:00 su - postgres
postgres 29607 29606  0 09:23 pts/1    00:00:00 -bash
[postgres@pgstandby data]$

启动之后，看到有wal receiver process。说明，此时备库依然是备库的角色了，并开始同主库进行同步了。

至此，完成了对于主备库的相互切换，并且验证了数据同步，以及注意事项。

七小结

本文档对于在CentOS7+PostgreSQL9.4.10的数据库环境，利用浮动IP来构建一套数据库的主备同步环境，给出详细搭建和配置的流程。通过自动监控启停浮动IP来实现网络的畅通。正常情况下，主库提供读写操作，备库提供只读操作。客户端和应用程序应该通过浮动IP来访问数据库，而不应该使用真实IP访问数据库。

wordpress迁移步骤和遇到的问题及解决

9月 4,2018 留下评论 NGINX

零背景说明：

我的这个站点目前托管在香港，采用的是LAMP，即Linux+Apache+MySQL+PHP架构。将于2018年9月6日到期。在到期之前，研究了一下，如果需要迁移到新主机的话，大致流程和步骤，这里简单总结一下。

即，模拟将现有的oracleonlinux.cn（LAMP架构）迁移至本地机器，并且启用新的blog.com域名（这个域名是虚拟的，通过NGINX模拟反向解析，然后配置本地hosts文件）采用LNMP架构。把之前的Apache换成NGINX，域名也更换一下。

一备份站点文件

即备份主机上的Wordpress及其全部子目录即可，可以采用Linux系统的tar命令来打包。我这里，由于主机服务商暂时不提供SSH远程访问权限，无法连到主机上直接执行备份，但是有Cpanel控制台，我是通过Cpanel控制台上选择的全部备份，然后将备份文件下载到本地：

[root@localhost ~]# ll backup-9.3.2018_08-30-42_oracleon.tar.gz 
-rw-r--r-- 1 nginx nginx 260642919 9月   3 21:06 backup-9.3.2018_08-30-42_oracleon.tar.gz
[root@localhost ~]#

二备份MySQL数据库

同样，通过Cpanel控制台上选择备份数据库，然后下载到本地：

[root@localhost ~]# ll oracleon_oracle.sql.gz 
-rw-r--r--. 1 root root 3155154 9月   3 19:53 oracleon_oracle.sql.gz
[root@localhost ~]#

三本地搭建LNMP环境

Linux：Centos 6.5，本地IP为172.16.11.80:

[root@localhost ~]# cat /etc/redhat-release 
CentOS release 6.5 (Final)
[root@localhost ~]# uname -rm
2.6.32-431.el6.x86_64 x86_64
[root@localhost ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:50:56:99:3D:E7  
          inet addr:172.16.11.80  Bcast:172.16.11.255  Mask:255.255.255.0
          inet6 addr: fe80::250:56ff:fe99:3de7/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:2903715683 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3509490556 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:1030640015360 (959.8 GiB)  TX bytes:3030630388115 (2.7 TiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:414816117 errors:0 dropped:0 overruns:0 frame:0
          TX packets:414816117 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:469426552437 (437.1 GiB)  TX bytes:469426552437 (437.1 GiB)

[root@localhost ~]#

NGINX：直接通过yum安装；

[root@localhost ~]# yum install nginx
已加载插件：fastestmirror
设置安装进程
Loading mirror speeds from cached hostfile
 * base: mirrors.163.com
 * epel: mirrors.ustc.edu.cn
 * extras: mirrors.shu.edu.cn
 * updates: mirrors.zju.edu.cn
解决依赖关系
--> 执行事务检查
---> Package nginx.x86_64 0:1.10.2-1.el6 will be 安装
--> 处理依赖关系 nginx-filesystem = 1.10.2-1.el6，它被软件包 nginx-1.10.2-1.el6.x86_64 需要
--> 处理依赖关系 nginx-all-modules = 1.10.2-1.el6，它被软件包 nginx-1.10.2-1.el6.x86_64 需要
--> 处理依赖关系 nginx-filesystem，它被软件包 nginx-1.10.2-1.el6.x86_64 需要
--> 执行事务检查
---> Package nginx-all-modules.noarch 0:1.10.2-1.el6 will be 安装
--> 处理依赖关系 nginx-mod-stream = 1.10.2-1.el6，它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-mail = 1.10.2-1.el6，它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-xslt-filter = 1.10.2-1.el6，它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-perl = 1.10.2-1.el6，它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-image-filter = 1.10.2-1.el6，它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-geoip = 1.10.2-1.el6，它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
---> Package nginx-filesystem.noarch 0:1.10.2-1.el6 will be 安装
--> 执行事务检查
---> Package nginx-mod-http-geoip.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-http-image-filter.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-http-perl.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-http-xslt-filter.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-mail.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-stream.x86_64 0:1.10.2-1.el6 will be 安装
--> 完成依赖关系计算

依赖关系解决

=================================================================================================================================
 软件包                                       架构                    版本                           仓库                   大小
=================================================================================================================================
正在安装:
 nginx                                        x86_64                  1.10.2-1.el6                   epel                  462 k
为依赖而安装:
 nginx-all-modules                            noarch                  1.10.2-1.el6                   epel                  7.7 k
 nginx-filesystem                             noarch                  1.10.2-1.el6                   epel                  8.5 k
 nginx-mod-http-geoip                         x86_64                  1.10.2-1.el6                   epel                   14 k
 nginx-mod-http-image-filter                  x86_64                  1.10.2-1.el6                   epel                   16 k
 nginx-mod-http-perl                          x86_64                  1.10.2-1.el6                   epel                   26 k
 nginx-mod-http-xslt-filter                   x86_64                  1.10.2-1.el6                   epel                   16 k
 nginx-mod-mail                               x86_64                  1.10.2-1.el6                   epel                   43 k
 nginx-mod-stream                             x86_64                  1.10.2-1.el6                   epel                   36 k

事务概要
=================================================================================================================================
Install       9 Package(s)

总下载量：629 k
Installed size: 1.6 M
确定吗？[y/N]：y
下载软件包：
(1/9): nginx-1.10.2-1.el6.x86_64.rpm                                                                      | 462 kB     00:00     
(2/9): nginx-all-modules-1.10.2-1.el6.noarch.rpm                                                          | 7.7 kB     00:00     
(3/9): nginx-filesystem-1.10.2-1.el6.noarch.rpm                                                           | 8.5 kB     00:00     
(4/9): nginx-mod-http-geoip-1.10.2-1.el6.x86_64.rpm                                                       |  14 kB     00:00     
(5/9): nginx-mod-http-image-filter-1.10.2-1.el6.x86_64.rpm                                                |  16 kB     00:00     
(6/9): nginx-mod-http-perl-1.10.2-1.el6.x86_64.rpm                                                        |  26 kB     00:00     
(7/9): nginx-mod-http-xslt-filter-1.10.2-1.el6.x86_64.rpm                                                 |  16 kB     00:00     
(8/9): nginx-mod-mail-1.10.2-1.el6.x86_64.rpm                                                             |  43 kB     00:00     
(9/9): nginx-mod-stream-1.10.2-1.el6.x86_64.rpm                                                           |  36 kB     00:00     
---------------------------------------------------------------------------------------------------------------------------------
总计                                                                                             719 kB/s | 629 kB     00:00     
运行 rpm_check_debug 
执行事务测试
事务测试成功
执行事务
  正在安装   : nginx-filesystem-1.10.2-1.el6.noarch                                                                          1/9 
  正在安装   : nginx-mod-http-geoip-1.10.2-1.el6.x86_64                                                                      2/9 
  正在安装   : nginx-mod-stream-1.10.2-1.el6.x86_64                                                                          3/9 
  正在安装   : nginx-mod-http-perl-1.10.2-1.el6.x86_64                                                                       4/9 
  正在安装   : nginx-mod-http-image-filter-1.10.2-1.el6.x86_64                                                               5/9 
  正在安装   : nginx-mod-http-xslt-filter-1.10.2-1.el6.x86_64                                                                6/9 
  正在安装   : nginx-1.10.2-1.el6.x86_64                                                                                     7/9 
  正在安装   : nginx-mod-mail-1.10.2-1.el6.x86_64                                                                            8/9 
  正在安装   : nginx-all-modules-1.10.2-1.el6.noarch                                                                         9/9 
  Verifying  : nginx-mod-mail-1.10.2-1.el6.x86_64                                                                            1/9 
  Verifying  : nginx-mod-http-geoip-1.10.2-1.el6.x86_64                                                                      2/9 
  Verifying  : nginx-mod-stream-1.10.2-1.el6.x86_64                                                                          3/9 
  Verifying  : nginx-all-modules-1.10.2-1.el6.noarch                                                                         4/9 
  Verifying  : nginx-mod-http-perl-1.10.2-1.el6.x86_64                                                                       5/9 
  Verifying  : nginx-mod-http-image-filter-1.10.2-1.el6.x86_64                                                               6/9 
  Verifying  : nginx-1.10.2-1.el6.x86_64                                                                                     7/9 
  Verifying  : nginx-filesystem-1.10.2-1.el6.noarch                                                                          8/9 
  Verifying  : nginx-mod-http-xslt-filter-1.10.2-1.el6.x86_64                                                                9/9 

已安装:
  nginx.x86_64 0:1.10.2-1.el6                                                                                                    

作为依赖被安装:
  nginx-all-modules.noarch 0:1.10.2-1.el6                     nginx-filesystem.noarch 0:1.10.2-1.el6                            
  nginx-mod-http-geoip.x86_64 0:1.10.2-1.el6                  nginx-mod-http-image-filter.x86_64 0:1.10.2-1.el6                 
  nginx-mod-http-perl.x86_64 0:1.10.2-1.el6                   nginx-mod-http-xslt-filter.x86_64 0:1.10.2-1.el6                  
  nginx-mod-mail.x86_64 0:1.10.2-1.el6                        nginx-mod-stream.x86_64 0:1.10.2-1.el6                            

完毕！
[root@localhost ~]# nginx -v
nginx version: nginx/1.10.2
[root@localhost ~]#

MySQL：通过RPM包安装:

[root@localhost ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar
..
..
100%[=======================================================================================>] 227,512,320 1.21M/s   in 2m 26s  

2018-09-04 14:51:22 (1.48 MB/s) - 已保存 “MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar” [227512320/227512320])

[root@localhost ~]# ll MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar 
-rw-r--r--. 1 root root 227512320 6月  18 16:11 MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar
[root@localhost ~]# tar -zxvf MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar 

gzip: stdin: not in gzip format
tar: Child returned status 1
tar: Error is not recoverable: exiting now
[root@localhost ~]# tar xvf MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar 
MySQL-client-5.6.41-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.41-1.el6.x86_64.rpm
MySQL-test-5.6.41-1.el6.x86_64.rpm
MySQL-server-5.6.41-1.el6.x86_64.rpm
MySQL-devel-5.6.41-1.el6.x86_64.rpm
MySQL-shared-5.6.41-1.el6.x86_64.rpm
MySQL-embedded-5.6.41-1.el6.x86_64.rpm
[root@localhost ~]# rpm -ivh MySQL-server-5.6.41-1.el6.x86_64.rpm 
warning: MySQL-server-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
        libnuma.so.1()(64bit) is needed by MySQL-server-5.6.41-1.el6.x86_64
        libnuma.so.1(libnuma_1.1)(64bit) is needed by MySQL-server-5.6.41-1.el6.x86_64
        libnuma.so.1(libnuma_1.2)(64bit) is needed by MySQL-server-5.6.41-1.el6.x86_64
[root@localhost ~]#

根据错误提示，安装numactl:

[root@localhost ~]# yum install numactl
已加载插件：fastestmirror
设置安装进程
Loading mirror speeds from cached hostfile
 * base: mirrors.163.com
 * epel: mirrors.ustc.edu.cn
 * extras: mirrors.shu.edu.cn
 * updates: mirrors.zju.edu.cn
解决依赖关系
--> 执行事务检查
---> Package numactl.x86_64 0:2.0.9-2.el6 will be 安装
--> 完成依赖关系计算

依赖关系解决

=================================================================================================================================
 软件包                        架构                         版本                                仓库                        大小
=================================================================================================================================
正在安装:
 numactl                       x86_64                       2.0.9-2.el6                         base                        74 k

事务概要
=================================================================================================================================
Install       1 Package(s)

总下载量：74 k
Installed size: 171 k
确定吗？[y/N]：y
下载软件包：
numactl-2.0.9-2.el6.x86_64.rpm                                                                            |  74 kB     00:00     
运行 rpm_check_debug 
执行事务测试
事务测试成功
执行事务
  正在安装   : numactl-2.0.9-2.el6.x86_64                                                                                    1/1 
  Verifying  : numactl-2.0.9-2.el6.x86_64                                                                                    1/1 

已安装:
  numactl.x86_64 0:2.0.9-2.el6                                                                                                   

完毕！
[root@localhost ~]# rpm -ivh MySQL-server-5.6.41-1.el6.x86_64.rpm 
warning: MySQL-server-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
        file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.41-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.71-1.el6.x86_64
..
..
file /usr/share/mysql/charsets/swe7.xml from install of MySQL-server-5.6.41-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.71-1.el6.x86_64
[root@localhost ~]#

再根据提示，系统自带mysql-libs-5.1.71与当前要安装的依赖冲突，先删除掉：

[root@localhost ~]# yum erase mysql-libs-5.1.71-1.el6.x86_64
已加载插件：fastestmirror
设置移除进程
解决依赖关系
--> 执行事务检查
---> Package mysql-libs.x86_64 0:5.1.71-1.el6 will be 删除
--> 处理依赖关系 libmysqlclient.so.16()(64bit)，它被软件包 2:postfix-2.6.6-2.2.el6_1.x86_64 需要
--> 处理依赖关系 libmysqlclient.so.16(libmysqlclient_16)(64bit)，它被软件包 2:postfix-2.6.6-2.2.el6_1.x86_64 需要
--> 处理依赖关系 mysql-libs，它被软件包 2:postfix-2.6.6-2.2.el6_1.x86_64 需要
--> 执行事务检查
---> Package postfix.x86_64 2:2.6.6-2.2.el6_1 will be 删除
--> 处理依赖关系 /usr/sbin/sendmail，它被软件包 cronie-1.4.4-12.el6.x86_64 需要
--> 执行事务检查
---> Package cronie.x86_64 0:1.4.4-12.el6 will be 删除
--> 处理依赖关系 cronie = 1.4.4-12.el6，它被软件包 cronie-anacron-1.4.4-12.el6.x86_64 需要
--> 执行事务检查
---> Package cronie-anacron.x86_64 0:1.4.4-12.el6 will be 删除
--> 处理依赖关系 /etc/cron.d，它被软件包 crontabs-1.10-33.el6.noarch 需要
--> 使用新的信息重新计算依赖关系
--> 执行事务检查
---> Package crontabs.noarch 0:1.10-33.el6 will be 删除
--> 完成依赖关系计算

依赖关系解决

=============================================================================================================================================================================
 软件包                              架构                        版本                                    仓库                                                           大小
=============================================================================================================================================================================
正在删除:
 mysql-libs                          x86_64                      5.1.71-1.el6                            @anaconda-CentOS-201311272149.x86_64/6.5                      4.0 M
为依赖而移除:
 cronie                              x86_64                      1.4.4-12.el6                            @anaconda-CentOS-201311272149.x86_64/6.5                      174 k
 cronie-anacron                      x86_64                      1.4.4-12.el6                            @anaconda-CentOS-201311272149.x86_64/6.5                       43 k
 crontabs                            noarch                      1.10-33.el6                             @anaconda-CentOS-201311272149.x86_64/6.5                      2.4 k
 postfix                             x86_64                      2:2.6.6-2.2.el6_1                       @anaconda-CentOS-201311272149.x86_64/6.5                      9.7 M

事务概要
=============================================================================================================================================================================
Remove        5 Package(s)

Installed size: 14 M
确定吗？[y/N]：y
下载软件包：
运行 rpm_check_debug 
执行事务测试
事务测试成功
执行事务
  正在删除   : cronie-anacron-1.4.4-12.el6.x86_64                                                                                                                        1/5 
  正在删除   : crontabs-1.10-33.el6.noarch                                                                                                                               2/5 
  正在删除   : cronie-1.4.4-12.el6.x86_64                                                                                                                                3/5 
  正在删除   : 2:postfix-2.6.6-2.2.el6_1.x86_64                                                                                                                          4/5 
  正在删除   : mysql-libs-5.1.71-1.el6.x86_64                                                                                                                            5/5 
  Verifying  : crontabs-1.10-33.el6.noarch                                                                                                                               1/5 
  Verifying  : cronie-anacron-1.4.4-12.el6.x86_64                                                                                                                        2/5 
  Verifying  : cronie-1.4.4-12.el6.x86_64                                                                                                                                3/5 
  Verifying  : 2:postfix-2.6.6-2.2.el6_1.x86_64                                                                                                                          4/5 
  Verifying  : mysql-libs-5.1.71-1.el6.x86_64                                                                                                                            5/5 

删除:
  mysql-libs.x86_64 0:5.1.71-1.el6                                                                                                                                           

作为依赖被删除:
  cronie.x86_64 0:1.4.4-12.el6           cronie-anacron.x86_64 0:1.4.4-12.el6           crontabs.noarch 0:1.10-33.el6           postfix.x86_64 2:2.6.6-2.2.el6_1          

完毕！
[root@localhost ~]#

接着安装MySQL-server：

[root@localhost ~]# rpm -ivh MySQL-server-5.6.41-1.el6.x86_64.rpm
warning: MySQL-server-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-server ########################################### [100%]
warning: user mysql does not exist - using root
warning: group mysql does not exist - using root
warning: user mysql does not exist - using root
warning: group mysql does not exist - using root
2018-09-04 15:09:54 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-09-04 15:09:54 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-09-04 15:09:54 0 [Note] /usr/sbin/mysqld (mysqld 5.6.41) starting as process 1647 ...
2018-09-04 15:09:54 1647 [Note] InnoDB: Using atomics to ref count buffer pool pages
..
..
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

..
..
New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

[root@localhost ~]#

安装MySQL客户端：

[root@localhost ~]# rpm -ivh MySQL-client-5.6.41-1.el6.x86_64.rpm 
warning: MySQL-client-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
[root@localhost ~]#

启动MySQL，用系统生成的随机口令先登录数据库，然后修改root口令，再创建新的库和用户blog，用于恢复原站点的数据库：

[root@localhost ~]# cat .mysql_secret 
# The random password set for the root user at Tue Sep  4 15:09:55 2018 (local time): QKkCVRsNEhJZEZj0

[root@localhost ~]# service mysql status
 ERROR! MySQL is not running
[root@localhost ~]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/localhost.localdomain.err'.
... SUCCESS! 
[root@localhost ~]# service mysql status
 SUCCESS! MySQL running (2098)
[root@localhost ~]# mysql -h localhost -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD=password('localblog');
Query OK, 0 rows affected (0.00 sec)

mysql> create database blog CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'blog'@'localhost' identified by 'blog123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on blog.* to 'blog'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@localhost ~]#

PHP：yum安装。

[root@localhost ~]# yum install php php-fpm
已加载插件：fastestmirror
..
..
已安装:
  php.x86_64 0:5.3.3-49.el6                                                           php-fpm.x86_64 0:5.3.3-49.el6                                                          

作为依赖被安装:
  apr.x86_64 0:1.3.9-5.el6_9.1                   apr-util.x86_64 0:1.3.9-3.el6_0.1      apr-util-ldap.x86_64 0:1.3.9-3.el6_0.1      httpd.x86_64 0:2.2.15-69.el6.centos     
  httpd-tools.x86_64 0:2.2.15-69.el6.centos      mailcap.noarch 0:2.1.31-2.el6          php-cli.x86_64 0:5.3.3-49.el6               php-common.x86_64 0:5.3.3-49.el6        

完毕！
[root@localhost ~]# yum install php php-fpm

四配置NGINX

这里，由于我是在本地服务器上模拟站点迁移，并没有新的域名。于是，我用NGINX模拟反向解析一个新的域名：blog.com，然后，把blog.com配置到本地hosts文件。

NGINX的配置文件路径为，/etc/nginx/conf.d。内容如下：

[root@localhost blog]# cat /etc/nginx/conf.d/blog.conf 
#
# The default server
#
server {
    listen       80;
    server_name  blog.com;

    #charset koi8-r;

    #access_log  logs/host.access.log  main;

    location / {
        root   /blog;
        index index.php  index.html index.htm;
    }

    error_page  404              /404.html;
    location = /404.html {
        root   /usr/share/nginx/html;
    }

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }

    # proxy the PHP scripts to Apache listening on 127.0.0.1:80
    #
    #location ~ \.php$ {
    #    proxy_pass   http://127.0.0.1;
    #}

    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    #
    location ~ \.php$ {
        root           /blog;
        fastcgi_pass   127.0.0.1:9000;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        include        fastcgi_params;
    }

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    #location ~ /\.ht {
    #    deny  all;
    #}
}
[root@localhost blog]#

该配置文件中的blog.com就是我要通过NGINX来反向代理的站点，/blog指的是我的站点的根目录。
然后，本地hosts文件中，把blog.com，指向172.16.11.96，添加如下一条记录即可：

172.16.11.80 blog.com
且，可以正常访问：
$ ping blog.com
PING blog.com (172.16.11.80): 56 data bytes
64 bytes from 172.16.11.80: icmp_seq=0 ttl=63 time=0.561 ms
64 bytes from 172.16.11.80: icmp_seq=1 ttl=63 time=0.638 ms
^C
--- blog.com ping statistics ---
2 packets transmitted, 2 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 0.561/0.600/0.638/0.039 ms

asher at MacBook-Air-3 in ~
$

五安装Wordpress

WordPress官方网站，下载最新的安装包，然后解压，放到/blog路径下，修改/blog机器子路径的权限，owner为NGINX的启动用户。这里需要配合前面的第4步骤，前面的NGINX把blog.com反向解析到哪个路径，这里就需要把Wordpress安装在哪儿。

[root@localhost ~]# wget https://wordpress.org/latest.tar.gz
..
[root@localhost ~]# mkdir -p /blog
[root@localhost ~]# mv latest.tar.gz /blog/
[root@localhost ~]# cd /blog/
[root@localhost blog]# tar -zxvf latest.tar.gz 
[root@localhost blog]# mv ./wordpress/* .
[root@localhost blog]# chown -R nginx:nginx /blog/

启动，NGINX、php-fpm，执行安装：

[root@localhost ~]# service nginx restart
停止 nginx：                                               [确定]
正在启动 nginx：                                           [确定]
[root@localhost ~]# service php-fpm restart
停止 php-fpm：                                             [失败]
正在启动 php-fpm：                                         [确定]
[root@localhost ~]#

浏览器打开blog.com，遇到下述错误：
Your PHP installation appears to be missing the MySQL extension which is required by WordPress.
经排查，是PHP版本太低，卸载前面安装的低版本PHP和PHP-fpm，然后安装高版本PHP和PHP-fpm，以及相关依赖包：

[root@localhost ~]# php -v
PHP 5.3.3 (cli) (built: Mar 22 2017 12:27:09) 
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies
[root@localhost ~]# yum erase php php-fpm
..
..
[root@localhost ~]# rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
Retrieving http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
Preparing...                ########################################### [100%]
   1:remi-release           ########################################### [100%]
[root@localhost ~]# yum install --enablerepo=remi --enablerepo=remi-php56 php php-fpm php-opcache php-devel php-mbstring php-mcrypt php-mysqlnd php-phpunit-PHPUnit php-pecl-xdebug php-pecl-xhprof 
..
..

再启动NGINX、php-fpm，执行安装：

[root@localhost blog]# php -v
PHP 5.6.37 (cli) (built: Jul 19 2018 20:06:19) 
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies
    with Xdebug v2.5.5, Copyright (c) 2002-2017, by Derick Rethans
[root@localhost blog]# service nginx restart
停止 nginx：                                               [确定]
正在启动 nginx：                                           [确定]
[root@localhost blog]# service php-fpm restart
停止 php-fpm：                                             [确定]
正在启动 php-fpm：                                         [确定]
[root@localhost blog]#

开始安装：

根据提示，填入前面配置MySQL数据库的时候，创建的数据库名blog,用户名blog及密码：

根据提示，安装程序无法创建wp-config.php配置文件，手工拷贝内容，在/blog路径下，创建该文件，内容为上述信息。继续安装：

设置超管用户的密码，这里只是测试，简单起见，设置blog、blog，真实环境下，要注意复杂度。

六还原MySQL数据库：

解压缩之前创建的MySQL数据库备份文件：

[root@localhost ~]# gzip -d oracleon_oracle.sql.gz
[root@localhost ~]# ll oracleon_oracle.sql
-rw-r--r--. 1 root root 15662095 9月 3 19:53 oracleon_oracle.sql
[root@localhost ~]#

blog用户登录数据库blog，执行脚本恢复：

  
[root@localhost ~]# mysql -h localhost -u blog -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use blog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------+
| Tables_in_blog        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.00 sec)

mysql> source ./oracleon_oracle.sql
..
..
Query OK, 0 rows affected (0.00 sec)

mysql>

七修改数据库中关于旧站点的连接信息：

mysql> UPDATE wp_options SET option_value = replace(option_value, 'www.oracleonlinux.cn','blog.com');
Query OK, 6 rows affected (0.02 sec)
Rows matched: 367  Changed: 6  Warnings: 0

mysql> UPDATE wp_options SET option_value = replace(option_value, 'oracleonlinux.cn','blog.com');
Query OK, 2 rows affected (0.01 sec)
Rows matched: 367  Changed: 2  Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'www.oracleonlinux.cn','blog.com');
Query OK, 95 rows affected (0.06 sec)
Rows matched: 735  Changed: 95  Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'oracleonlinux.cn','blog.com');
Query OK, 13 rows affected (0.07 sec)
Rows matched: 735  Changed: 13  Warnings: 0

mysql> UPDATE wp_comments SET comment_content = replace(comment_content, 'www.oracleonlinux.cn', 'blog.com');
Query OK, 8 rows affected (0.01 sec)
Rows matched: 284  Changed: 8  Warnings: 0

mysql> UPDATE wp_comments SET comment_content = replace(comment_content, 'oracleonlinux.cn', 'blog.com');
Query OK, 1 row affected (0.01 sec)
Rows matched: 284  Changed: 1  Warnings: 0

mysql> UPDATE wp_comments SET comment_author_url = replace(comment_author_url, 'www.oracleonlinux.cn', 'blog.com');
Query OK, 66 rows affected (0.01 sec)
Rows matched: 284  Changed: 66  Warnings: 0

mysql> UPDATE wp_comments SET comment_author_url = replace(comment_author_url, 'oracleonlinux.cn', 'blog.com');
Query OK, 10 rows affected (0.01 sec)
Rows matched: 284  Changed: 10  Warnings: 0

mysql>

八登录后台查看信息：

注意，这里需要使用旧站点的管理员用户和密码，因为后台的MySQL数据库中已经导入之前的数据库备份信息了。

登录之后，可以看到之前站点上发布的所有文章和数据。这里，需要重新安装以及启用之前站点上的主题和插件。

九设置NGINX的静态解析

由于之前的网站是LAMP，用Apache解析的，现在改为NGINX代理的话，需要重新配置静态解析。否则的话，会出现主页访问正常，但是其它页面以及标签页和单篇文章打开时报404错误。

在nginx的配置文件的location / 位置处，加上下述信息：

 if (!-e $request_filename) {
            rewrite ^([_0-9a-zA-Z-]+)?(/wp-.*) $2 last;
            rewrite ^([_0-9a-zA-Z-]+)?(/.*\.php)$ $2 last;
            rewrite ^ /index.php last;
        }

完整的用于该站点的反向代理解析文件如下：

#
# The default server
#
server {
    listen       80;
    server_name  blog.com;

    #charset koi8-r;

    #access_log  logs/host.access.log  main;

    location / {
        root   /blog;
        index index.php  index.html index.htm;
        
        if (!-e $request_filename) {
            rewrite ^([_0-9a-zA-Z-]+)?(/wp-.*) $2 last;
            rewrite ^([_0-9a-zA-Z-]+)?(/.*\.php)$ $2 last;
            rewrite ^ /index.php last;
        }
    }

    error_page  404              /404.html;
    location = /404.html {
        root   /usr/share/nginx/html;
    }

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }

    # proxy the PHP scripts to Apache listening on 127.0.0.1:80
    #
    #location ~ \.php$ {
    #    proxy_pass   http://127.0.0.1;
    #}

    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    #
    location ~ \.php$ {
        root           /blog;
        fastcgi_pass   127.0.0.1:9000;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        include        fastcgi_params;
    }

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    #location ~ /\.ht {
    #    deny  all;
    #}
}

最后，可以在本地看到的站点如下：

PostgreSQL数据库pg_hba.conf文件学习笔记

8月 30,2018 留下评论 PostgreSQL

对于PostgreSQL数据库的pg_hba.conf文件从以下几个方面来学习。

1 pg_hba.conf的作用

pg_hba.conf文件是数据库判断客户端/应用程序能否正常访问数据库的唯一依据。(HBA stands for host-based authentication.)

略微牵强一点儿的横向比较的话，其作用类似于Oracle数据库的listener.ora文件。

pg的客户端访问数据库的时候，需要提供数据库服务器主机名或者IP地址，端口号，数据库名，数据库用户名，以及数据库用户密码或者数据库认证方式。而这些信息是存在于pg_hba.conf文件中的。同样，Oracle客户端访问数据库的过程中，也需要提供Oracle数据库主机名或者IP地址，端口号，访问协议，以及服务名。而这些信息是记录在listener.ora文件中的。

2 pg_hba.conf的位置

默认情况下，该文件位于数据库服务器的data路径下，名为pg_hba.conf。当然，这些都不是绝对的，尤其对于Unix/Linux环境下，文件的扩展名只是一种约定的惯例，并不像windows平台下，但凡.exe代表可执行文件等。

可以在psql命令行中，通过超级用户执行show hba_file来获取pg_hba.conf的具体路径。

postgres=# show hba_file ;
              hba_file               
-------------------------------------
 /orabackup/pg9.6.4/data/pg_hba.conf
(1 row)

postgres=#

如果是没有权限的普通用户执行该命令，可能会有下述错误：

ai=> show hba_file;
ERROR:  must be superuser to examine "hba_file"
ai=>

如果需要修改或者调整配置文件pg_hba.conf的路径的话，需要通过修改postgresql.conf文件中hba_file参数来实现，并且要提前做好pg_hba.conf文件的备份，以及需要重启数据库才能使修改生效。

3 pg_hba.conf的内容

该文件是一个普通的文本类型文件，可以直接用文本编辑器编辑修改；
以#开头行的是注释行；
以行为单位，每一行是一个有效的记录，不允许不支持跨行的记录存在；
每一个生效的行记录都包含：连接类型、数据库名、用户名、允许的客户端IP地址或地址段、认证方式，这5个字段。且，各字段之间以空格分开。类似如下：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             172.0.0.0/8             md5

这里local开头的行，表示的是数据库允许通过主机服务器的Unix socket通信来访问和连接数据库。如果没有local开头的行，则意味着数据库不接受socket通信的连接访问方式。说白了，就是没有这一行的话，或注释掉改行的话，则无法通过简单的psql来访问数据库：

[postgres@localhost data]$ psql
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "postgres"
[postgres@localhost data]$

第1个host开头的行，意味着，允许接受客户端为数据库主机环路地址127.0.0.1上，通过提供数据库IP地址，端口，数据库名、用户名的方式来访问数据库，认证方式为trust，即不需要提供密码的方式来访问数据库。且，可以以数据库中已经存在的任意用户来访问任意数据库，如：

[postgres@localhost data]$ psql -h localhost -p 5432 -d postgres -U postgres
psql (9.6.4)
Type "help" for help.

postgres=# \q
[postgres@localhost data]$ psql -h 127.0.0.1 -p 5432 -d postgres -U ai
psql (9.6.4)
Type "help" for help.

postgres=>

第2个host开头的行，意味着，允许从IP范围是172.0.0.0到172.254.254.254之间的任意地址，以数据库中已经存在的用户，发起到任意数据库的连接请求，但是必须要提供用户口令，因为认证方式是是通过md5加密的。如：

[postgres@localhost data]$ psql -h 172.16.11.35 -p 5432 -d postgres -U enterprisedb
Password for user enterprisedb: 
psql (9.6.4, server 9.3.11.33)
Type "help" for help.

postgres=#

从IP为172.18.1.12的客户端，发起到172.16.11.35服务端的连接请求，在提供名为enterprisedb的数据库用户密码之后，顺利连接到服务器。

连接类型，除了local、host之外，还有hostssl和hostnossl方式；
数据库和用户名字段的all，表示可以访问任意数据库，以及以任意数据库用户发起对于数据库的访问，如果需要对于特定的数据库和特定的用户作限制的话，则应该明确写出库名和数据库用户名；
数据库在做连接验证的过程中，对于该文件是从上向下查找的，如果找到一条记录匹配客户端发起的数据库连接请求，则参照该记录来验证，不再继续向下找，验证成功则访问数据库成功，反之失败，如下：

pg_hba.conf含有下述2条记录：

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5 
host    all             all             127.0.0.1/32            trust

那么，此时，数据库选择第一条记录来验证，就必须的正确的输入数据库用户postgres的口令，否则会报错。如下：

[postgres@localhost data]$ psql -h 127.0.0.1 -p 5432 -d postgres -U postgres
Password for user postgres: 
psql: FATAL:  password authentication failed for user "postgres"
[postgres@localhost data]$

显然，对于这次验证失败，数据库并没有选择第2条验证方位为trust的无需密码的方式来验证；

对于数据库名字段上，如果需要匹配多个数据库的话，可以用逗号分隔开多个数据库的库名；
数据库字段名上，如果出现replication的话，并不表示对于名为replication的数据库名授权验证，而是验证一个来自物理备库的连接请求；
address字段上，如下：

172.20.143.89/32 for a single host，只匹配172.20.143.89这一个IP地址；
172.20.143.0/24 for a small network，匹配从172.20.143.0到172.20.143.254这个地址段；
10.6.0.0/16 for a larger one，匹配从10.6.0.0到10.6.254.254地址段；
::1/128 for a single host ，匹配IPv6的本地环路地址，等价于IPv4的127.0.0.1；
0.0.0.0/0 represents all IPv4 addresses，匹配所有IPv4的地址；
::0/0 represents all IPv6 addresses，匹配所有IPv6地址；

4 修改pg_hba.conf的内容

通过文本编辑器，按照其固定格式，直接修改完该文件之后，需要执行pg_ctl reload使其生效。

5 个人收获

从基础知识点上搞清楚了该文件的作用；
明白了该文件中local那一行的真正具体含义；
学会了该文件和postgrres.conf文件中的listen_address参数一起搭配来控制，通过网络来访问数据库的控制和限制；如果listen_addresses =’localhost’时，则该数据库不允许任何通过网络方式来的异机访问，只允许通过数据库服务器本机连接本机上的数据库；
学到了一点儿关于网络掩码的设置，如172.20.143.0/24的具体含义。

更为详细的信息，可以进一步查看官方文档：https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

如何在Linux上源码安装PostgreSQL数据库？

8月 21,2018 留下评论 Linux, PostgreSQL

零说明

本文记录在CentOS/RHEL Linux平台上通过源码安装PostgreSQL数据库、配置数据库的过程和步骤，及注意事项。

一前提条件

要求OS上已经安装下述软件。如果没有，可以直接通过yum来安装。

1 gmake或者make要求至少3.80版本以上

[root@localhost ~]# make -v
GNU Make 3.82
Built for x86_64-redhat-linux-gnu
Copyright (C) 2010  Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
[root@localhost ~]# gmake -v
GNU Make 3.82
Built for x86_64-redhat-linux-gnu
Copyright (C) 2010  Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
[root@localhost ~]# which make
/usr/bin/make
[root@localhost ~]# which gmake
/usr/bin/gmake
[root@localhost ~]# ll /usr/bin/make
-rwxr-xr-x. 1 root root 182776 11月  6 2016 /usr/bin/make
[root@localhost ~]# ll /usr/bin/gmake
lrwxrwxrwx. 1 root root 4 1月   8 2018 /usr/bin/gmake -> make
[root@localhost ~]#

这里，看到gmake版本为3.82。gmake是一个链接文件，其指向了make。其实，在CentOS/RHEL平台上，默认情况下，gmake等同于make。

2 C编译器

[root@localhost ~]# which gcc
/usr/bin/gcc
[root@localhost ~]# gcc -v
使用内建 specs。
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/lto-wrapper
目标：x86_64-redhat-linux
配置为：../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-linker-hash-style=gnu --enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto --enable-plugin --enable-initfini-array --disable-libgcj --with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install --with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install --enable-gnu-indirect-function --with-tune=generic --with-arch_32=x86-64 --build=x86_64-redhat-linux
线程模型：posix
gcc 版本 4.8.5 20150623 (Red Hat 4.8.5-16) (GCC) 
[root@localhost ~]#

如果没有的话，可以直接安装一个最新的gcc即可。

3 tar

[root@localhost ~]# which tar
/usr/bin/tar
[root@localhost ~]# tar --version
tar (GNU tar) 1.26
Copyright (C) 2011 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
这是自由软件：您可以自由地更改并重新发布它。
在法律所允许的范围内不含任何担保。

由 John Gilmore 和 Jay Fenlason 所写。
[root@localhost ~]#

用于解压缩源码格式的安装压缩包文件。

 如果下载的源文件格式类似于postgresql-9.4.10.tar.gz，则用tar -zxvf postgresql-9.4.10.tar.gz解压；
如果下载的源文件格式类似于postgresql-9.4.10.tar.bz2，则用tar jxvf postgresql-9.4.10.tar.bz2解压；
注意，第二种格式文件的解压，命令行选项中不带减号-。

4 GNU readline library

该库文件默认启用。用于在psql命令行下，可以通过键盘的上下箭头调出历史命令以及编辑之前的命令。如果不需要此功能的话，可以在configure的时候，带上--without-readline选项。

5 zlib compression library

该库文件默认启用。启用该选项则意味着，使用pg_dump/pg_restore对数据库进行备份/恢复的时候，不支持压缩？？？如果不需要此功能的话，可以在configure的时候，带上–without-zlib选项。

经过我初步验证测试，暂时还没搞清楚该库文件的具体功能和作用。暂时记下这个问题，留待我后续进一步验证一下？？？

https://dan.langille.org/2013/06/10/using-compression-with-postgresqls-pg_dump/

http://peter.eisentraut.org/blog/2015/12/07/check-your-pg-dump-compression-levels/

二源码安装

1 创建postgres用户

[root@dev-middleware ~]# groupadd postgres
[root@dev-middleware ~]# useradd -g postgres postgres
[root@dev-middleware ~]# passwd postgres
更改用户 postgres 的密码 。
新的 密码：
无效的密码： 它基于字典单词
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
[root@dev-middleware ~]#

2 创建数据库安装路径

假定将来要把数据库软件安装在/data/postgres/10.4/路径下，数据库的数据存放在/data/postgres/10.4/data路径下。
这里，先把/data/postgres/10.4/路径创建出来即可，/data/postgres/10.4/data路径不需提前创建。

[root@dev-middleware ~]# mkdir -p /data/postgres/10.4/
[root@dev-middleware ~]# chown -R postgres:postgres /data/postgres/10.4/
[root@dev-middleware ~]#

3 获取源码

到PostgreSQL官方网站，获取源码格式的数据库安装文件。

[root@dev-middleware ~]# su - postgres
[postgres@dev-middleware ~]$ pwd
/home/postgres
[postgres@dev-middleware ~]$ wget https://ftp.postgresql.org/pub/source/v10.4/postgresql-10.4.tar.gz
--2018-08-06 11:25:51--  https://ftp.postgresql.org/pub/source/v10.4/postgresql-10.4.tar.gz
正在解析主机 ftp.postgresql.org (ftp.postgresql.org)... 87.238.57.227, 217.196.149.55, 204.145.124.244, ...
正在连接 ftp.postgresql.org (ftp.postgresql.org)|87.238.57.227|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：26652442 (25M) [application/x-gzip]
正在保存至: “postgresql-10.4.tar.gz”

100%[======================================================================================================================================================================================================>] 26,652,442  5.83MB/s 用时 6.4s   

2018-08-06 11:26:03 (3.98 MB/s) - 已保存 “postgresql-10.4.tar.gz” [26652442/26652442])

[postgres@dev-middleware ~]$ ll
总用量 26028
-rw-rw-r-- 1 postgres postgres 26652442 5月   8 05:06 postgresql-10.4.tar.gz
[postgres@dev-middleware ~]$

4 解压源码

如果下载的源码文件格式为类似于postgresql-9.4.10.tar.bz2，则用tar jxvf postgresql-9.4.10.tar.bz2解压。

解压后，会生成一个新的postgresql-10.4路径，该路径下存放的就是源码格式的安装文件。

[postgres@dev-middleware ~]$ pwd
/home/postgres
[postgres@dev-middleware ~]$ ll
总用量 26028
-rw-rw-r-- 1 postgres postgres 26652442 5月   8 05:06 postgresql-10.4.tar.gz
[postgres@dev-middleware ~]$ 
[postgres@dev-middleware ~]$ tar -zxvf postgresql-10.4.tar.gz 
...
...
postgresql-10.4/GNUmakefile.in
postgresql-10.4/.gitattributes
postgresql-10.4/aclocal.m4
postgresql-10.4/configure.in
postgresql-10.4/INSTALL
[postgres@dev-middleware ~]$

5 执行configure

执行configure的命令行选项，–prefix参数，表示把PostgreSQL安装在哪个路径下。默认情况下，不带该参数时，则会安装在/usr/local/pgsql路径下。

[postgres@dev-middleware ~]$ pwd
/home/postgres
[postgres@dev-middleware ~]$ ll
总用量 26032
drwxrwxr-x 6 postgres postgres     4096 5月   8 05:06 postgresql-10.4
-rw-rw-r-- 1 postgres postgres 26652442 5月   8 05:06 postgresql-10.4.tar.gz
[postgres@dev-middleware ~]$ cd postgresql-10.4
[postgres@dev-middleware postgresql-10.4]$ ll
总用量 676
-rw-r--r--  1 postgres postgres    457 5月   8 04:51 aclocal.m4
drwxrwxr-x  2 postgres postgres   4096 5月   8 05:03 config
-rwxr-xr-x  1 postgres postgres 498962 5月   8 04:51 configure
-rw-r--r--  1 postgres postgres  76909 5月   8 04:51 configure.in
drwxrwxr-x 55 postgres postgres   4096 5月   8 05:03 contrib
-rw-r--r--  1 postgres postgres   1192 5月   8 04:51 COPYRIGHT
drwxrwxr-x  3 postgres postgres   4096 5月   8 05:03 doc
-rw-r--r--  1 postgres postgres   3638 5月   8 04:51 GNUmakefile.in
-rw-r--r--  1 postgres postgres    284 5月   8 04:51 HISTORY
-rw-r--r--  1 postgres postgres  71584 5月   8 05:06 INSTALL
-rw-r--r--  1 postgres postgres   1682 5月   8 04:51 Makefile
-rw-r--r--  1 postgres postgres   1212 5月   8 04:51 README
drwxrwxr-x 16 postgres postgres   4096 5月   8 05:06 src
[postgres@dev-middleware postgresql-10.4]$ ./configure --prefix=/data/postgres/10.4/
...
...
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[postgres@dev-middleware postgresql-10.4]$

6 执行make world

这里，也可以简单执行make就可以build安装文件的，但是，make world的意思是指把PostgreSQL相关的文档，HTML，以及其它的一些模块都会一起编译进去。比如，在有的环境下遇到无法使用uuid或者使用不了gin index的原因，就是在编译的时候，没有包含这些对应的模块。处理这种的问题的方法也不复杂，只需要进到当时安装时的那个源文件路径下，重新执行make world，然后make install-world。

建议，在初始安装的时候，就直接用make world。

[postgres@dev-middleware postgresql-10.4]$ make world
..
..
make[2]: 离开目录“/home/postgres/postgresql-10.4/contrib/vacuumlo”
make[1]: 离开目录“/home/postgres/postgresql-10.4/contrib”
PostgreSQL, contrib, and documentation successfully made. Ready to install.
[postgres@dev-middleware postgresql-10.4]$

7 执行make install-world

[postgres@dev-middleware postgresql-10.4]$ make install-world 
...
...
/bin/mkdir -p '/data/postgres/10.4/bin'
/bin/install -c  vacuumlo '/data/postgres/10.4/bin'
make[2]: 离开目录“/home/postgres/postgresql-10.4/contrib/vacuumlo”
make[1]: 离开目录“/home/postgres/postgresql-10.4/contrib”
PostgreSQL, contrib, and documentation installation complete.
[postgres@dev-middleware postgresql-10.4]$

8 初始化数据库

[postgres@dev-middleware postgresql-10.4]$ /data/postgres/10.4/bin/initdb -D /data/postgres/10.4/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory /data/postgres/10.4/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /data/postgres/10.4/bin/pg_ctl -D /data/postgres/10.4/data -l logfile start

[postgres@dev-middleware postgresql-10.4]$

9 启动数据库

[postgres@dev-middleware postgresql-10.4]$ cd
[postgres@dev-middleware ~]$ ps -ef|grep postgres
root     19521 10727  0 11:25 pts/0    00:00:00 su - postgres
postgres 19522 19521  0 11:25 pts/0    00:00:00 -bash
postgres 19882 19522  0 17:39 pts/0    00:00:00 ps -ef
postgres 19883 19522  0 17:39 pts/0    00:00:00 grep --color=auto postgres
[postgres@dev-middleware ~]$  /data/postgres/10.4/bin/pg_ctl -D /data/postgres/10.4/data -l /home/postgres/pg.log start
waiting for server to start.... done
server started
[postgres@dev-middleware ~]$ ps -ef|grep postgres
root     19521 10727  0 11:25 pts/0    00:00:00 su - postgres
postgres 19522 19521  0 11:25 pts/0    00:00:00 -bash
postgres 21158     1  0 17:39 pts/0    00:00:00 /data/postgres/10.4/bin/postgres -D /data/postgres/10.4/data
postgres 21160 21158  0 17:39 ?        00:00:00 postgres: checkpointer process   
postgres 21161 21158  0 17:39 ?        00:00:00 postgres: writer process   
postgres 21162 21158  0 17:39 ?        00:00:00 postgres: wal writer process   
postgres 21163 21158  0 17:39 ?        00:00:00 postgres: autovacuum launcher process   
postgres 21164 21158  0 17:39 ?        00:00:00 postgres: stats collector process   
postgres 21165 21158  0 17:39 ?        00:00:00 postgres: bgworker: logical replication launcher   
postgres 21168 19522  0 17:39 pts/0    00:00:00 ps -ef
postgres 21169 19522  0 17:39 pts/0    00:00:00 grep --color=auto postgres
[postgres@dev-middleware ~]$

10 修改环境变量

修改postgres用户的~/.bash_profile，PATH添加/data/postgres/10.4/bin，同时，添加PGDATA=/data/postgres/10.4/data环境变量。

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/data/postgres/10.4/bin

export PATH

PGDATA=/data/postgres/10.4/data
export PGDATA

使其环境变量生效：

[postgres@dev-middleware ~]$ source ~/.bash_profile 
[postgres@dev-middleware ~]$

11 修改数据库参数

a 修改pg_hba.conf文件

在/data/postgres/10.4/data/pg_hba.conf文件中，添加下面的一行：

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

其，表示当前数据库服务器，对使用IPV4地址的客户端机器，允许其通过任意IP地址，通过任意数据库用户发起对该数据库服务器上的任意数据库的访问，但是必须得提供数据库用户密码。其认证方式，是通过MD5加密认证的。

一句话简介：对于使用IPV4地址的客户端，数据库服务器不做限制，可以通过任意用户访问所有数据库。

b 修改postgresql.conf 文件

在/data/postgres/10.4/data/postgresql.conf 文件中，修改#listen_addresses = ‘localhost’为#listen_addresses = ‘*’。

然后，reload使其生效。

原因是，如果不修改的话，则客户端只能通过数据库服务器的环路地址127.0.0.1来访问数据库，不能使用服务器的公网IP地址来访问。

如下，修改之前：

[postgres@dev-middleware ~]$ psql
psql (10.4)
Type "help" for help.

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 localhost
(1 row)

postgres=# \q
[postgres@dev-middleware ~]$ psql -h 127.0.0.1 -p 5432 -d postgres
psql (10.4)
Type "help" for help.

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 localhost
(1 row)

postgres=# \q
[postgres@dev-middleware ~]$ psql -h 172.16.11.44 -p 5432 -d postgres
psql: could not connect to server: 拒绝连接
        Is the server running on host "172.16.11.44" and accepting
        TCP/IP connections on port 5432?
[postgres@dev-middleware ~]$

修改listen_addresses = ‘*’，且重启数据库使其生效之后：

[postgres@dev-middleware ~]$ vi /data/postgres/10.4/data/postgresql.conf 
...
...

listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/tmp'       # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
"/data/postgres/10.4/data/postgresql.conf" 658L, 22755C written
[postgres@dev-middleware ~]$ pg_ctl restart -m fast
waiting for server to shut down....2018-08-06 18:31:40.294 CST [12290] LOG:  received fast shutdown request
2018-08-06 18:31:40.306 CST [12290] LOG:  aborting any active transactions
2018-08-06 18:31:40.307 CST [12290] LOG:  worker process: logical replication launcher (PID 12297) exited with exit code 1
2018-08-06 18:31:40.314 CST [12292] LOG:  shutting down
2018-08-06 18:31:40.347 CST [12290] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2018-08-06 18:31:40.427 CST [14493] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-08-06 18:31:40.427 CST [14493] LOG:  listening on IPv6 address "::", port 5432
2018-08-06 18:31:40.433 CST [14493] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-08-06 18:31:40.487 CST [14494] LOG:  database system was shut down at 2018-08-06 18:31:40 CST
2018-08-06 18:31:40.498 CST [14493] LOG:  database system is ready to accept connections
 done
server started
[postgres@dev-middleware ~]$

可以通过数据库服务器的实际IP地址，访问数据库，

[postgres@dev-middleware ~]$ psql 
psql (10.4)
Type "help" for help.

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 *
(1 row)

postgres=# \q
[postgres@dev-middleware ~]$ psql -h 127.0.0.1 -p 5432 -d postgres
psql (10.4)
Type "help" for help.

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 *
(1 row)

postgres=# \q
[postgres@dev-middleware ~]$ psql -h 172.16.11.44 -p 5432 -d postgres -U postgres
Password for user postgres: 
psql (10.4)
Type "help" for help.

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 *
(1 row)

postgres=# \q
[postgres@dev-middleware ~]$

12 删除PostgreSQL数据库软件

postgres用户可以进入到之前解压的源码所在的路径，通过执行make uninstall来卸载已经安装的数据库软件。如下：

[postgres@dev-middleware ~]$ pwd
/home/postgres
[postgres@dev-middleware ~]$ ll
总用量 26036
-rw------- 1 postgres postgres      939 8月   6 18:29 pg.log
drwxrwxr-x 6 postgres postgres     4096 8月   6 11:29 postgresql-10.4
-rw-rw-r-- 1 postgres postgres 26652442 5月   8 05:06 postgresql-10.4.tar.gz
[postgres@dev-middleware ~]$ make uninstall
make: *** 没有规则可以创建目标“uninstall”。 停止。
[postgres@dev-middleware ~]$ cd postgresql-10.4
[postgres@dev-middleware postgresql-10.4]$ make uninstall 
...
...
make[1]: 进入目录“/home/postgres/postgresql-10.4/config”
rm -f '/data/postgres/10.4/lib/pgxs/config/install-sh'
rm -f '/data/postgres/10.4/lib/pgxs/config/missing'
make[1]: 离开目录“/home/postgres/postgresql-10.4/config”
[postgres@dev-middleware postgresql-10.4]$

13 重新编译安装PostgreSQL数据库软件

可以通过前面第4步的，重新解压源码，用新解压出来的源码文件，进行configure，make world,make install-world。

还有一种方式，就是把之前第5步骤执行configure之后的文件，恢复到configure之前的状态，然后通过执行configure，make world,make install-world。这个命令是make distclean。

如下是第二种方式的记录：

[postgres@dev-middleware postgresql-10.4]$ pwd
/home/postgres/postgresql-10.4
[postgres@dev-middleware postgresql-10.4]$ ll
总用量 1088
-rw-r--r--  1 postgres postgres    457 5月   8 04:51 aclocal.m4
drwxrwxr-x  2 postgres postgres   4096 5月   8 05:03 config
-rw-rw-r--  1 postgres postgres 373348 8月   6 11:29 config.log
-rwxrwxr-x  1 postgres postgres  39415 8月   6 11:29 config.status
-rwxr-xr-x  1 postgres postgres 498962 5月   8 04:51 configure
-rw-r--r--  1 postgres postgres  76909 5月   8 04:51 configure.in
drwxrwxr-x 55 postgres postgres   4096 5月   8 05:03 contrib
-rw-r--r--  1 postgres postgres   1192 5月   8 04:51 COPYRIGHT
drwxrwxr-x  3 postgres postgres   4096 5月   8 05:03 doc
-rw-rw-r--  1 postgres postgres   3638 8月   6 11:29 GNUmakefile
-rw-r--r--  1 postgres postgres   3638 5月   8 04:51 GNUmakefile.in
-rw-r--r--  1 postgres postgres    284 5月   8 04:51 HISTORY
-rw-r--r--  1 postgres postgres  71584 5月   8 05:06 INSTALL
-rw-r--r--  1 postgres postgres   1682 5月   8 04:51 Makefile
-rw-r--r--  1 postgres postgres   1212 5月   8 04:51 README
drwxrwxr-x 16 postgres postgres   4096 8月   6 11:29 src
[postgres@dev-middleware postgresql-10.4]$ make distclean
...
...
make[1]: 离开目录“/home/postgres/postgresql-10.4/src”
rm -rf tmp_install/
rm -f config.cache config.log config.status GNUmakefile
[postgres@dev-middleware postgresql-10.4]$ ll
总用量 676
-rw-r--r--  1 postgres postgres    457 5月   8 04:51 aclocal.m4
drwxrwxr-x  2 postgres postgres   4096 5月   8 05:03 config
-rwxr-xr-x  1 postgres postgres 498962 5月   8 04:51 configure
-rw-r--r--  1 postgres postgres  76909 5月   8 04:51 configure.in
drwxrwxr-x 55 postgres postgres   4096 5月   8 05:03 contrib
-rw-r--r--  1 postgres postgres   1192 5月   8 04:51 COPYRIGHT
drwxrwxr-x  3 postgres postgres   4096 5月   8 05:03 doc
-rw-r--r--  1 postgres postgres   3638 5月   8 04:51 GNUmakefile.in
-rw-r--r--  1 postgres postgres    284 5月   8 04:51 HISTORY
-rw-r--r--  1 postgres postgres  71584 5月   8 05:06 INSTALL
-rw-r--r--  1 postgres postgres   1682 5月   8 04:51 Makefile
-rw-r--r--  1 postgres postgres   1212 5月   8 04:51 README
drwxrwxr-x 16 postgres postgres   4096 8月   6 19:10 src
[postgres@dev-middleware postgresql-10.4]$

三小结

本文用于记录如何在Linux平台上，通过编译源码的方式，来安装配置PostgreSQL数据库，作一个简单记录。更为详细的信息可以参考官方文档。

mongodb connection refused的可能原因

7月 20,2018 留下评论 Linux, MongoDB

一 OS环境：

 
[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 
[root@localhost ~]# uname -rm
3.10.0-862.3.2.el7.x86_64 x86_64
[root@localhost ~]#

二 mongo信息：

MongoDB server version: 4.0.0

且防火墙，SELINUX全关闭的情况下，访问mongodb报错。

三错误信息如下：

[root@localhost ~]# mongo 172.16.143.250:27017/admin
MongoDB shell version v4.0.0
connecting to: mongodb://172.16.143.250:27017/admin
2018-07-20T09:30:52.691+0800 E QUERY    [js] Error: couldn't connect to server 172.16.143.250:27017, connection attempt failed: SocketException: Error connecting to 172.16.143.250:27017 :: caused by :: Connection refused :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed
[root@localhost ~]#

且在服务器本地及其它机器上telnet该机器的mongodb默认端口27017都不通：

$ telnet 172.16.143.250 27017
Trying 172.16.143.250...
telnet: Unable to connect to remote host: Connection refused

四原因及解决办法：

如果服务器上有多个IP地址的话，需要将mongodb的服务绑定到多个IP地址，在配置文件里加上：

bind_ip_all=on

当该参数配置为bind_ip_all=all时，启动mongodb会报出下述错误：

 Error parsing INI config file: the argument ('all') for option 'bind_ip_all' is invalid. Valid choices are 'on|off', 'yes|no', '1|0' and 'true|false'

重启mongodb，然后访问正常。

该机器172.16.143.250的IP地址如下：

[root@localhost mongodata]# ifconfig 
docker0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.10.10.1  netmask 255.255.255.0  broadcast 0.0.0.0
        inet6 fe80::42:2cff:fef4:41cb  prefixlen 64  scopeid 0x20
        ether 02:42:2c:f4:41:cb  txqueuelen 0  (Ethernet)
        RX packets 15725  bytes 857602 (837.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 20945  bytes 2675605 (2.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

enp0s31f6: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.16.143.250  netmask 255.255.255.0  broadcast 172.16.143.255
        inet6 fe80::3bf1:41bf:bb41:1a74  prefixlen 64  scopeid 0x20
        ether 2c:fd:a1:be:65:d3  txqueuelen 1000  (Ethernet)
        RX packets 83706  bytes 17618724 (16.8 MiB)
        RX errors 0  dropped 102  overruns 0  frame 0
        TX packets 30918  bytes 6621448 (6.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device interrupt 16  memory 0xdf200000-df220000  

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 332117  bytes 34574785 (32.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 332117  bytes 34574785 (32.9 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

veth6d13f43: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet6 fe80::38d0:60ff:fe0d:1867  prefixlen 64  scopeid 0x20
        ether 3a:d0:60:0d:18:67  txqueuelen 0  (Ethernet)
        RX packets 4059  bytes 273076 (266.6 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 5426  bytes 580733 (567.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

veth8741a2f: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet6 fe80::14d2:d7ff:fe8e:75f7  prefixlen 64  scopeid 0x20
        ether 16:d2:d7:8e:75:f7  txqueuelen 0  (Ethernet)
        RX packets 3401  bytes 245833 (240.0 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 4769  bytes 871463 (851.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

vetha20a789: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet6 fe80::e829:79ff:fea8:558d  prefixlen 64  scopeid 0x20
        ether ea:29:79:a8:55:8d  txqueuelen 0  (Ethernet)
        RX packets 4058  bytes 273390 (266.9 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 5423  bytes 637072 (622.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

vetha26bafa: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet6 fe80::58cc:3aff:fec9:ad8e  prefixlen 64  scopeid 0x20
        ether 5a:cc:3a:c9:ad:8e  txqueuelen 0  (Ethernet)
        RX packets 4063  bytes 273661 (267.2 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 5430  bytes 594535 (580.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@localhost mongodata]#

配置scp,ssh,rsync到另外一台主机无需密码的另外一种方式

3月 14,2018 有2条评论 AIX, Linux

一场景需求：

一台AIX主机，一台Linux主机。
需用从AIX主机上通过oracle用户SCP传输文件到Linux主机，且要求SCP传输文件的过程中，要脚本自动化处理，无需人工干预，即免去手动键入密码的环节。

可是AIX上没有ssh-copy-id的可用命令

# ssh-copy-id   
ksh: ssh-copy-id:  not found
# uname -M
IBM,8202-E4B
# uname -n
usp720
# uname -a
AIX usp720 1 6 00F67A854C00
# oslevel 
6.1.0.0
#

二解决方法：

1 AIX上，Oracle用户执行，ssh-keygen -t rsa：

ssh-keygen -t rsa
执行过程中，一路Enter回车即可。

2 AIX上，Oracle用户执行，cat ~/.ssh/id_rsa.pub文件内容：

3 Linux上，Oracle用户执行，把步骤2中的文件内容，写入~/.ssh/authorized_keys：

同时，修改~/.ssh/authorized_keys的文件权限为700。

 
chmod 700~/.ssh/authorized_keys

4 AIX上，执行：

 
bash-4.3$ hostname
usp720
bash-4.3$ ssh oracle@172.18.1.12
Last login: Wed Mar 14 16:41:42 2018 from 172.18.1.1
localhost-> hostname
localhost.localdomain
localhost->

三小记：

早在2008年的时候，在学习Oracle 10gR2 RAC在CentOS 4.8上安装部署时，配置RAC双节点，Oracle用户的互信，就采用的这种方法。10年前学的知识点被唤醒了。

在CentOS 7上安装redis

11月 22,2017 留下评论 Linux, Redis

1 下载：从Redis官方网站下载安装最新的稳定版安装包;

[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.2.1511 (Core) 
[root@localhost ~]# uname -rm
3.10.0-327.el7.x86_64 x86_64
[root@localhost ~]# wget http://download.redis.io/releases/redis-4.0.2.tar.gz
--2017-11-22 14:32:03--  http://download.redis.io/releases/redis-4.0.2.tar.gz
正在解析主机 download.redis.io (download.redis.io)... 109.74.203.151
正在连接 download.redis.io (download.redis.io)|109.74.203.151|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：1713990 (1.6M) [application/x-gzip]
正在保存至: “redis-4.0.2.tar.gz”

100%[===================================================================================================================================>] 1,713,990    890KB/s 用时 1.9s   

2017-11-22 14:32:09 (890 KB/s) - 已保存 “redis-4.0.2.tar.gz” [1713990/1713990])

[root@localhost ~]#

2 解压：解压安装包;

[root@localhost ~]# tar -zxvf redis-4.0.2.tar.gz 
...
...
redis-4.0.2/utils/releasetools/02_upload_tarball.sh
redis-4.0.2/utils/releasetools/03_test_release.sh
redis-4.0.2/utils/releasetools/04_release_hash.sh
redis-4.0.2/utils/releasetools/changelog.tcl
redis-4.0.2/utils/speed-regression.tcl
redis-4.0.2/utils/whatisdoing.sh
[root@localhost ~]#

3 编译：进入安装包，执行make;

 [root@localhost ~]# cd redis-4.0.2
[root@localhost redis-4.0.2]# pwd
/root/redis-4.0.2
[root@localhost redis-4.0.2]# ll
总用量 280
-rw-rw-r--  1 root root 131381 9月  21 22:12 00-RELEASENOTES
-rw-rw-r--  1 root root     53 9月  21 22:12 BUGS
-rw-rw-r--  1 root root   1815 9月  21 22:12 CONTRIBUTING
-rw-rw-r--  1 root root   1487 9月  21 22:12 COPYING
drwxrwxr-x  6 root root   4096 9月  21 22:12 deps
-rw-rw-r--  1 root root     11 9月  21 22:12 INSTALL
-rw-rw-r--  1 root root    151 9月  21 22:12 Makefile
-rw-rw-r--  1 root root   4223 9月  21 22:12 MANIFESTO
-rw-rw-r--  1 root root  20530 9月  21 22:12 README.md
-rw-rw-r--  1 root root  57764 9月  21 22:12 redis.conf
-rwxrwxr-x  1 root root    271 9月  21 22:12 runtest
-rwxrwxr-x  1 root root    280 9月  21 22:12 runtest-cluster
-rwxrwxr-x  1 root root    281 9月  21 22:12 runtest-sentinel
-rw-rw-r--  1 root root   7606 9月  21 22:12 sentinel.conf
drwxrwxr-x  3 root root   4096 9月  21 22:12 src
drwxrwxr-x 10 root root   4096 9月  21 22:12 tests
drwxrwxr-x  8 root root   4096 9月  21 22:12 utils
[root@localhost redis-4.0.2]# make 
cd src && make all
make[1]: 进入目录“/root/redis-4.0.2/src”
    CC Makefile.dep
make[1]: 离开目录“/root/redis-4.0.2/src”
make[1]: 进入目录“/root/redis-4.0.2/src”
...
...
    INSTALL redis-check-rdb
    INSTALL redis-check-aof

Hint: It's a good idea to run 'make test' ;)

make[1]: 离开目录“/root/redis-4.0.2/src”
[root@localhost redis-4.0.2]#

4 启动Redis；

 [root@localhost redis-4.0.2]# /root/redis-4.0.2/src/redis-server 
1745:C 22 Nov 14:39:22.049 # oO0OoO0OoO0Oo Redis is starting oO0OoO0OoO0Oo
1745:C 22 Nov 14:39:22.049 # Redis version=4.0.2, bits=64, commit=00000000, modified=0, pid=1745, just started
1745:C 22 Nov 14:39:22.049 # Warning: no config file specified, using the default config. In order to specify a config file use /root/redis-4.0.2/src/redis-server /path/to/redis.conf
1745:M 22 Nov 14:39:22.051 * Increased maximum number of open files to 10032 (it was originally set to 1024).
                _._                                                  
           _.-``__ ''-._                                             
      _.-``    `.  `_.  ''-._           Redis 4.0.2 (00000000/0) 64 bit
  .-`` .-```.  ```\/    _.,_ ''-._                                   
 (    '      ,       .-`  | `,    )     Running in standalone mode
 |`-._`-...-` __...-.``-._|'` _.-'|     Port: 6379
 |    `-._   `._    /     _.-'    |     PID: 1745
  `-._    `-._  `-./  _.-'    _.-'                                   
 |`-._`-._    `-.__.-'    _.-'_.-'|                                  
 |    `-._`-._        _.-'_.-'    |           http://redis.io        
  `-._    `-._`-.__.-'_.-'    _.-'                                   
 |`-._`-._    `-.__.-'    _.-'_.-'|                                  
 |    `-._`-._        _.-'_.-'    |                                  
  `-._    `-._`-.__.-'_.-'    _.-'                                   
      `-._    `-.__.-'    _.-'                                       
          `-._        _.-'                                           
              `-.__.-'                                               

1745:M 22 Nov 14:39:22.052 # WARNING: The TCP backlog setting of 511 cannot be enforced because /proc/sys/net/core/somaxconn is set to the lower value of 128.
1745:M 22 Nov 14:39:22.052 # Server initialized
1745:M 22 Nov 14:39:22.052 # WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.
1745:M 22 Nov 14:39:22.052 # WARNING you have Transparent Huge Pages (THP) support enabled in your kernel. This will create latency and memory usage issues with Redis. To fix this issue run the command 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' as root, and add it to your /etc/rc.local in order to retain the setting after a reboot. Redis must be restarted after THP is disabled.
1745:M 22 Nov 14:39:22.052 * Ready to accept connections

至此，Redis已经安装完毕。

5 通过redis-cli，访问和使用Redis：

 [root@localhost ~]# /root/redis-4.0.2/src/redis-cli 
127.0.0.1:6379> info
# Server
redis_version:4.0.2
redis_git_sha1:00000000
redis_git_dirty:0
redis_build_id:1e096713e5234ce6
redis_mode:standalone
os:Linux 3.10.0-327.el7.x86_64 x86_64
...
....
127.0.0.1:6379> set a testredis
OK
127.0.0.1:6379> get a
"testredis"
127.0.0.1:6379>

接着看文档，继续动手实践。

zookeeper启动之后又异常死掉的处理

6月 22,2017 留下评论 Linux

通过in-place的方式把一台CentOS 6.5的Linux机器升级到Centos 7.2之后，发现该机器上之前运行的zookeeper启动异常，记录如下：

 
[root@zkserver1 bin]# sh zkServer.sh start
JMX enabled by default
Using config: /usr/local/zookeeper-3.4.5/bin/../conf/zoo.cfg
Starting zookeeper ... STARTED
[root@zkserver1 bin]# ps -ef|grep zook
root       891   843  0 14:10 pts/0    00:00:00 grep --color=auto zook
[root@zkserver1 bin]# cat zookeeper.out 
2017-06-22 14:10:04,154 [myid:] - INFO  [main:QuorumPeerConfig@101] - Reading configuration from: /usr/local/zookeeper-3.4.5/bin/../conf/zoo.cfg
2017-06-22 14:10:04,166 [myid:] - INFO  [main:DatadirCleanupManager@78] - autopurge.snapRetainCount set to 3
2017-06-22 14:10:04,167 [myid:] - INFO  [main:DatadirCleanupManager@79] - autopurge.purgeInterval set to 0
2017-06-22 14:10:04,168 [myid:] - INFO  [main:DatadirCleanupManager@101] - Purge task is not scheduled.
2017-06-22 14:10:04,168 [myid:] - WARN  [main:QuorumPeerMain@113] - Either no config or no quorum defined in config, running  in standalone mode
2017-06-22 14:10:04,190 [myid:] - INFO  [main:QuorumPeerConfig@101] - Reading configuration from: /usr/local/zookeeper-3.4.5/bin/../conf/zoo.cfg
2017-06-22 14:10:04,191 [myid:] - INFO  [main:ZooKeeperServerMain@95] - Starting server
2017-06-22 14:10:04,206 [myid:] - INFO  [main:Environment@100] - Server environment:zookeeper.version=3.4.5-1392090, built on 09/30/2012 17:52 GMT
2017-06-22 14:10:04,207 [myid:] - INFO  [main:Environment@100] - Server environment:host.name=zkserver1
2017-06-22 14:10:04,207 [myid:] - INFO  [main:Environment@100] - Server environment:java.version=1.7.0_21
2017-06-22 14:10:04,208 [myid:] - INFO  [main:Environment@100] - Server environment:java.vendor=Oracle Corporation
2017-06-22 14:10:04,209 [myid:] - INFO  [main:Environment@100] - Server environment:java.home=/usr/java/jdk1.7.0_21/jre
2017-06-22 14:10:04,209 [myid:] - INFO  [main:Environment@100] - Server environment:java.class.path=/usr/local/zookeeper-3.4.5/bin/../build/classes:/usr/local/zookeeper-3.4.5/bin/../build/lib/*.jar:/usr/local/zookeeper-3.4.5/bin/../lib/slf4j-log4j12-1.6.1.jar:/usr/local/zookeeper-3.4.5/bin/../lib/slf4j-api-1.6.1.jar:/usr/local/zookeeper-3.4.5/bin/../lib/netty-3.2.2.Final.jar:/usr/local/zookeeper-3.4.5/bin/../lib/log4j-1.2.15.jar:/usr/local/zookeeper-3.4.5/bin/../lib/jline-0.9.94.jar:/usr/local/zookeeper-3.4.5/bin/../zookeeper-3.4.5.jar:/usr/local/zookeeper-3.4.5/bin/../src/java/lib/*.jar:/usr/local/zookeeper-3.4.5/bin/../conf:
2017-06-22 14:10:04,210 [myid:] - INFO  [main:Environment@100] - Server environment:java.library.path=/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
2017-06-22 14:10:04,211 [myid:] - INFO  [main:Environment@100] - Server environment:java.io.tmpdir=/tmp
2017-06-22 14:10:04,212 [myid:] - INFO  [main:Environment@100] - Server environment:java.compiler=
2017-06-22 14:10:04,212 [myid:] - INFO  [main:Environment@100] - Server environment:os.name=Linux
2017-06-22 14:10:04,213 [myid:] - INFO  [main:Environment@100] - Server environment:os.arch=amd64
2017-06-22 14:10:04,214 [myid:] - INFO  [main:Environment@100] - Server environment:os.version=3.10.0-327.el7.x86_64
2017-06-22 14:10:04,215 [myid:] - INFO  [main:Environment@100] - Server environment:user.name=root
2017-06-22 14:10:04,215 [myid:] - INFO  [main:Environment@100] - Server environment:user.home=/root
2017-06-22 14:10:04,216 [myid:] - INFO  [main:Environment@100] - Server environment:user.dir=/usr/local/zookeeper-3.4.5/bin
2017-06-22 14:10:04,227 [myid:] - INFO  [main:ZooKeeperServer@726] - tickTime set to 2000
2017-06-22 14:10:04,228 [myid:] - INFO  [main:ZooKeeperServer@735] - minSessionTimeout set to -1
2017-06-22 14:10:04,229 [myid:] - INFO  [main:ZooKeeperServer@744] - maxSessionTimeout set to -1
2017-06-22 14:10:04,274 [myid:] - INFO  [main:NIOServerCnxnFactory@94] - binding to port 0.0.0.0/0.0.0.0:2181
2017-06-22 14:10:04,343 [myid:] - ERROR [main:FileTxnSnapLog@210] - Parent /dubbo/com.onlyou.es.bck.service.IUserStoreService/consumers missing for /dubbo/com.onlyou.es.bck.service.IUserStoreService/consumers/consumer%3A%2F%2F172.16.11.73%2Fcom.onlyou.es.bck.service.IUserStoreService%3Fapplication%3Des-client-web%26category%3Dconsumers%26check%3Dfalse%26default.check%3Dfalse%26default.reference.filter%3DdubboConsumerFilter%26default.retries%3D0%26default.timeout%3D1000000%26dubbo%3D2.5.3%26interface%3Dcom.onlyou.es.bck.service.IUserStoreService%26methods%3DgetUserStoreByCorpIdAndStoreId%2CdeleteStoreUserById%2ClistStoreCode%2CgetUserStoreByUserId%2CsearchStoreIdsByUserId%2CsearchUserStaffList%2CsaveStoreUser%2CgetUserStoreByCorpId%2CsearchStoreInfosByUserId%26pid%3D488%26revision%3Ddev-SNAPSHOT%26side%3Dconsumer%26timestamp%3D1496999541671
2017-06-22 14:10:04,345 [myid:] - ERROR [main:ZooKeeperServerMain@63] - Unexpected exception, exiting abnormally
java.io.IOException: Failed to process transaction type: 1 error: KeeperErrorCode = NoNode for /dubbo/com.onlyou.es.bck.service.IUserStoreService/consumers
        at org.apache.zookeeper.server.persistence.FileTxnSnapLog.restore(FileTxnSnapLog.java:153)
        at org.apache.zookeeper.server.ZKDatabase.loadDataBase(ZKDatabase.java:223)
        at org.apache.zookeeper.server.ZooKeeperServer.loadData(ZooKeeperServer.java:250)
        at org.apache.zookeeper.server.ZooKeeperServer.startdata(ZooKeeperServer.java:377)
        at org.apache.zookeeper.server.NIOServerCnxnFactory.startup(NIOServerCnxnFactory.java:122)
        at org.apache.zookeeper.server.ZooKeeperServerMain.runFromConfig(ZooKeeperServerMain.java:112)
        at org.apache.zookeeper.server.ZooKeeperServerMain.initializeAndRun(ZooKeeperServerMain.java:86)
        at org.apache.zookeeper.server.ZooKeeperServerMain.main(ZooKeeperServerMain.java:52)
        at org.apache.zookeeper.server.quorum.QuorumPeerMain.initializeAndRun(QuorumPeerMain.java:116)
        at org.apache.zookeeper.server.quorum.QuorumPeerMain.main(QuorumPeerMain.java:78)
Caused by: org.apache.zookeeper.KeeperException$NoNodeException: KeeperErrorCode = NoNode for /dubbo/com.onlyou.es.bck.service.IUserStoreService/consumers
        at org.apache.zookeeper.server.persistence.FileTxnSnapLog.processTransaction(FileTxnSnapLog.java:211)
        at org.apache.zookeeper.server.persistence.FileTxnSnapLog.restore(FileTxnSnapLog.java:151)
        ... 9 more
[root@zkserver1 bin]#

原因：可能是服务器升级的过程中，导致zookeeper的数据文件被”破坏”了，导致重启zookeeper时异常。

解决：删除zookeeper的数据文件/tmp/zookeeper，然后重启zookeeper。

一则PostgreSQL数据库的性能问题定位处理

6月 2,2017 留下评论 Linux, PostgreSQL

一、先看一台运行PostgreSQL数据库服务器的top和iostat相关信息图：

top:

[root@db2 ~ 11:14:42]$ top

top - 11:16:10 up 2 days, 13:01,  5 users,  load average: 51.62, 41.75, 29.06
Tasks: 948 total,   5 running, 943 sleeping,   0 stopped,   0 zombie
Cpu(s):  9.0%us, 59.2%sy,  0.0%ni, 14.1%id, 17.2%wa,  0.0%hi,  0.5%si,  0.0%st
Mem:  16284812k total, 16159260k used,   125552k free,     5700k buffers
Swap:  8191992k total,   107980k used,  8084012k free,  8010540k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                
31450 postgres  20   0 4534m 2.3g 2.2g D 46.6 15.0   7:02.50 postgres                                                
30166 postgres  20   0 4528m 2.3g 2.2g D 46.0 14.9   4:05.40 postgres                                                
30185 postgres  20   0 4494m 2.2g 2.1g D 43.5 14.1   4:05.08 postgres                                                
31453 postgres  20   0 4529m 1.9g 1.8g D 42.9 12.3   3:39.13 postgres                                                
30232 postgres  20   0 4526m 2.3g 2.1g D 40.4 14.6   6:07.51 postgres                                                
 2811 postgres  20   0 4521m 874m 742m D 39.8  5.5   1:36.34 postgres                                                
31457 postgres  20   0 4534m 1.8g 1.7g R 39.5 11.6   3:55.23 postgres                                                
30956 postgres  20   0 4521m 1.7g 1.5g D 38.8 10.8   2:14.67 postgres                                                
 2714 postgres  20   0 4519m 1.4g 1.3g D 37.9  9.1   1:19.96 postgres                                                
30182 postgres  20   0 4525m 1.9g 1.8g D 37.9 12.5   3:40.31 postgres                                                
31444 postgres  20   0 4525m 2.2g 2.0g D 37.6 13.9   3:29.11 postgres                                                
31654 postgres  20   0 4526m 2.4g 2.2g D 36.7 15.3   5:04.19 postgres                                                
 2717 postgres  20   0 4519m 847m 718m D 36.1  5.3   1:37.20 postgres                                                
  753 postgres  20   0 4533m 3.7g 3.6g D 35.4 23.9  27:52.65 postgres                                                
31451 postgres  20   0 4433m 1.8g 1.7g D 35.4 11.5   2:36.85 postgres                                                
30701 postgres  20   0 4520m 1.7g 1.6g D 35.1 11.1   2:09.85 postgres                                                
31448 postgres  20   0 4435m 2.2g 2.1g D 33.3 13.9   4:16.70 postgres                                                
29192 postgres  20   0 4526m 2.3g 2.1g D 32.6 14.6   4:19.91 postgres                                                
30693 postgres  20   0 4525m 1.9g 1.8g D 32.0 12.4   2:29.72 postgres                                                
 3448 postgres  20   0 4451m 383m 305m D 31.4  2.4   0:49.98 postgres                                                
 3568 postgres  20   0 4388m 131m 113m D 29.5  0.8   0:10.03 postgres                                                
 3435 postgres  20   0 4376m 364m 355m D 29.2  2.3   0:12.23 postgres                                                
[root@db2 ~ 11:16:22]$

iostat:

 
[root@db2 ~ 11:16:25]$ iostat 1 10 /dev/emcpowerf 
Linux 2.6.32-279.el6.x86_64 (db2) 	2016年06月02日 	_x86_64_	(24 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.51    0.00    0.84    2.04    0.00   92.61

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf       210.87     10256.34     11724.86 2257542690 2580782824

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.07    0.00   80.00    7.12    0.00    2.82

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf     34443.00   3095056.00   1747336.00    3095056    1747336

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8.38    0.00   90.25    1.25    0.00    0.13

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf        83.79      7430.09     14483.79      89384     174240

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.00    0.00   70.16   10.69    0.00    1.14

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf      2887.25    323498.04     71545.10     329968      72976

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.76    0.00   86.36    3.17    0.00    0.71

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf      1996.00    222072.00     34056.00     222072      34056

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           7.75    0.00   89.37    2.50    0.00    0.38

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf      2344.00    229568.00    149152.00     229568     149152

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.68    0.00   82.81    5.88    0.00    1.63

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf      2585.00    242224.00    154320.00     242224     154320

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          14.57    0.00   24.36   34.20    0.00   26.87

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf      2692.08    283271.29    137940.59     286104     139320

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          11.80    0.00   85.10    2.02    0.00    1.07

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf      3367.33    268491.09    124879.21     271176     126128

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.77    0.00   85.36    4.32    0.00    0.54

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
emcpowerf      2792.00    274976.00    143192.00     274976     143192

[root@db2 ~ 11:23:07]$

以及，文件系统结构图：

[root@db2 ~ 11:03:54]$ df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext4     58G   14G   42G  25% /
tmpfs        tmpfs    7.8G     0  7.8G   0% /dev/shm
/dev/sda3     ext4    210G   54G  146G  27% /data/log
/dev/emcpowerd1
              ext4     99G   58G   37G  62% /data/pgsql/backup
/dev/emcpowerc1
              ext4     99G   89G  5.5G  95% /data/pgsql/data_old
/dev/emcpowerf
              ext4    197G  146G   42G  78% /data/pgsql/data
/dev/emcpowerg
              ext4    493G   66G  403G  14% /data/pgsql/backup/archive
[root@db2 ~ 11:04:14]$

二结论：数据库服务器已经出现性能瓶颈，具体问题出在系统IO上。通常情况是，应用系统的SQL写的不够好，或者是糟糕的SQL执行计划导致消耗过高的IO资源。

三分析思路

1 从top上，看到系统负载已经比较高：load average: 51.62, 41.75, 29.06

2 CPU负载上看，Cpu(s): 9.0%us, 59.2%sy, 0.0%ni, 14.1%id, 17.2%wa, 0.0%hi, 0.5%si, 0.0%st 。

其中， 9.0%us用于user space process处理较为正常。但是在系统负载较高的情况下，且有59.2%sy的CPU资源用于system kernel running。The amount of time spent in the kernel should be as low as possible.This number can peak much higher, especially when there is a lot of I/O happening.这里，意味着CPU消耗在用于系统调度上的时间偏高了，尤其是当系统出现大量IO操作时，该值可能非常高。这里的59%略高，系统有IO等待操作。

另，14.1%id, 系统CPU闲置时间比较低，通常情况下我们希望它不要太低，太低了说明CPU太忙，太高也不好，太高了意味着CPU一直处于空闲状态。17.2%wa,CPU用于等待IO操作完成的时间占比。这里，CPU在等待IO完成。

3 进程状态列，有大量的进程处于D状态。进程状态通常有：

R：TASK_RUNNING，可执行状态。处于此状态的进程正在CPU上运行；同一时间，少量进程处于该状态；

S：TASK_INTERRUPTIBLE，可中断睡眠状态。处于该状态的进程因为正在等待某些事件发生(如等待socket连接，等待信号量),而事件还未发生，所以被挂起；同一时间，大量进程处于该状态

D：TASK_UNINTERRUPTIBLE，不可中断睡眠状态。该状态的进程同样处于休眠状态，但是该进程不能被其它事件或信号所打断。常见于，在进程对硬件设备进行操作时(如进程调用read系统来对某个设备文件进行读操作，read系统需要调用到最终执行对应设备的驱动代码，并且与对应设备进行交互时)，可能需要使用TASK_UNINTERRUPTIBLE状态来保护进程，以避免进程在与设备进行交互的过程中被打断，最终造成设备陷入不可控的状态。

结合本例，可以看到有大量进程处于D状态，即不可中断的睡眠状态。说明有大量的数据库进程在与操作系统的IO系统进行交互，且这些进程不能被中断。说白了，就是进程在进行的IO操作迟迟未完成，且该进程不能被打断。就是系统出现了大量的IO等待。

4 从iostat上，也可以看到用于存放数据库的设备上IO操作频繁，该设备的IO请求很高。

这是去年今日(2016年6月2日)处理的一则PostgreSQL数据库性能问题定位的case，不是特意要放在今天(2017年6月2日)来说。而是，近期在看几篇Linux文档，加上之前翻看《PostgreSQL 9 Administration Cookbook》这本书，P249里面提到：

Not enough CPU power or disk I/O capacity for the current load

These are usually caused by suboptimal query plans, but sometimes you just have not a powerful enough computer.

Here, top is your friend for quick checks, and from the command line, run the following:

user@host:~$ top

First, watch the CPU idle reading in the top. If this is in low single-digits for most of the time, you probably have problems with CPU power.

If you have a high load average with still lots of CPU idle left, you are probably out of disk bandwidth. In this case, you should also have lots of postgres processes in status D.

索性，做一个整理总结，便于理清自己处理性能问题时，能有一个个清晰的思路和线索。

四参考：

1 Understanding Linux CPU stats

2 top进程状态详解R,S,D

CentOS 6.5升级至CentOS 7完整步骤及注意事项

5月 31,2017 留下评论

一背景：

1 公司内部大量开发、测试、UAT环境机器都是CentOS 6.5的物理机/虚拟机；
2 公司在阿里云上的ECS机器环境同样多数为CentOS 6.5;
3 近期，公司内部在推Docker技术，而相对稳定且版本不至于太低的docker，需要部署在至少RHEL/CentOS 7.0以上版本的操作系统上。

二说明：

本文档用于记录升级Centos 6.5到CentOS 7.2版本的操作步骤，以及升级后带来的问题和解决办法。升级前，本机的OS版本、内核版本、文件系统信息如下：

[root@dev-middleware ~]# cat /etc/redhat-release 
CentOS release 6.5 (Final)
[root@dev-middleware ~]# uname -rm
2.6.32-431.el6.x86_64 x86_64
[root@dev-middleware ~]# df -Th
Filesystem                   Type   Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root ext4    50G   40G  7.2G  85% /
tmpfs                        tmpfs  1.9G     0  1.9G   0% /dev/shm
/dev/sda1                    ext4   485M   32M  428M   7% /boot
/dev/mapper/VolGroup-lv_home ext4   144G  193M  136G   1% /home
[root@dev-middleware ~]#

根据从网络上的获取的相关参考信息，本文档中的内容和步骤并不适用于Centos其它版本6.X升级至CentOS 7的参考。且，该升级是in-place-upgrade的升级方式，即直接在原机上执行升级的操作，存在一定风险。而官方推荐的非in-place-upgrade的方式，即先备份相关的文件和数据，然后安装CentOS 7系统，再恢复还原相关数据的作法，相对比较安全可控。

三升级：

1 添加1个yum的repo文件：

vi /etc/yum.repo.d/upgrade.repo
内容为：

[upg]
name=CentOS-$releasever - Upgrade Tool
baseurl=http://dev.centos.org/centos/6/upg/x86_64/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6

2 安装升级工具：

yum install redhat-upgrade-tool preupgrade-assistant-contents

3 执行预升级检查：

preupg -l

preupg -s CentOS6_7

如果有类似下述错误：

I/O warning : failed to load external entity "/usr/share/openscap/xsl/security-guide.xsl"
compilation error: file /usr/share/preupgrade/xsl/preup.xsl line 40 element import
xsl:import : unable to load /usr/share/openscap/xsl/security-guide.xsl
I/O warning : failed to load external entity "/usr/share/openscap/xsl/oval-report.xsl"
compilation error: file /usr/share/preupgrade/xsl/preup.xsl line 41 element import
xsl:import : unable to load /usr/share/openscap/xsl/oval-report.xsl
I/O warning : failed to load external entity "/usr/share/openscap/xsl/sce-report.xsl"
compilation error: file /usr/share/preupgrade/xsl/preup.xsl line 42 element import
xsl:import : unable to load /usr/share/openscap/xsl/sce-report.xsl
OpenSCAP Error:: Could not parse XSLT file '/usr/share/preupgrade/xsl/preup.xsl' [oscapxml.c:416]
Unable to open file /root/preupgrade/result.html
Usage: preupg [options]
preupg: error: [Errno 2] No such file or directory: '/root/preupgrade/result.html'

则，原因是openscap软件包的版本太高。通过下述先删除，再安装的来办法解决：

yum erase openscap
yum install http://dev.centos.org/centos/6/upg/x86_64/Packages/openscap-1.0.8-1.0.1.el6.centos.x86_64.rpm
yum install redhat-upgrade-tool preupgrade-assistant-contents

4 再次执行，预升级检查：

preupg -s CentOS6_7

执行结果：

....
094/096 ...done (NIS server maps check)
095/096 ...done (NIS server MAXUID and MAXGID limits check)
096/096 ...done (NIS server config file back-up)
Assessment finished (time 02:15s)
Result table with checks and their results for main contents:
....

|Content for enabling and disabling services based on CentOS 6 system |needs_action |
---------------------------------------------------------------------------------------------------------------
Tarball with results is stored here /root/preupgrade-results/preupg_results-170510111635.tar.gz .
The latest assessment is stored in directory /root/preupgrade .
Summary information:
We found some potential in-place upgrade risks.
Read the file /root/preupgrade/result.html for more details.
Upload results to UI by command:
e.g. preupg -u http://127.0.0.1:8099/submit/ -r /root/preupgrade-results/preupg_results-*.tar.gz .

执行升级的过程中，可能会有的一些不兼容问题，则都在上述的预升级检查结果中，需要关注并处理。

5 导入仓库的RPM签名证书

 rpm --import http://172.16.11.36/yum/test/RPM-GPG-KEY-CentOS-7

需要说明的是，这里导入的是使用本地搭建的一个yum源的签名证书。这个本地源，是基于从网络上下载的CentOS-7-x86_64-DVD-1611.iso进行搭建的。当然，并不一定非要搭建本地yum源，也可以直接从下载的该ISO文件中，获取RPM-GPG-KEY-CentOS-7，直接在本地机器执行导入。为什么这么做，后面会有详细的解释和说明。
该文件的内容如下：

[root@localhost test]# cat RPM-GPG-KEY-CentOS-7
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.5 (GNU/Linux)

mQINBFOn/0sBEADLDyZ+DQHkcTHDQSE0a0B2iYAEXwpPvs67cJ4tmhe/iMOyVMh9
Yw/vBIF8scm6T/vPN5fopsKiW9UsAhGKg0epC6y5ed+NAUHTEa6pSOdo7CyFDwtn
4HF61Esyb4gzPT6QiSr0zvdTtgYBRZjAEPFVu3Dio0oZ5UQZ7fzdZfeixMQ8VMTQ
4y4x5vik9B+cqmGiq9AW71ixlDYVWasgR093fXiD9NLT4DTtK+KLGYNjJ8eMRqfZ
Ws7g7C+9aEGHfsGZ/SxLOumx/GfiTloal0dnq8TC7XQ/JuNdB9qjoXzRF+faDUsj
WuvNSQEqUXW1dzJjBvroEvgTdfCJfRpIgOrc256qvDMp1SxchMFltPlo5mbSMKu1
x1p4UkAzx543meMlRXOgx2/hnBm6H6L0FsSyDS6P224yF+30eeODD4Ju4BCyQ0jO
IpUxmUnApo/m0eRelI6TRl7jK6aGqSYUNhFBuFxSPKgKYBpFhVzRM63Jsvib82rY
438q3sIOUdxZY6pvMOWRkdUVoz7WBExTdx5NtGX4kdW5QtcQHM+2kht6sBnJsvcB
JYcYIwAUeA5vdRfwLKuZn6SgAUKdgeOtuf+cPR3/E68LZr784SlokiHLtQkfk98j
NXm6fJjXwJvwiM2IiFyg8aUwEEDX5U+QOCA0wYrgUQ/h8iathvBJKSc9jQARAQAB
tEJDZW50T1MtNyBLZXkgKENlbnRPUyA3IE9mZmljaWFsIFNpZ25pbmcgS2V5KSA8
c2VjdXJpdHlAY2VudG9zLm9yZz6JAjUEEwECAB8FAlOn/0sCGwMGCwkIBwMCBBUC
CAMDFgIBAh4BAheAAAoJECTGqKf0qA61TN0P/2730Th8cM+d1pEON7n0F1YiyxqG
QzwpC2Fhr2UIsXpi/lWTXIG6AlRvrajjFhw9HktYjlF4oMG032SnI0XPdmrN29lL
F+ee1ANdyvtkw4mMu2yQweVxU7Ku4oATPBvWRv+6pCQPTOMe5xPG0ZPjPGNiJ0xw
4Ns+f5Q6Gqm927oHXpylUQEmuHKsCp3dK/kZaxJOXsmq6syY1gbrLj2Anq0iWWP4
Tq8WMktUrTcc+zQ2pFR7ovEihK0Rvhmk6/N4+4JwAGijfhejxwNX8T6PCuYs5Jiv
hQvsI9FdIIlTP4XhFZ4N9ndnEwA4AH7tNBsmB3HEbLqUSmu2Rr8hGiT2Plc4Y9AO
aliW1kOMsZFYrX39krfRk2n2NXvieQJ/lw318gSGR67uckkz2ZekbCEpj/0mnHWD
3R6V7m95R6UYqjcw++Q5CtZ2tzmxomZTf42IGIKBbSVmIS75WY+cBULUx3PcZYHD
ZqAbB0Dl4MbdEH61kOI8EbN/TLl1i077r+9LXR1mOnlC3GLD03+XfY8eEBQf7137
YSMiW5r/5xwQk7xEcKlbZdmUJp3ZDTQBXT06vavvp3jlkqqH9QOE8ViZZ6aKQLqv
pL+4bs52jzuGwTMT7gOR5MzD+vT0fVS7Xm8MjOxvZgbHsAgzyFGlI1ggUQmU7lu3
uPNL0eRx4S1G4Jn5
=OGYX
-----END PGP PUBLIC KEY BLOCK-----
[root@localhost test]#

6 执行升级：

 
centos-upgrade-tool-cli --network 7 --instrepo=http://vault.centos.org/centos/7.2.1511/os/x86_64/
setting up repos...
.treeinfo | 1.1 kB 00:00
Preupgrade assistant risk check found risks for this upgrade.
You can run preupg --riskcheck --verbose to view these risks.
Addressing high risk issues is required before the in-place upgrade
and ignoring these risks may result in a broken upgrade and unsupported upgrade.
Please backup your data.
...
...

(276/277): zlib-1.2.7-17.el7.x86_64.rpm | 90 kB 00:00
(277/277): zlib-devel-1.2.7-17.el7.x86_64.rpm | 50 kB 00:00
testing upgrade transaction
rpm transaction 100% [======================================================================================================================================================]
rpm install 100% [==========================================================================================================================================================]
setting up system for upgrade
Finished. Reboot to start upgrade.

7 根据提示，重启升级。

升级后的OS、内核版本、文件系统信息如下：

[root@dev-middleware conf.d]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
[root@dev-middleware conf.d]# uname -rm
3.10.0-327.el7.x86_64 x86_64
[root@dev-middleware conf.d]# df -Th
文件系统 类型 容量 已用 可用 已用% 挂载点
/dev/mapper/VolGroup-lv_root ext4 50G 43G 4.3G 91% /
devtmpfs devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs tmpfs 1.9G 0 1.9G 0% /dev/shm
tmpfs tmpfs 1.9G 80M 1.8G 5% /run
tmpfs tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/sda1 ext4 477M 110M 338M 25% /boot
/dev/mapper/VolGroup-lv_home ext4 144G 354M 136G 1% /home
[root@dev-middleware conf.d]#

四遇到的问题，及解决办法

1 Downloading failed: invalid data in .treeinfo: No option ‘upgrade’ in section: ‘images-x86_64’
升级过程中，可能会遇到：No option ‘upgrade’ in section: ‘images-x86_64’：

redhat-upgrade-tool --network 7.0 --force --instrepo  http://mirrors.aliyun.com/centos/7.3.1611/os/x86_64/
setting up repos...
YumRepo Error: All mirror URLs are not using ftp, http[s] or file.
 Eg. Invalid release/
removing mirrorlist with no valid mirrors: /var/tmp/system-upgrade/base/mirrorlist.txt
YumRepo Error: All mirror URLs are not using ftp, http[s] or file.
 Eg. Invalid release/
removing mirrorlist with no valid mirrors: /var/tmp/system-upgrade/extras/mirrorlist.txt
YumRepo Error: All mirror URLs are not using ftp, http[s] or file.
 Eg. Invalid release/
removing mirrorlist with no valid mirrors: /var/tmp/system-upgrade/updates/mirrorlist.txt
No upgrade available for the following repos: base extras updates
.treeinfo                                                                                                                                             |  946 B     00:00     
getting boot images...

Downloading failed: invalid data in .treeinfo: No option 'upgrade' in section: 'images-x86_64'

原因，如果使用互联上的签名文件的话，互联网上的yum源都会有相同的报错，.treeinfo: No option ‘upgrade’ in section: ‘images-x86_64’。即，网上的yum源下的文件.treeinfo里都没有upgrade选项。所以，经过测试，才使用了上述升级步骤中的导入本地rpm签名证书。

2 升级后，遇到nginx启动报错，Starting nginx: /usr/sbin/nginx: error while loading shared libraries: libpcre.so.0: cannot open shared object file: No such file or directory

[root@dev-middleware ~]# systemctl status nginx.service
● nginx.service - LSB: start and stop nginx
Loaded: loaded (/etc/rc.d/init.d/nginx; bad; vendor preset: disabled)
Active: failed (Result: exit-code) since 六 2017-05-27 16:49:28 CST; 5min ago
Docs: man:systemd-sysv-generator(8)

5月 27 16:49:28 dev-middleware systemd[1]: Starting LSB: start and stop nginx...
5月 27 16:49:28 dev-middleware nginx[1661]: Starting nginx: /usr/sbin/nginx: error while loading shared libraries: libpcre.so.0: cannot open shared object file: No such file or directory
5月 27 16:49:28 dev-middleware nginx[1661]: [失败]
5月 27 16:49:28 dev-middleware systemd[1]: nginx.service: control process exited, code=exited status=1
5月 27 16:49:28 dev-middleware systemd[1]: Failed to start LSB: start and stop nginx.
5月 27 16:49:28 dev-middleware systemd[1]: Unit nginx.service entered failed state.
5月 27 16:49:28 dev-middleware systemd[1]: nginx.service failed.
[root@dev-middleware ~]#

解决办法：

a 查找libpcre.so文件

 
[root@dev-middleware ~]# find / -name libpcre.so*
/usr/lib64/libpcre.so.1.2.0
/usr/lib64/libpcre.so
/usr/lib64/libpcre.so.1
/usr/lib64/libpcre.so.0
[root@dev-middleware ~]#

b 创建链接文件

 
[root@dev-middleware ~]# link /usr/lib64/libpcre.so.1 /lib64/libpcre.so.0

c 输出LD_LIBRARY_PATH环境变量

 
[root@dev-middleware ~]# export LD_LIBRARY_PATH=/usr/local/lib:/lib64:$LD_LIBRARY_PATH
[root@dev-middleware ~]# echo $LD_LIBRARY_PATH
/usr/local/lib:/lib64:/usr/lib64:/usr/local/lib:
[root@dev-middleware ~]#

d 相关链接：

链接：
关于共享库找不到：
http://sempike.blogspot.com/2016/02/update-to-centos-7-libpcreso0-no-such.html
关于nginx启动报错：
http://blog.csdn.net/bzhxuexi/article/details/35821121
为防止以后出错，还是把export LD_LIBRARY_PATH=/usr/local/lib:/lib64:$LD_LIBRARY_PATH写入root的profile。

3 升级后，遇到类似问题：/ppas/9.3as/bin/edb-postgres: error while loading shared libraries: libsasl2.so.2: cannot open shared object file: No such file or directory

这是，之前一台运行CentOS 6.5的机器，上面跑着PostgreSQL数据库，升级到CentOS 7版本之后，数据库启动失败。

[enterprisedb@localhost ~]$ pg_ctl start
/ppas/9.3as/bin/edb-postgres: error while loading shared libraries: libsasl2.so.2: cannot open shared object file: No such file or directory
no data was returned by command ""/ppas/9.3as/bin/edb-postgres" -V"
The program "edb-postgres" is needed by pg_ctl but was not found in the
same directory as "/ppas/9.3as/bin/pg_ctl".
Check your installation.
[enterprisedb@localhost ~]$