Note: Postgres-Xc was not renamed Postgres-X2 when this article was written.

1. Download source code

git clone [email protected]:postgres-x2/postgres-x2.git
Copy the code

2. Install dependencies

For Ubuntu/Debian:

apt-get install -y git-core apt-get install -y gcc g++ apt-get install -y ccache apt-get install -y libreadline-dev apt-get install -y bison flex apt-get install -y zlib1g-dev apt-get install -y openssl libssl-dev apt-get install -y libpam-dev apt-get install -y libcurl4-dev apt-get install -y libbz2-dev apt-get install -y python-dev apt-get install -y ssh apt-get install -y libcurl4-dev Package libcurl4-dev is a virtual package provided by: Libcurl4-openssl-dev 7.38.0-4+deb8u2 libcurl4-nss-dev 7.38.0-4+deb8u2 libcurl4-gnutls-dev 7.38.0-4+deb8u2 apt-get libcurl4-gnutls-dev 7.38.0-4+deb8u2 apt-get install -y python-pip pip install lockfile pip install paramiko pip install setuptools pip install epydoc pip install psi Note: debian8 required pip install --pre psiCopy the code

For CentOS:

X86_64 yum install -- y ccache. X86_64 yum install -- y ccache. X86_64 yum install -- y ccache readline.x86_64 readline-devel.x86_64 yum install bison.x86_64 bison-devel.x86_64 yum install flex.x86_64 flex-devel.x86_64 yum install zlib.x86_64 zlib-devel.x86_64 yum install -y openssl.x86_64 openssl-devel.x86_64 yum X86_64 pam-devel.x86_64 yum install -- y libcurl. X86_64 libcurl-devel.x86_64 yum install bzip2-libs.x86_64 bzip2.x86_64 bzip2-devel.x86_64 yum install libssh2.x86_64 libssh2-devel.x86_64 yum install python-devel.x86_64 yum install -y python-pip.noarch# execute:
pip install lockfile
pip install paramiko
pip install setuptools
pip install epydoc
pip install psi
# or execute:
yum install python-lockfile.noarch
yum install python-PSI.x86_64
yum install python-paramiko.noarch
yum install python-setuptools.noarch
yum install epydoc.noarch
Copy the code

3. Compile and install

$ cd postgres-x2
$ ./configure --prefix=/home/wslu/pgsql --enable-debug --prefix specifies the path to install after the compilation is complete. It must use the full path and wslu is the user.
$ make # Execute compilation
$ make install # install
Copy the code

4. Initialize and start

4.1. Initialize GTM

$ cd /home/wslu/pgsql

Set the PTAH variable
$ export PATH=/home/user/pgsql/bin:$PATH
Use initgtm to initialize GTM
$ ./bin/initgtm -Z gtm -D data/gtm/
Copy the code

4.2. Initialize the database node

Initialize all database nodes (CO, DN) :

Use the db initialization command initdb
$ ./bin/initdb -U wslu -A trust --locale=C -D data/co1   -u user -d Data directory/node
$ ./bin/initdb -U wslu -A trust --locale=C -D data/co2
$ ./bin/initdb -U wslu -A trust --locale=C -D data/dn1
$ ./bin/initdb -U wslu -A trust --locale=C -D data/dn2
$ ./bin/initdb -U wslu -A trust --locale=C -D data/dn3
Copy the code

4.3. Edit the configuration file

Edit the data/co1 / postgresql. Conf:

# the default value
gtm_port = 6666
# pgxc_node_name cannot be repeated
pgxc_node_name = co1
Copy the code

Edit the data/co2 / postgresql. Conf:

gtm_port = 6666
pgxc_node_name = co2
Copy the code

Edit the data/dn1 / postgresql. Conf:

gtm_port = 6666
pgxc_node_name = dn1          
Copy the code

Edit the data/dn2 / postgresql. Conf:

gtm_port = 6666
pgxc_node_name = dn2
Copy the code

Edit the data/dn2 / postgresql. Conf:

gtm_port = 6666
pgxc_node_name = dn3
Copy the code

4.4. Start the Service

Start the GTM, Datanode, and Coordinator in sequence:

#./bin/gtm_ctl start -s GTM -d data/ GTM -l data/ GTM /gtm.log$./bin/gtm_ctl start -z GTM -d data/ gtm-l gtm.log // Start GTMLog to check whether GTM is started

$ ./bin/pg_ctl start -Z datanode -D data/dn1 -l data/dn1/postgresql.log  -o "-p 24071"// Start datanode dn1, DN1_PORT=24071Log /postgresql.log /postgresql.log /postgresql.log

$ ./bin/pg_ctl start -Z datanode -D data/dn2 -l data/dn2/postgresql.log  -o "-p 24072"// Start dn2, DN2_PORT=24072 $./bin/pg_ctl start -z datanode -d data/dn3 -l data/dn3/postgresql.log -o"-p 24073"// Start dn3, DN3_PORT=24073 $./bin/pg_ctl start -z coordinator -d data/co1 -l data/co1/postgresql.log -o"-p 24076"// Start CO1_PORT=24076 $./bin/pg_ctl start -z coordinator -d data/co2 -l data/co2/postgresql.log -o"-p 24077"// Start CO2, CO2_PORT= 24077Copy the code

