CentOS (linux) install PostgreSQL

Introduction##

PostgreSQL is a powerful open source database system. After more than 15 years of active development and continuous improvement, PostgreSQL has gained a high reputation in the industry for reliability, stability, and data consistency. Currently PostgreSQL can run on all mainstream operating systems, including Linux, Unix (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris and Tru64) and Windows. PostgreSQL is a fully transaction-safe database that fully supports foreign keys, unions, views, triggers, and stored procedures (and supports the development of stored procedures in multiple languages). It supports most of the SQL:2008 standard data types, including integer, numeric value, boolean, byte, character, date, time interval, and time. It also supports storing large binary objects , Including pictures, sounds and videos. PostgreSQL has native programming interfaces for many high-level development languages, such as C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, and other languages, as well as various documents.

As an enterprise-level database, PostgreSQL is proud of its various advanced features, such as multi-version concurrency control (MVCC), point-in-time recovery (PITR), table space, asynchronous replication, nested transactions, online hot backup , Planning and optimization of complex queries, and write-ahead logs for fault tolerance. It supports international character sets, multi-byte encodings, and supports operations such as sorting, case handling, and formatting in local languages. It is also fully scalable in the large amount of data it can manage and the allowed concurrent access time of a large number of users. Many PostgreSQL systems currently manage more than 4TB of data in actual production environments. The limit values of some PostgreSQL systems are listed in the following table:

Numerous functions and standard compatibility###

PostgreSQL is highly compatible with the SQL standard, and the functions it implements fully comply with the ANSI-SQL:2008 standard. Currently, subqueries (including subqueries in FROM), authorized reading, and serializable transaction isolation levels are fully supported. At the same time, PostgreSQL also has a complete relational database system catalog function, it supports the multi-mode function of a single database, and each catalog can be accessed through the dictionary information mode defined in the SQL standard.

Data integration features include (composite) primary keys, foreign keys with strict constraints or cascading update and delete functions, entry check constraints, unique constraints, and non-null constraints.

PostgreSQL also has many extension modules and more advanced functions. Among them, there are auto-increment fields realized by sequence for convenient use, LIMIT/OFFSET options that allow partial record sets to be returned, composite, unique, partial and functional indexes, indexes and support B-Tree, R-Tree, Hash or GiST storage method.

GiST (Universal Search Tree) index is an advanced system algorithm that combines different sorting algorithms with B-Tree, B+-Tree, R-Tree, partial summary tree, weightable B+-Tree, and many other search logics Together, it also provides an interface to allow the creation of user data types and extended query methods. In this way, GiST provides users with the flexibility to specify storage and define new methods for querying---it greatly surpasses the functions provided by standard B-Tree, R-Tree and other general search logic.

GiST has now become the basis for many other public projects that use PostgreSQL, such as OpenFTS and PostGIS projects. The OpenFTS (Open Source Full Text Search Engine) project provides considerable weight scores for online indexing and database searches. The PostGIS project adds geographic information management functions to PostgreSQL, allowing users to use PostgreSQL as a GIS spatial geographic information database, which is the same as the professional ESRI company's SDE system and Oracle's spatial geographic extension module.

Other advanced features include table inheritance, rules, and database incident response functions. The table inheritance function can create a new table with a relationship based on the original table, which allows database designers to use a table as a base table and derive a new table from the base table. And PostgreSQL can even use this method to implement single-level or multi-level inheritance.

The rule function is used to call the recalculation function of the query, allowing database designers to create rules based on different tables or views to realize the function of dynamically changing the original operation of the database to the new operation.

The event response function is an internal communication function, which transmits system information or events after the LISTEN and NOTIFY instructions used by the user, allowing brief point-to-point communication or fixed-point communication for specified database events. Since information can be sent from triggers or stored procedures, PostgreSQL users can monitor database events such as updates, additions, or deletions.

Highly customizable###

