postgreSQL修炼之道，从小工到专家 读书笔记 （一） ----postgresl的两种安装方式与简单配置

PostgreSQL的优势

. 是目前功能最强大的开源数据库

. 稳定可靠

. 开源免费

. 支持广泛

. 社区活跃

centOS7下PostgreSQL的安装和配置

yum安装postgresql

官方网站的教程：https://www.postgresql.org/download/linux/redhat/

可以在网页上根据提示选择版本和操作系统，然后进行安装：

##安装RPM存储库
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

##安装postgresql11客户端软件包
yum install postgresql11

##安装服务器软件包
yum install postgresql11-server

##初始化数据库并启用自动启动
/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11
systemctl start postgresql-11


##还可以安装第三方贡献的软件包
yum install postgresql-contrib.x86_64 

操作完以后，就已经安装好了postgresql11了，切换到 postgres 用户下，登陆数据库：

[root@localhost ~]# ps -ef | grep postgres
postgres 29268     1  0 14:17 ?        00:00:00 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
postgres 29270 29268  0 14:17 ?        00:00:00 postgres: logger   
postgres 29272 29268  0 14:17 ?        00:00:00 postgres: checkpointer   
postgres 29273 29268  0 14:17 ?        00:00:00 postgres: background writer   
postgres 29274 29268  0 14:17 ?        00:00:00 postgres: walwriter   
postgres 29275 29268  0 14:17 ?        00:00:00 postgres: autovacuum launcher   
postgres 29276 29268  0 14:17 ?        00:00:00 postgres: stats collector   
postgres 29277 29268  0 14:17 ?        00:00:00 postgres: logical replication launcher   
root     29279  7502  0 14:17 pts/0    00:00:00 grep --color=auto postgres
[root@localhost ~]# su - postgres
-bash-4.2$ psql
psql (11.5)
输入 "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=# 

在CentOS下，默认安装的postgresql的数据目录在 /var/lib/pgsql/11/data 目录下：

[root@localhost data]# ls -l /var/lib/pgsql/11/data
总用量 60
drwx------. 5 postgres postgres    41 11月 14 14:17 base
-rw-------. 1 postgres postgres    30 11月 14 14:17 current_logfiles
drwx------. 2 postgres postgres  4096 11月 14 14:17 global
drwx------. 2 postgres postgres    32 11月 14 14:17 log
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_commit_ts
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_dynshmem
-rw-------. 1 postgres postgres  4269 11月 14 14:17 pg_hba.conf
-rw-------. 1 postgres postgres  1636 11月 14 14:17 pg_ident.conf
drwx------. 4 postgres postgres    68 11月 14 14:17 pg_logical
drwx------. 4 postgres postgres    36 11月 14 14:17 pg_multixact
drwx------. 2 postgres postgres    18 11月 14 14:17 pg_notify
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_replslot
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_serial
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_snapshots
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_stat
drwx------. 2 postgres postgres    63 11月 14 14:20 pg_stat_tmp
drwx------. 2 postgres postgres    18 11月 14 14:17 pg_subtrans
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_tblspc
drwx------. 2 postgres postgres     6 11月 14 14:17 pg_twophase
-rw-------. 1 postgres postgres     3 11月 14 14:17 PG_VERSION
drwx------. 3 postgres postgres    60 11月 14 14:17 pg_wal
drwx------. 2 postgres postgres    18 11月 14 14:17 pg_xact
-rw-------. 1 postgres postgres    88 11月 14 14:17 postgresql.auto.conf
-rw-------. 1 postgres postgres 23892 11月 14 14:17 postgresql.conf
-rw-------. 1 postgres postgres    58 11月 14 14:17 postmaster.opts
-rw-------. 1 postgres postgres   104 11月 14 14:17 postmaster.pid

到此，yum安装postgresql就已经安装完成了，还需要修改一下配置文件，现在的数据库配置，是无法让用户连入的，可以尝试连接一下，可以收到如下报错：

解决办法：修改数据目录下的postgres.conf文件（默认安装的话，是在/var/lib/pgsql/11/data/目录下）,修改两个字段：

listen_addresses = '*'        
port = 5432 

