Postgresql standby（备机只读）环境搭建

下载Postgresql源码包，放在任意目录

设置/etc/sysctl.conf,增加以下内容

kernel.shmmni=4096
kernel.sem=501000641280000050100012800
fs.file-max=767246
net.ipv4.ip_local_port_range=102465000
net.core.rmem_default=1048576
net.core.rmem_max=1048576
net.core.wmem_default=262144
net.core.wmem_max=262144
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_max_syn_backlog=4096
net.core.netdev_max_backlog=10000
vm.overcommit_memory=0
net.ipv4.ip_conntrack_max=655360
sysctl-p生效

如果这里出现最后一个参数未生效可：

modprobeip_conntrack
echo"modprobeip_conntrack">>/etc/rc.local

设置/etc/security/limits.conf 增加以下内容

*softnofile131072
*hardnofile131072
*softnproc131072
*hardnproc131072
*softcoreunlimited
*hardcoreunlimited
*softmemlock50000000
*hardmemlock50000000

--vi/etc/pam.d/login
sessionrequiredpam_limits.so

设置环境变量

#vi.bash_profile在文件尾添加
exportPATH=$PATH:/usr/local/pgsql/bin
exportMANPATH=$MANPATH:/usr/local/pgsql/man
exportLD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib
exportPGDATA=/opt/pgdata

先修改好两台机器的名称

[root@primary~]#groupaddpostgres
[root@primary~]#useradd-gpostgrespostgres
[root@primary~]#passwdpostgres
[postgres@primary~]$tar-zxvfpostgresql-9.3.0.tar.gz
[postgres@primary~]$cdpostgresql-9.3.0
[postgres@primarypostgresql-9.3.0]$./configure--prefix/home/proxy_pg/pgsql#配置安装目录
[postgres@primarypostgresql-9.3.0]$make#这里需要安装基础的库(gcc、readline、zlib、)可以不必理会，make时会提示
[root@primary~]#mkdir/home/proxy_pg
[root@primary~]#chown-Rpostgres/home/proxy_pg
[postgres@primarypostgresql-9.3.0]$makeinstall#安装
[postgres@primarypgsql]$mkdir/home/proxy_pg/pgsql/data#在你想要存放数据的地方创建data文件夹
[postgres@primarybin]$./initdb-D../data/#初始化数据库

[postgres@primarydata]$vipostgresql.conf#修改data/postgresql.conf
port=5432#可以任意更改你想要的,这里直接用5432
wal_level=hot_standby#主库设置成hot_standby，从库才能以READ-ONLY模式打开
max_wal_senders=30#WALSTREAM日志发送进程数
log_connections=on#设置日志参数，记录连接信息
logging_collector=on#开启csvlog输出功能(默认为off)
log_directory='pg_log'#设置csv日志输出目录($PGDATA目录下，如果不存在该目录，则会自动新建)
log_destination='csvlog'#设置输出的日志式(类型)，pg中的日志类型有stderr、csvlog、syslog以及eventlog，本例中设置为csvlog
#该选项默认为stderr类型
archive_mode=on
archive_command='test!-f/archive/pg93/archive_active||cp%p/archive/pg93/%f'#备注：归档标识文件archive_active需要手工创建。
wal_keep_segments=1024#主库设置的wal_keep_segments参数为1024，一个较大的wal_keep_segments设置，允许
备库在宕机较长的时间内依然能够重新追上主库，当然这与主库的繁忙程度有关，主库越忙，
产生的WAL日志越多，之前的WAL日志越容易被覆盖，进而出现FATAL,XX000错误。


注：修改完logging_collector后需要重启数据库(当然你也可以继续配置，在所有配置都完成后再重启)

[root@primary~]#mkdir-p/archive/pg93
[root@primary~]#chown-Rpostgres/archive
[root@primary~]#cd/archive/pg93/
[root@primarypg93]#toucharchive_active

以上操作备库也执行

修改data/pg_hba.conf

hostreplicationpostgres192.168.5.0/24trust

启动主数据库bin/pg_ctl start -D ../data/

[postgres@primarybin]$./pg_ctlstart-D../data

检测数据库是否启动成功

[postgres@primarybin]$./psql-dpostgres
psql(9.3.0)
Type"help"forhelp.

postgres=##说明启动成功了

基础备份

基本流程：在主数据库服务器上执行pg_start_backup(),复制data目录，在执行pg_stop_backup()。

./psql�dpostgres
postgres=#selectpg_start_backup('basebackupforlogstreaming');

#使用这个方法后，所有请求在写日志之后不会再刷新到磁盘。直到执行pg_stop_backup()这个函数。

#下面需要拷贝一份data目录，并通过scp复制到子数据库中

[postgres@primarypgsql]$cp-rdatadata_bak

创建从数据库(standby)

#通过scp方式拷贝data_bac目录到从数据库下(当然也可以通过其他方式)

[postgres@primarypgsql]$scp-rdata_bak/postgres@192.168.5.252:/home/proxy_pg/pgsql/

#进入从数据库服务器，进入刚刚拷贝过来的data_bac目录下

[postgres@standby~]$cd/home/proxy_pg/pgsql/data_bak

#修改postgres.conf

port=5432#改成你想的端口
hot_standby=on#从库上可以执行只读操作
log_connections=on

copy完后，停止主数据库基础备份

postgres=#selectpg_stop_backup();
#增加recovery.conf配置下连接的主数据库信息(ip、端口、用户)
[postgres@standbyshare]$cprecovery.conf.sample../data/recovery.conf#可以从share中拷贝一份模板
[postgres@standbydata_bak]$virecovery.conf
standby_mode='on'
primary_conninfo='host=192.168.5.248port=5432user=postgres'
trigger_file='/tmp/postgresql.trigger.5432'

