文档章节

Postgres-XL + Keepalived 数据库部署

 一撮火
发布于 2017/06/28 09:34
字数 5074
阅读 206
收藏 1

1. 安装配置

配置表信息:

主机名:

IP地址

Hosts Name

VIP

192.168.1.15

P1

192.168.1.24

192.168.1.16

P2

192.168.1.24

192.168.1.17

P3

-

192.168.1.18

P4

-

192.168.1.19

P5

-

192.168.1.20

P6

-

192.168.1.23

P7

-

192.168.1.24

P0

-

环境:

CentOS 6 x64

Postgres-XL 9.5r1.5

Keepalived v1.2.17

2. 修改HOSTS

所有节点上都需要增加脚本如下:

# vi /etc/hosts

192.168.1.24 P0 P0.db
192.168.1.15 P1 P1.db
192.168.1.16 P2 P2.db
192.168.1.17 P3 P3.db
192.168.1.18 P4 P4.db
192.168.1.19 P5 P5.db
192.168.1.20 P6 P6.db
192.168.1.23 P7 P7.db

3. 创建用户

每个节点都建立用户postgres,并且建立.ssh目录,并配置相应的权限:

# useradd postgres
# passwd postgres
# su - postgres
# mkdir ~/.ssh
# chmod 700 ~/.ssh

4. 关闭防火墙

# chkconfig iptables off
# service iptable stop

### 检查是否已经关闭

# chkconfig --list | grep ip

iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off

5. 关闭SELINUX

selinux设置:

# vi /etc/selinux/config

设置SELINUX=disabled,保存退出。

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled

# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.


6. 安装依赖包

# yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl
# yum install -y gcc gcc-c++ automake autoconf libtool make
# yum install -y jadetex.noarch docbook*
# yum -y install openssl-*

监控用命令安装(iostat):

# yum install -y sysstat

7. ssh免密码登录

7.1. P1

GTM上生成Key:

# su - postgres
$ ssh-keygen -t rsa
$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
$ chmod 600 ~/.ssh/authorized_keys

GTM上的Key复制到其他节点

$ scp ~/.ssh/authorized_keys postgres@P2:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P3:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P4:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P5:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P6:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P7:~/.ssh/

7.2. P2

GTM Sandby生成Key:

# su - postgres
$ ssh-keygen -t rsa
$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
$ chmod 600 ~/.ssh/authorized_keys

GTM Sandby上的Key复制到其他节点

$ scp ~/.ssh/authorized_keys postgres@P1:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P3:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P4:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P5:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P6:~/.ssh/
$ scp ~/.ssh/authorized_keys postgres@P7:~/.ssh/

8. Keepalived

8.1. 安装

### 在P1和P2两台上进行安装

# tar xvf keepalived-1.2.17.tar.gz
# ./configure --prefix=/app/keepalived/
# make
# make install
# cp /app/keepalived/etc/rc.d/init.d/keepalived  /etc/rc.d/init.d/
# cp /app/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# mkdir /etc/keepalived
# cp /app/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
# cp /app/keepalived/sbin/keepalived /usr/sbin/

8.2. P1

### 修改P1上的keepalive配置文件

# vi /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER
    interface eth1
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.24/24 dev eth1
    }
}

virtual_server 192.168.1.24 6666 {
    delay_loop 3
    nat_mask 255.255.255.0
    persistence_timeout 30
    protocol TCP
    real_server 192.168.1.15 6666{
    weight 3
    notify_down /etc/keepalived/keepalived.sh
    TCP_CHECK {
      connect_timeout 3
      nb_get_retry 3   
      delay_before_retry 3          
      connect_port 6666             
    }
    }
}
# vi /etc/keepalived/keepalived.sh
#!/bin/bash
pkill keepalived
# chmod +x /etc/keepalived/keepalived.sh

 

8.3. P2

### 修改P1上的keepalive配置文件

# vi /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.24/24 dev eth1
    }
    notify_master /etc/keepalived/keepalived2.sh
}

virtual_server 192.168.1.24 6666 {
    delay_loop 3
#   lb_algo wrr
#   lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 30
    protocol TCP
    real_server 192.168.1.16 6666{
    weight 3
    TCP_CHECK {
      connect_timeout 3
      nb_get_retry 3   
      delay_before_retry 3          
      connect_port 6666             
    }
    }
}
# vi /etc/keepalived/keepalived2.sh
#!/bin/bash
ssh P2 "gtm_ctl promote -Z gtm -D /db/gtmSandby"
ssh P2 "gtm_ctl reconnect -Z gtm_proxy -D /db/gtmProxy1 -o \"-s 192.168.1.24 -t 6666\""
ssh P3 "gtm_ctl reconnect -Z gtm_proxy -D /db/gtmProxy2 -o \"-s 192.168.1.24 -t 6666\""
# chmod +x /etc/keepalived/keepalived2.sh

