下载PostgreSQL源码包，放在任意目录
设置/etc/sysctl.conf,增加以下内容
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
kernel.shmmni= 4096
kernel.sem =501000 6412800000 501000 12800
fs.file-max =767246
net.ipv4.ip_local_port_range= 1024 65000
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 生效
|
如果这里出现最后一个参数未生效可：
|
1
2
|
modprobeip_conntrack
echo
"modprobe ip_conntrack"
>> /etc/rc.local
|
设置/etc/security/limits.conf 增加以下内容
|
1
2
3
4
5
6
7
8
9
10
11
|
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000
--vi/etc/pam.d/login
sessionrequired pam_limits.so
|
设置环境变量
|
1
2
3
4
5
|
#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
export PGDATA=/opt/pgdata
|
先修改好两台机器的名称
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
[root@primary ~]# groupadd postgres
[root@primary ~]# useradd -g postgres postgres
[root@primary ~]# passwd postgres
[postgres@primary~]$ tar -zxvf postgresql-9.3.0.tar.gz
[postgres@primary~]$ cd postgresql-9.3.0
[postgres@primary postgresql-9.3.0]$ ./configure --prefix /home/proxy_pg/pgsql #配置安装目录
[postgres@primary postgresql-9.3.0]$ make #这里需要安装基础的库(gcc、readline、zlib、) 可以不必理会，make时会提示
[root@primary ~]# mkdir /home/proxy_pg
[root@primary ~]# chown -R postgres /home/proxy_pg
[postgres@primary postgresql-9.3.0]$ make install#安装
[postgres@primary pgsql]$ mkdir /home/proxy_pg/pgsql/data #在你想要存放数据的地方创建data文件夹
[postgres@primary bin]$ ./initdb -D ../data/ #初始化数据库
[postgres@primarydata]$ vi postgresql.conf #修改data/postgresql.conf
port = 5432 #可以任意更改你想要的,这里直接用5432
wal_level = hot_standby #主库设置成 hot_standby ，从库才能以READ-ONLY模式打开
max_wal_senders = 30 #WAL STREAM 日志发送进程数
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 -R postgres /archive
[root@primary ~]# cd /archive/pg93/
[root@primary pg93]#touch archive_active
|
以上操作备库也执行
修改data/pg_hba.conf
|
1
|
host replication postgres 192.168.5.0/24 trust
|
启动主数据库bin/pg_ctl start -D ../data/
|
1
|
[postgres@primarybin]$ ./pg_ctl start -D ../data
|
检测数据库是否启动成功
|
1
2
3
4
5
|
[postgres@primarybin]$ ./psql -d postgres
psql(9.3.0)
Type
"help"
for
help.
postgres=# #说明启动成功了
|
基础备份
基本流程：在主数据库服务器上执行pg_start_backup(),复制data目录，在执行pg_stop_backup()。
|
1
2
|
./psql–d postgres
postgres=#select pg_start_backup(
'base backup for logstreaming'
);
|
#使用这个方法后，所有请求在写日志之后不会再刷新到磁盘。直到执行pg_stop_backup()这个函数。
#下面需要拷贝一份data目录，并通过scp复制到子数据库中
|
1
|
[postgres@primarypgsql]$ cp -r data data_bak
|
创建从数据库(standby)
#通过scp方式拷贝data_bac目录到从数据库下(当然也可以通过其他方式)
|
1
|
[postgres@primarypgsql]$ scp -r data_bak/postgres@192.168.5.252:/home/proxy_pg/pgsql/
|
#进入从数据库服务器，进入刚刚拷贝过来的data_bac目录下
|
1
|
[postgres@standby~]$ cd /home/proxy_pg/pgsql/data_bak
|
#修改postgres.conf
|
1
2
3
|
port = 5432 #改成你想的端口
hot_standby = on #从库上可以执行只读操作
log_connections = on
|
copy完后，停止主数据库基础备份
|
1
2
3
4
5
6
7
|
postgres=#select pg_stop_backup();
#增加recovery.conf配置下连接的主数据库信息(ip、端口、用户)
[postgres@standby share]$ cp recovery.conf.sample ../data/recovery.conf #可以从share中拷贝一份模板
[postgres@standbydata_bak]$ vi recovery.conf
standby_mode=
'on'
primary_conninfo=
'host=192.168.5.248 port=5432 user=postgres'
trigger_file =
'/tmp/postgresql.trigger.5432'
|
#删除pid文件
|
1
|
[postgres@standbydata_bak]$ rm -f postmaster.pid
|
启动从数据库，并观察CSVLOG
|
1
|
bin/pg_ctl start -D ../data_bak/
|
观察CSVLOG
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[postgres@primarypg_log]$ tail -f postgresql-2014-10-28_155549.csv
2014-10-2815:55:49.522 GMT-8,,,27977,,544f4c05.6d49,1,,2014-10-28 15:55:49GMT-8,,0,LOG,00000,
"ending log output to stderr"
,,
"Future logoutput will go to log destination"
"csvlog"
"."
,,,,,,,
""
2014-10-2815:55:49.523 GMT-8,,,27979,,544f4c05.6d4b,1,,2014-10-28 15:55:49GMT-8,,0,LOG,00000,
"database system was shut down at 2014-10-28 15:33:33GMT-8"
,,,,,,,,,
""
2014-10-2815:55:49.612 GMT-8,,,27977,,544f4c05.6d49,2,,2014-10-28 15:55:49GMT-8,,0,LOG,00000,
"database system is ready to acceptconnections"
,,,,,,,,,
""
2014-10-2815:55:49.612 GMT-8,,,27983,,544f4c05.6d4f,1,,2014-10-28 15:55:49GMT-8,,0,LOG,00000,
"autovacuum launcher started"
,,,,,,,,,
""
2014-10-2815:56:34.436 GMT-8,,,28048,
""
,544f4c32.6d90,1,
""
,2014-10-2815:56:34 GMT-8,,0,LOG,00000,
"connection received:host=[local]"
,,,,,,,,,
""
2014-10-2815:56:34.437GMT-8,
"postgres"
,
"postgres"
,28048,
"[local]"
,544f4c32.6d90,2,
"authentication"
,2014-10-2815:56:34 GMT-8,2/1,0,LOG,00000,
"connection authorized: user=postgresdatabase=postgres"
,,,,,,,,,
""
2014-10-2816:06:14.597 GMT-8,,,28199,
""
,544f4e76.6e27,1,
""
,2014-10-2816:06:14 GMT-8,,0,LOG,00000,
"connection received: host=192.168.5.252port=49654"
,,,,,,,,,
""
2014-10-2816:06:14.598GMT-8,
"postgres"
,
""
,28199,
"192.168.5.252:49654"
,544f4e76.6e27,2,
"authentication"
,2014-10-2816:06:14 GMT-8,3/21,0,LOG,00000,
"replication connection authorized:user=postgres"
,,,,,,,,,
""
2014-10-2816:11:29.501 GMT-8,,,28251,
""
,544f4fb1.6e5b,1,
""
,2014-10-2816:11:29 GMT-8,,0,LOG,00000,
"connection received: host=192.168.5.252port=50285"
,,,,,,,,,
""
2014-10-2816:11:29.502GMT-8,
"postgres"
,
""
,28251,
"192.168.5.252:50285"
,544f4fb1.6e5b,2,
"authentication"
,2014-10-2816:11:29 GMT-8,3/28,0,LOG,00000,
"replication connection authorized:user=postgres"
,,,,,,,,,
""
这些信息说明流复制已经成功，从库正准备接收主库的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 ” 进程。
测试
主库上创建用户
|
1
2
3
|
postgres=# CREATE ROLE browser LOGIN ENCRYPTED PASSWORD
'browser'
postgres-# nosuperuser noinherit nocreatedb nocreateroleCONNECTION LIMIT 200;
CREATE ROLE
|
从库上验证
|
1
|
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)
|
1
|
[postgres@primarydata]$ mkdir -p/home/proxy_pg/pgsql/data/pg_tbs/tbs_browser
|
从库上也执行 mkdir -p (On Sandby)
|
1
|
[postgres@standbydata_bak]$ mkdir -p /home/proxy_pg/pgsql/data/pg_tbs/tbs_browser
|
主库上
|
1
2
3
|
postgres=# create tablespace tbs_browser owner browser LOCATION
postgres-#
'/home/proxy_pg/pgsql/data/pg_tbs/tbs_browser'
;
CREATE TABLESPACE
|
--在从库上验证
|
1
2
3
4
5
6
7
|
postgres=# \db
List of tablespaces
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" 也立刻创建过来了
主库上创建数据库
|
1
2
|
postgres=# CREATE DATABASE browser WITH OWNER = browser TEMPLATE= template0 ENCODING =
'UTF8'
TABLESPACE = tbs_browser;
CREATE DATABASE
|
--从库上验证
|
1
2
3
4
5
6
7
8
9
10
11
|
postgres=# \l
List ofdatabases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
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
(4 rows)
|
从库上数据库 "browser" 也立刻有了，几乎没有延时。
在从库上建表
|
1
2
|
postgres=# create table table3(id integer);
ERROR: cannot execute CREATE TABLE in a read-onlytransaction
|
说明：从库是以只读形式打开，只能执行读操作，不能写。
监控streaming
|
1
2
3
4
5
6
7
8
|
postgres=# select * from pg_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-28 16:11:29.501421+08 |streaming | 0/6001C70
| 0/6001C70 | 0/6001C70 | 0/6001C70 | 0 | async
(1 row)
|
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1575880，如需转载请自行联系原作者
