PostgreSQL - 1. PostgreSQL-安装和基本
2020-03-06 本文已影响0人
找呀找提莫
目录
[toc]
1 PostgreSQL简介
1.1 概述
PostgreSQL数据库是目前功能最强大的开源数据库，支持丰富的数据类型（如JSON和JSONB类型，数组类型）和自定义类型。而且它提供了丰富的接口，可以很容易地扩展它的功能，如可以在GiST框架下实现自己的索引类型等，它还支持使用C语言写自定义函数、触发器，也支持使用流行的语言写自定义函数、比如其中的PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python，PL/Tcl，等等。
1.2 PostgreSQL优势
- PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，并且正在实现新的功能以兼容最新的SQL标准：SQL2003。
- 稳定可靠：PostgreSQL是唯一能够做到数据零丢失的开源数据库。有报道称国外部分银行也在使用PostgreSQL数据库。
- 开源省钱：PostgreSQL数据库是开源的、免费的，而且是BSD协议，在使用和二次开发上基本没有限制。
- 支持广泛：PostgreSQL数据库支持大量的主流开发语言，包括C、C++、Perl、Python、Java、Tcl，以及PHP等。
- PostgreSQL社区活跃：PostgreSQL基本上每三个月推出一个补丁版本，这意味着已知的BUG很快会被修复，有应用场景的需求也会及时得到响应。
2 PostgreSQL安装
2.1 yum源中包含的PostgreSQL包
[root@localhost ~]# yum list | grep postgresql
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
freeradius-postgresql.x86_64 3.0.4-6.el7 local
libreoffice-postgresql.x86_64 1:5.0.6.2-3.el7 local
pcp-pmda-postgresql.x86_64 3.11.3-4.el7 local
postgresql.i686 9.2.18-1.el7 local
postgresql.x86_64 9.2.18-1.el7 local
postgresql-contrib.x86_64 9.2.18-1.el7 local
postgresql-devel.i686 9.2.18-1.el7 local
postgresql-devel.x86_64 9.2.18-1.el7 local
postgresql-docs.x86_64 9.2.18-1.el7 local
postgresql-jdbc.noarch 9.2.1002-5.el7 local
postgresql-jdbc-javadoc.noarch 9.2.1002-5.el7 local
postgresql-libs.i686 9.2.18-1.el7 local
postgresql-libs.x86_64 9.2.18-1.el7 local
postgresql-odbc.x86_64 09.03.0100-2.el7 local
postgresql-plperl.x86_64 9.2.18-1.el7 local
postgresql-plpython.x86_64 9.2.18-1.el7 local
postgresql-pltcl.x86_64 9.2.18-1.el7 local
postgresql-server.x86_64 9.2.18-1.el7 local
postgresql-test.x86_64 9.2.18-1.el7 local
postgresql-upgrade.x86_64 9.2.18-1.el7 local
qt-postgresql.i686 1:4.8.5-13.el7 local
qt-postgresql.x86_64 1:4.8.5-13.el7 local
qt5-qtbase-postgresql.i686 5.6.1-10.el7 local
qt5-qtbase-postgresql.x86_64 5.6.1-10.el7 local
2.2 rpm包安装PostgreSQL
[root@localhost ~]# yum install -y postgresql-server postgresql postgresql-libs
server端：postgresql-server
client端：postgresql
依赖包： postgresql-libs
- 安装完成后不能直接启动数据库，需要先手动初始化数据库：
[root@localhost ~]# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK
- 再启动数据库：
[root@localhost ~]# systemctl start postgresql
[root@localhost ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: active (running) since 一 2018-02-19 22:02:57 CST; 3min 37s ago
Process: 1286 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 1281 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1290 (postgres)
CGroup: /system.slice/postgresql.service
├─1290 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─1291 postgres: logger process
├─1293 postgres: checkpointer process
├─1294 postgres: writer process
├─1295 postgres: wal writer process
├─1296 postgres: autovacuum launcher process
├─1297 postgres: stats collector process
└─1391 postgres: postgres postgres [local] idle
2月 19 22:02:56 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
2月 19 22:02:57 localhost.localdomain systemd[1]: Started PostgreSQL database server.
- 切换到操作系统下的“postgres”用户，登陆数据库：
[root@localhost ~]# su - postgres
-bash-4.2$ psql
psql (9.2.18)
输入 "help" 来获取帮助信息.
postgres=# \l
资料库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)
postgres=#
- rpm包安装的PostgreSQL的数据目录，默认在/var/lib/pgsql/data
-bash-4.2$ ls -l /var/lib/pgsql/data/
总用量 48
drwx------ 5 postgres postgres 41 2月 19 22:01 base
drwx------ 2 postgres postgres 4096 2月 19 22:02 global
drwx------ 2 postgres postgres 18 2月 19 22:01 pg_clog
-rw------- 1 postgres postgres 4232 2月 19 22:01 pg_hba.conf
-rw------- 1 postgres postgres 1636 2月 19 22:01 pg_ident.conf
drwx------ 2 postgres postgres 32 2月 19 22:02 pg_log
drwx------ 4 postgres postgres 36 2月 19 22:01 pg_multixact
drwx------ 2 postgres postgres 18 2月 19 22:02 pg_notify
drwx------ 2 postgres postgres 6 2月 19 22:01 pg_serial
drwx------ 2 postgres postgres 6 2月 19 22:01 pg_snapshots
drwx------ 2 postgres postgres 25 2月 19 22:08 pg_stat_tmp
drwx------ 2 postgres postgres 18 2月 19 22:01 pg_subtrans
drwx------ 2 postgres postgres 6 2月 19 22:01 pg_tblspc
drwx------ 2 postgres postgres 6 2月 19 22:01 pg_twophase
-rw------- 1 postgres postgres 4 2月 19 22:01 PG_VERSION
drwx------ 3 postgres postgres 60 2月 19 22:01 pg_xlog
-rw------- 1 postgres postgres 19816 2月 19 22:01 postgresql.conf
-rw------- 1 postgres postgres 57 2月 19 22:02 postmaster.opts
-rw------- 1 postgres postgres 91 2月 19 22:02 postmaster.pid
rpm包的postgresql10安装、初始化数据库以及启动方法(centos-7)：
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
yum install postgresql10
yum install postgresql10-server
/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl enable postgresql-10
systemctl start postgresql-10
2.3 源码包安装PostgreSQL
- 下载源代码
[root@localhost ~]# wget https://ftp.postgresql.org/pub/source/v10.2/postgresql-10.2.tar.gz
- 安装依赖包
[root@localhost ~]# yum install -y zlib-devel readline-devel gcc
- 编译安装PostgreSQL
[root@localhost ~]# tar -zxvf postgresql-10.2.tar.gz
[root@localhost ~]# cd postgresql-10.2
[root@localhost postgresql-10.2]# ./configure --prefix=/usr/local/pgsql10.2 --with-python --with-perl
[root@localhost postgresql-10.2]# make && make install
[root@localhost postgresql-10.2]# cd /usr/local/
[root@localhost local]# ln -s pgsql10.2 pgsql
备注：
在PostgreSQL8.X中，编译命令里需要有“--enable-thread-safety”选项，而在PostgreSQL9.X以后的版本中不需要这个选项。
因为在日常使用中，一般要求客户端是线程安全的，PostgreSQL9.X以后的版本中考虑到这个问题，默认线程是安全的了。
--with-perl：加上这个选项，才能使用perl语言的PL/Perl过程语言写自定义函数，一般都需要。要使用这个选项需要先安装perl-ExtUtils-Embed和perl-devel。
--with-python：加上这个选项，才能使用perl语言的PL/Python过程语言写自定义函数，一般都需要。要使用这个选项需要先安装python-devel。
按照官方文档要求，使用make命令时，make的版本要在gmake3.8以上，目前大多数Linux发行版都满足要求。（检查方法：make --version）
不指定--prefix选项，默认路径将是/usr/local
异常处理：
编译时增加 --with-python
configure: error: header file <Python.h> is required for Python
解决方法：
yum install python-devel
编译时增加 --with-perl
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
解决方法：
yum install perl-ExtUtils-Embed
- 配置PostgreSQL环境变量
[root@localhost local]# vim /etc/profile
# PostgreSQL可执行文件路径
export PATH=/usr/local/pgsql/bin:$PATH
# PostgreSQL共享库路径
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
[root@localhost local]# source /etc/profile
- 创建数据库簇
[root@localhost local]# useradd postgres
[root@localhost local]# mkdir -pv /mydata/pgdata
mkdir: 已创建目录 "/mydata"
mkdir: 已创建目录 "/mydata/pgdata"
[root@localhost local]# chown -R postgres.postgres /mydata/pgdata/
[root@localhost local]# su - postgres
[postgres@localhost ~]$ /usr/local/pgsql/bin/initdb -D /mydata/pgdata/
- 启动PostgreSQL
[postgres@localhost pgsql]$ pg_ctl start -D /mydata/pgdata/
waiting for server to start....2018-02-20 00:52:03.616 CST [38915] LOG: listening on IPv6 address "::1", port 5432
2018-02-20 00:52:03.616 CST [38915] LOG: listening on IPv4 address "127.0.0.1", port 5432
2018-02-20 00:52:03.619 CST [38915] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-02-20 00:52:03.689 CST [38916] LOG: database system was shut down at 2018-02-20 00:44:56 CST
2018-02-20 00:52:03.692 CST [38915] LOG: database system is ready to accept connections
done
server started
- 停止PostgreSQL
[postgres@localhost pgsql]$ pg_ctl stop -D /mydata/pgdata/
备注：
可以在命令后增加 [-m SHUTDOWN-MODE]，用来控制数据库的停止方法；SHUTDOWN-MODE有以下三种：
smart：等所有的连接终止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库。
fast (PostgreSQL10.X默认)：快速关闭数据库，断开客户端的连接，让已有的事物回滚，然后正常关闭数据库。
相当于Oracle数据库关闭时的immediate(adj. 立即的)模式。
immediate：不完整的关闭数据库，相当于kill数据库进程，下次启动数据库需要进行恢复。相当于Oracle数据库关闭时的abort模式。
其中，比较常用的是fast模式。
- PostgreSQL启动脚本
要使用service命令启动PostgreSQL，需要把PostgreSQL的启动脚本拷贝到 /etc/init.d/ 下，并增加执行权限
[root@localhost start-scripts]# pwd
/root/postgresql-10.2/contrib/start-scripts
[root@localhost start-scripts]# ls
freebsd linux macos osx
[root@localhost start-scripts]# cp linux /etc/init.d/postgresql
[root@localhost ~]# cd /etc/init.d
[root@localhost init.d]# chmod 755 postgresql
需要修改启动脚本中的几个参数：
[root@localhost init.d]# vim postgresql
prefix=/usr/local/pgsql # 确保可以通过这个目录访问到源码安装的PostgereSQL
PGDATA="/mydata/pgdata" # 指向第5步创建的数据库簇
备注：
在centos7中，源码安装的PostgreSQL没有提供systemctl需要的相关脚本，可以继续使用service和chkconfig去管理。
2.4 安装crontrib目录下的工具
crontrib下面有一些工具比较实用，可以装上，方法如下：
[root@localhost postgresql-10.2]# pwd
/root/postgresql-10.2
[root@localhost postgresql-10.2]# cd contrib/
[root@localhost contrib]# make && make install
安装后 /usr/local/pgsql/bin 会增加三个命令：oid2name pg_standby vacuumlo
2.5 使用较大的数据块提高I/O性能
如果希望使用较大的数据块提高I/O性能，只能通过源码编译安装解决解决，在执行 ./configure 命令时指定较大数据块，同时也需要指定较大的WAL日志块和WAL日志文件的大小。
如果想指定32KB数据块、32KB的WAL日志块、64MB的WAL日志文件，./configure 的命令如下：
./configure --prefix=/usr/local/pgsql10.2 --with-python --with-perl --with-blocksize=32 --with-wal-blocksize=32 --with-wal-segsize=64
备注：
--with-blocksize 的范围在1-32KB，默认8KB
--with-wal-blocksize 的范围在1-64KB，默认8KB
--with-wal-segsize 的范围在1-1024MB，默认16MB
注意：
修改数据块后的PostgreSQL创建的PostgreSQL数据库，不能使用其它块大小的PostgreSQL程序启动。
3 PostgreSQL配置
3.1 PostgreSQL通用配置 - postgresql.conf
3.1.1 修改PostgreSQL监听IP和端口
监听IP和端口需要通过数据目录下的postgresql.conf文件修改：
[root@localhost pgdata]# pwd
/mydata/pgdata
[root@localhost pgdata]# vim postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
监听IP使用默认的localhost时，只能通过127.0.0.1访问数据库；
如果需要通过其他网络远程访问PostgreSQL，可以使用“,”作为分隔符，把IP地址添加到listen_addresses后，或者使用“*”，让所有IP都可以访问数据库。
3.1.2 配置数据库log
- 日志开关以及保存目录名
logging_collector = on # 日志的开关，默认是off，不会收集日志
log_directory = 'log' # 日志的保存目录名，默认是在数据目录下的log目录里，使用默认值即可，不需要修改
- 日志的轮转配置
可以使用以下几种方案：
- 每天生成一个新的日志
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #日志文件名
log_truncate_on_rotation = off # 同名日志覆盖开关
log_rotation_age = 1d # 按时间轮转日志
log_rotation_size = 0 # 按日志大小轮转日志
- 每当日志写满一定大小（如10MB），进行一次日志轮转
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 10M
- 只保留7天的日志，进行循环覆盖
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
说明：
%a 代表星期，例如：在星期二时，就会生成postgresql-Tue.log这个日志；
每当遇到同名的日志时，会对旧的日志文件进行覆盖，而不是在旧的日志文件上进行追加。
3.1.3 内存参数配置
PostgreSQL安装完成后，可以主要修改以下两个主要内存参数：
- shared_buffer：共享内存的大小，主要用于共享数据块，默认是128MB；
如果服务器内存有富余，可以把这个参数适当改大一些，这样数据库就可以缓存更多的数据块，当读取数据时，就可以从共享内存中读取，而不需要去文件读取。 - work_mem：单个SQL执行时，排序、hash join所使用的内存，SQL运行完成后，内存就释放了，默认是4MB；
增加这个参数，可以提高排序操作的速度。
注意
-
postgresql.auto.conf
对于9.4版本以后的PostgreSQL，系统会生成一个postgresql.auto.conf，如过在postgresql.auto.conf和postgresql.conf都做了相同的配置，那么PostgreSQL会优先使用postgresql.auto.conf中的配置。 -
修改postgresql.conf导致服务崩溃
遇到这种情况，可以通过查看位于PostgreSQl数据目录(/mydata/pgdata)下的日志文件来查找故障原因。
3.2 PostgreSQL访问配置 - pg_hba.conf
pg_hba.conf文件指定了允许哪些用户以何种方式连接到PostgreSQL数据库，对于这个文件的修改可以动态生效。
以IPv4的连接验证配置来说：
# IPv4 local connections:
host all all 127.0.0.1/32 trust
-
host：连接类型，可以设置为local（本地连接）、host（远程连接，hostssl或hostnossl）、hostssl(使用ssl加密的远程连接)、hostnossl（不使用ssl加密的远程连接）;日常使用host配置即可。
- 第一个all：数据库，可以设置为all（全部数据库）、sameuser（和登陆用户同名的数据库）、samerole（暂时还不知道是干啥的）、replication（replication connections，貌似是用于主从复制的）、一个数据库名、一个用逗号分隔的列表；
- 第二个all：用户名，可以设置为all（任何用户）、一个用户名、+用户组（用+开头的用户组名）、一个用逗号分隔的列表、@文件（用@开头的文件，文件内容是用户名）；
- 127.0.0.1/32：允许连接的主机IP/掩码
- trust：身份验证模式，可以设置为ident（从pg_ident.conf中读取用户映射）、trust（最不安全的免密登陆）、md5（还不知道该咋用呢）、password（明文密码）；
3.3 PostgreSQL用户映射配置 - pg_ident.conf
如果在身份验证模式时，配置为ident方式，则当用户连接时，系统会尝试访问pg_ident.conf，如果该文件存在，则系统会基于文件内容将当前执行登陆的操作系统用户映射为一个PostgreSQL的内部用户身份来登陆。
查看当前登陆用户：
postgres=# select user;
user
----------
postgres
(1 row)
