# 1. 编译安装
# 1.1 卸载历史版本
通过如下命令可查看系统中已经安装的历版本信息。
rpm -qa | grep postgresql
1
执行如下命令，删除历史版本记录。
yum remove postgresql*
1
完成后，可以find是否有遗留的安装信息，如果有，可以使用rm删除(小心使用rm)。
# 1.2 下载安装包
点击PostgreSQL (opens new window)下载安装包程序。
# 1.3 配置安装
将下载的程序包上传至/opt目录下，并解压。
cd /opt
tar -zvxf postgresql-10.5.tar.gz
mv postgresql-10.5 pgsql
cd pgsql
1
2
3
4
5
6
7
2
3
4
5
6
7
# 1.3.1 安装依赖
yum -y install -y gcc gcc-c++
yum -y install -y readline-devel
yum -y install -y readline;
yum -y install -y uuid uuid-devel
yum -y install -y perl-ExtUtils-Embed
yum -y install -y openssl openssl-devel
yum -y install -y libxml2 libxml2-devel
yum -y install -y libxslt libxslt-devel
yum -y install -y tcl tcl-devel
yum -y install -y python python-devel
yum -y install -y pam pam-devel
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 1.3.2 配置
cd /opt/pgsql
./configure --prefix=/opt/pgsql --with-pgport=2435 --with-perl --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --with-uuid=ossp --with-blocksize=16 --enable-dtrace --enable-debug
1
2
3
2
3
# 1.3.3 编译&安装
cd /opt/pgsql
gmake world && gmake install-world
1
2
3
2
3
完成后在/opt目录下会出现pgsql安装目录。
# 2. 用户及目录
# 2.1 添加用户组
创建名称为postgres的用户组，并将用户postgres添加到改组。
groupadd postgres
useradd -g postgres postgres
1
2
3
2
3
# 2.2 目录与授权
# 2.2.1 数据目录
- 创建数据目录
mkdir -p /opt/pgsql/data
1
- 数据目录授权 将目录授权给用户组postgres中的postgres用户，赋予写权限。
chown postgres:postgres /opt/pgsql/data -R
1
# 2.2.2 表空间
- 创建表空间目录。
mkdir -p /opt/pgsql/tbs/uav
1
- 读写权限
chmod 755 /opt/pgsql/tbs/uav
1
- 授权用户 授权目录写权限给用户组postgres下的postgres用户。
chown postgres:postgres /opt/pgsql/tbs/uav -R
1
注意：以上操作均使用系统用户root进行操作。
# 3. 用户配置
- 切换用户为postgres
su - postgres
1
- 配置.bash_profile文件
vi ~/.bash_profile
1
在文件末尾加入如下内容:
# 安装目录
export PGHOME=/opt/pgsql
# 数据目录
export PGDATA=/opt/pgsql/data
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
alias rm='rm -i'
alias ll='ls -lh'
# 启动项别名
alias pg_start='pg_ctl start -D $PGDATA'
# 重启服务别名
alias pg_restart='pg_ctl restart -D $PGDATA'
# 关闭项别名
alias pg_stop='pg_ctl stop -D $PGDATA -m fast'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
- 刷新使文件生效
source ~/.bash_profile
1
# 4. 初始话&启动服务
# 4.1 初始化
su postgres
initdb -D /opt/pgsql/data
1
2
3
2
3
# 4.2 启动服务
pg_start
1
# 5. 资源创建
# 5.1 登录数据库
切换系统当前用户为postgres，进入pg命令行。
su postgres
psql
1
2
3
2
3
# 5.2 创建数据库用户
这里创建名为apegeek，密码为apegeek@uav的用户。
create user apegeek password 'apegeek@uav';
1
# 5.3 创建表空间
确保表空间目录已经创建，且postgres用户有写权限。如有疑问可通过3.1.4.4了解创建授权过程。
这里创建了名为tbs_uav的表空间，它的所有者为用户apegeek。
create tablespace tbs_uav owner apegeek location '/opt/pgsql/tbs/uav/';
1
# 5.4 创建数据库
创建名称为uav的数据库，隶属于表空间uav，所有者为apegeek。
create database uav owner apegeek template template1 tablespace uav;
1
# 5.5 用户授权
使用postgres用户登录pg数据库。调整用户apegeek为超级用户。
alter user apegeek with superuser;
1
将数据库uav所有权限授权给用户apegeek。
grant all privileges on database uav to apegeek;
1
退出postgres用户。
\q
# 5.6 配置优化
# 5.6.1 postgresql.conf
- 切换为root用户，编辑postgresql.conf
vi /opt/pgsql/data/postgresql.conf
1
- 修改配置项
- listen_addresses = '*'
- max_connections = 500
- superuser_reserved_connections = 20
# 5.6.2 pg_hba.conf
- 切换为root用户，编辑 pg_hba.conf
vi /opt/pgsql/data/pg_hba.conf
1
- 在尾部追加如下内容
host all all 0.0.0.0/0 md5
1
# 5.7 创建Schema
# 5.7.1 创建
使用已经创建的apegeek用户登录uav数据库。
psql -U apegeek -d uav -h 127.0.0.1 -p 端口
1
create schema if not exists apegeek authorization apegeek;
1
以上步骤对于初学者熟悉PG相关操作有一定帮助，实际部署可使用uav-script模块下的initdb脚本快速实现。
# 5.8.2 修改Search Path
登录目标数据库，执行以下命令：
alter user apegeek set search_path to apegeek;
show search_path;
1
2
3
2
3
# 6. 数据库配置
# 6.1 postgresql.conf
- 切换为root用户，编辑postgresql.conf
vi /opt/pgsql/data/postgresql.conf
1
- 修改配置项
- listen_addresses = '*'
- max_connections = 300
- superuser_reserved_connections = 20
# 6.2 pg_hba.conf
- 切换为root用户，pg_hba.conf
vi /opt/pgsql/data/pg_hba.conf
1
- 在尾部追加
host all all 0.0.0.0/0 md5
# 6.3 重启服务
su postgres
pg_restart
# 7. 基于安装包安装
# 7.1 安装RPM包
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
1
# 7.2 安装PG Server
yum install postgresql10-server
1
# 7.3 数据库初始话
/usr/pgsql-10/bin/postgresql-10-setup initdb
1
# 7.4 设置开机启动
systemctl enable postgresql-10.service
1
# 7.5 重新载入配置
systemctl daemon-reload
1
# 7.6 启动服务
systemctl start postgresql-10.service
1
# 7.7 登录数据库
su postgres
psql
1
2
3
2
3
# 7.8 修改默认用户密码
alter user postgres password 'apegeekuav';
1
# 7.9 修改配置
- 切换为root用户
su root
1
- postgresql.conf
vi /var/lib/pgsql/10/data/postgresql.conf
1
修改配置项：
- port = 24359
- listen_addresses = '*'
- max_connections = 300
- superuser_reserved_connections = 20
- pg_hba.conf
vi /var/lib/pgsql/10/data/pg_hba.conf
1
在文件末尾加入如下内容：
host all all 0.0.0.0/0 md5
# 7.10 重启服务
systemctl restart postgresql-10.service
1
# 7.11 验证配置
su postgres
psql -p 24359
1
2
3
2
3
# 8. 其他
# 8.1 开启UUID
- 登录数据库
psql -U apegeek -d uav -h 127.0.0.1 -p 5432
- 安装扩展
create extension "uuid-ossp" 通过脚本导入函数 psql -d uav -f /opt/pgsql/share/extension/uuid-ossp--1.1.sql
select uuid_generate_v1();
select uuid_generate_v1mc();
select uuid_generate_v4();
1
2
3
2
3
出现错误 ERROR: function uuid_generate_v4() does not exist SQL 错误 [42883]: ERROR: function uuid_generate_v4() does not exist 建议：No function matches the given
# 8.2 命令
# 8.2.1 查看活动会话
select * from pg_stat_activity where datname = 'apegeek';
1
# 8.2.2 杀死指定进程
select pg_terminate_backend('pid');
1
# 8.2.3 删除用户
drop user apegeek;
1
# 8.2.4 转义字符
针对`符号，需要配置backslash_quote = on
# 8.3 数据库技巧
# 8.3.1 自增主键
<selectKey keyProperty="id" resultType="int" order="BEFORE">
SELECT nextval('t_job_qrtz_trigger_group_id_seq'::regclass) as id
</selectKey>
1
2
3
2
3
# 8.4 PostGis
# 8.4.1 安装工具包
yum install wget net-tools epel-release -y
1
# 8.4.2 安装PostGis
yum install postgis25_10 postgis25_10-client -y
1
# 8.4.3 安装扩展工具
yum install ogr_fdw10 -y
1
# 8.4.4 在数据库中启用PostGis
psql -d postgres -c "CREATE EXTENSION postgis;"
psql -d postgres -c "CREATE EXTENSION postgis_topology;"
psql -d postgres -c "CREATE EXTENSION postgis_sfcgal;"
psql -d postgres -c "CREATE EXTENSION fuzzystrmatch;"
psql -d postgres -c "CREATE EXTENSION postgis_tiger_geocoder;"
psql -d yourdatabase -c "CREATE EXTENSION address_standardizer;"
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 8.5 监控模块
pg_stat_statements模块提供了pg的监控能力。
# 8.5.1 编译
进入pg源码目录下的contrib目录。
cd /opt/psql/contrib
make
make install
1
2
3
4
5
2
3
4
5
# 8.5.2 安装插件
以postgres用户登录psql客户端。
create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();
1
2
3
2
3
# 8.5.3 配置
vi /opt/pgsql/data/postgresql.conf
1
在顶部加入如下内容后，重启数据服务。
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
1
2
2
# 8.5.4 SQL性能分析
# 8.5.4.1 最耗时的SQL
select * from pg_stat_statements order by total_time desc limit 5;
1
# 8.5.4.2 查询读取Buffer次数最多的SQL
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
1
# 8.5.44.3 查出使用表扫描最多的表
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
1
# 8.5.4.4 查询当前正在运行的访问到上述表的慢查询
select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';
1
# 8.5.5 处理慢SQL
对于上面的方法查出来的慢SQL，首先需要做的可能是Cancel或Kill掉他们，使业务先恢复。
select pg_cancel_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
select pg_terminate_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
1
2
3
2
3