#删除pid文件

[postgres@standbydata_bak]$rm-fpostmaster.pid

启动从数据库，并观察CSVLOG

bin/pg_ctlstart-D../data_bak/

观察CSVLOG

[postgres@primarypg_log]$tail-fpostgresql-2014-10-28_155549.csv
2014-10-2815:55:49.522GMT-8,27977,544f4c05.6d49,1,2014-10-2815:55:49GMT-8,LOG,00000,"endinglogoutputtostderr","Futurelogoutputwillgotologdestination""csvlog"".",""
2014-10-2815:55:49.523GMT-8,27979,544f4c05.6d4b,"databasesystemwasshutdownat2014-10-2815:33:33GMT-8",""
2014-10-2815:55:49.612GMT-8,2,"databasesystemisreadytoacceptconnections",27983,544f4c05.6d4f,"autovacuumlauncherstarted",""
2014-10-2815:56:34.436GMT-8,28048,"",544f4c32.6d90,2014-10-2815:56:34GMT-8,"connectionreceived:host=[local]",""
2014-10-2815:56:34.437GMT-8,"postgres","[local]","authentication",2/1,"connectionauthorized:user=postgresdatabase=postgres",""
2014-10-2816:06:14.597GMT-8,28199,544f4e76.6e27,2014-10-2816:06:14GMT-8,"connectionreceived:host=192.168.5.252port=49654",""
2014-10-2816:06:14.598GMT-8,"192.168.5.252:49654",3/21,"replicationconnectionauthorized:user=postgres",""
2014-10-2816:11:29.501GMT-8,28251,544f4fb1.6e5b,2014-10-2816:11:29GMT-8,"connectionreceived:host=192.168.5.252port=50285",""
2014-10-2816:11:29.502GMT-8,"192.168.5.252:50285",3/28,""

这些信息说明流复制已经成功，从库正准备接收主库的WAL-STREAM。

主库观察WAL-Sender进程

[postgres@primarybin]$ ps -ef |grep post

postgres 2825127977 0 16:11 ? 00:00:00 postgres: wal sender processpostgres 192.168.5.252(50285) streaming 0/60009A8

说明：将输出结果省略部分，可以看到 " wal sender process repuser"进程

在从库上观察 WAL-接收进程

[postgres@standbydata_bak]$ ps -ef |grep post

postgres 1191611911 0 16:11 ? 00:00:00 postgres: wal receiverprocess streaming 0/60009A8

说明：同样省略部分输出结果，可以看到“ wal receiver process ” 进程。

测试

主库上创建用户

postgres=#CREATEROLEbrowserLOGINENCRYPTEDPASSWORD'browser'
postgres-#nosuperusernoinheritnocreatedbnocreateroleCONNECTIONLIMIT200;
CREATEROLE

从库上验证

postgres=#\du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------+-----------

browser| No inheritance +| {}

| 200 connections |

postgres| Superuser,Create role,Create DB,Replication | {}

说明：果然，在从库上就立刻创建了新用户 'browser'

主库上创建表空间(On Master)

[postgres@primarydata]$mkdir-p/home/proxy_pg/pgsql/data/pg_tbs/tbs_browser

从库上也执行 mkdir -p (On Sandby)

[postgres@standbydata_bak]$mkdir-p/home/proxy_pg/pgsql/data/pg_tbs/tbs_browser

主库上

postgres=#createtablespacetbs_browserownerbrowserLOCATION
postgres-#'/home/proxy_pg/pgsql/data/pg_tbs/tbs_browser';
CREATETABLESPACE

--在从库上验证

postgres=#\db
Listoftablespaces
Name|Owner|Location
-------------+----------+----------------------------------------------
pg_default|postgres|
pg_global|postgres|
tbs_browser|browser|/home/proxy_pg/pgsql/data/pg_tbs/tbs_browser

(3 rows)

表空间"tbs_browser" 也立刻创建过来了

主库上创建数据库

postgres=#CREATEDATABASEbrowserWITHOWNER=browserTEMPLATE=template0ENCODING='UTF8'TABLESPACE=tbs_browser;
CREATEDATABASE

--从库上验证

postgres=#\l
Listofdatabases
Name|Owner|Encoding|Collate|Ctype|Accessprivileges
-----------+----------+----------+-------------+-------------+-----------------------
browser|browser|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|
postgres|postgres|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|
template0|postgres|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|=c/postgres+
|||||postgres=CTc/postgres
template1|postgres|UTF8|zh_CN.UTF-8|zh_CN.UTF-8|=c/postgres+
|||||postgres=CTc/postgres
(4rows)

从库上数据库 "browser" 也立刻有了，几乎没有延时。

在从库上建表

postgres=#createtabletable3(idinteger);
ERROR:cannotexecuteCREATETABLEinaread-onlytransaction

说明：从库是以只读形式打开，只能执行读操作，不能写。

监控streaming

postgres=#select*frompg_stat_replication;
pid|usesysid|usename|application_name|client_addr|client_hostname|client_port|backend_start|state|sent_location
|write_location|flush_location|replay_location|sync_priority|sync_state
-------+----------+----------+------------------+---------------+-----------------+-------------+-------------------------------+-----------+---------------
+----------------+----------------+-----------------+---------------+------------
28251|10|postgres|walreceiver|192.168.5.252||50285|2014-10-2816:11:29.501421+08|streaming|0/6001C70
|0/6001C70|0/6001C70|0/6001C70|0|async
(1row)

原文链接：https://www.f2er.com/postgresql/195507.html

Postgresql standby（备机只读）环境搭建

猜你在找的Postgre SQL相关文章