Postgres-XC 1.0.2 install in 8 KVM host enviroment

来源：互联网发布：新建double类型java 编辑：程序博客网时间：2024/06/06 03:45

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
Postgres-XL的项目发起人Mason Sharp
pgpool的作者石井达夫(Tatsuo Ishii)
PG-Strom的作者海外浩平(Kaigai Kohei)
Greenplum研发总监姚延栋
周正中(德哥), PostgreSQL中国用户会创始人之一
汪洋，平安科技数据库技术部经理
……

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/
PostgreSQL中国社区： http://postgres.cn/
PostgreSQL专业1群： 3336901（已满）
PostgreSQL专业2群： 100910388
PostgreSQL专业3群： 150657323

[环境 : ]

主机 : 8核CPU, 98G MEM, OCZ PCI-E SSD硬盘. (Ubuntu desktop 12.04 x64)

创建8个虚拟机(CentOS 5.9 x64, 1vCPU, 8GMEM)

虚拟机IP地址 : 192.168.122.171 - 192.168.122.178

虚拟机环境的创建参照:

http://blog.163.com/digoal@126/blog/static/163877040201322462042878/

虚拟机数据存储使用主机的nfs :


root@digoal-PowerEdge-R610:/data03# cat /etc/exports
/data03/171     192.168.122.0/24(rw,no_root_squash,sync)
/data03/172     192.168.122.0/24(rw,no_root_squash,sync)
/data03/173     192.168.122.0/24(rw,no_root_squash,sync)
/data03/174     192.168.122.0/24(rw,no_root_squash,sync)
/data03/175     192.168.122.0/24(rw,no_root_squash,sync)
/data03/176     192.168.122.0/24(rw,no_root_squash,sync)
/data03/177     192.168.122.0/24(rw,no_root_squash,sync)
/data03/178     192.168.122.0/24(rw,no_root_squash,sync)

nfs的使用参照 :

http://blog.163.com/digoal@126/blog/static/163877040201322410101184/

192.168.122.171: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/171 /data02

192.168.122.172: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/172 /data02

192.168.122.173: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/173 /data02

192.168.122.174: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/174 /data02

192.168.122.175: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/175 /data02

192.168.122.176: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/176 /data02

192.168.122.177: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/177 /data02

192.168.122.178: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/178 /data02

[Postgres-XC组件环境]

gtm :

192.168.122.171

$PGDATA: /data02/pgxc_gtm

$PGPORT: 1921

gtm_standby :

192.168.122.172

$PGDATA: /data02/pgxc_gtm

$PGPORT: 1921

gtm_proxy, coordinator, datanode :

192.168.122.173 - 192.168.122.178 :

gtm_proxy: /data02/pgxc_gtm_proxy , $PORT: 1924

coordinate: /data02/pgxc_coordinate , $PGPORT: 1921 , pooler_manager_port: 1922

datanode: /data02/pgxc_datanode , $PGPORT: 1923

[架构如图 : ]

[pg_xc安装参考 : ]

http://blog.163.com/digoal@126/blog/static/16387704020121952051174/

[在所有节点下载Postgres-XC]

http://sourceforge.net/projects/postgres-xc/files/latest/download

192.168.122.171: cp pgxc-v1.0.2.tar.gz /data03/171

192.168.122.172: cp pgxc-v1.0.2.tar.gz /data03/172

192.168.122.173: cp pgxc-v1.0.2.tar.gz /data03/173

192.168.122.174: cp pgxc-v1.0.2.tar.gz /data03/174

192.168.122.175: cp pgxc-v1.0.2.tar.gz /data03/175

192.168.122.176: cp pgxc-v1.0.2.tar.gz /data03/176

192.168.122.177: cp pgxc-v1.0.2.tar.gz /data03/177

192.168.122.178: cp pgxc-v1.0.2.tar.gz /data03/178

[虚拟机操作系统用户及目录配置 : ]

gtm以及gtm_standby节点配置 :


