Install and simply set up PostgreSQL notes under CentOS7

Why is PostgreSQL?

Before the advent of. NET core, the most common development component on the Microsoft platform was the. NET Framework + SQL Server, but now that the. NET core has finally made cross-platform deployment a reality, will this model be common? Personally, the gold partner is likely to be getting a lot of momentum because of the future. NET application will be deployed on Linux, do people deploy a Windows environment in order to use SQL Server? It's not possible to think about it, so choosing a suitable database for. NET core on Linux becomes very important. It's not hard to choose, because there are two options, one is MySQL (the world's most popular Open-source database) and the other is PostgreSQL (the world's most advanced open source database), I chose PostgreSQL from my current knowledge.

Full-text search of MySQL on 51job, with 1568 pages of content, and full-text search PostgreSQL with only 44 pages of content (September 16, 2016 data), indicating that enterprise and employee data using MySQL far exceed PostgreSQL data, Then why do I have to choose PostgreSQL? This is to start from three aspects, one is that I have learned and used MySQL, I feel MySQL easy to get started, proficient in difficulties, the general programmer is not trained to control MySQL, there is a project, the development phase seemingly and SQL Server is not very different, But after the system is deployed to the production environment, the performance problem will be exposed soon, so I have to gig, learn to use, reconstruct the entire data access layer, (some students may ask, PostgreSQL may be more difficult than MySQL, the amount, I am most worried about the project team in the SQL script technology is not clear, MySQL for the optimization of the script did not drop ...) Secondly, I am interested in GIS application system at present, and PostgreSQL has an incomparable advantage, that is, Postgis,postgis can perfectly support spatial data storage and spatial analysis. The third is the JSON data type built from PostgreSQL9.3, and the 9.4 starts to support JSONB, which signifies that PostgreSQL is actually a combination of a relational database and a NoSQL database, and that the NoSQL performance of PostgreSQL is beneficial to even hyper- After the mongodb!. , which is a good news for GIS big data applications. What other reason do I have to deny PostgreSQL?

~~~~~~~~~~~~~~~~ I am the split line, above all is nonsense ~~~~~~~~~~~~~~~~

Installation environment: CentOS7.2, same as here environment

Before installing, it's always a good habit to look at the official installation instructions

CentOS7.2 's own PostgreSQL is version 9.2, there is no nosql feature, so we use the RPM package installation method, PostgreSQL's repository package address list here.

1. First install the PostgreSQL rpm

sudo yum install http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-redhat95-9.5-2.noarch.rpm-y

2. Then install the PostgreSQL server and third-party expansion packs

sudo yum install postgresql95-server postgresql95-contrib-y

3. Initializing the database

Sudo/usr/pgsql-9.5/bin/postgresql95-setup Initdb

4. Configure the database service to boot and start the database service immediately

sudo systemctl enable postgresql-9.5. Servicesudo service PostgreSQL-9.5 start

5. Check the database service status, there is green, no red instructions to start OK

Service postgresql-9.5 Status

6. Modify postgres user password, switch to Postgres user

sudo passwd postgres

Su Postgres

7. Then log in to the database using the Psql tool, listing the current database with commands Psql and \l

On CentOS, the default PostgreSQL data directory is the/var/lib/pgsql/version number/data

PostgreSQL configuration file in this directory/var/lib/pgsql/version number/DATA/POSTGRESQL.CONFG, there is a configuration file also need a little attention, that is, access control configuration file/var/lib/pgsql/version number/ Data/pg_hba.conf

8. Some settings need to be made for the service profile postgresql.conf first:

Remove the # number before the #listen_addresses = ' localhost ' , then change the following localhost to * and then remove #port = 5432 # before the # , and finally the #password_encryption = on front of the # number is removed, as shown in, use the WQ command to save the exit.

(Friendly tip: postgresql.conf can be modified in a number of parameters, the 2nd red box can modify the listening port, in addition to this file can also modify the size of the cache and other parameters)

9. Configure the pg_hba.conf content again, change the ident in the red box above to MD5, and then add the host all 0.0.0.0/0 MD5 to the bottom, as shown in:

10. Restart the postgresql-9.5 service to make the configuration file more efficient

Service postgresql-9.5 Restart

11. Next we create a database

Psql

CREATE DATABASE TestDB;

As shown, we have created a database named TestDB.

12. Then create a user

CREATE USER ' 111111 '

As shown, this allows us to create a user named think8848, followed by createdb LOGIN PASSWORD '111111' This means that the user can create a database and log in, and his password is 111111.

13. Next, assign all TestDB permissions to think8848 students

GRANT  All  on DATABASE  to think8848

14. Exit Postgres User Login, command for \q, yes, you see wrong, is a backslash and a Q

15. Open Firewall 5432 Port

sudo firewall-cmd--zone=public--add-port=5432/tcp--permanentsudo firewall-cmd--reload

16. Try logging in to the database with think8848

Psql-u think8848-h 127.0.0.1-p 5432-d testdb-w

Finally, say two psql very common commands, a \c, switch the database, such as: \c Postgres; , its role and the SQL Server's Use postgres; The other is \d, which lists all the tables under the current library.

17. I intend to use PostgreSQL as a database server, but never intended to use psql when management tools, so still need to find a GUI management tools to do ah, do not know which is good, first grabbed over a use to see, PGADMIN4, here ,

Just build a server and see what's in it.

And give me a louder name.

Then configure the server IP and other information

After the point of Save and did not react to the servers on the right click Refresh, or no effect, is this goods no use? Well, try the IT guy's killer tricks, close the program and reopen it ...

Looks good, can use a graphical interface to manage the database, and then free, we can not ask too much, right?

Install and simply set up PostgreSQL notes under CentOS7