CENTOS7 Installation Configuration PostgreSQL9.6

Reference Documentation:

1. Installation initialization: https://www.postgresql.org/download/linux/redhat/
2. Simple to use: https://mozillazg.github.io/2014/06/hello-postgresql.html

This article deals with the Yum installation of PostgreSQL, access configuration and simple use.

AVerifying the Environment1.Operating System

centos-7-x86_64-everything-1511

2. Postgressql version

PostgreSQL 9.6.3: https://www.postgresql.org/download/linux/redhat/

Twoinstallation1.Install RPM

[[Email Protected]_master ~] # Yum install-y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/ pgdg-centos96-9.6-3.noarch.rpm

2.Installing the client

[[Email Protected]_master ~] # Yum install-y postgresql96

3. Install server-side

# Yum installs PostgreSQL and by default a system account called "Postgres" is built to perform PostgreSQL; # at the same time, a database user named "Postgres" is generated in the database, and the password is automatically generated and needs to be modified after entering the database; # PostgreSQL is logged in as a password-free system account with the same name as the database user. [email protected]_master ~]#  yum install-y postgresql96-server

4.Initialize

[Email Protected]_master bin] # /usr/pgsql-9.6/bin/postgresql96-setup Initdb

5. Set Boot up

[[Email Protected]_master ~] # Systemctl Enable postgresql-9.6

6. Start

[[Email Protected]_master ~] # systemctl start postgresql-9.6

Three The configuration uses 1.Modify User Password

# Yum installs PostgreSQL and by default a system account called "Postgres" is built to perform PostgreSQL; [[Email Protected]_master ~] # Su-postgres # after switching the user, the prompt changes to "-bash-4.2$"; # a database user named "Postgres" is also generated in the database, and the password is automatically generated; # PostgreSQL Login with the same name of the database user system account,-bash-4.2$ psql-U postgres# Enter the database after the password change; postgres=#  alter user postgres with password ' [email protected] '

2.Allow remote access

# configuration file, the default is only native access to PostgreSQL; # Modify listen_addresses = ' localhost ' for listen_addresses = ' * ', allowing all remote access; # Modifying the configuration file requires restarting the service. [email protected]_master ~]#  sed-i "s| #listen_addresses = ' localhost ' |listen_addresses = ' * ' |g '/var /lib/pgsql/9.6/data/postgresql.conf

3.Host authentication

# after the 82nd line, "IPv4 local Connections" under the new allowed clients; # "Host" on behalf of the host type, the first "all" for the DB, the second "all" for the user, "172.29.3.67/32" on behalf of the client IP, "trust" on behalf of the authentication method; # authentication method In addition to "trust", there are "peer", "ident", "MD5", "password" and so on, specific reference Pg-hba file: https://www.postgresql.org/docs/current/ static/auth-pg-hba-conf.html# Modifying the Pg.hba file requires restarting the service. [[Email protected]_master ~]#  vim/var/lib/pgsql/9.6/data/pg_hba.confhost    All All             172.29.3.67/32          Trust

4.Setting Environment Variables

[[Email Protected]_master ~] # vim/etc/profileExport path= $PATH:/usr/pgsql-9.6/~]#  source/etc/profile

5. Restart the service

[[Email Protected]_master ~] # systemctl Restart postgresql-9.6

6. Iptables

# PostgreSQL opens tcp5432 port by default [[Email Protected]_master ~] # vim/etc/sysconfig/iptables-A input-m State--state new-m tcp-p TCP--dport 5432-~]#  s Ervice iptables Restart

FourUse validation1.View Ports

[[Email Protected]_master ~] # NETSTAT-TUNLP

2. Simple use 1) create user

postgres=#  create user postuser1 with password ' [email protected] ';

2) Create a database

# also specify the owner of the database postgres=#  Create database postdb1 owner Postuser1;

3)Database Empowerment

# the account can only be logged in to the console postgres=#  Grant all privileges on the database postdb1 to Postuser1;

4) Log in to create a new database

# log in to the new database using the newly created account at the operating system level, and log in to the "postdb1=>" prompt; # If you use the postgres=# \c postdb1 directly under the Postgres account; Log in, the logged-on user remains postgres,-bash-4.2$ psql-u postuser1-d postdb1-h 127.0.0.1-p 5432

5) Create a table

postdb1=> CREATE TABLE TB1 (          ID int primary KEY,          name VARCHAR (),           salary real          );

6) Inserting data

postdb1=> INSERT INTO TB1 (          ID, name, salary)          values (          'Mike' , 5000.00          );

7)Enquiry

 from TB1;

3. Pgadmin Connect PostgreSQL

pgadmin:https://www.pgadmin.org/download/

The version as of 2017-05-19 is: Pgadmin 4 v1.5

1)Add Server

Open pgadmin-> Add New server--(usual label) name customization--(connection label) host name and PostgreSQL user password on demand, the rest can be saved by default configuration.

2) Graphical view

CENTOS7 Installation Configuration PostgreSQL9.6