一、具体安装环境:
数据库操作系统 | 64位redhst 7.5 |
数据库软件及版本 | Postgresrpm-12包 |
数据库服务器JDK版本 | 64位Open JDK1.8.0_292 for linux |
二、部署步骤
请注意主机初始化,主节点和从节点操作步骤一样(如只部署单节点。则只在一个节点上行执行
以下操作)
1.环境初始化
关闭防火墙
root 用户下执行
## 关闭防火墙 systemctl stop firewalld.service ## 开机启动关闭防火墙 systemctl disable firewalld.service |
关闭SELinux
root 用户下执行
## 关闭SELinux sed -i '/SELINUX=/d' /etc/selinux/config echo "SELINUX=disabled" >> /etc/selinux/config ## 查看SELinux配置 cat /etc/selinux/config|grep -v ^#|grep -v '^$'0 |
配置内核参数
root 用户下执行
## 修改内核参数 echo " fs.file-max = 76724200 kernel.sem = 10000 10240000 10000 1024 kernel.shmmni = 4096 kernel.shmall = 1073741824 kernel.shmmax = 4398046511104 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.wmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_max = 4194304 fs.aio-max-nr = 40960000 vm.dirty_ratio=20 vm.dirty_background_ratio=3 vm.dirty_writeback_centisecs=100 vm.dirty_expire_centisecs=500 vm.swappiness=10 vm.min_free_kbytes=524288">> /etc/sysctl.d/99-sysctl.conf && sysctl --system |
重启操作系统
root 用户下执行
## 重启操作系统,让配置生效 reboot |
2. 安装postgres 数据库
请注意数据库安装,主节点和从节点安装步骤一样(如只部署单节点。则只在一个节点上行执行以下操作)
root 用户下执行
yum install -y libicu-devel libxslt python-devel glibc-devel libmpc |
## 注意安装顺序,root用户执行 rpm -ivh postgresql12-libs-12.4-1PGDG.rhel7.x86_64.rpm yum install -y postgresql12-12.4-1PGDG.rhel7.x86_64.rpm rpm -ivh postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm yum install -y postgresql12-contrib-12.4-1PGDG.rhel7.x86_64.rpm rpm -ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm rpm -ivh libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm rpm -ivh llvm5.0-* yum install -y devtoolset-7-* rpm -ivh llvm-toolset-7-* yum install -y postgresql12-devel-12.4-1PGDG.rhel7.x86_64.rpm rpm -ivh pg_repack12-1.4.6-1.rhel7.x86_64.rpm rpm -ivh python2-psycopg2-2.8.5-2.rhel7.x86_64.rpm yum install -y postgresql12-plpython-12.4-1PGDG.rhel7.x86_64.rpm |
##root用户执行 mkdir -p /data/pgsql/data mkdir -p /data/pgsql/arclog groupadd postgres useradd -g postgres postgres chown -R postgres:postgres /data/pgsql/ |
root用户执行
echo 'export PGHOME=/usr/pgsql-12/ export PGDATA=/data/pgsql/data export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH'>>/etc/profile |
环境变量生效
source /etc/profile |
postgres用户执行
##重请注意标红部分为修改后的值 su - postgres cat .bash_profile [ -f /etc/profile ] && source /etc/profile PGDATA=/data/pgsql/data #仅修改此处一行 export PGDATA # If you want to customize your settings, # Use the file below. This is not overridden # by the RPMS. [ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile |
环境变量生效
## 重启使部分参数生效 source .bash_profile |
postgres用户执行
initdb -k -E UTF8 --locale=C --lc-ctype=zh_CN.UTF8 -D /data/pgsql/data |
启动数据库
postgres用户执行
pg_ctl start -D /data/pgsql/data/ |
3. 主节点配置
主节点配置的操作请都在主节点执行
用户/密码:repuser/repuser
[root@pg ~]# su - postgres Last login: Mon July 9 14:50:35 CST 2024 on pts/0 -bash-4.2$ createuser -U postgres repuser -P -c 5 --replication Enter password for new role: Enter it again: |
设置超级用户密码
postgres用户执行
-bash-4.2$ psql psql (12.4) Type "help" for help. postgres=# alter user postgres with password 'postgres123@123'; ALTER ROLE |
配置pg_hba.conf
postgres用户执行
pg_hba.conf 在/data/pgsql/data目录下
## 请注意 192.168.1.99 为本配置文档中的从节点ip地址,请根据实际情况配置 host replication repuser 192.168.1.99/32 md5 ###(如果现场部署单机版pg库此配置不添加) host all all 0.0.0.0/0 md5 |
配置postgres.conf
postgres用户执行
postgres.conf 在/data/pgsql/data目录下改成如下配置:
listen_addresses = '*' archive_mode = on archive_command = 'test ! -f /data/pgsql/arclog/%f && cp %p /data/pgsql/arclog/%f' wal_level = replica wal_keep_segments = 16 wal_sender_timeout = 60s max_connections = 500 |
postgres用户执行
pg_ctl restart -D /data/pgsql/data/ |
到此单节点pg部署完毕。以下从节点不必执行。
从库验证是否能够登陆到主库
请在从节点的postgres用户执行,本配置文档中主节点的ip地址为:192.168.1.99
-bash-4.2$ psql -h 192.168.1.99 -U postgres Password for user postgres: psql (12.4) Type "help" for help. postgres=# |
4.从节点配置
从节点配置的操作请都在从节点执行
postgres用户执行
-bash-4.2$ pg_ctl stop -D /data/pgsql/data/ waiting for server to shut down.... done server stopped |
清空从节点数据文件目录中的内容
postgres用户执行
-bash-4.2$ rm -rf /data/pgsql/data/* -bash-4.2$ cd /data/pgsql/data/ -bash-4.2$ ll total 0 |
从主机点获取数据
postgres用户执行
主节点ip:192.168.1.100
复制用户:postgres
数据文件目录:/data/pgsql/data
-bash-4.2$ pg_basebackup -h 192.168.1.100 -p 15432 -U repuser-Fp -Xs -Pv -R -D /data/pgsql/data Password: 输入repuser的密码,3.3.1章节创建的 pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/3000060 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_21167" 412492145/412492145 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/3000138 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: syncing data to disk ... pg_basebackup: base backup completed |
编辑standby.signal
postgres用户执行
Standby.signal 在/data/pgsql/data目录下
# 添加 standby_mode = 'on' |
修改从节点postgres.conf
postgres用户执行
主节点ip:192.168.1.100 。请根据实际情况进行修改。
其他地方请按照文档内容修改
primary_conninfo = 'host=192.168.1.100 port=15432 user=test_user password=user123@321' recovery_target_timeline = latest hot_standby = on max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s hot_standby_feedback = on |
postgres用户执行
-bash-4.2$ pg_ctl -D /data/pgsql/data -l logfile start waiting for server to start...... done server started |
验证主从关系
请在主节点的postgres 用户下执行
命令:
select client_addr,sync_state from pg_stat_replication;
-bash-4.2$ psql psql (12.4) Type "help" for help. postgres=# select client_addr,sync_state from pg_stat_replication; client_addr | sync_state ----------------+------------ 192.168.1.99 | async (1 row) |
postgres12内网安装rpm包,内附带所需依赖包资源-CSDN文库
postgres-12内网安装包。