Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:
- Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
- Postgres-XL的项目发起人Mason Sharp
- pgpool的作者石井达夫(Tatsuo Ishii)
- PG-Strom的作者海外浩平(Kaigai Kohei)
- Greenplum研发总监姚延栋
- 周正中(德哥), PostgreSQL中国用户会创始人之一
- 汪洋，平安科技数据库技术部经理
- ……
|
 |
[环境 : ]
主机 : 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
虚拟机环境的创建参照:
虚拟机数据存储使用主机的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的使用参照 :
192.168.122.171: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/171 /data02192.168.122.172: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/172 /data02192.168.122.173: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/173 /data02192.168.122.174: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/174 /data02192.168.122.175: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/175 /data02192.168.122.176: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/176 /data02192.168.122.177: /bin/mount -t nfs -o tcp 192.168.122.1:/data03/177 /data02192.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: 1921gtm_standby :192.168.122.172$PGDATA: /data02/pgxc_gtm$PGPORT: 1921gtm_proxy, coordinator, datanode :192.168.122.173 - 192.168.122.178 :gtm_proxy: /data02/pgxc_gtm_proxy , $PORT: 1924coordinate: /data02/pgxc_coordinate , $PGPORT: 1921 , pooler_manager_port: 1922datanode: /data02/pgxc_datanode , $PGPORT: 1923
[架构如图 : ]
[pg_xc安装参考 : ]
[在所有节点下载Postgres-XC]
192.168.122.171: cp pgxc-v1.0.2.tar.gz /data03/171192.168.122.172: cp pgxc-v1.0.2.tar.gz /data03/172192.168.122.173: cp pgxc-v1.0.2.tar.gz /data03/173192.168.122.174: cp pgxc-v1.0.2.tar.gz /data03/174192.168.122.175: cp pgxc-v1.0.2.tar.gz /data03/175192.168.122.176: cp pgxc-v1.0.2.tar.gz /data03/176192.168.122.177: cp pgxc-v1.0.2.tar.gz /data03/177192.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 pgxcmkdir /data02/pgxc_gtm_proxymkdir /data02/pgxc_coordinatemkdir /data02/pgxc_datanodechown -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=falseARC=false
rm /etc/localtimecp /usr/share/zoneinfo/PRC /etc/localtime
vi /etc/sysconfig/i18n-- LANG="en_US.UTF-8"
chkconfig acpid offchkconfig avahi-daemon offchkconfig bluetooth offchkconfig hidd offchkconfig smartd offchkconfig yum-updatesd offchkconfig hplip offchkconfig isdn offchkconfig iscsi offchkconfig iscsid offchkconfig multipathd on
vi /etc/sysctl.conf# Controls the maximum size of a message, in byteskernel.msgmnb = 65536# Controls the default maxmimum size of a mesage queuekernel.msgmax = 65536# Controls the maximum shared segment size, in byteskernel.shmmax = 68719476736# Controls the maximum number of shared memory segments, in pageskernel.shmall = 4294967296kernel.shmmni = 4096kernel.sem = 50100 64128000 50100 1280fs.file-max = 7672460net.ipv4.ip_local_port_range = 9000 65000net.core.rmem_default = 1048576net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_max_syn_backlog = 4096net.core.netdev_max_backlog = 10000vm.overcommit_memory = 0net.ipv4.ip_conntrack_max = 655360fs.aio-max-nr = 1048576net.ipv4.tcp_timestamps = 0sysctl -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/hosts127.0.0.1 localhost.localdomain localhost192.168.122.171 db-192-168-122-171.sky-mobi.com db-192-168-122-171192.168.122.172 db-192-168-122-172.sky-mobi.com db-192-168-122-172192.168.122.173 db-192-168-122-173.sky-mobi.com db-192-168-122-173192.168.122.174 db-192-168-122-174.sky-mobi.com db-192-168-122-174192.168.122.175 db-192-168-122-175.sky-mobi.com db-192-168-122-175192.168.122.176 db-192-168-122-176.sky-mobi.com db-192-168-122-176192.168.122.177 db-192-168-122-177.sky-mobi.com db-192-168-122-177192.168.122.178 db-192-168-122-178.sky-mobi.com db-192-168-122-178
vi /etc/sysconfig/selinuxSELINUX=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-prohibitedCOMMIT# Completed on Tue Jul 3 10:59:01 2012
[在所有虚拟机安装pgxc软件]
su - roottar -jxvf flex-2.5.35.tar.bz2cd flex-2.5.35./configure && make && make installcd /data02/tar -zxvf pgxc-v1.0.2.tar.gzcd 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 worldgmake install-worldln -s /opt/pgxc_1.0.2 /opt/pgxc
[Postgres-XC各组件配置]
1. gtm (192.168.122.171)
su - pgxcvi .bash_profileexport PGPORT=1921export PGDATA=/data02/pgxc_gtmexport LANG=en_US.utf8export PGHOME=/opt/pgxcexport LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=`date +"%Y%m%d%H%M"`export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATHexport PGUSER=postgresexport PGHOST=$PGDATAexport PGDATABASE=postgresalias rm='rm -i'alias ll='ls -lh'
2. gtm_standby
(192.168.122.172)
su - pgxcvi .bash_profileexport PGPORT=1921export PGDATA=/data02/pgxc_gtmexport LANG=en_US.utf8export PGHOME=/opt/pgxcexport LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=`date +"%Y%m%d%H%M"`export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATHexport PGUSER=postgresexport PGHOST=$PGDATAexport PGDATABASE=postgresalias rm='rm -i'alias ll='ls -lh'
3.
gtm_proxy, coordinate, datanode
(192.168.122.173 - 192.168.122.178)
su - pgxcvi .bash_profileexport LANG=en_US.utf8export PGHOME=/opt/pgxcexport LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=`date +"%Y%m%d%H%M"`export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATHexport PGUSER=postgresexport PGDATABASE=postgresalias rm='rm -i'alias ll='ls -lh'
[初始化Postgres-XC集群]
1. 初始化datanode (192.168.122.173 - 192.168.122.178)
su - pgxc192.168.122.173: initdb -D /data02/pgxc_datanode --nodename=datanode_1 -E UTF8 --locale=C -U postgres -W192.168.122.174: initdb -D /data02/pgxc_datanode --nodename=datanode_2 -E UTF8 --locale=C -U postgres -W192.168.122.175: initdb -D /data02/pgxc_datanode --nodename=datanode_3 -E UTF8 --locale=C -U postgres -W192.168.122.176: initdb -D /data02/pgxc_datanode --nodename=datanode_4 -E UTF8 --locale=C -U postgres -W192.168.122.177: initdb -D /data02/pgxc_datanode --nodename=datanode_5 -E UTF8 --locale=C -U postgres -W192.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 - pgxc192.168.122.173: initdb -D /data02/pgxc_coordinate --nodename=coordinate_1 -E UTF8 --locale=C -U postgres -W192.168.122.174: initdb -D /data02/pgxc_coordinate --nodename=coordinate_2 -E UTF8 --locale=C -U postgres -W192.168.122.175: initdb -D /data02/pgxc_coordinate --nodename=coordinate_3 -E UTF8 --locale=C -U postgres -W192.168.122.176: initdb -D /data02/pgxc_coordinate --nodename=coordinate_4 -E UTF8 --locale=C -U postgres -W192.168.122.177: initdb -D /data02/pgxc_coordinate --nodename=coordinate_5 -E UTF8 --locale=C -U postgres -W192.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 - pgxcinitgtm -Z gtm -D /data02/pgxc_gtmcd /data02/pgxc_gtm[ pgxc@db - 192 - 168 - 122 - 171 pgxc_gtm ] $ lltotal 4.0K- rw ------- 1 pgxc pgxc 2.2K Apr 2 13 : 49 gtm . confvi gtm . confnodename = 'one' # 所有gtm节点nodename唯一.listen_addresses = '0.0.0.0'port = 1921startup = ACTkeepalives_idle = 60keepalives_interval = 10keepalives_count = 10log_file = 'gtm.log'log_min_messages = WARNINGsynchronous_backup = on
4. 初始化gtm_standby (192.168.122.172)
su - pgxcinitgtm -Z gtm -D /data02/pgxc_gtmcd /data02/pgxc_gtm[pgxc@db-192-168-122-171 pgxc_gtm]$ lltotal 4.0K-rw------- 1 pgxc pgxc 2.2K Apr 2 13:49 gtm.confvi gtm.confnodename = 'two' # 所有gtm节点nodename唯一.listen_addresses = '0.0.0.0'port = 1921startup = STANDBYactive_host = 'db-192-168-122-171'active_port = 1921keepalives_idle = 60keepalives_interval = 10keepalives_count = 10log_file = 'gtm.log'log_min_messages = WARNINGsynchronous_backup = on
5. 启动gtm
su - pgxc[pgxc@db-192-168-122-171 pgxc_gtm]$ gtm_ctl start -Z gtm -D /data02/pgxc_gtmserver starting[pgxc@db-192-168-122-171 pgxc_gtm]$ gtm_ctl status -Z gtm -D /data02/pgxc_gtmgtm_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_gtmserver starting[pgxc@db-192-168-122-172 pgxc_gtm]$ gtm_ctl status -Z gtm -D /data02/pgxc_gtmgtm_ctl: server is running (PID: 16358)0 slave
7. 初始化gtm_proxy (192.168.122.173 - 192.168.122.178)
su - pgxcinitgtm -Z gtm_proxy -D /data02/pgxc_gtm_proxycd /data02/pgxc_gtm_proxyvi gtm_proxy.confnodename = '1' # 所有代理节点nodename唯一.(本例为: 1,2,3,4,5,6)listen_addresses = '0.0.0.0'port = 1924worker_threads = 1gtm_host = 'db-192-168-122-171'gtm_port = 1921gtm_connect_retry_idle = 30gtm_connect_retry_count = 10gtm_connect_retry_interval = 10err_wait_idle = 60err_wait_count = 10err_wait_interval = 10keepalives_idle = 60keepalives_interval = 10keepalives_count = 10log_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_proxyserver starting[pgxc@db-192-168-122-173 pgxc_gtm_proxy]$ gtm_ctl status -Z gtm_proxy -D /data02/pgxc_gtm_proxygtm_ctl: server is running (PID: 16509)"-D" "/data02/pgxc_gtm_proxy"
....................... 其他节点启动略
9. 配置datanode
(192.168.122.173 - 192.168.122.178)
su - pgxccd /data02/pgxc_datanodevi postgresql.conflisten_addresses = '0.0.0.0'port = 1923max_connections = 1600 # 与max_prepared_transactions一致, #(datanode的max_connection>=coordinator.max_coordinators*coordinator.max_connection.)superuser_reserved_connections = 13unix_socket_directory = '.'unix_socket_permissions = 0700tcp_keepalives_idle = 60tcp_keepalives_interval = 10tcp_keepalives_count = 10shared_buffers = 1024MBmax_prepared_transactions = 1600 # 与max_connections一致maintenance_work_mem = 512MBmax_stack_depth = 8MBwal_level = hot_standbysynchronous_commit = offwal_buffers = 16384kBwal_writer_delay = 10mscheckpoint_segments = 64archive_mode = onarchive_command = '/bin/date'max_wal_senders = 32wal_sender_delay = 10mswal_keep_segments = 256hot_standby = onmax_standby_archive_delay = 300smax_standby_streaming_delay = 300swal_receiver_status_interval = 1shot_standby_feedback = onrandom_page_cost = 1.0effective_cache_size = 8192MBlog_destination = 'csvlog'logging_collector = onlog_directory = 'pg_log'log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'log_file_mode = 0600log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 10MBlog_checkpoints = onlog_connections = onlog_disconnections = onlog_error_verbosity = verboselog_statement = 'ddl'track_activity_query_size = 2048log_autovacuum_min_duration = 0datestyle = '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 = 1924pgxc_node_name = 'datanode_1' # 集群中的每个datanode名字必须唯一. 对应initdb 时指定的值.enforce_two_phase_commit = onenable_fast_query_shipping = onenable_remotejoin = onenable_remotegroup = on
.......................
其他节点配置略
配置pg_hba.conf
vi pg_hba.confhost all all 192.168.122.173/32 trusthost all all 192.168.122.174/32 trusthost all all 192.168.122.175/32 trusthost all all 192.168.122.176/32 trusthost all all 192.168.122.177/32 trusthost all all 192.168.122.178/32 trusthost all all 0.0.0.0/0 md5
....................... 其他节点配置略
10. 启动datanode
(192.168.122.173 - 192.168.122.178)
su - pgxcpg_ctl -Z datanode start -D /data02/pgxc_datanode
....................... 其他节点启动略
11. 配置coordinate
(192.168.122.173 - 192.168.122.178)
su - pgxccd /data02/pgxc_coordinate/vi postgresql.conflisten_addresses = '0.0.0.0'port = 1921max_connections = 100superuser_reserved_connections = 13unix_socket_directory = '.'unix_socket_permissions = 0700tcp_keepalives_idle = 60tcp_keepalives_interval = 10tcp_keepalives_count = 10shared_buffers = 1024MBmax_prepared_transactions = 16 # 用作二阶事务, 必须>=max_coordinatorsmaintenance_work_mem = 512MBmax_stack_depth = 8MBwal_level = minimalsynchronous_commit = offwal_buffers = 16384kBwal_writer_delay = 10mscheckpoint_segments = 128random_page_cost = 1.0effective_cache_size = 8192MBlog_destination = 'csvlog'logging_collector = onlog_directory = 'pg_log'log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'log_file_mode = 0600log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 10MBlog_checkpoints = onlog_connections = onlog_disconnections = onlog_error_verbosity = verboselog_autovacuum_min_duration = 0datestyle = '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 portmin_pool_size = 1max_pool_size = 6416 # >=(max_connection*max_datanodes+max_coordinators); #(datanode的max_connection>=max_coordinators*this.max_connection.)max_coordinators = 16 # Maximum number of Coordinatorsmax_datanodes = 64 # Maximum number of Datanodesgtm_host = '127.0.0.1' # Host name or address of GTM, 指定本机的gtm_proxygtm_port = 1924 # Port of GTM, 指定本机的gtm_proxypgxc_node_name = 'coordinate_1' # Coordinator or Datanode name, 集群中的coordinate名字必须唯一, 与initdb初始化时指定的匹配.enforce_two_phase_commit = on # Enforce the usage of two-phase commit on transactionsenable_fast_query_shipping = onenable_remotejoin = onenable_remotegroup = on
....................... 其他节点配置略
vi pg_hba.confhost all all 192.168.122.173/32 trusthost all all 192.168.122.174/32 trusthost all all 192.168.122.175/32 trusthost all all 192.168.122.176/32 trusthost all all 192.168.122.177/32 trusthost all all 192.168.122.178/32 trusthost all all 0.0.0.0/0 md5
....................... 其他节点配置略
12. 启动coordinate
(192.168.122.173 - 192.168.122.178)
su - pgxcpg_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 postgrespostgres=# 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 | -92278231016389 | datanode_1 | D | 1923 | 192.168.122.173 | t | t | -67501244116390 | datanode_2 | D | 1923 | 192.168.122.174 | f | f | -104762391416391 | datanode_3 | D | 1923 | 192.168.122.175 | f | f | 178752538216392 | datanode_4 | D | 1923 | 192.168.122.176 | f | f | -8306363816393 | datanode_5 | D | 1923 | 192.168.122.177 | f | f | 13788965016394 | 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 postgresalter 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 | 102795532724581 | datanode_1 | D | 1923 | 192.168.122.173 | t | f | -67501244124582 | datanode_2 | D | 1923 | 192.168.122.174 | f | t | -104762391424578 | datanode_3 | D | 1923 | 192.168.122.175 | f | f | 178752538224579 | datanode_4 | D | 1923 | 192.168.122.176 | f | f | -8306363824580 | datanode_5 | D | 1923 | 192.168.122.177 | f | f | 13788965016409 | 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 postgresalter 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 | 18350485116417 | datanode_1 | D | 1923 | 192.168.122.173 | t | f | -67501244116418 | datanode_2 | D | 1923 | 192.168.122.174 | f | f | -104762391416419 | datanode_3 | D | 1923 | 192.168.122.175 | f | t | 178752538216420 | datanode_4 | D | 1923 | 192.168.122.176 | f | f | -8306363816421 | datanode_5 | D | 1923 | 192.168.122.177 | f | f | 13788965016422 | 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 postgresalter 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 | -133865153616417 | datanode_1 | D | 1923 | 192.168.122.173 | t | f | -67501244116418 | datanode_2 | D | 1923 | 192.168.122.174 | f | f | -104762391416419 | datanode_3 | D | 1923 | 192.168.122.175 | f | f | 178752538216420 | datanode_4 | D | 1923 | 192.168.122.176 | f | t | -8306363816421 | datanode_5 | D | 1923 | 192.168.122.177 | f | f | 13788965016409 | 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 postgresalter 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 | 205840953016417 | datanode_1 | D | 1923 | 192.168.122.173 | t | f | -67501244116418 | datanode_2 | D | 1923 | 192.168.122.174 | f | f | -104762391416419 | datanode_3 | D | 1923 | 192.168.122.175 | f | f | 178752538216420 | datanode_4 | D | 1923 | 192.168.122.176 | f | f | -8306363816421 | datanode_5 | D | 1923 | 192.168.122.177 | f | t | 13788965016409 | 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 postgresalter 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 | 1581430616437 | datanode_1 | D | 1923 | 192.168.122.173 | t | f | -67501244116432 | datanode_2 | D | 1923 | 192.168.122.174 | f | f | -104762391416433 | datanode_3 | D | 1923 | 192.168.122.175 | f | f | 178752538216434 | datanode_4 | D | 1923 | 192.168.122.176 | f | f | -8306363816435 | datanode_5 | D | 1923 | 192.168.122.177 | f | f | 13788965016436 | 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的信息.
[参考]
5498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
