Installation and deployment in a PG-XL cluster environment

Cluster installation

Pay attention to the point

  1. Based on server information and PGXL port rules, the PGXL cluster information table is designed in advance (as follows: Description of each server node).
  2. When COMPILING PGXL, install the compilation tool, upload the source code, and verify that the compilation and installation are successful.
  3. SSH password-free configuration, environment variable configuration, cluster initialization, and PSQL operations are performed as user postgres.
  4. To configure the firewall for the Intranet or test machine, disable the firewall directly. Better operation: Run the firewall command to open the specified port.

This section describes the node planning of the server

The following is a three-node cluster configuration

The name of the node The host name Server IP Open ports
gtm_10 edcpggtm 1 x. 8.12.10 6666
gtm_proxy_11 edcpgdb01 1 x. 8.12.11 6666
gtm_proxy_12 edcpgdb02 1 x. 8.12.12 6666
coord_11 edcpgdb01 1 x. 8.12.11 5432, 6667,
coord_12 edcpgdb02 1 x. 8.12.12 5432, 6667,
dn_11 edcpgdb01 1 x. 8.12.11 5433, 6668,
dn_12 edcpgdb02 1 x. 8.12.12 5433, 6668, 
Firewall -cmd --zone=public --add-port=6666/ TCP --permanent 1x.8.12.12) firewall-cmd --zone=public --add-port=5432/ TCP --permanent firewall-cmd --zone=public --add-port=5433/ TCP --permanent firewall-cmd --zone=public --add-port=6666/tcp --permanent firewall-cmd --zone=public --add-port=6667/tcp --permanent firewall-cmd --zone=public --add-port=6668/tcp --permanentCopy the code

Configure hosts for all planned nodes

Vim /etc/hosts 1x.8.12.10 edcpggtm 1x.8.12.11 edcpgdb01 1x.8.12.12 edcpgdb02Copy the code

Linux users

User name: postgres Password: XXXX

Useradd postgres # Creates user postgres by passwd postgres # Changes the password of user postgres. The password is as follows: XXXXCopy the code

The installation directory

mkdir -p /usr/local/pgxl10
Copy the code

Data and configuration directory

mkdir -p /data/pgxl10
Copy the code

Compile the installation command

Environment to prepare

yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl gcc
Copy the code

Install the configuration

/configure --prefix=/usr/local/pgxl10 /usr/local/pgxl10 /usr/local/pgxl10/ chown -R postgres:postgres /data/pgxl10/Copy the code

Encryption-free communication configuration

SSH directory (unified cluster management and maintenance on the GTM node :pgxc_ctl)

SSH # Run the following command to switch to user postgres on the GTM node: ssh-keygen -t rsa cat ~/. SSH /id_rsa.pub >> ~/. SSH /authorized_keys chmod 600 ~/. SSH /authorized_keys # postgres@edcpgdb01:~/. SSH/SCP ~/. SSH /authorized_keys postgres@edcpgdb02:~/ postgres@edcpgdb01Copy the code

Configuring Environment Variables

# Switch to user postgres. All nodes have to be configured, Vim ~/.bashrc # Export PG_HOME=/usr/local/ pgxL10 export LD_LIBRARY_PATH=$PG_HOME/lib:$LD_LIBRARY_PATH export PG_DATA=/data/ pgXl10 export PATH=$PG_HOME/bin:$PATH # Verify whether environment variables are configured source ~/Copy the code

PG installation configuration modified

Pgxc_ctl # Run the following command on the PGXC console: Create an empty installation configuration file prepare config Empty # Modify the postgres user home directory ~/pgxc_ctl/pgxc_ctl.conf pgxcInstallDir=$PG_HOME # pg-xl program directory PgxcOwner =$USER # pg-xl owner USER dataDirRoot=$PG_DATA # pg-XL data store directory coordPgHbaEntries=(0.0.0.0/0) # Set to all IP addresses DatanodePgHbaEntries =(0.0.0.0/0) # Add coordination and datanode infrastructure to cat > $coordExtraConfig <<EOF... Add a coordination node between EOF in cat > $datanodeExtraConfig <<EOF...... Add data node configurations between EOFCopy the code