9. 下载安装包及编译

# wget http://files.postgres-xl.org/postgres-xl-9.5r1.4.tar.gz

### 编译及安装pgxl及子项目contrib
# tar xvf postgres-xl-9.5r1.5.tar.gz
# cd postgres-xl-9.5r1.5
# ./configure --prefix=/db/pgxl
# make
# make install

### cortrib中有很多postgres很牛的工具,一般要装上。如ltree,uuid,postgres_fdw等等。
# cd contrib/
# make all
# make install

10. 配置环境变量

### 各节点都需要配置

# su - postgres
$ vi + .bashrc
export PGHOME=/db/pgxl
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
$ source .bashrc
# yum install ntpdate -y
# vi /etc/ntp.conf
# 加入下面的指定同步时间的服务器IP
server 192.168.1.253
# rpm -qa | grep ntp #查询一下是否已经安装# 启动ntpd服务
# chkconfig --list | grep ntp #看下服务情况
# chkconifg ntpd on
# service ntpd start 或 /etc/init.d/ntpd start

11. Postgres-XL组件配置

11.1. 创建数据目录

各节点分别按上面的表进行目录创建:

11.1.1. P1

# mkdir /db/gtm
# mkdir /db/coord1
# mkdir /db/tablespace
# chown -R postgres.postgres /db

11.1.2. P2

# mkdir /db/gtmProxy1
# mkdir /db/gtmSandby
# mkdir /db/coord2
# mkdir /db/tablespace
# chown -R postgres.postgres /db

11.1.3. P3

# mkdir /db/gtmProxy2
# mkdir /db/coord3
# mkdir /db/tablespace
# chown -R postgres.postgres /db

11.1.4. P4

# mkdir /db/datan1
# mkdir /db/tablespace
# mkdir /db/datan5
# chown -R postgres.postgres /db

11.1.5. P5

# mkdir /db/datan2
# mkdir /db/tablespace
# mkdir /db/datan6
# chown -R postgres.postgres /db

11.1.6. P6

# mkdir /db/datan3
# mkdir /db/tablespace
# mkdir /db/datan7
# chown -R postgres.postgres /db

11.1.7. P7

# mkdir /db/datan4
# mkdir /db/tablespace
# mkdir /db/datan8
# chown -R postgres.postgres /db

11.2. 初始化各节点

11.2.1. P1

11.2.1.1. GTM

# su - postgres
$ initgtm -Z gtm -D /db/gtm
$ vi /db/gtm/gtm.conf
nodename = 'gtm'   #节点名称,任意指定,不能与其他节点重复
listen_addresses = '*' #GTM监听的ip地址,*代表监听所有的集群ip
port =6666 #gtm监控的端口号
startup = ACT #act代表gtm是主库,如果是standy,设置为'STANDBY'
keepalives_idle = 120
keepalives_interval = 10
keepalives_count = 10


11.2.1.2. Coordinators1

初始化Coordinator1

$ initdb -D /db/coord1 --nodename coord1 -E UTF8 --locale=C -U postgres -W
$ vi /db/coord1/postgresql.conf
# - Connection Settings -
listen_addresses = '*'
port = 5432
max_connections = 200
tcp_keepalives_idle = 600
tcp_keepalives_interval = 15
tcp_keepalives_count = 5
max_prepared_transactions = 200
shared_buffers = 512MB

# CLIENT CONNECTION DEFAULTS
statement_timeout = 600000                      # in milliseconds, 0 is disabled
lock_timeout = 600000                   # in milliseconds, 0 is disabled

# DATA NODES AND CONNECTION POOLING
#----------------------------------
pooler_port = 6543
max_pool_size = 200
pool_conn_keepalive = 600
pool_maintenance_timeout = 30

# GTM CONNECTION
#--------------------------
gtm_host = 'P0'   #gtm所在的主机地址
gtm_port = 6666    #gtm配置中,gtm端口号配置为6666
pgxc_node_name = 'coord1'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 6MB

# QUERY TUNING
#----------------------
effective_cache_size = 1GB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'

修改pg_hba.conf

