[PostgreSql] What issues should be considered for production-level database installation?

Hello everyone, I'm Antetokounmpo (coder)!

I asked a small partner in the company to write a production-level PostgreSQL installation document, and he said to me: "Can't you install it with one command? Do you still need to write a document?". I know what he wants to say is this command: yum install postgresql-server , I am also quite speechless. You need to know how to install production-level applications. Compared with the small toys you install in the practice process, you need to be more rigorous and consider more issues.

• First of all, it is a security issue. Can it be installed with root? Firewall how to do? Will the number of open files and connections in the database exceed the limit of the number of open file handles by default in Linux?
• The second is reasonable planning. Which directory is your data placed in? Is this directory uniform across the company? Linux distributions are different, is the software installation directory the same? Is the data directory a separate disk mount? Are the separately mounted disks used for RAID data redundancy?
• Then there is the question of convenient operation and maintenance. Have you configured reasonable environment variables? If not, do you have to type the full path every time you execute a command? Does PostgreSQL have the auto-start function at boot?
• Have database users been properly planned, and can your data operations (add, delete, modify, search) users be able to create new tables, truncat tables, and delete tables? Does having these functions for data manipulation users increase the risk of human error?
• After your database is installed, do you have enterprise-level unified management and fault monitoring? How to apply in the process? Etc., etc.

The above questions need to be written in the "Database Installation Manual", how can there be only one command? This article is only a guide, by installing the PostgreSQL database, to explain how to write a complete "Database Installation Manual". If there are insufficient considerations, netizens are welcome to add.

1. Download the installation package

To install PostgreSQL, first we need to download the installation package. The postgresql installation in the production environment is generally installed by means of source code compilation. The installation method using source code compilation has several advantages (the disadvantage is that it is relatively troublesome):

• What model of the company's server, what operating system version are available. If you use yum or apt-get to install, it is difficult to unify the version, and the postgreSQL source with different operating system distributions is different. Even if it is unified, the location of the installation directory, lib storage location, etc. cannot be unified between different operating systems.
• To install postgresql source code compilation, you can flexibly choose the version we need to install, and download which version you need. And using yum or apt-get to install only a few versions specified by the energy library. For source code installation, we can specify whatever directory we want to specify. All PostgreSQL in the company is in this directory, which is also a kind of specification.

Therefore, in order to adapt to more scenarios, in order to achieve the unified standard of operation and maintenance within the company, the general production environment will be installed by compiling the postgresql source code. The download address of the PostgreSql official source installation package is: https://www.postgresql.org/download/

Find the "file browser" as shown above, click to enter the file browser page, and download the installation package ending with "tar.gz".

2. Necessary preparations

It should be noted that the preparation work needs to be performed by the root user. We take CentOS linux distribution operating system as an example, the command is as follows. For other linux distributions, the preparation steps are similar, and the linux commands need to be adjusted.

1. Install the necessary compilation environment

First, you need to install some necessary dependencies. The PostgreSQL installation process needs to use these linux packages.

 yum install -y cmake make gcc zlib zlib-devel gcc-c++ perl readline readline-devel  python36 \
tcl openssl ncurses-devel openldap pam flex

2. Create a postgre user

Add a user group postgres through groupadd, and then use the adduser command to add a postgres user of the operating system. The user group of this user is -g postgres .

 groupadd postgres
adduser -g postgres postgres

In the CentOS or RedHat distribution, the adduser command will automatically create a new home directory for the user when adding an operating system user /home/postgres , which is also the home directory for our subsequent installation of PostgreSql. Permissions for this directory belong to the postgres user in the postgres group.

 passwd postgres

After creating a new user, we use the passwd command to modify the password of the postgres user in the operating system.

If you are using another linux OS distribution, you may need to create this directory manually, and manually execute the command to authorize the postgres user. The command is as follows

 mkdir -p /home/postgres;
