pg12.1集群安装

 

安装PG

安装准备

 1，源码下载   从官网下载一份源码即可

https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz

2，环境配置

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake bison flex 1

开始安装

 

1，新建用户&用户组&目录&权限

groupadd postgres
useradd postgres -g postgres -d /home/postgres
mkdir -p /usr/local/pgsql
chown -R postgres:postgres /usr/local/pgsql

 

 

2，编译

./configure --prefix=/usr/local/pgsql --with-pgport=5432 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --with-blocksize=16
其中--prefix指定安装路径，–with选项是指安装本文件依赖的库文件，--with-pgport指定端口号
gmake world && gmake install-world
cd contrib && make && make install

 

 

3，相关配置

加载动态库，将库目录加入到系统库文件检索路径中：

echo "/usr/local/pgsql/lib/" >> /etc/ld.so.conf

为方便使用psql命令，向/home/postgres/.bash_profile文件中加入以下内容：

echo 'PATH=$PATH:/usr/local/pgsql/bin/' >> /home/postgres/.bash_profile

创建数据目录并修改权限

mkdir -p /data1/pg${port}/
chown -R postgres:postgres /data1/pg${port}
chmod 0700 /data1/pg${port}

 

初始化

su - postgres
echo "export PGDATA=/data1/pg${port}" >> ~/.bash_profile
echo "export PGPORT=5432" >> ~/.bash_profile
initdb --pgdata=/data1/pg${port}/ --encoding=utf8 --locale=C --username=postgres

至此安装完成

 

集群搭建

 

主库 

1,初始化

initdb -D /data1/pg5190

2,创建replication用户

create role replica login replication encrypted password 'replica';

3,增加权限  /data1/pgxxxx/pg_hba.conf配置文件，

host    replication     replica            xx.xx.xx.xx/32             md5

4,修改配置postgresql.conf文件

listen_addresses = '*'                     
port = 30103
max_connections = 100
superuser_reserved_connections = 10
full_page_writes = on
wal_log_hints = off
max_wal_senders = 50
hot_standby = on
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 10MB
log_statement = 'mod'
log_timezone = 'PRC'
timezone = 'PRC'
unix_socket_directories = '/tmp'
shared_buffers = 512MB
temp_buffers = 16MB
work_mem = 32MB
effective_cache_size = 2GB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB
dynamic_shared_memory_type = posix
## PITR
full_page_writes = on
wal_buffers = 16MB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
wal_level = replica

5,重启主库

pg_ctl -D /data1/pgxxxx/ -l /data1/pgxxxx/log/pgxxxx.log restart

 

从库

 

1. 从库安装完成后，不初始化，若已经初始化，清空data目录即可（不需要删除）

2. 拷贝主库数据目录到从库，

pg_basebackup -h xx.xx.xx.xx -p xxxx -U replica -F p -P -D /data1/pgxxxx/  -X  stream
    * 备注：
-h，主库主机，-p，主库服务端口；
-U，复制用户；
-F，p是默认输出格式，输出数据目录和表空间相同的布局，t表示tar格式输出；
-P，同--progress，显示进度；
-D，输出到指定目录；
    * 因为主库采用的是md5认证，这里需要密码认证。

3. 从库配置文件配置postgresql.conf

listen_addresses = '*'                     
port = 30103
max_connections = 100
superuser_reserved_connections = 10
full_page_writes = on
wal_log_hints = off
max_wal_senders = 50
hot_standby = on
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 10MB
log_statement = 'mod'
log_timezone = 'PRC'
timezone = 'PRC'
unix_socket_directories = '/tmp'
shared_buffers = 512MB
temp_buffers = 16MB
work_mem = 32MB
effective_cache_size = 2GB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB
dynamic_shared_memory_type = posix
## PITR
full_page_writes = on
wal_buffers = 16MB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
wal_level = hot_standby
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
primary_conninfo = 'host=xx.xx.xx.xx port=xxxx user=replica password=replica options=''-c wal_sender_timeout=5000'''

 

4,在从库数据目录下创建文件standby.signal（重要），此文件用于标识从库

touch standby.signal

 

5. 启动从库服务

pg_ctl -D /data1/pgxxxx/ -l /data1/pgxxxx/log/pgxxxx.log restart