[root@db-192-168-122-171 ~]# useradd pgxc
[root@db-192-168-122-171 ~]# mkdir /data02/pgxc_gtm
[root@db-192-168-122-171 ~]# chown -R pgxc:pgxc /data02/pgxc_gtm
[root@db-192-168-122-172 ~]# useradd pgxc
[root@db-192-168-122-172 ~]# mkdir /data02/pgxc_gtm
[root@db-192-168-122-172 ~]# chown -R pgxc:pgxc /data02/pgxc_gtm

gtm_proxy, datanode, coordinator节点配置 :

(192.168.122.173 - 192.168.122.178)

useradd pgxc

mkdir /data02/pgxc_gtm_proxy

mkdir /data02/pgxc_coordinate

mkdir /data02/pgxc_datanode

chown -R pgxc:pgxc /data02/*

[所有虚拟机操作系统配置]

yum -y install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel

crontab -e

-- 8 * * * * /usr/sbin/ntpdate asia.pool.ntp.org && /sbin/hwclock --systohc

/usr/sbin/ntpdate asia.pool.ntp.org && /sbin/hwclock --systohc

vi /etc/sysconfig/clock

-- ZONE="Asia/Shanghai"

UTC=false

ARC=false

rm /etc/localtime

cp /usr/share/zoneinfo/PRC /etc/localtime

vi /etc/sysconfig/i18n

-- LANG="en_US.UTF-8"

chkconfig acpid off

chkconfig avahi-daemon off

chkconfig bluetooth off

chkconfig hidd off

chkconfig smartd off

chkconfig yum-updatesd off

chkconfig hplip off

chkconfig isdn off

chkconfig iscsi off

chkconfig iscsid off

chkconfig multipathd on

vi /etc/sysctl.conf

# Controls the maximum size of a message, in bytes

kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 4294967296

kernel.shmmni = 4096

kernel.sem = 50100 64128000 50100 1280

fs.file-max = 7672460

net.ipv4.ip_local_port_range = 9000 65000

net.core.rmem_default = 1048576

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

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

fs.aio-max-nr = 1048576

net.ipv4.tcp_timestamps = 0

sysctl -p

vi /etc/security/limits.conf

* 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/hosts

127.0.0.1 localhost.localdomain localhost

192.168.122.171 db-192-168-122-171.sky-mobi.com db-192-168-122-171

192.168.122.172 db-192-168-122-172.sky-mobi.com db-192-168-122-172

192.168.122.173 db-192-168-122-173.sky-mobi.com db-192-168-122-173

192.168.122.174 db-192-168-122-174.sky-mobi.com db-192-168-122-174

192.168.122.175 db-192-168-122-175.sky-mobi.com db-192-168-122-175

192.168.122.176 db-192-168-122-176.sky-mobi.com db-192-168-122-176

192.168.122.177 db-192-168-122-177.sky-mobi.com db-192-168-122-177

192.168.122.178 db-192-168-122-178.sky-mobi.com db-192-168-122-178

vi /etc/sysconfig/selinux

SELINUX=disabled

vi /etc/sysconfig/iptables

# Generated by iptables-save v1.3.5 on Tue Jul 3 10:59:01 2012

*filter

:INPUT ACCEPT [0:0]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [369:57193]

:RH-Firewall-1-INPUT - [0:0]

-A INPUT -j RH-Firewall-1-INPUT

-A FORWARD -j RH-Firewall-1-INPUT

-A RH-Firewall-1-INPUT -s 192.168.0.0/255.255.0.0 -j ACCEPT

-A RH-Firewall-1-INPUT -s 10.0.0.0/255.0.0.0 -j ACCEPT

-A RH-Firewall-1-INPUT -s 172.16.0.0/255.255.0.0 -j ACCEPT

-A RH-Firewall-1-INPUT -i lo -j ACCEPT

-A RH-Firewall-1-INPUT -p icmp -m icmp --icmp-type any -j ACCEPT

-A RH-Firewall-1-INPUT -p esp -j ACCEPT

-A RH-Firewall-1-INPUT -p ah -j ACCEPT

-A RH-Firewall-1-INPUT -d 224.0.0.251 -p udp -m udp --dport 5353 -j ACCEPT

-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT

-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT

-A RH-Firewall-1-INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

-A RH-Firewall-1-INPUT -p udp -m state --state NEW -m udp --dport 161 -j ACCEPT

-A RH-Firewall-1-INPUT -p udp -m state --state NEW -m udp --dport 162 -j ACCEPT

-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

COMMIT

# Completed on Tue Jul 3 10:59:01 2012

[在所有虚拟机安装pgxc软件]

su - root

tar -jxvf flex-2.5.35.tar.bz2

cd flex-2.5.35

./configure && make && make install

cd /data02/

tar -zxvf pgxc-v1.0.2.tar.gz

cd pgxc-v1.0.2

./configure --prefix=/opt/pgxc_1.0.2 --with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --enable-debug && gmake world

gmake install-world

ln -s /opt/pgxc_1.0.2 /opt/pgxc

[Postgres-XC各组件配置]

1. gtm (192.168.122.171)


su - pgxc
vi .bash_profile
export PGPORT=1921
export PGDATA=/data02/pgxc_gtm
export LANG=en_US.utf8
export PGHOME=/opt/pgxc
export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/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 PGUSER=postgres
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'

2. gtm_standby (192.168.122.172)


su - pgxc
vi .bash_profile
export PGPORT=1921
export PGDATA=/data02/pgxc_gtm
export LANG=en_US.utf8
export PGHOME=/opt/pgxc
export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/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 PGUSER=postgres
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'

3. gtm_proxy, coordinate, datanode (192.168.122.173 - 192.168.122.178)

su - pgxc

vi .bash_profile

export LANG=en_US.utf8

export PGHOME=/opt/pgxc

export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/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 PGUSER=postgres

export PGDATABASE=postgres

alias rm='rm -i'

alias ll='ls -lh'

[初始化Postgres-XC集群]

1. 初始化datanode (192.168.122.173 - 192.168.122.178)

su - pgxc

192.168.122.173: initdb -D /data02/pgxc_datanode --nodename=datanode_1 -E UTF8 --locale=C -U postgres -W

192.168.122.174: initdb -D /data02/pgxc_datanode --nodename=datanode_2 -E UTF8 --locale=C -U postgres -W

192.168.122.175: initdb -D /data02/pgxc_datanode --nodename=datanode_3 -E UTF8 --locale=C -U postgres -W

192.168.122.176: initdb -D /data02/pgxc_datanode --nodename=datanode_4 -E UTF8 --locale=C -U postgres -W

192.168.122.177: initdb -D /data02/pgxc_datanode --nodename=datanode_5 -E UTF8 --locale=C -U postgres -W

192.168.122.178: initdb -D /data02/pgxc_datanode --nodename=datanode_6 -E UTF8 --locale=C -U postgres -W

2. 初始化coordinate (192.168.122.173 - 192.168.122.178)

su - pgxc

192.168.122.173: initdb -D /data02/pgxc_coordinate --nodename=coordinate_1 -E UTF8 --locale=C -U postgres -W

192.168.122.174: initdb -D /data02/pgxc_coordinate --nodename=coordinate_2 -E UTF8 --locale=C -U postgres -W

192.168.122.175: initdb -D /data02/pgxc_coordinate --nodename=coordinate_3 -E UTF8 --locale=C -U postgres -W

192.168.122.176: initdb -D /data02/pgxc_coordinate --nodename=coordinate_4 -E UTF8 --locale=C -U postgres -W

192.168.122.177: initdb -D /data02/pgxc_coordinate --nodename=coordinate_5 -E UTF8 --locale=C -U postgres -W

192.168.122.178: initdb -D /data02/pgxc_coordinate --nodename=coordinate_6 -E UTF8 --locale=C -U postgres -W

3. 初始化gtm (192.168.122.171)


su - pgxc
initgtm -Z gtm -D /data02/pgxc_gtm
cd /data02/pgxc_gtm
[pgxc@db-192-168-122-171 pgxc_gtm]$ ll
total 4.0K
-rw------- 1 pgxc pgxc 2.2K Apr  2 13:49 gtm.conf
vi gtm.conf
nodename = 'one'  # 所有gtm节点nodename唯一.
listen_addresses = '0.0.0.0'
port = 1921
startup = ACT
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm.log'
log_min_messages = WARNING
synchronous_backup = on

4. 初始化gtm_standby (192.168.122.172)


su - pgxc
initgtm -Z gtm -D /data02/pgxc_gtm
cd /data02/pgxc_gtm
[pgxc@db-192-168-122-171 pgxc_gtm]$ ll
total 4.0K
-rw------- 1 pgxc pgxc 2.2K Apr  2 13:49 gtm.conf
vi gtm.conf
nodename = 'two'  # 所有gtm节点nodename唯一.
listen_addresses = '0.0.0.0'
port = 1921
startup = STANDBY
active_host = 'db-192-168-122-171'
active_port = 1921
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm.log'
log_min_messages = WARNING
synchronous_backup = on

5. 启动gtm

su - pgxc

[pgxc@db-192-168-122-171 pgxc_gtm]$ gtm_ctl start -Z gtm -D /data02/pgxc_gtm

server starting

[pgxc@db-192-168-122-171 pgxc_gtm]$ gtm_ctl status -Z gtm -D /data02/pgxc_gtm

gtm_ctl: server is running (PID: 16716)

"-D" "/data02/pgxc_gtm"

1 master

6. 启动gtm_standby


su - pgxc
[pgxc@db-192-168-122-172 pgxc_gtm]$ gtm_ctl start -Z gtm -D /data02/pgxc_gtm
server starting
[pgxc@db-192-168-122-172 pgxc_gtm]$ gtm_ctl status -Z gtm -D /data02/pgxc_gtm
gtm_ctl: server is running (PID: 16358)
0 slave

7. 初始化gtm_proxy (192.168.122.173 - 192.168.122.178)


su - pgxc
initgtm -Z gtm_proxy -D /data02/pgxc_gtm_proxy
cd /data02/pgxc_gtm_proxy
vi gtm_proxy.conf
nodename = '1'   # 所有代理节点nodename唯一.(本例为: 1,2,3,4,5,6)
listen_addresses = '0.0.0.0'
port = 1924
worker_threads = 1
gtm_host = 'db-192-168-122-171'
gtm_port = 1921
gtm_connect_retry_idle = 30
gtm_connect_retry_count = 10
gtm_connect_retry_interval = 10
err_wait_idle = 60
err_wait_count = 10
err_wait_interval = 10
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm_proxy.log'
log_min_messages = WARNING

....................... 其他节点配置略

8. 启动gtm_proxy (192.168.122.173 - 192.168.122.178)

su - pgxc

[pgxc@db-192-168-122-173 pgxc_gtm_proxy]$ gtm_ctl start -Z gtm_proxy -D /data02/pgxc_gtm_proxy

server starting

[pgxc@db-192-168-122-173 pgxc_gtm_proxy]$ gtm_ctl status -Z gtm_proxy -D /data02/pgxc_gtm_proxy

gtm_ctl: server is running (PID: 16509)

"-D" "/data02/pgxc_gtm_proxy"

....................... 其他节点启动略

9. 配置datanode (192.168.122.173 - 192.168.122.178)


su - pgxc
cd /data02/pgxc_datanode
vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 1923
max_connections = 1600     # 与max_prepared_transactions一致, #(datanode的max_connection>=coordinator.max_coordinators*coordinator.max_connection.)
superuser_reserved_connections = 13
unix_socket_directory = '.'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 1024MB
max_prepared_transactions = 1600  # 与max_connections一致
maintenance_work_mem = 512MB
max_stack_depth = 8MB
wal_level = hot_standby
synchronous_commit = off
wal_buffers = 16384kB
wal_writer_delay = 10ms
checkpoint_segments = 64
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 32
wal_sender_delay = 10ms
wal_keep_segments = 256
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
wal_receiver_status_interval = 1s
hot_standby_feedback = on
random_page_cost = 1.0
effective_cache_size = 8192MB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_statement = 'ddl'
track_activity_query_size = 2048
log_autovacuum_min_duration = 0
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
gtm_host = '127.0.0.1'  # 配置为gtm_proxy的地址和端口. 获取gxid,sequence,timestamp等全局信息.
gtm_port = 1924
pgxc_node_name = 'datanode_1'  # 集群中的每个datanode名字必须唯一. 对应initdb 时指定的值.
enforce_two_phase_commit = on
enable_fast_query_shipping = on
enable_remotejoin = on
enable_remotegroup = on

....................... 其他节点配置略

配置pg_hba.conf

vi pg_hba.conf

host all all 192.168.122.173/32 trust

host all all 192.168.122.174/32 trust

host all all 192.168.122.175/32 trust

host all all 192.168.122.176/32 trust

host all all 192.168.122.177/32 trust

host all all 192.168.122.178/32 trust

host all all 0.0.0.0/0 md5

....................... 其他节点配置略

10. 启动datanode (192.168.122.173 - 192.168.122.178)

su - pgxc

pg_ctl -Z datanode start -D /data02/pgxc_datanode

....................... 其他节点启动略

11. 配置coordinate (192.168.122.173 - 192.168.122.178)


su - pgxc
cd /data02/pgxc_coordinate/
vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 1921
max_connections = 100
superuser_reserved_connections = 13
unix_socket_directory = '.'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 1024MB
max_prepared_transactions = 16      # 用作二阶事务, 必须>=max_coordinators 
maintenance_work_mem = 512MB
max_stack_depth = 8MB
wal_level = minimal
synchronous_commit = off
wal_buffers = 16384kB
wal_writer_delay = 10ms
checkpoint_segments = 128
random_page_cost = 1.0
effective_cache_size = 8192MB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_autovacuum_min_duration = 0
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
pooler_port = 1922                      # Pool Manager TCP port
min_pool_size = 1
max_pool_size = 6416                      # >=(max_connection*max_datanodes+max_coordinators); #(datanode的max_connection>=max_coordinators*this.max_connection.)
max_coordinators = 16                  # Maximum number of Coordinators
max_datanodes = 64                     # Maximum number of Datanodes
gtm_host = '127.0.0.1'                    # Host name or address of GTM, 指定本机的gtm_proxy
gtm_port = 1924                 # Port of GTM, 指定本机的gtm_proxy
pgxc_node_name = 'coordinate_1'                 # Coordinator or Datanode name, 集群中的coordinate名字必须唯一, 与initdb初始化时指定的匹配.
enforce_two_phase_commit = on           # Enforce the usage of two-phase commit on transactions
enable_fast_query_shipping = on
enable_remotejoin = on
enable_remotegroup = on

....................... 其他节点配置略

vi pg_hba.conf

host all all 192.168.122.173/32 trust

host all all 192.168.122.174/32 trust

host all all 192.168.122.175/32 trust

host all all 192.168.122.176/32 trust

host all all 192.168.122.177/32 trust

host all all 192.168.122.178/32 trust

host all all 0.0.0.0/0 md5

....................... 其他节点配置略

12. 启动coordinate (192.168.122.173 - 192.168.122.178)

su - pgxc

pg_ctl -Z coordinator start -D /data02/pgxc_coordinate

....................... 其他节点启动略

13. 创建node, 需要在每个coordinator节点操作, 因为coordinator上创建node以及group的操作不会在所有的coordinator中同步, 需要手工操作.

# 同时不建议使用IP地址, 建议使用主机名. 下一篇中会对本例的IP地址修改为主机名举例.

# datanode可以设置primary和prefered.

# primary表示replicated table的dml操作发生在该节点, 因此所有coordinator节点必须统一配置同一个primary.

# 只能有一个datanode的primary=true. 其他都是false.

# prefered表示replicated table的读操作优先选择该节点. 因此每个coordinator配置为离它最近的节点即可.

192.168.122.173 :


psql -h 192.168.122.173 -p 1921 -U postgres postgres
postgres=# select * from pgxc_node;
  node_name   | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |  node_id   
--------------+-----------+-----------+-----------+----------------+------------------+------------
 coordinate_1 | C         |      5432 | localhost | f              | f                | -922782310
(1 row)

# 注意以上为初始的pgxc_node信息有误, 每个节点都需要修改.


alter node coordinate_1 with (host='127.0.0.1',port=1921);
create node datanode_1 with (type=datanode, host='192.168.122.173', port=1923, primary=true, preferred=true);
create node datanode_2 with (type=datanode, host='192.168.122.174', port=1923, primary=false, preferred=false);
create node datanode_3 with (type=datanode, host='192.168.122.175', port=1923, primary=false, preferred=false);
create node datanode_4 with (type=datanode, host='192.168.122.176', port=1923, primary=false, preferred=false);
create node datanode_5 with (type=datanode, host='192.168.122.177', port=1923, primary=false, preferred=false);
create node datanode_6 with (type=datanode, host='192.168.122.178', port=1923, primary=false, preferred=false);
postgres=# select oid,* from pgxc_node order by node_name;
  oid  |  node_name   | node_type | node_port |    node_host    | nodeis_primary | nodeis_preferred |   node_id   
-------+--------------+-----------+-----------+-----------------+----------------+------------------+-------------
 11129 | coordinate_1 | C         |      1921 | 127.0.0.1       | f              | f                |  -922782310
 16389 | datanode_1   | D         |      1923 | 192.168.122.173 | t              | t                |  -675012441
 16390 | datanode_2   | D         |      1923 | 192.168.122.174 | f              | f                | -1047623914
 16391 | datanode_3   | D         |      1923 | 192.168.122.175 | f              | f                |  1787525382
 16392 | datanode_4   | D         |      1923 | 192.168.122.176 | f              | f                |   -83063638
 16393 | datanode_5   | D         |      1923 | 192.168.122.177 | f              | f                |   137889650
 16394 | datanode_6   | D         |      1923 | 192.168.122.178 | f              | f                |  -678318491
(7 rows)
select pgxc_pool_reload();

192.168.122.174 :


psql -h 192.168.122.174 -p 1921 -U postgres postgres
alter node coordinate_2 with (host='127.0.0.1',port=1921);
create node datanode_1 with (type=datanode, host='192.168.122.173', port=1923, primary=true, preferred=false);
create node datanode_2 with (type=datanode, host='192.168.122.174', port=1923, primary=false, preferred=true);
create node datanode_3 with (type=datanode, host='192.168.122.175', port=1923, primary=false, preferred=false);
create node datanode_4 with (type=datanode, host='192.168.122.176', port=1923, primary=false, preferred=false);
create node datanode_5 with (type=datanode, host='192.168.122.177', port=1923, primary=false, preferred=false);
create node datanode_6 with (type=datanode, host='192.168.122.178', port=1923, primary=false, preferred=false);
postgres=# select oid,* from pgxc_node order by node_name;
  oid  |  node_name   | node_type | node_port |    node_host    | nodeis_primary | nodeis_preferred |   node_id   
-------+--------------+-----------+-----------+-----------------+----------------+------------------+-------------
 11129 | coordinate_2 | C         |      1921 | 127.0.0.1       | f              | f                |  1027955327
 24581 | datanode_1   | D         |      1923 | 192.168.122.173 | t              | f                |  -675012441
 24582 | datanode_2   | D         |      1923 | 192.168.122.174 | f              | t                | -1047623914
 24578 | datanode_3   | D         |      1923 | 192.168.122.175 | f              | f                |  1787525382
 24579 | datanode_4   | D         |      1923 | 192.168.122.176 | f              | f                |   -83063638
 24580 | datanode_5   | D         |      1923 | 192.168.122.177 | f              | f                |   137889650
 16409 | datanode_6   | D         |      1923 | 192.168.122.178 | f              | f                |  -678318491
(7 rows)
select pgxc_pool_reload();

192.168.122.175 :


psql -h 192.168.122.175 -p 1921 -U postgres postgres
alter node coordinate_3 with (host='127.0.0.1',port=1921);
create node datanode_1 with (type=datanode, host='192.168.122.173', port=1923, primary=true, preferred=false);
create node datanode_2 with (type=datanode, host='192.168.122.174', port=1923, primary=false, preferred=false);
create node datanode_3 with (type=datanode, host='192.168.122.175', port=1923, primary=false, preferred=true);
create node datanode_4 with (type=datanode, host='192.168.122.176', port=1923, primary=false, preferred=false);
create node datanode_5 with (type=datanode, host='192.168.122.177', port=1923, primary=false, preferred=false);
create node datanode_6 with (type=datanode, host='192.168.122.178', port=1923, primary=false, preferred=false);
postgres=# select oid,* from pgxc_node order by node_name;
  oid  |  node_name   | node_type | node_port |    node_host    | nodeis_primary | nodeis_preferred |   node_id   
-------+--------------+-----------+-----------+-----------------+----------------+------------------+-------------
 11129 | coordinate_3 | C         |      1921 | 127.0.0.1       | f              | f                |   183504851
 16417 | datanode_1   | D         |      1923 | 192.168.122.173 | t              | f                |  -675012441
 16418 | datanode_2   | D         |      1923 | 192.168.122.174 | f              | f                | -1047623914
 16419 | datanode_3   | D         |      1923 | 192.168.122.175 | f              | t                |  1787525382
 16420 | datanode_4   | D         |      1923 | 192.168.122.176 | f              | f                |   -83063638
 16421 | datanode_5   | D         |      1923 | 192.168.122.177 | f              | f                |   137889650
 16422 | datanode_6   | D         |      1923 | 192.168.122.178 | f              | f                |  -678318491
(7 rows)
select pgxc_pool_reload();

192.168.122.176 :


psql -h 192.168.122.176 -p 1921 -U postgres postgres
alter node coordinate_4 with (host='127.0.0.1',port=1921);
create node datanode_1 with (type=datanode, host='192.168.122.173', port=1923, primary=true, preferred=false);
create node datanode_2 with (type=datanode, host='192.168.122.174', port=1923, primary=false, preferred=false);
create node datanode_3 with (type=datanode, host='192.168.122.175', port=1923, primary=false, preferred=false);
create node datanode_4 with (type=datanode, host='192.168.122.176', port=1923, primary=false, preferred=true);
create node datanode_5 with (type=datanode, host='192.168.122.177', port=1923, primary=false, preferred=false);
create node datanode_6 with (type=datanode, host='192.168.122.178', port=1923, primary=false, preferred=false);
postgres=# select oid,* from pgxc_node order by node_name;
  oid  |  node_name   | node_type | node_port |    node_host    | nodeis_primary | nodeis_preferred |   node_id   
-------+--------------+-----------+-----------+-----------------+----------------+------------------+-------------
 11129 | coordinate_4 | C         |      1921 | 127.0.0.1       | f              | f                | -1338651536
 16417 | datanode_1   | D         |      1923 | 192.168.122.173 | t              | f                |  -675012441
 16418 | datanode_2   | D         |      1923 | 192.168.122.174 | f              | f                | -1047623914
 16419 | datanode_3   | D         |      1923 | 192.168.122.175 | f              | f                |  1787525382
 16420 | datanode_4   | D         |      1923 | 192.168.122.176 | f              | t                |   -83063638
 16421 | datanode_5   | D         |      1923 | 192.168.122.177 | f              | f                |   137889650
 16409 | datanode_6   | D         |      1923 | 192.168.122.178 | f              | f                |  -678318491
(7 rows)
select pgxc_pool_reload();

192.168.122.177 :


psql -h 192.168.122.177 -p 1921 -U postgres postgres
alter node coordinate_5 with (host='127.0.0.1',port=1921);
create node datanode_1 with (type=datanode, host='192.168.122.173', port=1923, primary=true, preferred=false);
create node datanode_2 with (type=datanode, host='192.168.122.174', port=1923, primary=false, preferred=false);
create node datanode_3 with (type=datanode, host='192.168.122.175', port=1923, primary=false, preferred=false);
create node datanode_4 with (type=datanode, host='192.168.122.176', port=1923, primary=false, preferred=false);
create node datanode_5 with (type=datanode, host='192.168.122.177', port=1923, primary=false, preferred=true);
create node datanode_6 with (type=datanode, host='192.168.122.178', port=1923, primary=false, preferred=false);
postgres=# select oid,* from pgxc_node order by node_name;
  oid  |  node_name   | node_type | node_port |    node_host    | nodeis_primary | nodeis_preferred |   node_id   
-------+--------------+-----------+-----------+-----------------+----------------+------------------+-------------
 11129 | coordinate_5 | C         |      1921 | 127.0.0.1       | f              | f                |  2058409530
 16417 | datanode_1   | D         |      1923 | 192.168.122.173 | t              | f                |  -675012441
 16418 | datanode_2   | D         |      1923 | 192.168.122.174 | f              | f                | -1047623914
 16419 | datanode_3   | D         |      1923 | 192.168.122.175 | f              | f                |  1787525382
 16420 | datanode_4   | D         |      1923 | 192.168.122.176 | f              | f                |   -83063638
 16421 | datanode_5   | D         |      1923 | 192.168.122.177 | f              | t                |   137889650
 16409 | datanode_6   | D         |      1923 | 192.168.122.178 | f              | f                |  -678318491
(7 rows)
select pgxc_pool_reload();

192.168.122.178 :


psql -h 192.168.122.178 -p 1921 -U postgres postgres
alter node coordinate_6 with (host='127.0.0.1',port=1921);
create node datanode_1 with (type=datanode, host='192.168.122.173', port=1923, primary=true, preferred=false);
create node datanode_2 with (type=datanode, host='192.168.122.174', port=1923, primary=false, preferred=false);
create node datanode_3 with (type=datanode, host='192.168.122.175', port=1923, primary=false, preferred=false);
create node datanode_4 with (type=datanode, host='192.168.122.176', port=1923, primary=false, preferred=false);
create node datanode_5 with (type=datanode, host='192.168.122.177', port=1923, primary=false, preferred=false);
create node datanode_6 with (type=datanode, host='192.168.122.178', port=1923, primary=false, preferred=true);
postgres=# select oid,* from pgxc_node order by node_name;
  oid  |  node_name   | node_type | node_port |    node_host    | nodeis_primary | nodeis_preferred |   node_id   
-------+--------------+-----------+-----------+-----------------+----------------+------------------+-------------
 11129 | coordinate_6 | C         |      1921 | 127.0.0.1       | f              | f                |    15814306
 16437 | datanode_1   | D         |      1923 | 192.168.122.173 | t              | f                |  -675012441
 16432 | datanode_2   | D         |      1923 | 192.168.122.174 | f              | f                | -1047623914
 16433 | datanode_3   | D         |      1923 | 192.168.122.175 | f              | f                |  1787525382
 16434 | datanode_4   | D         |      1923 | 192.168.122.176 | f              | f                |   -83063638
 16435 | datanode_5   | D         |      1923 | 192.168.122.177 | f              | f                |   137889650
 16436 | datanode_6   | D         |      1923 | 192.168.122.178 | f              | t                |  -678318491
(7 rows)
select pgxc_pool_reload();

[其他]

1. 每个coordinator节点应该创建其他coordinator节点的信息(这样的话ddl语句才会在所有的coordinator节点执行, 详见下一篇BLOG, pgxc use caveat), 以及创建完全一致的group的信息.

http://blog.163.com/digoal@126/blog/static/1638770402013332335933/

[参考]

1. http://blog.163.com/digoal@126/blog/static/163877040201322462042878/

2. http://blog.163.com/digoal@126/blog/static/163877040201322410101184/

3. http://blog.163.com/digoal@126/blog/static/16387704020121952051174/

4. http://nchc.dl.sourceforge.net/project/postgres-xc/Presentation/20120516_PGConTutorial/20120515_PGXC_Tutorial_global.pdf

5. http://www.slideshare.net/stormdb_cloud_database/postgresxc-write-scalable-postgresql-cluster

6. http://www.pgcon.org/2012/schedule/attachments/224_Postgres-XC_tutorial.pdf

7. http://wiki.postgresql.org/images/4/44/Pgxc_HA_20121024.pdf

8. https://github.com/koichi-szk/PGXC-Tools

0 0