chmod -R 775 /home/postgres;
chown -R postgres:postgres /home/postgres;

3. Open firewall ports

Open the firewall and use the following two commands to open the standard port 5432 of postgreSQL. In fact, the author recommends not to use this port when installing postgreSQL, because the more fixed the port, the greater the possibility of being attacked. We randomly choose an uncommon port, the security will be better, here I still use the standard port.

 firewall-cmd --zone=public --add-port=5432/tcp --permanent;
firewall-cmd --reload

4. Modify the maximum number of file handles opened by the operating system

Set in /etc/security/limits.conf (you need root permission to modify this file), you can add the following two lines, indicating that the soft limit of the maximum number of open file descriptors for the user postgres is 65535, and the hard limit is 65535.

 postgres soft   nofile         65535
postgres hard   nofile        65535

This configuration takes effect after closing the linux access terminal session and logging in again. The value of ulimit -n will become 65535. The purpose of this step is to prevent the limit on the number of open file handles in the Linux operating system and avoid unnecessary failures.

5. Create the necessary directories

Make a plan when creating a directory. The following /home/postgres/postgre14 is the postgreSql installation directory I planned; /home/postgres/pgdata is the postgreSql data storage directory I planned.

 mkdir -p /home/postgres/{postgre14,pgdata}

It should be noted here that the directory planning of /home/postgres/pgdata is not standard, here I just show you the operation. In fact, in the production environment, the postgreSql data storage directory should be a separate disk mounted by the server, which is separate from the Linux operating system and the installation program. The advantages of doing so are: First, it is convenient for data backup (hardware level or software level), and second, problems with the operating system do not affect data security.

3. Compile and install

After the preparations are done, we start the real installation. The installation process uses the postgres user and uses the su command to switch from the root user to the postgres user. And decompress the installation package, the decompression path is /home/postgres .

 su - postgres
tar -xzvf ./postgresql-14.4.tar.gz -C /home/postgres

The file path after decompression is postgresql-14.4 , enter this directory, let's execute the compilation and installation, we specify the result directory of the compilation and installation as /home/postgres/postgre14 . The process of make and insatll in the last step is time-consuming, so wait for a while. If there is no error message, our installation is completed, which is actually very fast, not much more troublesome than the yum installation method.

 cd  /home/postgres/postgresql-14.4
./configure --prefix=/home/postgres/postgre14
make  && make install

Fourth, configure environment variables

After the installation is complete, let's configure some environment variables for postgres, such as PGPORT port, PGDATA data directory, PGHOME installation directory, LD_LIBRARY_PATH installation lib directory, and PATH command script directory.

 cat >>  ~/.bash_profile <<"EOF"
export PGPORT=5432
export PGDATA=/home/postgres/pgdata
export PGHOME=/home/postgres/postgre14
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
EOF

After the configuration is complete, use the following command to make the configuration take effect.

 source  ~/.bash_profile

With environment variables, our operation and maintenance work will be more convenient. For example: under the postgres user, we can execute the command psql , and we don't need to type the full path, for example: /home/postgres/postgre14/bin/psql .

Five, database and parameter initialization

After the database installation is complete, we need to complete a database initialization. Specify -D data directory, -E character encoding, --locale this and locale, -U

 $PGHOME/bin/initdb -D $PGDATA  -E UTF8 --locale=en_US.utf8 -U postgres

After completing this operation, the postgres user exists in the database, and some configuration files are initialized under the PGDATA directory of the data directory, such as: /home/postgres/pgdata/postgresql.conf parameter file, /home/postgres/pgdata/pg_hba.conf user access permission file.

Let's modify the parameter file in the hope that it can adapt to our operating system and our running needs. The meaning of the command below is to write the content of the file contained in EOF to the /home/postgres/pgdata/postgresql.conf configuration file.

 cat >> /home/postgres/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
EOF

