基于源代码安装

# 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

# 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

# 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

# 1.3.3 编译&安装

cd /opt/pgsql

gmake world && gmake install-world
1
2
3

编译安装

完成后在/opt目录下会出现pgsql安装目录。


# 2. 用户及目录

# 2.1 添加用户组

创建名称为postgres的用户组,并将用户postgres添加到改组。

groupadd postgres 

useradd -g postgres postgres
1
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. 用户配置

  1. 切换用户为postgres
su - postgres
1
  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
  1. 刷新使文件生效
source ~/.bash_profile
1

# 4. 初始话&启动服务

# 4.1 初始化

su postgres

initdb -D /opt/pgsql/data
1
2
3

# 4.2 启动服务

pg_start
1

# 5. 资源创建

# 5.1 登录数据库

切换系统当前用户为postgres,进入pg命令行。

su postgres

psql
1
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

# 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

# 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

修改配置项:

  1. port = 24359
  2. listen_addresses = '*'
  3. max_connections = 300
  4. 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

# 8. 其他

# 8.1 开启UUID

  1. 登录数据库

psql -U apegeek -d uav -h 127.0.0.1 -p 5432

  1. 安装扩展

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

出现错误 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

# 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

# 8.5 监控模块

pg_stat_statements模块提供了pg的监控能力。

# 8.5.1 编译

进入pg源码目录下的contrib目录。

cd /opt/psql/contrib

make

make install
1
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

# 8.5.3 配置

vi /opt/pgsql/data/postgresql.conf
1

在顶部加入如下内容后,重启数据服务。

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
1
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
更新时间: 12/13/2022, 7:50:55 PM