PG cluster addition (configured based on the above node planning)

Syntax format:

Adding a Cluster Node

add gtm master name host port dir add gtm slave name host port dir add gtm_proxy name host port dir add coordinator master name host port pooler dir< extraServerConf extraPgHbaConf add coordinator slave name host port pooler dir archDir  add datanode master name host port pooler dir waldir extraServerConf extraPgHbaConf add datanode slave name host port pooler dir waldir archDirCopy the code

Cluster nodes are abnormal.

remove gtm master [ clean ]
remove gtm slave [ clean ]
remove gtm_proxy nodename [ clean ]
remove coordinator [ master| slave ] nodename [ clean ]
remove datanode [ master| slave ] nodename [ clean ]
Copy the code

To add a cluster node (GTM node), run the following command postgres and pgxc_ctl to configure the cluster:

#GTM
add gtm master gtm_10 edcpggtm 6666 $PG_DATA/gtm
#GTM Proxy
add gtm_proxy gtm_proxy_11 edcpgdb01 6666 $PG_DATA/gtm_proxy
add gtm_proxy gtm_proxy_12 edcpgdb02 6666 $PG_DATA/gtm_proxy
#Coordinator
add coordinator master node_11 edcpgdb01 5432 6667 $PG_DATA/coord none none
add coordinator master node_12 edcpgdb02 5432 6667 $PG_DATA/coord none none

#Data Node
add datanode master dn_11 edcpgdb01 5433 6668 $PG_DATA/dn none none none
add datanode master dn_12 edcpgdb02 5433 6668 $PG_DATA/dn none none none
Copy the code

PG connection and user password

The following IP addresses and ports are available for client connections: 1x.8.12.11:5432 1x.8.12.12:5432 # PSQL connect to client, CREATE database account and set PASSWORD (e.g. : in 1x.8.12.11:5432) PSQL CREATE USER bi WITH PASSWORD 'XXXX '; Create a bi userCopy the code

Cluster testing

1. PSQL command execution command format: PSQL -h -p # -h:host address, -p: port configuration 2. Access tests through client tools (e.g., Navicat)Copy the code

Cluster optimization

To be continuously improved…..

Configure client security authentication

# pg_hba.conf file in the data root directory of each coordination node Type Database User Address Method Local All All 127.0.0.1/32 Trust Type Value range Local Local socket connection and host Other machines common or SSL socket connection, HostSSL SOCKET, HostNOSSL common socket Database Value range "all", "Sameuser ", "samerole", "Replication" and the database name, or multiple database names separated by commas. The value range of User is all, User name,+ group name, and multiple User names separated by commas. Address IP Address, network segment Address and mask. Samenet can match any Address in the subnet where the server resides. Samehost matches any of the server's own IP methods "trust", "reject", "MD5 "," password"," scram-SHA-256 "," GSS "," sSPI "," ident", "peer", "PAM ", "ldap", "radius" or "cert". "Md5" or "SCram-SHA-256" is the preferred encrypted password for this send.Copy the code

Configure important parameters for the PG node

# worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 # worker_threads = 1 Max_connections Specifies the maximum number of connections that a coordination node can provide for clients. The total number of connections to the database is the sum of all coordination nodes. Max_pool_size Specifies the pool of connections that a coordination node can use to connect to a data node. Because a connection on the coordination node may connect to all data nodes at the same time max_prepared_transactions is greater than or equal to the number of coordination nodes persistent_datanode_connections = on Max_connections Specifies the maximum number of connections between a coordination node and a data node, since all connections on all coordination nodes may be connected to the same data node at the same time. Max_prepared_transactions is the same as max_connections # Shared_buffers defaults to 128MB The recommended configuration is between 20% and 40% of the physical memory. Work_mem defaults to 4MB. The amount of memory used for sorting and hashing operations before writing to temporary files is calculated in terms of the number of parallel tasks. Max_worker_processes Maximum number of background processes. The default is 8. Max_parallel_workers Specifies the maximum number of threads to support parallel query based on the CPU capacity of the server. The default value is 8. Max_worker_processes max_parallel_workers_per_GATHER Specifies the maximum number of working programs that can be started on a single Collect or gather and merge node. The default value is 2. The max_wal_size parameter is used to speed up the loading of big data. During the loading of big data, the system speeds up the checkpoint, and the data is dumped to disk by the checkpoint. Increasing the value of the max_wal_size parameter reduces the number of checkpoints. Maintenance_work_mem helps improve performance and speed up the efficiency of creating Index and ALTER TABLE ADD FOREIGN KEY. The default value is 64 MB. You can set the value higher than work_memCopy the code

