1. Main content of this paper

• PostgreSQL 12 installation
• PostgreSQL 12 basic configuration
• PostgreSQL 12 remote access configuration
• PostgreSQL basic management

2. Environmental information and scope of application

• Environmental information

Software	Edition
CentOS	7.6 Release
PostgreSQL	12.x

• Scope of application

Software	Edition
CentOS	CentOS 7.x
PostgreSQL	9.x-12.x

2、 PostgreSQL installation

1. Import Yum source

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

1. Install PostgreSQL service

sudo yum install -y postgresql12 postgresql12-server

Installing PostgreSQL 11 is Yum install postgresql12 postgresql12 server
Install postgresql95 postgresql95 server
And so on

2. Initialize the database

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb 

#Initializing database ... OK

3. Start PostgreSQL service

#Start PostgreSQL service
sudo systemctl start postgresql-12

#Set PostgreSQL service to start for power on
sudo systemctl enable postgresql-12

The service name of version 9. X is postgresql-9. X

2- Modify the password of Postgres account

After PostgreSQL is installed successfully, a Linux user named Postgres will be created by default. After the database is initialized, a database named Postgres will be created to store the basic information of the database, such as user information, etc., which is equivalent to the default MySQL database named mysql.

A super user will be initialized in Postgres databasepostgres

In order to facilitate our use of Postgres account for management, we can change the password of this account

1. Enter the PostgreSQL command line

Switching Linux user to Postgres through Su command will automatically enter the command line

su postgres

2. Start SQL shell

psql

3. Change password

ALTER USER postgres WITH PASSWORD 'NewPassword';

3- Configure remote access

1. Open port

sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

2. Modify IP binding

#Modify profile
vi /var/lib/pgsql/12/data/postgresql.conf

#Change the listening address to*
#The default listen addresses configuration is commented out, so you can add this line directly at the beginning of the configuration file
listen_addresses='*'

3. Allow all IP access

#Modify profile
vi /var/lib/pgsql/12/data/pg_hba.conf

#Add at the end of asking price
host  all  all 0.0.0.0/0 md5

4. Restart PostgreSQL service

#Restart PostgreSQL service
sudo systemctl restart postgresql-12

After configuration, you can use the client to connect

4- Common syntax examples of PostgreSQL shell

Start SQL shell:

su postgres
psql

1. Examples of database-related syntax

#Create database
CREATE DATABASE mydb;

#View all databases
\l

#Switch the current database
\c mydb

Create table
CREATE TABLE test(id int,body varchar(100));

#View all tables under the current database
\d

2. Examples of the user and access authorization syntax

#New user
CREATE USER test WITH PASSWORD 'test';

#Give all permissions to the specified account and database
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

#Remove all permissions of specified account specified database
REVOKE ALL PRIVILEGES ON DATABASE mydb TO test