Pure Linux PostgreSQL offline installation tutorial for girlfriend

PostgreSQL database installation, based on version 10.5.1,
Use *. gz binary compression package to install manually on Linux system.
Installation package: postgresql-10.5.1-linux-x64-binaries.tar.gz

1. Create postgres user

groupadd postgres							//Add group members
useradd -g postgres postgres				//Add user
passwd  postgres							//reset password 

2. Create required directory

Enter download directory

//After decompression, the pgsql directory will be generated automatically
tar -zxvf postgresql-10.1-linux-x64-binaries.tar.gz -C /opt

Create data directory:

//Create data directory under pgsql
mkdir -p /opt/pgsql/data    	

//Create log directory under pgsql
mkdir -p /opt/pgsql/log 		

to grant authorization:

//Grant postgres user permission to pgsql directory root grant normal user authorization
chown -R postgres.postgres pgsql		

If you do not authorize, a permission error will be reported during subsequent initialization:

fixing permissions on existing directory /data/service/postgresql/data ... initdb: could not change permissions of directory "/data/service/postgresql/data": Operation not permitted

3. Initialization (root cannot be used for postgreSQL operation)

//Enter postgres user
su  - postgres		

//Enter the bin directory of pgsql to open and close the database
cd /opt/pgsql/bin

//Initialize database  
//-E set encoding format - D set the data directory just created
./initdb -E utf8 -D /opt/pgsql/data

4. Start database

Use postgres user to start in / opt/pgsql/bin directory

//Start mode 1
cd /opt/pgsql/bin
./pg_ctl -D /opt/pgsql/data/ > /opt/pgsql/log/postgres.log start

//Start mode 2
./postgres -D /opt/pgsql/data/ > /opt/pgsql/log/postgres.log &

Log in to the database:

[postgres@server2 bin]$ ./psql

psql.bin (10.5.1)
Type "help" for help.

postgres=# 

After the postgresql installation is completed, normal services can be started. If you want to create your own database, users and passwords can use:
Validation use:
Add new users and create databases

postgres=# create user csz with password '123';
CREATE ROLE
postgres=# create database test with encoding='utf8' owner=csz;
CREATE DATABASE

Verify login

// -u user-d database
[postgres@server2 bin]$ ./psql -U csz-d test
psql.bin (10.5.1)
Type "help" for help.

test=> 

5. Configure remote access

vim /opt/pgsql/postgresql.conf

Amend to read:

listen_addresses = '*'
port = 5432

Note: after PostgreSQL is installed, only connection requests from local host will be accepted by default,
listen_addresses is set to * to allow the database server to listen for connection requests from any host,
And the external service port is 5432 (the default port).

vim /opt/pgsql/pg_hba.conf

Add the following configuration at the bottom of the file:

host all all 0.0.0.0/0 trust

Note: the above configuration allows any IP and user to access the database,
It is recommended to be used only for testing, and security issues need to be considered in the public network.

ip can also be specified for access

host  all    all    127.0.0.1/32     trust
host  all    all    192.168.1.0/24    md5

Among them, the number 24 is the subnet mask, indicating that 192.168.1.0 – 192.168.1.255 computers are allowed to access!

//Restart PostgreSQL after configuration to make the configuration effective
cd /opt/pgsql/bin
./pg_ctl -D /opt/pgsql/data  restart

6. Configure firewall

Firewall can be installed with network

yum install firewalld systemd -y

① . view open ports

firewall-cmd --list-ports

② . open 5432 port

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

③ . restart firewall

systemctl reload firewalld

④ . firewall command

//state
systemctl status firewalld

//start-up
systemctl start firewalld

//close
systemctl stop firewalld