$ vi + /db/coord1/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5


11.2.2. P2

11.2.2.1. GTM Sandby

# su - postgres
$ initgtm -Z gtm -D /db/gtmSandby
$ vi /db/gtmSandby/gtm.conf
nodename = 'gtms'   #节点名称,任意指定,不能与其他节点重复
listen_addresses = '*' #GTM监听的ip地址,*代表监听所有的集群ip
port =6666 #gtm监控的端口号
startup = STANDBY #act代表gtm是主库,如果是standy,设置为'STANDBY'

active_host = 'P1'
active_port = 6666

keepalives_idle = 120
keepalives_interval = 10
keepalives_count = 10

11.2.2.2. GTM Proxy1

# su - postgres
$ initgtm -Z gtm_proxy -D /db/gtmProxy1
$ vi /db/gtmProxy1/gtm_proxy.conf

### 设置如下:

nodename='gtmProxy1'
listen_addresses = '*'
port=6667
worker_threads = 11   #按节点数进行设置
gtm_host='P0'
gtm_port=6666
keepalives_idle = 120
keepalives_interval = 10
keepalives_count = 10

11.2.2.3. Coordinators2

$ initdb -D /db/coord2 --nodename coord2 -E UTF8 --locale=C -U postgres -W
$ vi /db/coord2/postgresql.conf
# - Connection Settings -
listen_addresses = '*'
port = 5432
max_connections = 200
tcp_keepalives_idle = 600
tcp_keepalives_interval = 15
tcp_keepalives_count = 5
max_prepared_transactions = 200
shared_buffers = 512MB

# CLIENT CONNECTION DEFAULTS
statement_timeout = 600000                      # in milliseconds, 0 is disabled
lock_timeout = 600000                   # in milliseconds, 0 is disabled

# DATA NODES AND CONNECTION POOLING
#----------------------------------
pooler_port = 6543
max_pool_size = 200
pool_conn_keepalive = 600
pool_maintenance_timeout = 30

# GTM CONNECTION
#--------------------------
gtm_host = 'P0'   #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666    #gtm配置中,gtm端口号配置为6666
pgxc_node_name = 'coord2'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 6MB

# QUERY TUNING
#----------------------
effective_cache_size = 1GB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/coord2/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.3. P3

11.2.3.1. GTM Proxy2

# su - postgres
$ initgtm -Z gtm_proxy -D /db/gtmProxy2
$ vi /db/gtmProxy2/gtm_proxy.conf
nodename='gtmProxy2'
listen_addresses = '*'
port=6667
worker_threads = 11   #按节点数进行设置
gtm_host='P0'
gtm_port=6666
keepalives_idle = 120
keepalives_interval = 10
keepalives_count = 10

11.2.3.2. Coordinators3

$ initdb -D /db/coord3 --nodename coord3 -E UTF8 --locale=C -U postgres -W
$ vi /db/coord3/postgresql.conf
# - Connection Settings -
listen_addresses = '*'
port = 5432
max_connections = 200
tcp_keepalives_idle = 600
tcp_keepalives_interval = 15
tcp_keepalives_count = 5
max_prepared_transactions = 200
shared_buffers = 512MB

# CLIENT CONNECTION DEFAULTS
statement_timeout = 600000                      # in milliseconds, 0 is disabled
lock_timeout = 600000                   # in milliseconds, 0 is disabled

# DATA NODES AND CONNECTION POOLING
#----------------------------------
pooler_port = 6543
max_pool_size = 200
pool_conn_keepalive = 600
pool_maintenance_timeout = 30

# GTM CONNECTION
#--------------------------
gtm_host = 'P0'   #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666    #gtm配置中,gtm端口号配置为6666
pgxc_node_name = 'coord3'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 6MB

# QUERY TUNING
#----------------------
effective_cache_size = 1GB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/coord3/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.4. P4

11.2.4.1. Datanode1

$ initdb -D /db/datan1 --nodename datan1 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan1/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5551
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5561
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan1'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan1/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.4.2. Datanode5

$ initdb -D /db/datan5 --nodename datan5 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan5/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5552
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5562
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan5'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB
# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan5/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

 

11.2.5. P5

11.2.5.1. Datanode2

$ initdb -D /db/datan2 --nodename datan2 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan2/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5552
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5562
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan2'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB
# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan2/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.5.2. Datanode6

$ initdb -D /db/datan6 --nodename datan6 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan6/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5552
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5562
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan6'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan6/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.6. P6

