前言
PostgreSQL 号称是这个世界上最高级的开源数据库
作为一个运维人员是怎样也无法忽视的
由于特性丰富，很多 CMDB 都是基于它开发的，再加上当前的物联网热潮，IOT 场景中大量涉及时空数据的处理，这些方面都是它的专长
之前的 gitlab 可以对接 mysql 也可以对接 PostgreSQL ，但是官网推荐使用 PostgreSQL 作为其后端数据库，因为使用 PostgreSQL 就可以使用所有的 gitlab 特性，而如果使用 mysql ，部分特性将会无法正常工作，我想应该是数据库层面的特性导致的这种差异吧，PostgreSQL 有更为丰富的特性支持
这里对 PostgreSQL 的安装做一个简单的演示，详细特性可以参考 PostgreSQL Documentation ，后期关于它的细节特性，再一点点展开
Tip: 当前的最新稳定版为 Aug. 10, 2017 发布的 PostgreSQL 9.6.4 ，同时 PostgreSQL 10 Beta 3 也在 2017-08-10 进行了发布
概要
系统环境
代码语言：javascript复制[root@much ~]# hostnamectl
Static hostname: much
Icon name: computer-vm
Chassis: vm
Machine ID: 33dc28f7e76c4903ad9b603b77e29a7c
Boot ID: 902c440d59844169b696287b0d63d1e4
Virtualization: kvm
Operating System: CentOS Linux 7 (Core)
CPE OS Name: cpe:/o:centos:centos:7
Kernel: Linux 3.10.0-514.21.1.el7.x86_64
Architecture: x86-64
[root@much ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:04:c7:5a brd ff:ff:ff:ff:ff:ff
inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
valid_lft 85861sec preferred_lft 85861sec
inet6 fe80::2bb7:5b3:9584:d8eb/64 scope link
valid_lft forever preferred_lft forever
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:b5:a5:da brd ff:ff:ff:ff:ff:ff
inet 192.168.56.206/24 brd 192.168.56.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:feb5:a5da/64 scope link
valid_lft forever preferred_lft forever
4: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN qlen 1000
link/ether 52:54:00:16:5e:11 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
5: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN qlen 1000
link/ether 52:54:00:16:5e:11 brd ff:ff:ff:ff:ff:ff
[root@much ~]# uname -a
Linux much 3.10.0-514.21.1.el7.x86_64 #1 SMP Thu May 25 17:04:51 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
[root@much ~]# 目标
- Centos7 下安装 PostgreSQL
安装 postgresql repo
PostgreSQL Core Distribution 里有不同平台下的 PostgreSQL 版本
这里根据我的具体环境选择 Linux downloads (Red Hat family)
根据提示选择合适的 版本，平台和架构
代码语言：javascript复制[root@much ~]# ll /etc/yum.repos.d/
total 32
-rw-r--r--. 1 root root 1664 11月 30 2016 CentOS-Base.repo
-rw-r--r--. 1 root root 1309 11月 30 2016 CentOS-CR.repo
-rw-r--r--. 1 root root 649 11月 30 2016 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 314 11月 30 2016 CentOS-fasttrack.repo
-rw-r--r--. 1 root root 630 6月 16 01:08 CentOS-Media.repo
-rw-r--r--. 1 root root 884 5月 25 02:37 CentOS-OpenShift-Origin15.repo
-rw-r--r--. 1 root root 1331 11月 30 2016 CentOS-Sources.repo
-rw-r--r--. 1 root root 2893 11月 30 2016 CentOS-Vault.repo
[root@much ~]# yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
Loaded plugins: fastestmirror, langpacks
pgdg-centos96-9.6-3.noarch.rpm | 4.7 kB 00:00
Examining /var/tmp/yum-root-9Au8QL/pgdg-centos96-9.6-3.noarch.rpm: pgdg-centos96-9.6-3.noarch
Marking /var/tmp/yum-root-9Au8QL/pgdg-centos96-9.6-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-centos96.noarch 0:9.6-3 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
pgdg-centos96 noarch 9.6-3 /pgdg-centos96-9.6-3.noarch 2.7 k
Transaction Summary
================================================================================
Install 1 Package
Total size: 2.7 k
Installed size: 2.7 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pgdg-centos96-9.6-3.noarch 1/1
Verifying : pgdg-centos96-9.6-3.noarch 1/1
Installed:
pgdg-centos96.noarch 0:9.6-3
Complete!
[root@much ~]# ll /etc/yum.repos.d/
total 36
-rw-r--r--. 1 root root 1664 11月 30 2016 CentOS-Base.repo
-rw-r--r--. 1 root root 1309 11月 30 2016 CentOS-CR.repo
-rw-r--r--. 1 root root 649 11月 30 2016 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 314 11月 30 2016 CentOS-fasttrack.repo
-rw-r--r--. 1 root root 630 6月 16 01:08 CentOS-Media.repo
-rw-r--r--. 1 root root 884 5月 25 02:37 CentOS-OpenShift-Origin15.repo
-rw-r--r--. 1 root root 1331 11月 30 2016 CentOS-Sources.repo
-rw-r--r--. 1 root root 2893 11月 30 2016 CentOS-Vault.repo
-rw-r--r--. 1 root root 1012 9月 21 2016 pgdg-96-centos.repo
[root@much ~]# cat /etc/yum.repos.d/pgdg-96-centos.repo
[pgdg96]
name=PostgreSQL 9.6 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
[pgdg96-source]
name=PostgreSQL 9.6 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
[pgdg96-updates-testing]
name=PostgreSQL 9.6 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/testing/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
[pgdg96-source-updates-testing]
name=PostgreSQL 9.6 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/testing/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
[root@much ~]# 之后就可以列出新版的 pgsql
代码语言：javascript复制[root@much ~]# yum list all | grep postgresql
freeradius-postgresql.x86_64 3.0.4-8.el7_3 updates
libreoffice-postgresql.x86_64 1:5.0.6.2-5.el7_3.1 updates
pcp-pmda-postgresql.x86_64 3.11.3-4.el7 base
postgresql.i686 9.2.18-1.el7 base
postgresql.x86_64 9.2.18-1.el7 base
postgresql-contrib.x86_64 9.2.18-1.el7 base
postgresql-devel.i686 9.2.18-1.el7 base
postgresql-devel.x86_64 9.2.18-1.el7 base
postgresql-docs.x86_64 9.2.18-1.el7 base
postgresql-jdbc.noarch 42.1.4-1.rhel7 pgdg96
postgresql-jdbc-javadoc.noarch 42.1.4-1.rhel7 pgdg96
postgresql-libs.i686 9.2.18-1.el7 base
postgresql-libs.x86_64 9.2.18-1.el7 base
postgresql-odbc.x86_64 09.03.0100-2.el7 base
postgresql-plperl.x86_64 9.2.18-1.el7 base
postgresql-plpython.x86_64 9.2.18-1.el7 base
postgresql-pltcl.x86_64 9.2.18-1.el7 base
postgresql-server.x86_64 9.2.18-1.el7 base
postgresql-test.x86_64 9.2.18-1.el7 base
postgresql-unit96.x86_64 3.1-1.rhel7 pgdg96
postgresql-unit96-debuginfo.x86_64 3.1-1.rhel7 pgdg96
postgresql-upgrade.x86_64 9.2.18-1.el7 base
postgresql96.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-contrib.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-debuginfo.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-devel.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-docs.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-libs.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-odbc.x86_64 09.06.0410-1PGDG.rhel7 pgdg96
postgresql96-plperl.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-plpython.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-pltcl.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-server.x86_64 9.6.4-1PGDG.rhel7 pgdg96
postgresql96-tcl.x86_64 2.3.1-1.rhel7 pgdg96
postgresql96-tcl-debuginfo.x86_64 2.3.1-1.rhel7 pgdg96
postgresql96-test.x86_64 9.6.4-1PGDG.rhel7 pgdg96
qt-postgresql.i686 1:4.8.5-13.el7 base
qt-postgresql.x86_64 1:4.8.5-13.el7 base
qt5-qtbase-postgresql.i686 5.6.1-10.el7 base
qt5-qtbase-postgresql.x86_64 5.6.1-10.el7 base
[root@much ~]# 安装客户端
代码语言：javascript复制[root@much ~]# yum install postgresql96
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* c7-media:
* extras: mirrors.163.com
* updates: mirrors.sohu.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql96.x86_64 0:9.6.4-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql96-libs(x86-64) = 9.6.4-1PGDG.rhel7 for package: postgresql96-9.6.4-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql96-9.6.4-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql96-libs.x86_64 0:9.6.4-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql96 x86_64 9.6.4-1PGDG.rhel7 pgdg96 1.4 M
Installing for dependencies:
postgresql96-libs x86_64 9.6.4-1PGDG.rhel7 pgdg96 312 k
Transaction Summary
================================================================================
Install 1 Package ( 1 Dependent package)
Total download size: 1.7 M
Installed size: 8.1 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql96-libs-9.6.4-1PGDG.rhel7.x86_64.rpm | 312 kB 00:03
(2/2): postgresql96-9.6.4-1PGDG.rhel7.x86_64.rpm | 1.4 MB 00:04
--------------------------------------------------------------------------------
Total 414 kB/s | 1.7 MB 00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql96-libs-9.6.4-1PGDG.rhel7.x86_64 1/2
Installing : postgresql96-9.6.4-1PGDG.rhel7.x86_64 2/2
Verifying : postgresql96-9.6.4-1PGDG.rhel7.x86_64 1/2
Verifying : postgresql96-libs-9.6.4-1PGDG.rhel7.x86_64 2/2
Installed:
postgresql96.x86_64 0:9.6.4-1PGDG.rhel7
Dependency Installed:
postgresql96-libs.x86_64 0:9.6.4-1PGDG.rhel7
Complete!
[root@much ~]# 查看 psql 的版本
代码语言：javascript复制[root@much ~]# psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "root")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "root")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "?" (for internal commands) or "help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@postgresql.org>.
[root@much ~]# psql --version
psql (PostgreSQL) 9.6.4
[root@much ~]# 查看一下多安装了哪些命令
代码语言：javascript复制[root@much ~]# rpm -ql postgresql96-9.6.4-1PGDG.rhel7.x86_64 | grep bin
/usr/pgsql-9.6/bin/clusterdb
/usr/pgsql-9.6/bin/createdb
/usr/pgsql-9.6/bin/createlang
/usr/pgsql-9.6/bin/createuser
/usr/pgsql-9.6/bin/dropdb
/usr/pgsql-9.6/bin/droplang
/usr/pgsql-9.6/bin/dropuser
/usr/pgsql-9.6/bin/pg_archivecleanup
/usr/pgsql-9.6/bin/pg_basebackup
/usr/pgsql-9.6/bin/pg_config
/usr/pgsql-9.6/bin/pg_dump
/usr/pgsql-9.6/bin/pg_dumpall
/usr/pgsql-9.6/bin/pg_isready
/usr/pgsql-9.6/bin/pg_receivexlog
/usr/pgsql-9.6/bin/pg_restore
/usr/pgsql-9.6/bin/pg_rewind
/usr/pgsql-9.6/bin/pg_test_fsync
/usr/pgsql-9.6/bin/pg_test_timing
/usr/pgsql-9.6/bin/pg_upgrade
/usr/pgsql-9.6/bin/pg_xlogdump
/usr/pgsql-9.6/bin/pgbench
/usr/pgsql-9.6/bin/psql
/usr/pgsql-9.6/bin/reindexdb
/usr/pgsql-9.6/bin/vacuumdb
/usr/pgsql-9.6/share/man/man3/SPI_getbinval.3
[root@much ~]#安装服务端
代码语言：javascript复制[root@much ~]# yum install postgresql96-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* c7-media:
* extras: mirrors.163.com
* updates: mirrors.sohu.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql96-server.x86_64 0:9.6.4-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql96-server x86_64 9.6.4-1PGDG.rhel7 pgdg96 4.3 M
Transaction Summary
================================================================================
Install 1 Package
Total download size: 4.3 M
Installed size: 18 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql96-server-9.6.4-1PGDG.rhel7.x86_64.rpm | 4.3 MB 00:28
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql96-server-9.6.4-1PGDG.rhel7.x86_64 1/1
Verifying : postgresql96-server-9.6.4-1PGDG.rhel7.x86_64 1/1
Installed:
postgresql96-server.x86_64 0:9.6.4-1PGDG.rhel7
Complete!
[root@much ~]# echo $?
0
[root@much ~]# 查看一下多安装了哪些命令
代码语言：javascript复制[root@much ~]# rpm -qa | grep -i postgresql
postgresql96-9.6.4-1PGDG.rhel7.x86_64
postgresql96-libs-9.6.4-1PGDG.rhel7.x86_64
postgresql96-server-9.6.4-1PGDG.rhel7.x86_64
[root@much ~]# rpm -ql postgresql96-server-9.6.4-1PGDG.rhel7.x86_64 | grep bin
/usr/pgsql-9.6/bin/initdb
/usr/pgsql-9.6/bin/pg_controldata
/usr/pgsql-9.6/bin/pg_ctl
/usr/pgsql-9.6/bin/pg_resetxlog
/usr/pgsql-9.6/bin/postgres
/usr/pgsql-9.6/bin/postgresql96-check-db-dir
/usr/pgsql-9.6/bin/postgresql96-setup
/usr/pgsql-9.6/bin/postmaster
[root@much ~]# 初始化数据库
代码语言：javascript复制[root@much ~]# /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
[root@much ~]# 