• listen_addresses represents the network card address monitored by the postgre process, and configuration * represents monitoring all.
• port Service listening port configuration
• logging_collector=on , start the log collector
• log_directory represents the folder location of the log file in the PGDATA directory.
• log_filename represents the name of the log file

Only some parameters that need to be modified under normal circumstances are listed here, and the performance tuning parameters are not listed one by one. I may write an article on PostgreSQL performance tuning in the future. Welcome everyone to pay attention to me!

 # 数据库能够接受的最大请求连接并发数
max_connections = 100
# 数据库服务器将使用的共享内存缓冲区量。建议值：数据库独立服务器的1/4内存。
shared_buffers =1GB

After the basic running parameters are configured, some access permissions need to be configured. Use the cat command as follows to write the contents of the file contained in EOF into the /home/postgres/pgdata/pg_hba.conf configuration file:

 cat   > /home/postgres/pgdata/pg_hba.conf << EOF
# TYPE  DATABASE    USER    ADDRESS       METHOD
local      all       all                    trust
host      all       all    0.0.0.0/0        md5
EOF

• The content of the first line represents the local machine, all users can access all databases of postgre, and the password sending method does not need to be encrypted (trust).
• The content of the second line is used for remote access, specifying the ip range of remote users who can access the postgreSql database, 0.0.0.0/0 means that all ips are available. If you want to specify the ip range, you can configure it like this 192.168.3.1/24 (indicated ip range is: 192.168.3.1 to 192.168.3.255). md5 indicates that the database access password is sent using md5 encryption.

6. Start the database and create a new user

After completing all the above operations, we can start the database. Use the following commands to start and stop the database.

 #启动命令
pg_ctl start
#重启命令
pg_ctl restart
#查看数据库运行状态
pg_ctl status
#停止数据库
pg_ctl stop

After the database is started, we look at some of the processes running on the database as follows:

The database is started successfully, we enter the psql command to enter the database management command line

 $ psql

In the process of database initialization, a new user postgres is created, and we change the password for it.

 alter user postgres with password '你设置的密码';

Generally speaking, our program will not directly use the postgres user during the running process, but create a new user.

 #新建一个用户 acai
CREATE USER acai WITH password '密码';
#新建一个数据库acaidb，属主是acai
CREATE DATABASE acaidb OWNER acai;
# 为用户acai赋权，all表示所有权限，acaidb的管理用户
GRANT all ON  DATABASE acaidb TO acai;

In fact, in the production environment, we need to create two users, one database management user is used to manage operations such as tables and views; the other is a program user, which is used to manipulate the data in the database tables and complete additions, deletions, and changes.

 CREATE USER acaiuser WITH password '********';
GRANT  update,delete,insert,select  ON  ALL TABLES IN SCHEMA public TO acaiuser;

7. Self-start at boot

In order to enable PostgreSQL to start automatically every time the server is restarted in the future, we write a self-starting configuration file. The following commands need to be executed as root user

 cat > /etc/systemd/system/postgre14.service <<"EOF"
[Unit]
Description=PostgreSQL database server 14.4
Documentation=man:postgres(1)
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres

Environment=PGPORT=5432
Environment=PGDATA=/home/postgres/pgdata

ExecStart=/home/postgres/postgre14/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/home/postgres/postgre14/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/home/postgres/postgre14/bin/pg_ctl reload -D ${PGDATA} -s

KillMode=mixed
KillSignal=SIGINT
TimeoutSec=120

[Install]
WantedBy=multi-user.target
EOF

Execute the following command to make the PostgreSQL database startup script take effect.

 systemctl daemon-reload
systemctl enable postgre14

To start and stop the database later to view the database status, you can use the following commands

 systemctl start postgre14
systemctl stop postgre14
systemctl status postgre14

Welcome to pay attention to my announcement number: Antetokounmpo, reply 003 and present the PDF version of the author's column "The Way of Docker Cultivation", more than 30 high-quality docker articles. Antetokounmpo Blog: zimug.com