其中，listen_addresses简单的配置成了"*"，表示监听本地的所有地址；port端口默认是5432，如果本机的5432端口被占用，也可以修改为其他端口

然后重启postgresql服务即可生效（还记得怎么重启和关闭吗，systemctl start postgresql-11 就是启动命令了，那么关闭和重启命令，就类比一下吧）

重启以后，再次尝试连接这台服务器，又看到了如下报错

其中，8.144是本机的ip地址，从报错中可以看到，我们还需要修改pg_hba.conf 文件，来允许本地机器访问这个数据库，pg_hba.conf文件与postgres.conf文件在同一目录下，在pg_hba.conf文件中添加行：

host    all             all              0.0.0.0/0               md5

这句话表示，只要有正确的用户名和密码，所有ip都可以登陆数据库。现在再次测试就能发现已经可以连入到数据库啦

附修改postgres用户密码操作：

首先进入到postgres用户下： su - postgres

然后进入到psql工具中： psql

输入命令：alter user postgres with password 'passwd' ;

注意两个点，一是单引号，单引号里的就是你想要修改的密码；二是分号，句末要有分号，回车后才能生效

[root@localhost ~]# su - postgres
上一次登录：二 11月 19 16:23:58 CST 2019pts/0 上
[postgres@localhost ~]$ psql
psql (11.5)
Type "help" for help.

postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=# \q
[postgres@localhost ~]$ 

我就成功的把我的postgres用户的密码改为postgres啦 

yum安装pg操作很简单，但很多配置都是默认的，不够灵活，除了yum安装，还可以用源码安装postgresql

源码安装postgresql

11.5版本下载地址为：https://ftp.postgresql.org/pub/source/v11.5/postgresql-11.5.tar.gz

下载好压缩包以后，首先解压缩，然后编译和安装

##环境和依赖软件安装
yum install gcc-c++ -y
yum install zlib zlib-devel -y
yum install openssl openssl-devel
yum install readline readline-devel -y
yum install pam pam-devel -y
yum install perl perl-ExtUtils-Embed -y
yum install libxml2 libxml2-devel -y
yum install libxslt libxslt-devel -y
yum install tcl tcl-devel -y
yum install openldap openldap-devel -y
yum install python python-devel -y
yum install docbook-dtds docbook-style-xsl fop libxslt -y

##解压
tar -zxvf postgresql-11.5.tar.gz 

##进入解压的目录
cd postgresql-11.5/

##configure
./configure --prefix=/usr/local/pgsql11.5 --with-perl --with-python

##make
make

##make install
make install

##查看安装目录
[root@localhost ~]# ls -l /usr/local/pgsql11.5/
总用量 16
drwxr-xr-x. 2 root root 4096 11月 14 15:12 bin
drwxr-xr-x. 6 root root 4096 11月 14 15:12 include
drwxr-xr-x. 4 root root 4096 11月 14 15:12 lib
drwxr-xr-x. 6 root root 4096 11月 14 15:12 share


##为/usr/local/pgsql11.5 建立一个 /usr/local/pgsql 的链接
[root@localhost ]# cd /usr/local/
[root@localhost local]# ln -sf /usr/local/pgsql11.5 /usr/local/pgsql 
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  pgsql  pgsql11.5  sbin  share  src

这里把 --prefix 路径设置为 /usr/local/pgsql11.5/ ，如果没有指定路径，那么默认路径为 /usr/local，如果之后要升级，将现有数据库停掉，然后将链接 /usr/local/pgsql 指向编译好的新版本的pgsql 就可以了。

安装后的配置：

添加postgres用户，创建pgsql的data目录和log目录，并更改权限：

useradd postgres #添加用户
passwd postgres #设置密码
mkdir -p /data/pgsql/data
mkdir -p /data/pgsql/log
chown -R postgres:postgres /data/pgsql

设置postgresql可执行文件的路径

 export PATH=/usr/local/pgsql/bin/:$PATH 

设置共享库的路径

export LD_LIBRARY_PATH=/usr/local/pgsql/lib 

若想这个配置对所有用户生效，可以把以上内容添加到 /etc/profile中：

