在国产芯片服务器上构建postgresql-xl的大数据平台

基于海之舟大数据平台服务器，操作系统，使用二台设备构建。

端口、ip配置见下表

大数据软件postgresql-xl，ssh软件操作系统已自带，用户zs(操作系统已创建）,工作目录/home/zs/
1.以下操作二台服务器均需配置
su zs
nano /etc/hosts //编辑文件，加入以下行
192.168.1.125 gtm
192.168.1.125 coordinator1
192.168.1.125 datanode1
192.168.1.8 coordinator2
192.168.1.8 datanode2
vi .bashrc //配置环境变量
export PGHOME=/usr/local/pgxl10
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
export dataDirRoot=$HOME/data
su root //重启服务器，使配置生效
reboot

2.在gtm上操作
2.1设置ssh 二台设备无密码登陆
su zs
ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
ssh zs@datanode1 mkdir -p .ssh
cat .ssh/id_rsa.pub | ssh zs@datanode1 ‘cat >> .ssh/authorized_keys’
ssh zs@datanode2 mkdir -p .ssh
cat .ssh/id_rsa.pub | ssh zs@datanode2 ‘cat >> .ssh/authorized_keys’
以上操作，提问全部回车。
2.2 建立大数据库
zs@bigdataA:~$pgxc_ctl
/bin/bash
Installing pgxc_ctl_bash script as /home/zs/pgxc_ctl/pgxc_ctl_bash.
Installing pgxc_ctl_bash script as /home/zs/pgxc_ctl/pgxc_ctl_bash.
Reading configuration using /home/zs/pgxc_ctl/pgxc_ctl_bash --home /home/zs/pgxc_ctl --configuration /home/zs/pgxc_ctl/pgxc_ctl.conf
Finished reading configuration.
******** PGXC_CTL START ***************

Current directory: /home/zs/pgxc_ctl
PGXC prepare config empty
PGXC exit
zs@bigdataA:~$ nano /home/zs/pgxc_ctl/pgxc_ctl.conf
zs@bigdataA:~$ cp /home/zs/pgxc_ctl/pgxc_ctl.conf /home/zs/pgxc_ctl.conf-all-in-2-init
zs@bigdataA:~$ pgxc_ctl
/bin/bash
Installing pgxc_ctl_bash script as /home/zs/pgxc_ctl/pgxc_ctl_bash.
Installing pgxc_ctl_bash script as /home/zs/pgxc_ctl/pgxc_ctl_bash.
Reading configuration using /home/zs/pgxc_ctl/pgxc_ctl_bash --home /home/zs/pgxc_ctl --configuration /home/zs/pgxc_ctl/pgxc_ctl.conf
Finished reading configuration.
******** PGXC_CTL START ***************
Current directory: /home/zs/pgxc_ctl
PGXC add gtm master gtm gtm 20001 $dataDirRoot/gtm
Initialize GTM master
The files belonging to this GTM system will be owned by user “zs”.
This user must also own the server process.

fixing permissions on existing directory /home/zs/data/gtm … ok
creating configuration files … ok
creating control file … ok

Success.
Done.
Start GTM master
server starting
PGXC add coordinator master coord1 gtm 30001 30011 $dataDirRoot/coord_master.1 none none
Actual Command: ssh zs@gtm “( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/coord_master.1 --nodename coord1 ) > /tmp/bigdataA_STDOUT_671_7 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@gtm:/tmp/bigdataA_STDOUT_671_7 /tmp/STDOUT_671_8 > /dev/null 2>&1
The files belonging to this database system will be owned by user “zs”.
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.

creating directory /home/zs/data/coord_master.1 … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … creating cluster information … 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.
Starting coordinator master coord1
2016-11-04 01:22:10.335 CST [1005] LOG: listening on IPv4 address “0.0.0.0”, port 30001
2016-11-04 01:22:10.336 CST [1005] LOG: listening on IPv6 address “::”, port 30001
2016-11-04 01:22:10.377 CST [1005] LOG: listening on Unix socket “/tmp/.s.PGSQL.30001”
2016-11-04 01:22:10.435 CST [1005] LOG: redirecting log output to logging collector process
2016-11-04 01:22:10.435 CST [1005] HINT: Future log output will appear in directory “pg_log”.
Done.
ALTER NODE
pgxc_pool_reload
t
(1 row)

vacuumdb: vacuuming database “postgres”
vacuumdb: vacuuming database “template1”
PGXC

PGXC add coordinator master coord1 gtm 30001 30011 $dataDirRoot/coord_master.1 none none
Actual Command: ssh zs@gtm “( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/coord_master.1 --nodename coord1 ) > /tmp/bigdataA_STDOUT_671_7 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@gtm:/tmp/bigdataA_STDOUT_671_7 /tmp/STDOUT_671_8 > /dev/null 2>&1
The files belonging to this database system will be owned by user “zs”.
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.

creating directory /home/zs/data/coord_master.1 … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … creating cluster information … 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.
Starting coordinator master coord1
2016-11-04 01:22:10.335 CST [1005] LOG: listening on IPv4 address “0.0.0.0”, port 30001
2016-11-04 01:22:10.336 CST [1005] LOG: listening on IPv6 address “::”, port 30001
2016-11-04 01:22:10.377 CST [1005] LOG: listening on Unix socket “/tmp/.s.PGSQL.30001”
2016-11-04 01:22:10.435 CST [1005] LOG: redirecting log output to logging collector process
2016-11-04 01:22:10.435 CST [1005] HINT: Future log output will appear in directory “pg_log”.
Done.
ALTER NODE
pgxc_pool_reload
t
(1 row)

vacuumdb: vacuuming database “postgres”
vacuumdb: vacuuming database “template1”
PGXC add coordinator master coord2 datanode2 30002 30012 $dataDirRoot/coord_master.2 none none
Actual Command: ssh zs@datanode2 “( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/coord_master.2 --nodename coord2 ) > /tmp/bigdataA_STDOUT_671_11 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@datanode2:/tmp/bigdataA_STDOUT_671_11 /tmp/STDOUT_671_12 > /dev/null 2>&1
The files belonging to this database system will be owned by user “zs”.
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.

creating directory /home/zs/data/coord_master.2 … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … creating cluster information … 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.
INFO: please do not close this session until you are done adding the new node
pgxc_lock_for_backup
t
(1 row)

Actual Command: ssh zs@datanode2 “( pg_ctl start -w -Z restoremode -D $dataDirRoot/coord_master.2 -o ‘-i -b’ ) > /tmp/bigdataA_STDOUT_671_14 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@datanode2:/tmp/bigdataA_STDOUT_671_14 /tmp/STDOUT_671_15 > /dev/null 2>&1
2019-10-07 11:26:12.056 CST [1370] LOG: listening on IPv4 address “0.0.0.0”, port 30002
2019-10-07 11:26:12.056 CST [1370] LOG: listening on IPv6 address “::”, port 30002
2019-10-07 11:26:12.106 CST [1370] LOG: listening on Unix socket “/tmp/.s.PGSQL.30002”
2019-10-07 11:26:12.183 CST [1370] LOG: redirecting log output to logging collector process
2019-10-07 11:26:12.183 CST [1370] HINT: Future log output will appear in directory “pg_log”.
SET
SET
SET
binary_upgrade_set_next_pg_authid_oid
(1 row)
ALTER ROLE
UPDATE 1
UPDATE 1
REVOKE
GRANT
CREATE NODE
You are now connected to database “postgres” as user “zs”.
SET
SET
SET
SET
SET
SET
set_config
(1 row)

SET
SET
SET
UPDATE 1
UPDATE 1
COMMENT
You are now connected to database “template1” as user “zs”.
SET
SET
SET
SET
SET
SET
set_config

(1 row)

SET
SET
SET
UPDATE 1
UPDATE 1
COMMENT
Actual Command: ssh zs@datanode2 “( pg_ctl stop -w -Z restoremode -D $dataDirRoot/coord_master.2 ) > /tmp/bigdataA_STDOUT_671_16 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@datanode2:/tmp/bigdataA_STDOUT_671_16 /tmp/STDOUT_671_17 > /dev/null 2>&1
Starting coordinator master coord2
2019-10-07 11:26:17.231 CST [1443] LOG: listening on IPv4 address “0.0.0.0”, port 30002
2019-10-07 11:26:17.231 CST [1443] LOG: listening on IPv6 address “::”, port 30002
2019-10-07 11:26:17.282 CST [1443] LOG: listening on Unix socket “/tmp/.s.PGSQL.30002”
2019-10-07 11:26:17.358 CST [1443] LOG: redirecting log output to logging collector process
2019-10-07 11:26:17.358 CST [1443] HINT: Future log output will appear in directory “pg_log”.
Done.
CREATE NODE
t
(1 row)

ALTER NODE
pgxc_pool_reload
t
(1 row)

vacuumdb: vacuuming database “postgres”
vacuumdb: vacuuming database “template1”
PGXC add datanode master dn1 gtm 40001 40011 $dataDirRoot/dn_master.1 none none none
Actual Command: ssh zs@gtm “( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/dn_master.1 --nodename dn1 ) > /tmp/bigdataA_STDOUT_671_20 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@gtm:/tmp/bigdataA_STDOUT_671_20 /tmp/STDOUT_671_21 > /dev/null 2>&1
The files belonging to this database system will be owned by user “zs”.
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.
creating directory /home/zs/data/dn_master.1 … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … creating cluster information … 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.
INFO: please do not close this session until you are done adding the new node
pgxc_lock_for_backup
t
(1 row)

Actual Command: ssh zs@gtm “( pg_ctl start -w -Z restoremode -D $dataDirRoot/dn_master.1 -o ‘-i -b’ ) > /tmp/bigdataA_STDOUT_671_23 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@gtm:/tmp/bigdataA_STDOUT_671_23 /tmp/STDOUT_671_24 > /dev/null 2>&1
2016-11-04 01:26:13.536 CST [1328] LOG: listening on IPv4 address “0.0.0.0”, port 40001
2016-11-04 01:26:13.536 CST [1328] LOG: listening on IPv6 address “::”, port 40001
2016-11-04 01:26:13.580 CST [1328] LOG: listening on Unix socket “/tmp/.s.PGSQL.40001”
2016-11-04 01:26:13.622 CST [1328] LOG: redirecting log output to logging collector process
2016-11-04 01:26:13.622 CST [1328] HINT: Future log output will appear in directory “pg_log”.
SET
SET
SET
binary_upgrade_set_next_pg_authid_oid
(1 row)

ALTER ROLE
UPDATE 1
UPDATE 1
REVOKE
GRANT
CREATE NODE
CREATE NODE
You are now connected to database “postgres” as user “zs”.
SET
SET
SET
SET
SET
SET
set_config
(1 row)

SET
SET
SET
UPDATE 1
UPDATE 1
COMMENT
You are now connected to database “template1” as user “zs”.
SET
SET
SET
SET
SET
SET
set_config
(1 row)

SET
SET
SET
UPDATE 1
UPDATE 1
COMMENT
Actual Command: ssh zs@gtm “( pg_ctl stop -w -Z restoremode -D $dataDirRoot/dn_master.1 ) > /tmp/bigdataA_STDOUT_671_25 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@gtm:/tmp/bigdataA_STDOUT_671_25 /tmp/STDOUT_671_26 > /dev/null 2>&1
Starting datanode master dn1.
2016-11-04 01:26:18.175 CST [1447] LOG: listening on IPv4 address “0.0.0.0”, port 40001
2016-11-04 01:26:18.176 CST [1447] LOG: listening on IPv6 address “::”, port 40001
2016-11-04 01:26:18.191 CST [1447] LOG: listening on Unix socket “/tmp/.s.PGSQL.40001”
2016-11-04 01:26:18.246 CST [1447] LOG: redirecting log output to logging collector process
2016-11-04 01:26:18.246 CST [1447] HINT: Future log output will appear in directory “pg_log”.
Done.
CREATE NODE
pgxc_pool_reload t
(1 row)

CREATE NODE
pgxc_pool_reload
t
(1 row)

EXECUTE DIRECT
pgxc_pool_reload
t
(1 row)

PGXC add datanode master dn2 datanode2 40002 40012 $dataDirRoot/dn_master.2 none none none
Actual Command: ssh zs@datanode2 “( PGXC_CTL_SILENT=1 initdb -D $dataDirRoot/dn_master.2 --nodename dn2 ) > /tmp/bigdataA_STDOUT_671_29 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@datanode2:/tmp/bigdataA_STDOUT_671_29 /tmp/STDOUT_671_30 > /dev/null 2>&1
The files belonging to this database system will be owned by user “zs”.
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.

creating directory /home/zs/data/dn_master.2 … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … creating cluster information … 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.
INFO: please do not close this session until you are done adding the new node
pgxc_lock_for_backup
t
(1 row)

Actual Command: ssh zs@datanode2 “( pg_ctl start -w -Z restoremode -D $dataDirRoot/dn_master.2 -o ‘-i -b’ ) > /tmp/bigdataA_STDOUT_671_32 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@datanode2:/tmp/bigdataA_STDOUT_671_32 /tmp/STDOUT_671_33 > /dev/null 2>&1
2019-10-07 11:31:28.128 CST [1586] LOG: listening on IPv4 address “0.0.0.0”, port 40002
2019-10-07 11:31:28.128 CST [1586] LOG: listening on IPv6 address “::”, port 40002
2019-10-07 11:31:28.178 CST [1586] LOG: listening on Unix socket “/tmp/.s.PGSQL.40002”
2019-10-07 11:31:28.255 CST [1586] LOG: redirecting log output to logging collector process
2019-10-07 11:31:28.255 CST [1586] HINT: Future log output will appear in directory “pg_log”.
SET
SET
SET
binary_upgrade_set_next_pg_authid_oid
(1 row)

ALTER ROLE
UPDATE 1
UPDATE 1
REVOKE
GRANT
CREATE NODE
CREATE NODE
CREATE NODE
You are now connected to database “postgres” as user “zs”.
SET
SET
SET
SET
SET
SET
set_config

(1 row)

SET
SET
SET
UPDATE 1
UPDATE 1
COMMENT
You are now connected to database “template1” as user “zs”.
SET
SET
SET
SET
SET
SET
set_config
(1 row)

SET
SET
SET
UPDATE 1
UPDATE 1
COMMENT
Actual Command: ssh zs@datanode2 “( pg_ctl stop -w -Z restoremode -D $dataDirRoot/dn_master.2 ) > /tmp/bigdataA_STDOUT_671_34 2>&1” < /dev/null > /dev/null 2>&1
Bring remote stdout: scp zs@datanode2:/tmp/bigdataA_STDOUT_671_34 /tmp/STDOUT_671_35 > /dev/null 2>&1
Starting datanode master dn2.
2019-10-07 11:31:33.339 CST [1659] LOG: listening on IPv4 address “0.0.0.0”, port 40002
2019-10-07 11:31:33.339 CST [1659] LOG: listening on IPv6 address “::”, port 40002
2019-10-07 11:31:33.389 CST [1659] LOG: listening on Unix socket “/tmp/.s.PGSQL.40002”
2019-10-07 11:31:33.466 CST [1659] LOG: redirecting log output to logging collector process
2019-10-07 11:31:33.466 CST [1659] HINT: Future log output will appear in directory “pg_log”.
Done.
CREATE NODE
pgxc_pool_reload
t
(1 row)

CREATE NODE
pgxc_pool_reload
t
(1 row)

EXECUTE DIRECT
pgxc_pool_reload
t
(1 row)

EXECUTE DIRECT
pgxc_pool_reload
t
(1 row)

PGXC
可看到，所有节点均已正常启动
PGXC monitor all
Running: gtm master
Running: coordinator master coord1
Running: coordinator master coord2
Running: datanode master dn1
Running: datanode master dn2
PGXC exit
2.3 操作大数据库
zs@bigdataA:~$ psql -h datanode1 -p 30001 postgres zs //进入数据库
psql (PGXL 10r1.1, based on PG 10.6 (Postgres-XL 10r1.1))
Type “help” for help.
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------±----------±----------±----------±---------------±-----------------±------------
coord1 | C | 30001 | gtm | f | f | 1885696643
coord2 | C | 30002 | datanode2 | f | f | -1197102633
dn1 | D | 40001 | gtm | f | f | -560021589
dn2 | D | 40002 | datanode2 | f | f | 352366662
(4 rows)
postgres=# CREATE TABLE t3(bid serial PRIMARY KEY,id integer,xm varchar(255),zh varchar(255),nr bytea,tjsj timestamp DEFAULT now()); //建立表t3
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------±-----------±---------±------
public | t1 | table | zs
public | t1_bid_seq | sequence | zs
public | t2 | table | zs
public | t2_bid_seq | sequence | zs
public | t3 | table | zs
public | t3_bid_seq | sequence | zs
(6 rows)
postgres=#\q