[summary] if the server where CentOS is located is limited to security reasons and the customer does not configure us to access the Internet, how to install the PostgreSQL database? This article will explain in detail how to install PostgreSQL12 database offline on CentOS7 system through specific steps.

1 offline installation package preparation

First, you can visit the website
https://yum.postgresql.org/13/redhat/rhel-7-x86_64/repoview/postgresqldbserver13.group.html
To download the corresponding offline installation package file (this article takes postgresql13 as an example). The specific example screenshot is as follows:

Generally, you need to download the following RPM installation packages:

postgresql13-13.0-1PGDG.rhel7.x86_64
#Install extensions
postgresql13-contrib-13.0-1PGDG.rhel7.x86_64
#Connect to the client
postgresql13-libs-13.0-1PGDG.rhel7.x86_64
#Installation files for database
postgresql13-server-13.0-1PGDG.rhel7.x86_64

Note: the specific version needs to be selected according to your own needs. centos7 corresponds to rhel7. If it is centos8, select rhel8 to download.

Then, you need to use the SSL tool to upload the locally downloaded RMP installation file to the CentOS7 server.
(although the server cannot access the website resources online, it can log in remotely for management).

2 offline installation

First, enter the directory where the above offline installation files are located, and use the ll command to query whether the installation package has been uploaded offline. The screenshot of the interface is as follows:

In postgresql13_ Under the RPM directory, execute the following command to install the installation package:

yum localinstall *.rpm 

The network may be accessed during the installation, but the installation will not be affected. When prompted to confirm the installation, select y confirm the installation and wait for the installation. After successfully installing postgresql 13 database, a service will be automatically created. You can take a look at the service named postgresql-13 and use the following command to view the service status:

systemctl status postgresql-13  

After the first installation, this command displays relevant file information:

Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)

You can view the contents of this file through the cat command, which contains relevant data storage directory information:

cat /usr/lib/systemd/system/postgresql-13.service

 Location of database directory
Environment=PGDATA=/var/lib/pgsql/13/data/ 
#Program directory
ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA} 

3 database initialization

With the data storage directory and program directory of the database, you can use the following commands to initialize the database. The specific example code is as follows:

su - postgres
cd /usr/pgsql-13/bin/
#Initialize database
./initdb -D /var/lib/pgsql/13/data/

After successfully executing the command, you need to exit the postgresql command environment, start the postgresql service with root user, and set the postgresql service to start automatically. In this way, after the server is restarted, there is no need to manually restart the postgresql service. The specific commands are as follows:

systemctl enable  postgresql-13 && systemctl restart postgresql-13

Finally, you need to configure the security of PG database, such as changing password, creating user, setting login policy, etc. some example codes are given below:

su - postgres
psql

#Change password

alter role postgres with password 'your_root_pwd###';

#Modify configuration files, configure login policies, etc

vim /var/lib/pgsql/12/data/pg_hba.conf
#Add a line
host    all             all             0.0.0.0/0         md5   

Note: trust is password free and md5 is encryption
#Modify other database configuration information

vim /var/lib/pgsql/13/data/postgresql.conf
listen_addresses = '*' 
max_connections = 700

#Restart service

systemctl restart  postgresql-13

After restarting, check the database status again. The command is as follows:

So far, the database installation is completed...

4 psql command details

psql -h <hostname or ip> -p <port> [Database name] [User name]

**psql Common connection parameters**

abbreviation	explain
-h	Database server host
-p	Database server port
-U	Database user name
-d	Connected database name
-c	Run one sql command, Then exit
-f	Execute the commands in the file, Then exit
-l	List available databases, Then exit
-V	Output version information and exit
-q	There are no redundant messages, only query output
-H	Query results in HTML Tabular output

psql command

Common commands	explain
\q	Exit command line
\l	View database list
\c	Switch database
\x	Switching between horizontal and vertical display of query results
\s	View command history
\h	View all sql keyword
\?	Help for command line operations
\i	implement sql file
\timing	implement SQL Time consuming switch
\encoding	Display character set
\pset border 0	The output has no border
\pset border 1	Borders exist only internally
\pset border 2	There are borders inside and outside
\d command	explain
\d	Displays tables, indexes, and views
+	Display more information, including comments on tables and columns
\da	Displays all available aggregate functions and the data types they operate on
\dA	Display access method
\dAc	Display method subcategory
\dAf	Display method family
\dAo	Display method home operator
\dAp	Support functions of display method family
\db	Show all tablespaces
\dc	Displays the available conversions between all character sets
\dC	Show all type conversions
\dd	Show all visible objects,Or all matches pattern Description of
\dD	Show all available domains
\ddp	Show default permissions
\deu	Show user mappings
\df	Show matching functions(stored procedure)
\dF	Display text search configuration
\dFd	Display text search dictionary
\dFp	Display text search parser
\dFt	Display text search template
\dg	Show roles
\di	Show matching indexes
\dl	Displays a list of large objects, and\lo_list identical
\dL	Display program language
\dm	Display materialized view
\dn	Show all available schemas (namespaces)
\do	Displays all available operators, along with their operands and the data types returned
\dO	Show collation
\dp	Show access to tables, views, and sequences
\drds	Displays the role settings for each database
\ds	Show matching sequences
\dt	Show matching tables
\dT	Display data type
\du	Show roles
\dv	Show matching views
\dx	List extension
\dy	Show event triggers

Example:

1. The current database is in front of the command prompt. Use \ l to view the current database list:

2. You can create a new database:

3. Execute the command \ c to switch the current database to test:

4. Execute \ d to view all tables under the current database:

5. Create a new table and view it again:

CREATE TABLE school (
    id        integer not null ,
    name      character(32),
    number    char(5),
    constraint sch_key primary key(id)
);

6. Execute \ d school to view the details of the table:

7. Execute \ q to exit the database: