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:66692、准备工作（不特别指明，四个节点做相同的操作）：a.编译安装pgx2，同时编译contrib。./configure --prefix=/opt/pgx2 make; make install cd contrib make; make installb.建立用户postgres，将安装目录属主赋给postgres。chown -R postgres:postgres pgx2c.配置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
[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
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:
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:
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:
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）
易网时代-编程资源站