11.2.6.1. Datanode3

$ initdb -D /db/datan3 --nodename datan3 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan3/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5551
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5561
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan3'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan3/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.6.2. Datanode7

$ initdb -D /db/datan7 --nodename datan7 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan7/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5552
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5562
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan7'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan7/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.7. P7

11.2.7.1. Datanode4

$ initdb -D /db/datan4 --nodename datan4 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan4/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5551
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5561
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan4'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan4/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

11.2.7.2. Datanode8

$ initdb -D /db/datan8 --nodename datan8 -E UTF8 --locale=C -U postgres -W
$ vi /db/datan8/postgresql.conf
# - CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 5552
max_connections = 700
# DATA NODES AND CONNECTION POOLING
#----------------------------------------------
pooler_port = 5562
max_pool_size = 700
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_prepared_transactions = 700
shared_buffers = 512MB

# GTM CONNECTION
#-----------------------------
gtm_host = 'P0'    #gtm所在的主机地址,有sandby时为虚拟IP地址
gtm_port = 6666 #gtm端口号
pgxc_node_name = 'datan8'

# RESOURCE USAGE (except WAL)
#-----------------------------
work_mem = 1MB

# QUERY TUNING
#----------------------
effective_cache_size = 512MB

# CLIENT CONNECTION DEFAULTS
#--------------------------
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
$ vi + /db/datan8/pg_hba.conf
# "local" is for Unix domain socket connections only
host    all             all             192.168.1.15/32     trust
host    all             all             192.168.1.16/32     trust
host    all             all             192.168.1.17/32     trust
host    all             all             192.168.1.18/32     trust
host    all             all             192.168.1.19/32     trust
host    all             all             192.168.1.20/32     trust
host    all             all             192.168.1.23/32     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

 

11.2.8. 核对各节点配置

11.2.8.1. 核对端口

$ find /db -name postgresql.conf | xargs grep "port = "

11.2.8.2. 核对GTM参数

$ find /db -name postgresql.conf | xargs grep "gtm_"

11.2.8.3. 核对节点内存

$ find /db -name postgresql.conf | xargs grep shared_buffers

11.2.8.4. 核对节点名

$ find /db -name postgresql.conf | xargs grep pgxc_node_name

11.2.8.5. 核对节点最大连接数

$ find /db -name postgresql.conf | xargs grep max_connections

11.2.8.6. 核对节点最大可连接数

$ find /db -name postgresql.conf | xargs grep max_prepared_transactions

11.2.8.7. 核对节点work_mem

$ find /db -name postgresql.conf | xargs grep work_mem

11.2.8.8. 核对节点effective_cache_size

$ find /db -name postgresql.conf | xargs grep effective_cache_size

12. 配置集群启动与停止

该操作在P1服务器上执行。

12.1. 生成pgxc_ctl配置文件

$ pgxc_ctl
/bin/bash
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
ERROR: File "/home/postgres/pgxc_ctl/pgxc_ctl.conf" not found or not a regular file. No such file or directory
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
Reading configuration using /home/postgres/pgxc_ctl/pgxc_ctl_bash --home /home/postgres/pgxc_ctl --configuration /home/postgres/pgxc_ctl/pgxc_ctl.conf
Finished reading configuration.
   ******** PGXC_CTL START ***************

Current directory: /home/postgres/pgxc_ctl
PGXC prepare  ---执行该命令将会生成一份配置文件模板
PGXC ^C  ---按ctrl c退出

 

12.2. 编辑pgxc_ctl.conf配置文件

$ vi /home/postgres/pgxc_ctl/pgxc_ctl.conf

### 修改配置文件内的对应内容,内容与安装配置表一致

pgxcInstallDir=$PGHOME
pgxlDATA=/db   # 自定义,用于下面的其他配置

pgxcOwner=postgres

#---- GTM Master -----------------------------------------
gtmName=gtm
gtmMasterServer=P1
gtmMasterPort=6666
gtmMasterDir=$pgxlDATA/gtm

#---- GTM Slave -----------------------------------------------
gtmSlave=y
gtmSlaveName=gtms
gtmSlaveServer=P2
gtmSlavePort=6666
gtmSlaveDir=$pgxlDATA/gtmSandby

#---- GTM-Proxy Master -------
gtmProxyDir=$pgxlDATA

