Postgres-X2部署步驟

ygfb 9年前發布 | 17K 次閱讀 Postgres PostgreSQL 數據庫服務器

Postgre2015大象會，大家都很關注PostgreSQL的集群，目前，開發人員已經轉向Postgres-X2，近期根據自己和同事部署xl的過程部署了一下Postgres-X2。本次部署試驗是利用pgxc_ctl部署的，更加靈活的部署集群。

1、整體概括：

一共四個節點，一個gtm，一個coordinator，兩個datanode。

a. GTM節點
        IP:192.168.238.129
        nodename:gtm
        port:6666
    b.coordinator
        IP:192.168.238.130
        nodename:coord1
        port:5432
        pooler_port:6668
    c.datanode1
        IP:192.168.238.131
        nodename:datanode1
        port:15432
        pooler_port:6669
    d.datanode2
        IP:192.168.238.132
        nodename:datanode2
        port:15432
        pooler_port:6669

2、準備工作(不特別指明，四個節點做相同的操作)：

a.編譯安裝pgx2，同時編譯contrib。

./configure --prefix=/opt/pgx2
make; make install
cd contrib
make; make install

b.建立用戶postgres，將安裝目錄屬主賦給postgres。

chown -R postgres:postgres pgx2

c.配置ssh連接

[postgres@localhost~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
ea:c9:48:2d:dc:0d:ab:9b:3d:99:cb:bd:db:3b:ba:fa root@localhost.localdomain
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|                 |
|                 |
|                 |
|      . S        |
|   . o =         |
|    + =o.        |
|   . X+o ..      |
|    =.O=E=oo     |
+-----------------+
[postgres@localhost~]#

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

vi /etc/hosts
192.168.238.129 localhost.localdomain

--分發密鑰，gtm節點向其他節點分發
scp ~/.ssh/authorized_keys postgres@192.168.238.130
scp ~/.ssh/authorized_keys postgres@192.168.238.131
scp ~/.ssh/authorized_keys postgres@192.168.238.132

d.配置環境變量

[postgres@localhost ~]$ cat .bashrc 
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
    . /etc/bashrc
fi

export PGHOME=/opt/pgx2/
export PGUSER=postgres
export LD_LIBRARY_PATH=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH

# User specific aliases and functions
[postgres@localhost ~]$

建議：

在ssh連接時效率很慢，可以用ssh -v進行檢測，這里就不做說明了。

修改/etc/ssh/sshd_config中的GSSAPIAuthentication和UseDNS為no就可提高ssh連接速度。

為了方便起見我將所有節點的iptables關閉，大家可自行配置。

3、部署節點

a.配置pgxc_ctl.conf

--在/home/postgres/pgxc_ctl下

--conf內容
cat pgxc_ctl.conf
#user and path
pgxcOwner=postgres
pgxcUser=$pgxcOwner
pgxcInstallDir=/opt/pgx2

#gtm and gtmproxy
gtmMasterDir=$HOME/pgxc/nodes/gtm
gtmMasterPort=6666
gtmMasterServer=192.168.238.129
gtmSlave=n

#gtmproxy
gtmProxy=n
gtmProxyDir=$HOME/pgxc/nodes/coord
gtmProxyNames=(gtm_pxy1)
gtmProxyServers=(192.168.238.130)
gtmProxyPorts=(20001)
gtmProxyDirs=($gtmProxyDir/gtm_pxy1)
gtmPxyExtraConfig=(none)
gtmPxySpecificExtraConfig=(none)

#coordinator
coordMasterDir=$HOME/pgxc/nodes/coord
coordNames=(coord1)
coordPorts=(5432)
poolerPorts=(6668)
coordPgHbaEntries=(192.168.238.0/24)
coordMasterServers=(192.168.238.130)
coordMasterDirs=($coordMasterDir/coord1)
coordMaxWALsernder=0
coordMaxWALSenders=($coordMaxWALsernder)
coordSlave=n
coordSpecificExtraConfig=(none)
coordSpecificExtraPgHba=(none)

#datanode
datanodeNames=(datanode1 datanode2)
datanodePorts=(15432 15432)
datanodePoolerPorts=(6669 6669)
datanodePgHbaEntries=(192.168.238.0/24)
datanodeMasterServers=(192.168.238.131 192.168.238.132)
datanodeMasterDir=$HOME/pgxc/nodes/dn_master
datanodeMasterDirs=($datanodeMasterDir/datanode1 $datanodeMasterDir/datanode2)
datanodeMaxWALsernder=0
datanodeMaxWALSenders=($datanodeMaxWALsernder $datanodeMaxWALsernder)
datanodeSlave=n
primaryDatanode=datanode1
datanodeSpecificExtraConfig=(none none)
datanodeSpecificExtraPgHba=(none none)

b.利用pgxc_ctl部署節點

pgxc_ctl init all

[postgres@localhost ~]$ pgxc_ctl init all
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
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 to read configuration.
******** PGXC_CTL START ***************

Current directory: /home/postgres/pgxc_ctl
Initialize GTM master
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.

fixing permissions on existing directory /home/postgres/pgxc/nodes/gtm ... ok
creating configuration files ... ok

Success. You can now start the GTM server using:

gtm -D /home/postgres/pgxc/nodes/gtm
or
gtm_ctl -Z gtm -D /home/postgres/pgxc/nodes/gtm -l logfile start

waiting for server to shut down... done
server stopped
Done.
Start GTM master
gtm_ctl: PID file "/home/postgres/pgxc/nodes/gtm/gtm.pid" does not exist
Is server running?
server starting
Initialize all the coordinator masters.
Initialize coordinator master coord1.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/pgxc/nodes/coord/coord1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /home/postgres/pgxc/nodes/coord/coord1/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
creating cluster information ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
You can now start the database server of the Postgres-XC coordinator using:

postgres --coordinator -D /home/postgres/pgxc/nodes/coord/coord1
or
pg_ctl start -D /home/postgres/pgxc/nodes/coord/coord1 -Z coordinator -l logfile

You can now start the database server of the Postgres-XC datanode using:

postgres --datanode -D /home/postgres/pgxc/nodes/coord/coord1
or
pg_ctl start -D /home/postgres/pgxc/nodes/coord/coord1 -Z datanode -l logfile

Done.
Starting coordinator master.
Starting coordinator master coord1
Done.
Initialize all the datanode masters.
Initialize the datanode master datanode1.
Initialize the datanode master datanode2.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/pgxc/nodes/dn_master/datanode1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /home/postgres/pgxc/nodes/dn_master/datanode1/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
creating cluster information ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
You can now start the database server of the Postgres-XC coordinator using:

postgres --coordinator -D /home/postgres/pgxc/nodes/dn_master/datanode1
or
pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode1 -Z coordinator -l logfile

You can now start the database server of the Postgres-XC datanode using:

postgres --datanode -D /home/postgres/pgxc/nodes/dn_master/datanode1
or
pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode1 -Z datanode -l logfile

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/pgxc/nodes/dn_master/datanode2 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /home/postgres/pgxc/nodes/dn_master/datanode2/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
creating cluster information ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success.
You can now start the database server of the Postgres-XC coordinator using:

postgres --coordinator -D /home/postgres/pgxc/nodes/dn_master/datanode2
or
pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode2 -Z coordinator -l logfile

You can now start the database server of the Postgres-XC datanode using:

postgres --datanode -D /home/postgres/pgxc/nodes/dn_master/datanode2
or
pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode2 -Z datanode -l logfile

Done.
Starting all the datanode masters.
Starting datanode master datanode1.
Starting datanode master datanode2.
Done.
ALTER NODE coord1 WITH (HOST='192.168.238.130', PORT=5432);
ALTER NODE
CREATE NODE datanode1 WITH (TYPE='datanode', HOST='192.168.238.131', PORT=15432, PRIMARY);
CREATE NODE
CREATE NODE datanode2 WITH (TYPE='datanode', HOST='192.168.238.132', PORT=15432);
CREATE NODE
Done.

4、運行演示

[postgres@localhost ~]$ psql -h 192.168.238.130 -p 5432 -d postgres -U postgres
psql (PGXC 1.3devel, based on PG 9.4beta1)
Type "help" for help.

postgres=# create table test(id int, name text) distribute by replication;
CREATE TABLE
postgres=# insert into test values (1,'wang'),(2,'shuo');
INSERT 0 2
postgres=# select * from test;
 id | name 
----+------
  1 | wang
  2 | shuo
(2 rows)

postgres=# \q
[postgres@localhost ~]$ psql -h 192.168.238.131 -p 15432 -d postgres -U postgres 
psql (PGXC 1.3devel, based on PG 9.4beta1)
Type "help" for help.

postgres=# select * from test;
 id | name 
----+------
  1 | wang
  2 | shuo
(2 rows)

postgres=# \q
[postgres@localhost ~]$ psql -h 192.168.238.132 -p 15432 -d postgres -U postgres 
psql (PGXC 1.3devel, based on PG 9.4beta1)
Type "help" for help.

postgres=# select * from test;
 id | name 
----+------
  1 | wang
  2 | shuo
(2 rows)

postgres=#

總結：

相較于手動部署，利用pgxc_ctl部署效率以及正確率是非常高的，歡迎大家嘗試。

來自：http://my.oschina.net/Suregogo/blog/536537

本文由用戶 ygfb 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1449408397034.html

Postgres PostgreSQL 數據庫服務器

Postgres-X2部署步驟

相關經驗

相關資訊

相關文檔

目錄