Linux kernel parameter optimization

The Linux transparent large page is closed

Under the root user in vi/etc/rc. Local finally add the following code if the test - f/sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabled fi if test -f /sys/kernel/mm/transparent_hugepage/defrag; Then echo never > / sys/kernel/mm/transparent_hugepage/defrag fi effect immediately need to be performed, using the command sh/etc/rc. The local view the changes: Cat/sys/kernel/mm/transparent_hugepage/enabled always madvise [never] # if turned back again, after restart need chmod + x/etc/rc. D/rc. LocalCopy the code

User roles

Create User is a wrapper for the Create Role command. By default, Create User has LOGIN permission. Create Role name [[WITH] option [...]] option contains the following list: SUPERUSER | NOSUPERUSER super administrator permissions, The default NOSUPERUSER | CREATEDB | NOCREATEDB whether can create the database, the default NOCREATED | CREATEROLE | NOCREATEROLE Whether can create role with the permissions can also change, and delete other roles, the default NOCREATEROLE | INHERIT | NOINHERIT INHERIT its subordinate role permissions, the default is to INHERIT | LOGIN | NOLOGIN whether can log in, CreateUser default is LOGIN CreateRole default is NOLOGIN is considered to be the user can log in, can't LOGIN is considered a role | REPLICATION | NOREPLICATION whether duplicate role, this level is higher, Generally if super administrator to have this role, the default is NOREPLICATION | BYPASSRLS | NOBYPASSRLS whether to bypass the row level security policies, NOBYPASSRLS by default, when run pg_dump no authority complains, Super tube and owner user are commonly BYPASSRLS | CONNECTION LIMIT connlimit if can log in, this parameter LIMIT user or a role on the number of simultaneous connections, - 1 for no limit (the default) | / ENCRYPTED PASSWORD 'PASSWORD' login PASSWORD, to come into force when the authentication is a PASSWORD authentication. When not specified is null, when open the password authentication, null cannot login | VALID UNTIL 'timestamp set a password expiration time, after this time the password will be failure. Don't set to never expire | ROLE IN role_name [,...]. Lists one or more roles that the new user or role will join as a member of the list. | ROLE role_name [, ...] List one or more roles that will be members of the new role. | ADMIN role_name [, ...] Similar to the ROLE clauseCopy the code

Permissions related

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...]  | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...]  | ALL TABLES IN SCHEMA schema_name [, ...]  } TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ), [...].  | ALL [ PRIVILEGES ] ( column_name [, ...]  ) } ON [ TABLE ] table_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { { USAGE | SELECT | UPDATE } [, ...]  | ALL [ PRIVILEGES ] } ON { SEQUENCE sequence_name [, ...]  | ALL SEQUENCES IN SCHEMA schema_name [, ...]  } TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...]  | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN domain_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPER fdw_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN SERVER server_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ]  arg_type [, ...] ] ) ] [, ...]  | ALL FUNCTIONS IN SCHEMA schema_name [, ...]  } TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE lang_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { { SELECT | UPDATE } [, ...]  | ALL [ PRIVILEGES ] } ON LARGE OBJECT loid [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { { CREATE | USAGE } [, ...]  | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPE type_name [, ...]  TO role_specification [, ...]  [ WITH GRANT OPTION ] where role_specification can be: [ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER GRANT role_name [, ...]  TO role_name [, ...]  [ WITH ADMIN OPTION ]Copy the code