PostgreSQL installation, configuration and simple usage methods

[root@node1 ~]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)

[root@node1 ~]#yum -y install pgdg-centos96-9. #yum source installation[root@node1 ~]# yum -y install postgresql-server #install postgresql#Installing the generated files[root@node1 ~]# rpm -ql postgresql-server
/etc//postgresql
/usr/bin/initdb
/usr/bin/pg_basebackup
/usr/bin/pg_controldata
/usr/bin/pg_ctl
/usr/bin/pg_receivexlog
/usr/bin/pg_resetxlog
/usr/bin/postgres
/usr/bin/postgresql-check-db-dir
/usr/bin/postgresql-setup
/usr/bin/postmaster
/usr/lib/systemd/system/
/usr/lib//
/var/lib/pgsql
/var/lib/pgsql/.bash_profile
/var/lib/pgsql/backups
/var/lib/pgsql/data
/var/run/postgresql、
#Start postgresql#Directly start the error:[root@node1 ~]# systemctl start 
Job for  failed because the control process exited with error code. See "systemctl status " and "journalctl -xe" for details.
#The above prompts that the database has not been initialized yet, so let's initialize it firstpostgresql-setup initdb
Initializing database ... OK #Tip initialization is successful#Restart Postgresql[root@node1 ~]# systemctl start 
[root@node1 ~]# netstat -tnlp
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 
tcp  0  0 127.0.0.1:5432  0.0.0.0:*  LISTEN  1512/postgres  
tcp6  0  0 ::1:5432    :::*  LISTEN  1512/postgres 
#Check the running status[root@node1 ~]# systemctl status 
●  - PostgreSQL database server
 Active: active (running) since Sat 2016-11-26 22:49:07 CST; 1min 33s ago
#Switch to the "postgres" user under the operating system and log in to the database[root@node1 ~]# su - postgres
-bash-4.2$ psql
psql (9.2.15)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
  \h for help with SQL commands
  \? for help with psql commands
  \g or terminate with semicolon to execute query
  \q to quit
#So far，The basic installation has been completed。

#Start compile and install[root@node1 soft]# tar xf postgresql-9.6..bz2
[root@node1 soft]# cd postgresql-9.6.1
# yum -y groupinstall "Development tools" #Development package group# yum -y install perl-ExtUtils-Embed readline-devel zlib-devel python-devel #dependency package# ./configure --prefix=/usr/local/postgresql-9.6.1 --with-perl --with-python --with-blocksize=32 --with-wal-blocksize=64 --with-wal-segsize=64
# make && make install
#Configuration after installation[root@node1 postgresql-9.6.1]# cat /etc//
export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/data/pgdata
[root@node1 postgresql-9.6.1]# source /etc//
[root@node1 postgresql-9.6.1]# echo "/usr/local/pgsql/lib" > /etc//
[root@node1 postgresql-9.6.1]# ldconfig
#Create a database directory and initialize the database[root@node1 postgresql-9.6.1]# mkdir /data/pgdata/
[root@node1 postgresql-9.6.1]# chown -R  /data/pgdata/
[root@node1 postgresql-9.6.1]# su - postgres
-bash-4.2$ initdb
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
fixing permissions on existing directory /data/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
 pg_ctl -D /data/pgdata -l logfile start
#Installing the tools in the contrib directory# cd postgresql-9.6.1/contrib/
# make
# make install
#Start and stop the database# pg_ctl start -D $PGDATA #PGDATA is the data directory of pgsql# pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
in-mIt is the method of setting up a database stop，There are three types of
smart：After all connections are aborted，Close the database。If the client does not terminate，则无法Close the database。
fast：快速Close the database，Disconnect the client，Let existing transactions roll back，然后正常Close the database。
immediate：立即Close the database，The database process is stopped immediately，Exit directly，The database needs to be repaired next time。

Edit files in the data directory，Find the following content：
#listen_addresses = 'localhost'   # what IP address(es) to listen on;
#port = 5432       # (change requires restart)
listen_addressesIndicates the address to be monitored，To log in to this database on the network，Need to change this address to"*"，or0.0.0.0。
portIndicates the listening port，Can not change，After modifying these two parameters，Need to restart to take effect。
#Parameters related to database loglogging_collector = on #Login collection, on means to openlog_directory = 'pg_log' #Define the directory for the collection of logsThe following solutions can be used for switching logs and whether to select overwrites.
Plan 1：Generate a new log file every day
log_filename = ‘postgresql-%Y-%m-%d_%H%M%'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
Plan 2：Whenever the log is filled with a certain size（like10MBspace），Switch a log
log_filename = ‘postgresql-%Y-%m-%d_%H%M%'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 10M
Plan 3：Only retain7Day's log，Perform loop coverage 
log_filename = ‘postgresql-%'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0