I. Introduction

1. Main content of this article

• PostgreSQL 12 installation (yum)

• PostgreSQL 12 basic configuration

• PostgreSQL 12 Remote Access Configuration

• PostgreSQL Basic Administration

2. Environmental information and scope of application of this article

• environmental information

• scope of application

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
Installing PostgreSQL 9.5 is yum 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 the PostgreSQL service

#Start the PostgreSQL service
sudo systemctl start postgresql-12

#Set the PostgreSQL service to start at boot
sudo systemctl enable postgresql-12

The service name of the 9.x version is postgresql-9.x

2. Modify the postgres account password

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

A super user will be initialized in the postgres databasepostgres

In order to facilitate us to use the postgres account for management, we can modify the password of the account

1. Enter the PostgreSQL command line

Switching the linux user to postgres through the 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 the configuration file
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 the configuration file
vi /var/lib/pgsql/12/data/pg_hba.conf

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

4. Restart the PostgreSQL service

#Restart the PostgreSQL service
sudo systemctl restart postgresql-12

After the configuration is complete, you can use the client to connect

Four, PostgreSQL shell common syntax examples

Start the 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 in the current database
\d

2. Example of user and access authorization syntax

# create a new user
CREATE USER test WITH PASSWORD 'test';

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

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

Permission codes: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE

5. Remarks

1. Related reading

• https://www.postgresql.org/download/

• https://www.runoob.com/postgresql/postgresql-privileges.html