5. Configure cluster nodes

Specify the dynamic library location:

$ export LD_LIBRARY_PATH=/home/wslu/pgsql/lib:$LD_LIBRARY_PATH
Copy the code

Configuring cluster nodes:

Create node co1_port=24076$./bin/psql -p 24076 postgres postgres

 CREATE NODE dn1 WITH (HOST = 'localhost', type = 'datanode', PORT = 24071, id = 1, content = 1); //Register the node with the same port number as aboveCREATE NODE dn2 WITH (HOST = 'localhost', type = 'datanode', PORT = 24072, id = 2, content = 2);
 CREATE NODE dn3 WITH (HOST = 'localhost', type = 'datanode', PORT = 24073, id = 3, content = 3);
 CREATE NODE co1 WITH (HOST = 'localhost', type = 'coordinator', PORT = 24076, id = 4, content = 4);
 CREATE NODE co2 WITH (HOST = 'localhost', type = 'coordinator', PORT = 24077, id = 5, content = 5);
 SELECT pgxc_pool_reload();
Copy the code

The cluster configuration is complete.

6. Common operations

6.1. Stop the Cluster

$ ./bin/pg_ctl stop -D data/co1 -m immediate
$ ./bin/pg_ctl stop -D data/co2 -m immediate
$ ./bin/pg_ctl stop -D data/dn1 -m immediate
$ ./bin/pg_ctl stop -D data/dn2 -m immediate
$ ./bin/pg_ctl stop -D data/dn3 -m immediate
$ ./bin/gtm_ctl stop -Z gtm -D data/gtm 
$ rm -f data/gtm/register.node  
Copy the code

6.2. Start the cluster

$ ./bin/gtm_ctl start -Z gtm -D data/gtm -p ./bin -l data/gtm/gtm.log $ ./bin/pg_ctl start -l data/dn1/postgresql.log -Z  datanode -D data/dn1 -o"-p 24071"
$ ./bin/pg_ctl start -l data/dn2/postgresql.log -Z datanode -D data/dn2 -o "-p 24072"
$ ./bin/pg_ctl start -l data/dn3/postgresql.log -Z datanode -D data/dn3 -o "-p 24073"
$ ./bin/pg_ctl start -l data/co1/postgresql.log -Z coordinator -D data/co1 -o "-p 24076"
$ ./bin/pg_ctl start -l data/co2/postgresql.log -Z coordinator -D data/co2 -o "-p 24077"
Copy the code

6.3. Clear data

To clear data, stop the server cluster and then clear the data store directory:

$ ./bin/pg_ctl stop -D data/co1 -m immediate
$ ./bin/pg_ctl stop -D data/co2 -m immediate
$ ./bin/pg_ctl stop -D data/dn1 -m immediate
$ ./bin/pg_ctl stop -D data/dn2 -m immediate
$ ./bin/pg_ctl stop -D data/dn3 -m immediate
$ ./bin/gtm_ctl stop -Z gtm -D data/gtm 
$ rm -f data/gtm/register.node 
$ rm -rf data
Copy the code

7. Configure Datanode hot backup

7.1. Modify pg_hba.conf of all CO and DN files

Comment out the following two lines:

Conf host replication wslu 127.0.0.1/32 trust Host replication wslu ::1/128 trust $vi Data/CO2 /pg_hba.conf Host replication wslu 127.0.0.1/32 trust Host replication wslu ::1/128 trust $vi Data /dn1/pg_hba.conf Host replication wslu 127.0.0.1/32 Trust Host replication wslu ::1/128 trust $vi Data /dn2/pg_hba.conf Host replication wslu 127.0.0.1/32 Trust Host replication wslu ::1/128 trust $vi Data /dn3/pg_hba.conf Host replication wslu 127.0.0.1/32 trust Host replication wslu ::1/128 trustCopy the code

To facilitate testing, set the verification mode to Trust. In actual production, you need to change it to MD5, that is, authentication based on the account password.

7.2. Modify the Postgresql. conf file of all CO and DN files

Add the following:

$ vi data/co1/postgresql.conf
listen_addresses = The '*' 
log_line_prefix = '%t:%r:%u@%d:[%p]: '
#logging_collector = on
port = 24076
wal_level = archive
 
$ vi data/co2/postgresql.conf
listen_addresses = The '*' 
log_line_prefix = '%t:%r:%u@%d:[%p]: '
#logging_collector = on    
port = 24077
wal_level = archive
 
$ vi data/dn1/postgresql.conf
hot_standby = on
#logging_collector = on
listen_addresses = The '*' 
log_line_prefix = '%t:%r:%u@%d:[%p]: '
wal_keep_segments = 10
wal_level = hot_standby
max_wal_senders = 5 
include_if_exists = 'synchronous_standby_names.conf'
port = 24071
 