gtmProxy=y                              
gtmProxyNames=(gtmProxy1 gtmProxy2)  
gtmProxyServers=(P2 P3)           
gtmProxyPorts=(6667 6667)               
gtmProxyDirs=$gtmProxyDir/gtmProxy1 $gtmProxyDir/gtmProxy2 
gtmPxyExtraConfig=none
gtmPxySpecificExtraConfig=(none none)

#---- Coordinators ---------
coordMasterDir=$pgxlDATA
coordSlaveDir=$pgxlDATA/coord_slave
coordArchLogDir=$pgxlDATA/coord_archlog

coordNames=(coord1 coord2 coord3)      
coordPorts=(5432 5432 5432)          
poolerPorts=(6543 6543 6543)         
coordPgHbaEntries=(0.0.0.0/0)

coordMasterServers=(P1 P2 P3)        
coordMasterDirs=($coordMasterDir/coord1 $coordMasterDir/coord2 $coordMasterDir/coord3)
coordMaxWALsernder=0    #没设置备份节点,设置为0
coordMaxWALSenders=($coordMaxWALsernder $coordMaxWALsernder $coordMaxWALsernder) #数量保持和coordMasterServers一致

coordSlave=n
coordSpecificExtraConfig=(none none none)
coordSpecificExtraPgHba=(none none none)

#---- Datanodes ----------
datanodeMasterDir=$pgxlDATA
datanodeSlaveDir=$pgxlDATA/dn_slave
datanodeArchLogDir=$pgxlDATA/datanode_archlog

primaryDatanode=datan1             # 主数据节点
datanodeNames=(datan1 datan2  datan3 datan4 datan5 datan6 datan7 datan8)
datanodePorts=(5551 5551 5551 5551 5552 5552 5552 5552)   
datanodePoolerPorts=(5561 5561 5561 5561 5562 5562 5562 5562) 
datanodePgHbaEntries=(0.0.0.0/0)

datanodeMasterServers=(P4 P5 P6 P7 P4 P5 P6 P7)
datanodeMasterDirs=($datanodeMasterDir/datan1 $datanodeMasterDir/datan2 $datanodeMasterDir/datan3 $datanodeMasterDir/datan4 $datanodeMasterDir/datan5 $datanodeMasterDir/datan6 $datanodeMasterDir/datan7 $datanodeMasterDir/datan8)
datanodeMaxWalSender=0
datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender)

datanodeSlave=n

12.3.启动

启动顺序为Postgres-XL → keepalived
12.3.1.P1

$ pgxc_ctl -c /db/pgxc_ctl.conf start all
$ su -
# /etc/init.d/keepalived start

12.3.2.P2

# /etc/init.d/keepalived start


12.4.停止

停止顺序为Postgres-XL → keepalived
12.4.1.P1

$ pgxc_ctl -c /db/pgxc_ctl.conf stop all
$ su -
# /etc/init.d/keepalived stop

12.4.2.P2

# /etc/init.d/keepalived stop

 

14. 注册节点

查询节点注册情况的SQL:

postgres=# select * from pgxc_node;

 

14.1. Coordinators

14.1.1. P1

$ psql -p 5432 -Upostgres
create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

