Deploying PolarDB database under Linux

• Chapter I: Environmental preparation
• ① Check the enabled status and configuration of THP
• ② Modify the configuration file sysctl.conf
• ③ New profile polardb_limits.conf
• ④ Create polardb dedicated system user
• ⑤ Authorize polardb dedicated system users
• Chapter 2: database installation and configuration
• ① Switch users and install database
• ② Edit profile bash_profile
• ③ Initialize database
• ④ Modify the configuration file postgresql.conf
• ⑤ Modify profile pg_hba.conf
• ⑥ Enabling and disabling database services
• ⑦ Check that the database is available normally
• Chapter III: others
• ① Solve the problem of initialization database error when / data path is not empty
• ② Database version view
• ③ Database command help

Chapter I: Environmental preparation

① Check the enabled status and configuration of THP

The following configurations are recommended by Alibaba cloud database experts to optimize the performance of polardb.

# Check whether the status of THP is always. The current status is included in brackets
cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

# Confirm that the page size is 2M
grep Hugepage /proc/meminfo
Hugepagesize:       2048 kB

② Modify the configuration file sysctl.conf

/Insert contents into / etc/sysctl.conf, and then execute sudo sysctl -p command to take effect.

fs.aio-max-nr=1048576
fs.file-max=76724600
fs.nr_open=20480000
kernel.sem=4096  2147483647  2147483646  512000
kernel.shmall=107374182
kernel.shmmax=274877906944
kernel.shmmni=819200
net.core.netdev_max_backlog=10000
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.somaxconn=4096
net.core.wmem_default=262144
net.core.wmem_max=4194304
net.ipv4.ip_local_port_range=40000  65535
net.ipv4.tcp_fin_timeout=5
net.ipv4.tcp_keepalive_intvl=20
net.ipv4.tcp_keepalive_probes=3
net.ipv4.tcp_keepalive_time=60
net.ipv4.tcp_max_syn_backlog=4096
net.ipv4.tcp_max_tw_buckets=262144
net.ipv4.tcp_mem=8388608  12582912  16777216
net.ipv4.tcp_rmem=8192  87380  16777216
net.ipv4.tcp_synack_retries=2
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_wmem=8192  65536  16777216
vm.dirty_background_bytes=409600000
vm.dirty_expire_centisecs=3000
vm.dirty_ratio=80
vm.dirty_writeback_centisecs=100
vm.mmap_min_addr=65536
vm.nr_hugepages=0
vm.nr_overcommit_hugepages=1000000
vm.overcommit_memory=0
vm.overcommit_ratio=90
vm.swappiness=0
vm.zone_reclaim_mode=0

③ New profile polardb_limits.conf

Create the following file: / etc/security/limits.d/polardb_limits.conf Add the following parameters.

* soft    nofile  655360
* hard    nofile  655360
* soft    nproc   655360
* hard    nproc   655360
* soft    memlock unlimited
* hard    memlock unlimited
* soft    core    unlimited
* hard    core    unlimited

④ Create polardb dedicated system user

# Create user group
groupadd polardb

# Create users under user group polardb
useradd -g polardb polardb

# Change user password
passwd polardb
 Change user polardb Your password.
New password:
Re enter the new password:
passwd: All authentication tokens have been successfully updated.

⑤ Authorize polardb dedicated system users

The visudo command is edited directly, and the following authorization command is added near the root All authorization. /+Keywords can be searched, and the n key means to find the next one.

Chapter 2: database installation and configuration

① Switch users and install database

# Switch users
su - polardb

# Installing polardb database
sudo rpm -ivh /file/PolarDB-O-0200-2.0.0-20201023165925.alios7.x86_64.rpm

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for polardb: 
Preparing...                          ################################# [100%]
Updating / installing...
   1:PolarDB-O-0200-2.0.0-202010231659################################# [100%]

② Edit profile bash_profile

Add the following parameters to ~ /. Bash_ Under the profile file. Note: the folder pointed to by PGDATA in the following configuration file must be empty, otherwise an error will be reported during initialization. Please refer to Section 1 of Chapter 3.

export PGPORT=5432
export PGDATA=/data
export LANG=en_US.utf8
export PGHOME=/usr/local/polardb_o_current
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PFSHOME/bin/:$PATH
export PGHOST=$PGDATA
export PGUSER=polardb 
export PGDATABASE=polardb

# Modify profile
vim ~/.bash_profile
# Validate profile
source ~/.bash_profile

③ Initialize database

When initializing the database, many logs will be printed in the middle.

initdb -D $PGDATA -E UTF8 --locale=C --data-checksums -U polardb

You should succeed when you have the following prompt. Note: if there is a problem with initialization, please refer to Section 1 of Chapter 3.

④ Modify the configuration file postgresql.conf

Jump to the / data/polardbdata / directory and modify the postgresql.conf configuration file generated by initialization. When editing, you can jump to the end by shift+g and then edit to insert the following contents into the document.

listen_addresses = '*'
port = 5432
max_connections = 2048
unix_socket_directories = '.'
timezone = 'UTC-8'
log_timezone = 'UTC-8'
log_destination = 'csvlog'
logging_collector = on

Note: after the database is enabled, delete the cache file $pgdata / polar every time before modifying the postgresql.conf file_ node_ Set static.conf again, otherwise the setting may not take effect.

⑤ Modify profile pg_hba.conf

Modify the file in the / data/polardbdata / Directory: pg_hba.conf, add the following parameters.

host all         all 0.0.0.0/0 md5  
host replication all 0.0.0.0/0 md5

⑥ Enabling and disabling database services

Common database commands:

# Start service
pg_ctl start -D $PGDATA
# Out of Service
pg_ctl stop -D $PGDATA

Service enabling success flag:

Service deactivation success flag:

⑦ Check that the database is available normally

You can check the database directly with psql or with the statements given below.

# Database connection check
psql -h$PGDATA -p$PGPORT

It's OK to enter the following database command line

Chapter III: others

① Solve the problem of initialization database error when / data path is not empty

An error is reported during initialization. Modify PGDATA to point to an empty directory for which polardb user has permission.

I plan to create an empty folder polardbdata under data.

cd /data
mkdir polardbdata

Modify profile

# Modify profile
vim ~/.bash_profile
# Validate profile
source ~/.bash_profile

When the database is initialized again, many logs will be printed in the middle.

initdb -D $PGDATA -E UTF8 --locale=C --data-checksums -U polardb

You should succeed when you have the following prompt.

② Database version view

# Version view
psql -h$PGDATA -p$PGPORT -c"select version()"

③ Database command help

# Database connection check
psql -h$PGDATA -p$PGPORT