金庸武功之“”左右互搏术“”postgresql 主从异步流复制配置
|
一.环境准备 a.关闭selinxu b.关闭iptables c.centos6.5 d.postgresql9.4.4 master：192.168.1.211 slave: 192.168.1.212 时间同步： #同步系统时间 [root@localhost ~]# rm -rf /etc/localtime [root@localhost ~]# ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime [root@localhost ~]# yum install -y ntpdate [root@localhost ~]# /usr/sbin/ntpdate -u 202.120.2.101 && hwclock -w
[root@localhost ~]# systemctl enable ntpdate && systemctl start ntpdate # 如果在启动ntp时报错，那是端口号被占用了，如下处理 [root@localhost ~]# yum install -y lsof [root@localhost ~]# lsof -i:123 [root@localhost ~]# kill -9 $进程号 [root@localhost ~]# systemctl enable ntpdate && systemctl start ntpdate [root@localhost ~]# crontab -e */5 * * * * /usr/sbin/ntpdate -u 202.120.2.101 && hwclock -w 二.安装，master && slave 一样 四、源码包安装 1、在三台安装依赖包 yum -y install gcc* yum -y install readline-devel 2、在三台增加用户 # adduser postgres
3.下载PostgreSQL 源码包 # wgethttps://ftp.postgresql.org/pub/source/v9.4.4/postgresql-9.4.4.tar.bz2 4.解压源码包 # tar xjf postgresql-9.4.4.tar.bz2 #需要安装 yum install -y bzip2 5.进入解压后的目录 # cd postgresql-9.4.4 6.开始编译安装PostgreSQL数据库。 [root@postgresql01 postgresql-9.4.4]# ./configure --prefix=/usr/local/pgsql [root@postgresql01 postgresql-9.4.4]#make [root@postgresql01 postgresql-9.4.4]# make install 7.设置环境 pgsql [root@postgresql01 postgres]# vi /etc/profile PATH=$PATH:$HOME/bin:/usr/local/pgsql/bin 保存退出。 让环境变量生效 [root@postgresql01 postgres]# source /etc/profile 8.初始化数据库 8.1新建数据目录 [root@postgresql01 postgres]# mkdir /data/pg/data 8.2更改权限 [root@postgresql01 postgres]# chown postgres:postgres/data/pg/data 8.3切换到postgres用户 [root@postgresql01 postgres]# su - postgres 8.4 init db [postgres@postgresql01 ~]$ /usr/local/pgsql/bin/initdb -D /data/pg/data 到这里数据的初始化就完成 9.系统服务 9.1回到root[postgres@postgresql01 ~]$ exit 9.2复制安装目录下的linux文件/etc/init.d/ 进入postgresql的安装目录 [root@postgresql01 postgres]# cd /root/postgresql-9.4.4/ [root@postgresql01 postgresql-9.4.4]# cp contrib/start-scripts/linux /etc/init.d/postgresql 9.3修改/etc/init.d/postgresql 注意：红色是修改部分 [root@postgresql postgresql-9.4.4]# vi /etc/init.d/postgresql #! /bin/sh # chkconfig: 2345 98 02 # description: PostgreSQL RDBMS # This is an example of a start/stop script for SysV-style init,such # as is used on Linux systems. You should edit some of the variables # and maybe the 'echo' commands. # # Place this file at /etc/init.d/postgresql (or # /etc/rc.d/init.d/postgresql) and make symlinks to # /etc/rc.d/rc0.d/K02postgresql # /etc/rc.d/rc1.d/K02postgresql # /etc/rc.d/rc2.d/K02postgresql # /etc/rc.d/rc3.d/S98postgresql # /etc/rc.d/rc4.d/S98postgresql # /etc/rc.d/rc5.d/S98postgresql # Or,if you have chkconfig,simply: # chkconfig --add postgresql # Proper init scripts on Linux systems normally require setting lock # and pid files under /var/run as well as reacting to network # settings,so you should treat this with care. # Original author: Ryan Kirkpatrick <pgsql@rkirkpat.net> # contrib/start-scripts/linux ## EDIT FROM HERE # Installation prefix prefix=/usr/local/pgsql # Data directory PGDATA="/data/pg/data" # Who to run the postmaster as,usually "postgres". (NOT "root") PGUSER=postgres # Where to keep a log file PGLOG="$PGDATA/serverlog" # It's often a good idea to protect the postmaster from being killed by the # OOM killer (which will tend to preferentially kill the postmaster because # of the way it accounts for shared memory). Setting the OOM_SCORE_ADJ value # to -1000 will disable OOM kill altogether. If you enable this,you probably # want to compile PostgreSQL with "-DLINUX_OOM_SCORE_ADJ=0",so that # individual backends can still be killed by the OOM killer. #OOM_SCORE_ADJ=-1000 # Older Linux kernels may not have /proc/self/oom_score_adj,but instead # /proc/self/oom_adj,which works similarly except the disable value is -17. # For such a system,enable this and compile with "-DLINUX_OOM_ADJ=0". #OOM_ADJ=-17 ## STOP EDITING HERE # The path that is to be used for the script PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin # What to use to start up the postmaster. (If you want the script to wait # until the server has started,you could use "pg_ctl start -w" here. # But without -w,pg_ctl adds no value.) DAEMON="$prefix/bin/postmaster" # What to use to shut down the postmaster PGCTL="$prefix/bin/pg_ctl" set -e # Only start if we can find the postmaster. test -x $DAEMON || { echo "$DAEMON not found" if [ "$1" = "stop" ] then exit 0 else exit 5 fi } # Parse command line parameters. case $1 in start) echo -n "Starting PostgreSQL: " test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1 echo "ok" ;; stop) echo -n "Stopping PostgreSQL: " su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast" restart) echo -n "Restarting PostgreSQL: " su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w" reload) echo -n "Reload PostgreSQL: " su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s" status) su - $PGUSER -c "$PGCTL status -D '$PGDATA'" *) # Print help echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2 exit 1 esac exit 0 9.4启动数据库 [root@postgresql01 postgresql-9.4.4]#cd /etc/init.d [root@postgresql01 postgresql-9.4.4]#chmod +x postgresql [root@postgresql01 postgresql-9.4.4]# /etc/init.d/postgresql start 9.5让数据库开机启动 [root@postgresql01 postgresql-9.4.4]# chkconfig --add postgresql [root@postgresql01 postgresql-9.4.4]# chkconfig postgresql on 9.6创建数据操作历史记录文件 [root@postgresql01 postgresql-9.4.4]# touch /usr/local/pgsql/.pgsql_history [root@postgresql01 postgresql-9.4.4]# chown postgres:postgres /usr/local/pgsql/.pgsql_history 10.测试使用 [postgres@postgresql01 ~]$ createdb test [postgres@postgresql01 ~]$ psql test psql (9.4.4) Type "help" for help. test=# 源码编译安装成功 这里安装以后要把数据库停掉，省的后面端口被占用后，配置改完了，重启不生效，楼主就是这个小问题导致试验了好几次都不成功，切记，装完把数据库停掉。 三.配置master 首先，在 vi pg_hba.conf host all all 192.168.1.0/24 md5 host replication repluser 192.168.1.0/24 md5 其次，vi postgresql.conf listen_addresses = '*' max_connections = 100 wal_level = hot_standby max_wal_senders = 5 wal_keep_segments=16
在主库增加同步的用户名与密码 [postgres@postgresql01 ~]$psql -d postgres psql (9.4.3) Type "help" for help. postgres=#CREATE ROLE repluser REPLICATION LOGIN PASSWORD '123456'; CREATE ROLE postgres=#
四.配置standby 首先把数据库数据目录下的内容删除 cd /data/pg/data rm -rf * 然后执行基础备份 [root@pg-slave data]# pg_basebackup -h 192.168.1.211 -U repluser -F p -x -P -R -D /data/pg/data/ -l repluserbackup20170913 执行完后，由于使用了R选项，所以会生成recovery.conf文件 vi recovery.conf standby_mode = 'on' primary_conninfo = 'user=repluser password=123456 host=192.168.1.211 port=5432 sslmode=disable sslcompression=1' recovery_target_timeline = 'latest' vi postgresql.conf hot_standby = on 启动standby 这里启动时要确保数据库之前没有启动，端口没被占用，这样修改的配置文件才会生效。切记 五.验证 在master上建个表，插入数据 postgres=# create table test01(id int primary key,note text); postgres=# insert into test01 values(1,'22222'); postgres=# insert into test01 values(3,'22222'); postgres=# insert into test01 values(3,'33333'); postgres=# insert into test01 values(4,'44444'); postgres=# select * from test01; id | note ----+------- 1 | 22222 2 | 22222 3 | 33333 4 | 44444 (4 rows) postgres=# 在slave上 postgres=# select * from test01; id | note ----+------- 1 | 22222 2 | 22222 3 | 33333 4 | 44444 (4 rows) 还有一点就是，在master上执行 postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) 而在slave上 postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) 在master postgres=# select client_addr,sync_state from pg_stat_replication; client_addr | sync_state ---------------+------------ 192.168.1.212 | async (1 row) 说明94是从服务器，在接收流，而且是异步流复制。 此外，还可以分别在主、从节点上运行 ps aux | grep postgres 来查看进程： 主服务器（211）上： [postgres@pg-master ~]$ ps aux | grep postgres postgres 1193 0.0 1.2 262720 13020 ? S 16:56 0:00 /usr/local/pgsql/bin/postmaster -D /data/pg/data postgres 1195 0.0 0.3 262820 3020 ? Ss 16:56 0:00 postgres: checkpointer process postgres 1196 0.0 0.2 262720 2228 ? Ss 16:56 0:00 postgres: writer process postgres 1197 0.0 0.5 262720 5192 ? Ss 16:56 0:00 postgres: wal writer process postgres 1198 0.0 0.1 263148 1788 ? Ss 16:56 0:00 postgres: autovacuum launcher process postgres 1199 0.0 0.0 118012 988 ? Ss 16:56 0:00 postgres: stats collector process postgres 1281 0.0 0.2 263352 2340 ? Ss 17:31 0:00 postgres: wal sender process repluser 192.168.1.212(38215) streaming 0/30194E0 root 1414 0.0 0.1 145452 1608 pts/0 S 19:11 0:00 su - postgres postgres 1415 0.0 0.1 108320 1884 pts/0 S 19:11 0:00 -bash postgres 1445 0.0 0.1 110252 1172 pts/0 R+ 19:19 0:00 ps aux postgres 1446 0.0 0.0 103260 876 pts/0 S+ 19:19 0:00 grep postgres 可以看到有一个 wal sender 进程。 从服务器（212）上： [postgres@pg-slave ~]$ ps aux | grep postgres postgres 1155 0.0 1.4 265236 14660 ? S 17:31 0:00 /usr/local/pgsql/bin/postmaster -D /data/pg/data postgres 1156 0.0 0.1 265336 1696 ? Ss 17:31 0:00 postgres: startup process recovering 000000010000000000000003 postgres 1157 0.0 0.2 265336 2520 ? Ss 17:31 0:00 postgres: checkpointer process postgres 1158 0.0 0.2 265236 2048 ? Ss 17:31 0:00 postgres: writer process postgres 1159 0.0 0.0 117860 900 ? Ss 17:31 0:00 postgres: stats collector process postgres 1160 0.0 0.1 269764 1944 ? Ss 17:31 0:03 postgres: wal receiver process streaming 0/30194E0 root 1184 0.0 0.1 145452 1604 pts/0 S 17:49 0:00 su - postgres postgres 1185 0.0 0.1 108320 1876 pts/0 S 17:49 0:00 -bash postgres 1247 0.0 0.1 110252 1168 pts/0 R+ 19:19 0:00 ps aux postgres 1248 0.0 0.0 103260 876 pts/0 S+ 19:19 0:00 grep postgres （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |