暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

源码安装PG11 + 备库流复制部署

原创 舒益达 2019-12-19
1374

源码安装PG11 + 备库流复制部署

CentOS release 6.9 (Final)
PostgreSQL 11.5
主:10.10.10.1
备:10.10.10.2

安装PG11
创建用户

groupadd postgres
useradd -g postgres postgres
复制

给文件夹权限

mkdir -p /u01/pgsql/data
chown -R postgres:postgres /u01
复制

安装依赖包

yum -y install -y readline-devel zlib-devel
复制

下载源码包, 并解压

wget https://ftp.postgresql.org/pub/source/v11.5/postgresql-11.5.tar.bz2
tar -jxvf postgresql-11.5.tar.bz2
cd postgresql-11.5 
./configure -prefix=/u01/pgsql -with-blocksize=8 -with-wal-blocksize=8   
gmake -j 32 world  
gmake install-world
复制

检验安装情况

psql -V
复制

修改环境变量

vi /home/postgres/.bash_profile 
#.bash_profile
export PGPORT=5555  
export PGDATA=/u01/pgsql/data  
export LANG=en_US.utf8  
export PGHOME=/u01/pgsql  
export LD_LIBRARY_PATH= $PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export DATE=`date +“%Y%m%d%H%M”`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGHOST=$PGDATA  
export PGDATABASE=postgres  
export PGUSER=postgres  
复制

生效

source /home/postgres/.bash_profile
复制

初始化数据库:

/u01/pgsql/bin/pg_ctl init -D /u01/pgsql/data
复制

修改参数文件

vi postgresql.conf
#-----------------------------
#PostgreSQL configuration file
#-----------------------------
##固定参数
listen_addresses = '*'
superuser_reserved_connections = 10
unix_socket_directories = '.,/tmp'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
password_encryption = md5
huge_pages = try
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
vacuum_cost_limit = 10000
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
bgwriter_flush_after = 512kB
effective_io_concurrency = 0
max_worker_processes = 256
parallel_leader_participation = on
old_snapshot_threshold = 6h
wal_level = hot_standby
synchronous_commit = off
full_page_writes = on
wal_compression = on
wal_buffers = 16MB
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.2
checkpoint_flush_after = 256kB
archive_mode = on
archive_command = '/u01/pgsql/archive_command'
data_directory = '/u01/pgsql/data'
max_wal_senders = 64
max_replication_slots = 64
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
wal_receiver_status_interval = 1s
hot_standby_feedback = on
max_logical_replication_workers = 64
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
random_page_cost = 1.1
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 5s
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 256MB
track_io_timing = on
track_functions = pl
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_freeze_max_age = 1200000000
autovacuum_multixact_freeze_max_age = 1250000000
autovacuum_vacuum_cost_delay = 0ms
idle_in_transaction_session_timeout = '6h'
vacuum_freeze_table_age = 200000000
vacuum_multixact_freeze_table_age = 200000000
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements'
deadlock_timeout = 1s

##环境参数
log_timezone = 'PRC'
datestyle = 'iso,mdy'
timezone = 'PRC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'

##动态参数
port = 5555
max_connections = 2000
shared_buffers = 2GB
max_prepared_transactions = 400
work_mem = 8MB
maintenance_work_mem = 1GB
autovacuum_work_mem = 1GB
max_parallel_maintenance_workers = 2
max_parallel_workers_per_gather = 0
max_parallel_workers = 2
max_wal_size = 4GB
min_wal_size = 1GB
max_sync_workers_per_subscription = 8
effective_cache_size = 4GB
autovacuum_max_workers = 2

##
jit = on
temp_file_limit = -1
wal_keep_segments = 0
track_commit_timestamp = off
vacuum_defer_cleanup_age = 0
log_duration = off
default_transaction_deferrable = off
statement_timeout = 0
lock_timeout = 0
idle_in_transaction_session_timeout = 0
extra_float_digits = 0
old_snapshot_threshold = -1
archive_timeout = '10min'
autovacuum_vacuum_cost_limit = 10000
log_line_prefix = '%u %d %r %p %m %e '
#-----------------------------
复制

添加白名单(在pg_hba.conf后面添加)

host     all             all          10.10.10.2/32          trust
host   replication      replica       10.10.10.2/32          md5 
复制

启动DB

pg_ctl start
复制

检查进程是否启动

ps -ef|grep postgres
复制

登录

psql
复制

创建同步账号

create user  replica   password 'replica';
create role  replica login replication encrypted password 'replica';
复制

备库搭建方式同主库
在备库连接主库测试

psql -h 10.10.10.1 -U replica
复制

备份

pg_basebackup -D /u01/pgsql/data -F p -X stream -v -P -h 10.10.10.1 -p 5555 -U replica
复制

添加recovery配置文件

vi recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=10.10.10.1 port=5555 user=replica password=replica'
recovery_target_timeline = 'latest'
复制

启动备库

pg_ctl start
复制

校验主备同步情况

主: create table test(timestamptimestamp);  
备: select * from test ;
主: insert into test values (now());   
备: select * from test ;
复制

分别在主备库执行

pg_controldata -D /u01/pgsql/data/ | grep 'Database cluster state'

#主:Database cluster state:               in production
#备:Database cluster state:               in archive recovery
复制

参考:
https://help.aliyun.com/knowledge_detail/44440.html?spm=5176.11065259.1996646101.searchclickresult.46147d47WLbOo4
https://yq.aliyun.com/articles/700363?spm=a2c4e.11155435.0.0.5cea6596YCVulh

最后修改时间:2019-12-19 22:14:55
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
2人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论