源码安装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
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
1565次阅读
2025-04-21 16:58:09
9.9 分高危漏洞,尽快升级到 pgAdmin 4 v9.2 进行修复
严少安
384次阅读
2025-04-11 10:43:23
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
372次阅读
2025-04-15 14:48:05
转发有奖 | PostgreSQL 16 PGCM高级认证课程直播班招生中!
墨天轮小教习
181次阅读
2025-04-14 15:58:34
中国PostgreSQL培训认证体系新增PGAI应用工程师方向
开源软件联盟PostgreSQL分会
179次阅读
2025-05-06 10:21:13
PG生态赢得资本市场青睐:Databricks收购Neon,Supabase融资两亿美元,微软财报点名PG
老冯云数
138次阅读
2025-05-07 10:06:22
SQL 优化之 OR 子句改写
xiongcc
135次阅读
2025-04-21 00:08:06
告别老旧mysql_fdw,升级正当时
NickYoung
116次阅读
2025-04-29 11:15:18
PostgreSQL中文社区亮相于第八届数字中国峰会
PostgreSQL中文社区
101次阅读
2025-05-07 10:06:20
PostgreSQL的dblink扩展模块使用方法
szrsu
101次阅读
2025-04-24 17:39:30