$ vi data/dn2/postgresql.conf
hot_standby = on
#logging_collector = on
listen_addresses = The '*' 
log_line_prefix = '%t:%r:%u@%d:[%p]: '
wal_keep_segments = 10
wal_level = hot_standby
max_wal_senders = 5 
include_if_exists = 'synchronous_standby_names.conf'
port = 24072
 
$ vi data/dn3/postgresql.conf
hot_standby = on
#logging_collector = on
listen_addresses = The '*' 
log_line_prefix = '%t:%r:%u@%d:[%p]: '
wal_keep_segments = 10
wal_level = hot_standby
max_wal_senders = 5 
include_if_exists = 'synchronous_standby_names.conf'
port = 24073
Copy the code

7.3. Create a standby DN

Run the following command to create the standby Datanode directory with the database cluster enabled:

$ pg_basebackup -D data/dn1s -Fp -Xs -v -P -h localhost -p 24071 -U wslu
$ pg_basebackup -D data/dn2s -Fp -Xs -v -P -h localhost -p 24072 -U wslu
$ pg_basebackup -D data/dn3s -Fp -Xs -v -P -h localhost -p 24073 -U wslu
Copy the code

7.4. Create recovery.conf on all standby DNS

$ vi dn1s/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'user=wslu host=localhost port=24071 sslmode=disable sslcompression=1'
 
$ vi dn2s/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'user=wslu host=localhost port=24072 sslmode=disable sslcompression=1'
 
$ vi dn3s/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'user=wslu host=localhost port=24073 sslmode=disable sslcompression=1'
Copy the code

7.5. Create a synchronous_standby_names.conf file for all primary DNS

vi data/dn1/synchronous_standby_names.conf 
synchronous_standby_names=The '*'
Copy the code

7.6. Add standby DN nodes to all CO nodes

Using CO1 as an example, the same operation is performed for CO2 (for other PG commercial databases that support hot backup, the type is not Datanode but standby) :

$./bin/psql -p 24076 postgres postgres    //Go to CO1 to create node co1_port=24076
 CREATE NODE dn1s WITH (HOST = 'localhost', type = 'datanode', PORT = 34071, id = 6, content = 1); //Register the node with the same port number as aboveCREATE NODE dn2s WITH (HOST = 'localhost', type = 'datanode', PORT = 34072, id = 7, content = 2);
 CREATE NODE dn3s WITH (HOST = 'localhost', type = 'datanode', PORT = 34073, id = 8, content = 3);
Copy the code

7.7. Start all standby DN services

./bin/pg_ctl start -D data/dn1s -l data/dn1s/postgresql.log  -o "-p 34071"
./bin/pg_ctl start -D data/dn2s -l data/dn2s/postgresql.log  -o "-p 34072"
./bin/pg_ctl start -D data/dn3s -l data/dn3s/postgresql.log  -o "-p 34073"
Copy the code

Accordingly, the instructions for stopping the services of all standby DN nodes are:

./bin/pg_ctl stop -D data/dn1s -m immediate
./bin/pg_ctl stop -D data/dn2s -m immediate
./bin/pg_ctl stop -D data/dn3s -m immediate
Copy the code

8. Q&A

8.1. How can I promote the standby DN to the primary DN

I didn’t succeed, but looking at other PostgreSQL distributed databases, I did the following:

  1. Kill the primary DN process and create a trigger file (for example, promote) in the directory of the secondary DN.
  2. throughKill -sigusr1 Indicates the process ID of the standby DNThe command sends a SIGUSR1 signal to the postMaster process at the standby DN.
  3. The primary CO performs similarlyalter node dn1s with(promote);The instructions.
  4. Exit PSQL and reconnect to PSQL.
  5. In this case, the standby DN functions as the primary DN and can perform DDL, DML and other operations.

8.2. When the standby DN fails, how can I disable data synchronization between the primary AND standby DN

So you close walSender and Walreciever.

This involves the source level, generally do two steps:

  1. Change the primary DN status toOutSync(other database practices).
  2. In the codeSyncRepStandbyNamesSet to"".

supplement

The description about configuring the standby DN in this tutorial can only back up the data of each DN. If a DN fails, the system automatically switches to the standby DN.

Also, I did not see how to create a standby DN node in the PGXC_Nodes system table in the regression test of postgres-xc (now github renamed postgres-x2) source code.

However, GreenPlum (a PostgreSQL based distributed database) has this feature for reference.

Welcome to follow my wechat public number [database kernel] : share mainstream open source database and storage engine related technology.

The title The url
GitHub dbkernel.github.io
zhihu www.zhihu.com/people/dbke…
SegmentFault segmentfault.com/u/dbkernel
The Denver nuggets Juejin. Im/user / 5 e9d3e…
OsChina my.oschina.net/dbkernel
CNBlogs www.cnblogs.com/dbkernel