alter node coord1 with (type=coordinator,host='P1', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

14.1.2. P2

$ psql -p 5432 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

alter node coord2 with (type=coordinator,host='P2', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

14.1.3. P3

$ psql -p 5432 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

alter node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

14.2. Datanodes

14.2.1. P4

14.2.1.1. Datanode1

$ psql -p 5551 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

alter node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

 

14.2.1.2. Datanode5

$ psql -p 5552 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

alter node datan5 with (type=datanode, host='P4',port=5552);

14.2.2. P5

14.2.2.1. Datanode2

$ psql -p 5551 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

alter node datan2 with (type=datanode, host='P5',port=5551);

14.2.2.2. Datanode6

$ psql -p 5552 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

alter node datan6 with (type=datanode, host='P5',port=5552);

14.2.3. P6

14.2.3.1. Datanode3

$ psql -p 5551 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

alter node datan3 with (type=datanode, host='P6',port=5551);

14.2.3.2. Datanode7

$ psql -p 5552 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

alter node datan7 with (type=datanode, host='P6',port=5552);

14.2.4. P7

14.2.4.1. Datanode4

$ psql -p 5551 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

create node datan8 with (type=datanode, host='P7',port=5552);

alter node datan4 with (type=datanode, host='P7',port=5551);

14.2.4.2. Datanode8

$ psql -p 5552 -Upostgres
create node coord1 with (type=coordinator,host='P1', port=5432);

create node coord2 with (type=coordinator,host='P2', port=5432);

create node coord3 with (type=coordinator,host='P3', port=5432);

create node datan1 with (type=datanode, host='P4',port=5551,primary,preferred);

create node datan2 with (type=datanode, host='P5',port=5551);

create node datan3 with (type=datanode, host='P6',port=5551);

create node datan4 with (type=datanode, host='P7',port=5551);

create node datan5 with (type=datanode, host='P4',port=5552);

create node datan6 with (type=datanode, host='P5',port=5552);

create node datan7 with (type=datanode, host='P6',port=5552);

alter node datan8 with (type=datanode, host='P7',port=5552);

14.2.5. 核对各节点注册信息

14.2.5.1. Coordinators

$ psql -p 5432 -Upostgres -c"select * from pgxc_node order by node_host, node_port;"

14.2.5.2. Datanodes

$ psql -p 5551 -Upostgres -c"select * from pgxc_node order by node_host, node_port;"

$ psql -p 5552 -Upostgres -c"select * from pgxc_node order by node_host, node_port;"

 

© 著作权归作者所有

粉丝 1
博文 3
码字总数 5404
作品 0
黄浦
私信 提问
Postgres-XL 10R1 发布,SQL 数据库解决方案

第二象限公司(2ndQuadrant)近期发布了Postgres-XL最新版本10R1。感兴趣的同学可以访问该网站并下载试用。 Postgres-XL是一个ACID的、可方便进行水平扩展的 SQL 数据库解决方案。基于 Post...

闻术苑
2018/10/29
1K
0
postgresql9.6主从高可用源码环境编译配置详解

系统版本:centos7 8核 32G内存 主从服务器IP: 192.168.125.33 postgreSQL master 192.168.125.34 postgreSQL slave 1、创建数据库管理账户 # groupadd pggroup # useradd -g pggroup pgus......

断臂人
01/06
0
0
Postgres-XL 10R1 release版本发布

2018年10月25日2ndQuadrant发布了Postgres-XL 10R1版本。Postgres-XL是一个大规模并行数据库,它基于PG社区版本开发,并和PG高度兼容,支持Business Intelligence负载和读写大事务负载。 PG...

yzs的专栏
07/21
0
0
Postgres XL FAQ

Q. What does XL stand for? XL is short for eXtensible Lattice. It also connotes an extra large version of PostgreSQL, in this case across multiple systems. Q. Is this a “NoSQL......

javasql
2014/07/31
360
0
Postgres-XL 9.5 R1 Beta1 发布

Postgres-XL 9.5 R1 Beta1 发布,Postgres-XL 是一个通用的 ACID 开源的、可方便进行水平扩展的 SQL 数据库解决方案。基于 PostgreSQL 9.5 数据库构建。Postgres-XL 可用于商业智能、大数据分...

oschina
2016/02/19
1K
4

没有更多内容

加载失败,请刷新页面

加载更多

Nginx 快速安装详解

一、Nginx Nginx (engine x) 是一个高性能的HTTP和反向代理web服务器,同时也提供了IMAP/POP3/SMTP服务。Nginx是由伊戈尔·赛索耶夫为俄罗斯访问量第二的Rambler.ru站点(俄文:Рамбле...

网络小虾米
17分钟前
4
0
技术分享 | slave_relay_log_info 表认知的一些展开

作者:胡呈清 slave_relay_log_info 表是这样的: mysql> select * from mysql.slave_relay_log_info\G *************************** 1. row *************************** Number_of_lin......

爱可生
19分钟前
3
0
nginx配置http访问自动跳转到https

server {listen 80;server_name www.域名.com;rewrite ^(.*) https://$server_name$1 permanent;}server {listen 443;server_name www.域名.com;root /home/www;ssl on;......

很好亦平凡ms
19分钟前
3
0
SpreadJS:一款中国研发的类Excel开发工具,功能涵盖Excel的 95% 以上

Excel 作为一款深受用户喜爱的电子表格工具,借助其直观的界面、出色的计算性能、数据分析和图表,已经成为数据统计领域不可或缺的软件之一。 基于Excel对数据处理与分析的卓越表现,把Excel...

葡萄城技术团队
19分钟前
2
0
用javafx框架tornadofx做了个天气预报的程序

class WeatherApp : App(WeatherView::class)class WeatherView : View("十五天天气预报") { val weatherVM: WeatherViewModel by inject() val controller: WeatherController by......

oschina4cyy
23分钟前
3
1

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部