PostgreSQL stored procedure development can use many programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++ and its own PL/pgSQL. PL/pgSQL is very similar to Oracle's PL/SQL, with built-in Hundreds of functions ranging from basic arithmetic calculations and string processing to encrypted logic calculations and highly compatible with Oracle. Triggers and stored procedures can be developed using C language and can be loaded into the database as internal library files. The great flexibility of development expands the database capabilities. Correspondingly, PostgreSQL also includes a framework that allows developers to define and create their own data types that can be used in functions, as well as to define new processing methods for operators. With such capabilities, PostgreSQL now has various Advanced data types, including geometric figures, spatial geography, network addresses, and even ISBN/ISSN (International Standard Book Number/International Standard Serial Number), can all be added to the system.

Since there are many stored procedure languages that can be used, a lot of library interfaces are also produced, which allows various compiled or interpreted languages to be used in PostgreSQL, including Java (JDBC), ODBC, Perl, Python, Ruby, C, C++, PHP, Lisp, Scheme, Qt, etc.

The most important point is that the source code of PostgreSQL can be obtained freely. Its authorization is under a very free open source license. This authorization allows users to use, modify and release the source code of PostgreSQL in various open source or closed source projects. Users can make any modifications and improvements to the source code according to their wishes. Therefore, PostgreSQL is not only a powerful enterprise-level database system, but also a database development platform where users can develop private, network and commercial software products.

Download yum源##

Address: https://yum.postgresql.org/repopackages.php

Install yum source##

yum install pgdg-centos10-10-2.noarch.rpm

View installable packages##

yum list postgres*

Install the database##

yum install postgresql10-server.x86_64 

initialization##

First find the installed service, not postgres, check

[ root@d2-test-2 init.d]# ls /etc/init.d/| grep post
postfix
postgresql-10
udev-post

Initialize db

service postgresql-10 initdb

start up###

service postgresql-10 start

View status

service postgresql status

Restart###

service postgresql restart

connection##

By default root cannot connect, you need to switch to user postgres

$ sudo su - postgres   
- bash-4.1$ psql   
psql(8.4.20)   
Type "help"for help.   
postgres=#    

A few simple commands##

(1) List all databases

mysql: show databases
psql: \l or\list

(2) Switch database

mysql: use dbname
psql: \c dbname

(3) List the data tables under the current database

mysql: show tables
psql: \d

(4) List all fields of the specified table

mysql: show columns from table name
psql: \d tablename

(5) View the basic situation of the specified table

mysql: describe tablename
psql: \d+ tablename

(6) sign out

mysql:quit or\q
psql:\q

(7) View pgsl version

pg_ctl --version

(8) Command line login database

psql -h 192.168.2.125-p 5432<dbname><username>

(9) change Password

psql login

then,\password postgres

Configuration after installation##

The database is installed by default as: /var/lib/pgsql/10/data

Modify the listening IP and port###

After entering the data directory, edit postgresql.conf

Modify to * to allow all

# listen_addresses ='*'                 # what IP address(es) to listen on;   
          # comma-separated list of addresses;   
          # defaults to 'localhost','*'= all   
          # ( change requires restart)   
# port =5432                            # (change requires restart)

Reboot

service postgresql-10 restart

Modify connection permissions###

By default, only local users can access, so in addition to modifying the ip, you must modify the permissions.

Modify pg_hba.conf

# TYPE DATABASE  USER    CIDR-ADDRESS     METHOD
# " local" is for Unix domain socket connections only
local all    all               trust
# IPv4 local connections:
host  all    all    127.0.0.1/32     trust
host  all    all    192.168.1.0/24    md5
# IPv6 local connections:
host  all    all    ::1/128       trust

The point is

host  all    all    192.168.1.0/24    md5

It means that all hosts on the network segment 192.168.1.0 are allowed to use all legal database user names to access the database and provide encrypted password authentication.

Among them, 24 is the subnet mask, which means that hosts 192.168.1.0--192.168.1.255 are allowed to access.

reference##

• https://blog.csdn.net/p106786860/article/details/49877483
• https://blog.csdn.net/ll136078/article/details/12747403