PostgreSQL安裝、配置及簡(jiǎn)單使用方法

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

[root@node1 ~]# yum -y install pgdg-centos96-9.6-3.noarch.rpm #yum源安裝
[root@node1 ~]# yum -y install postgresql-server #安裝postgreesql
#安裝生成的文件
[root@node1 ~]# rpm -ql postgresql-server
/etc/pam.d/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/postgresql.service
/usr/lib/tmpfiles.d/postgresql.conf
/var/lib/pgsql
/var/lib/pgsql/.bash_profile
/var/lib/pgsql/backups
/var/lib/pgsql/data
/var/run/postgresql、
#啟動(dòng)postgresql
#直接啟動(dòng)會(huì)報(bào)錯(cuò)：
[root@node1 ~]# systemctl start postgresql.service
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.
#上面是提示數(shù)據(jù)庫(kù)還沒(méi)有初始化，所以我們先初始化一下
postgresql-setup initdb
Initializing database ... OK #提示初始化成功
#重新啟動(dòng)Postgresql
[root@node1 ~]# systemctl start postgresql.service
[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 
#查看運(yùn)行狀態(tài)
[root@node1 ~]# systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
 Active: active (running) since Sat 2016-11-26 22:49:07 CST; 1min 33s ago
#切換到操作系統(tǒng)下的“postgres”用戶，登錄數(shù)據(jù)庫(kù)
[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
#到此為止，基本安裝已經(jīng)完成。

#開(kāi)始編譯安裝
[root@node1 soft]# tar xf postgresql-9.6.1.tar.bz2
[root@node1 soft]# cd postgresql-9.6.1
# yum -y groupinstall "Development tools" #開(kāi)發(fā)包組
# yum -y install perl-ExtUtils-Embed readline-devel zlib-devel python-devel #依賴包
# ./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
#安裝后的配置
[root@node1 postgresql-9.6.1]# cat /etc/profile.d/postgresql.sh
export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/data/pgdata
[root@node1 postgresql-9.6.1]# source /etc/profile.d/postgresql.sh
[root@node1 postgresql-9.6.1]# echo "/usr/local/pgsql/lib" > /etc/ld.so.conf.d/pgsql.conf
[root@node1 postgresql-9.6.1]# ldconfig
#創(chuàng)建數(shù)據(jù)庫(kù)目錄并初始化數(shù)據(jù)庫(kù)
[root@node1 postgresql-9.6.1]# mkdir /data/pgdata/
[root@node1 postgresql-9.6.1]# chown -R postgres.postgres /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
#安裝contrib目錄下的工具
# cd postgresql-9.6.1/contrib/
# make
# make install
#啟動(dòng)和停止數(shù)據(jù)庫(kù)
# pg_ctl start -D $PGDATA #PGDATA是pgsql的數(shù)據(jù)目錄
# pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
其中-m是制定數(shù)據(jù)庫(kù)的停止方法，有以下三種
smart：等所有的連接中止后，關(guān)閉數(shù)據(jù)庫(kù)。如果客戶端不中止，則無(wú)法關(guān)閉數(shù)據(jù)庫(kù)。
fast：快速關(guān)閉數(shù)據(jù)庫(kù)，斷開(kāi)客戶端的連接，讓已有的事務(wù)回滾，然后正常關(guān)閉數(shù)據(jù)庫(kù)。
immediate：立即關(guān)閉數(shù)據(jù)庫(kù)，相當(dāng)于數(shù)據(jù)庫(kù)進(jìn)程立即停止，直接退出，下次啟動(dòng)數(shù)據(jù)庫(kù)需要進(jìn)行修復(fù)。

在數(shù)據(jù)目錄下編輯postgresql.conf文件，找到如下內(nèi)容：
#listen_addresses = 'localhost'   # what IP address(es) to listen on;
#port = 5432       # (change requires restart)
listen_addresses表示要監(jiān)聽(tīng)的地址，要讓網(wǎng)絡(luò)上的主機(jī)登錄這臺(tái)數(shù)據(jù)庫(kù)，需要把這個(gè)地址改成"*"，或者0.0.0.0。
port表示監(jiān)聽(tīng)的端口，可以不更改，修改這兩個(gè)參數(shù)后，需要重啟才能生效。
#與數(shù)據(jù)庫(kù)Log相關(guān)的參數(shù)
logging_collector = on #日志的收集，on表示打開(kāi)
log_directory = 'pg_log' #定義日志的收集目錄
日志的切換和是否選擇覆蓋則可以使用如下幾種方案
方案一：每天生產(chǎn)一個(gè)新的日志文件
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
方案二：每當(dāng)日志寫滿一定的大?。ㄈ?0MB空間），則切換一個(gè)日志
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 10M
方案三：只保留7天的日志，進(jìn)行循環(huán)覆蓋 
log_filename = ‘postgresql-%a.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0