vim /etc/profile

##在文件末尾添加以下两行：
export PATH=/usr/local/pgsql/bin/:$PATH 
export LD_LIBRARY_PATH=/usr/local/pgsql/lib 

#保存退出，然后重新加载环境变量
source /etc/profile

创建数据库簇

设定数据库中数据目录的环境变量：

export PGDATA=/data/pgsql/data

将这一行也添加到/etc/profile中，方法同上面的添加方法

执行创建数据库簇命令：

su - postgres

initdb

安装contrib目录下的工具

contrib目录下的工具有很多都比较实用，一般都会安装上，安装方法为：

cd /root/postgresql-11.5/contrib/
make
make install

启动和停止数据库

##启动数据库
su - postgres
pg_ctl start -D $PGDATA

waiting for server to start....2019-11-14 16:26:44.611 CST [28169] LOG:  listening on IPv6 address "::1", port 5432
2019-11-14 16:26:44.612 CST [28169] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-11-14 16:26:44.613 CST [28169] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-11-14 16:26:44.622 CST [28170] LOG:  database system was shut down at 2019-11-14 16:20:54 CST
2019-11-14 16:26:44.624 CST [28169] LOG:  database system is ready to accept connections
 done
server started


##停止数据库
pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]

停止数据库中的 -m 选项是指数据库的三种停止方法：

smart：等所有连接终止后，关闭数据库，如果客户端连接不终止，那么就一直无法关闭数据库

fast：快速关闭数据库，断开数据库连接，让已有事务回滚，然后正常关闭数据库

immediate：立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要进行恢复

其中，比较常用关闭数据库的方法是 fast 方法，即 pg_ctl stop -D $PGDATA -m fast

另外

在数据仓库中使用postgresql时，如果希望使用较大的数据块提高I/O性能，那只能采用源码安装的方法，在执行 ./configure 命令时，指定较大的数据块，一般也需要指定较大的WAL日志块和WAL日志文件的大小。

例如，想指定128KB的数据库、128KB的WAL日志块、64MB的WAL日志文件，那么configure命令如下：

./configure --prefix=/usr/local/pgsql11.5 --with-perl --with-python --with-blocksize=128 --with-wal-blocksize=128 --with-wal-segsize=64

Postgresql的简单配置

修改监听的IP和端口

在数据目录下的编辑postgresql.conf文件

listen_address 表示监听的ip地址，默认为“localhost”，如果想从其他的机器上也登陆这个数据库，可以直接吧监听的ip地址改为“*”

listen_addresses = “*”

port = 5432   

修改了参数以后，需要重启数据库才能生效

修改pg_hba.conf就不再赘述

与数据库log相关参数

打开日志收集设置

logging_collector = on

日志目录一般使用默认值即可

log_directory = 'log'

关于日志的切换和是否选择覆盖有几种方案：

方案一：每天生成一个新的日志文件

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off  
log_rotation_age = 1d
log_rotation_size = 0

方案二：当日志写满一定的大小，则切换一个日志

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

内存参数的设置

postgresql安装完毕以后，可以修改一下两个主要内存参数

shared_buffers   --------共享内存的大小，主要用于共享数据块

work_mem        --------单个SQL执行时，排序、hash join 所使用的内存，SQL运行完以后，内存就释放了

shared_buffers 默认值为 128MB，work_mem 为4MB，如果机器内存足够大，可以调大shared_buffers的值，这样数据库就能缓存更多的数据块，读取数据时，可以从共享内存中读，而不需要再从文件上去读取，通常设置为实际RAM的10％是合理的

work_mem 设置的大一些，会让排序操作快一些，通常设置为实际RAM的2%-4%，根据需要排序结果集的大小而定

effective_cache_size 是postgresql能够使用的最大缓存，这个数字对于独立的pgsql服务器而言应该足够大，比如4G的内存，可以设置为3.5G

max_connections 通常，max_connections的目的是防止max_connections *work_mem超出了实际内存大小。比如，如果将work_mem设置为实际内存的2%大小，则在极端情况下，如果有50个查询都有排序要求，而且都使用2％的内存，则会导致swap的产生，系统性能就会大大降低