PostgreSQL

一、 部署

源码部署

• 1.1 安装依赖

  yum -y groupinstall "Development Tools"
yum -y install readline-devel zlib-devel libicu-devel bison flex

• 1.2 下载源码

  wget https://ftp.postgresql.org/pub/source/v16.4/postgresql-16.4.tar.gz

• 1.3 编译安装

  tar -xvf postgresql-16.4.tar.gz
cd postgresql-16.4
./configure --prefix=/usr/local/pgsql-16.4
make && make install

• 1.4 创建用户/组

  groupadd postgres
useradd -m -g postgres postgres
chown -R postgres:postgres /usr/local/pgsql-16.4/

• 1.5 配置环境变量

  切换用户： su -l postgres
  编辑文件： vim ~/.bash_profile
  添加如下内容：

  PGPORT=5432
  PGHOME=/usr/local/pgsql-16.4
  PGDATA=/usr/local/pgsql-16.4/data
  LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
  PATH=$PGHOME/bin:$PATH
  export PGPORT PGHOME PGDATA LD_LIBRARY_PATH PATH

• 1.6 初始化数据库

  su -l postgres
mkdir -p /usr/local/pgsql-16.4/data
/usr/local/pgsql-16.4/bin/initdb -D /usr/local/pgsql-16.4/data

• 1.7 启动数据库

  /usr/local/pgsql-16.4/bin/pg_ctl -D /usr/local/pgsql-16.4/data -l logfile start

• 1.8 创建用户和DB

  进入pgsql终端： /usr/local/pgsql-16.4/bin/psql -d postgres
  创建数据库：

  CREATE USER $username WITH PASSWORD '$password';
  CREATE DATABASE $dbname OWNER $username;

  

• 1.9 配置远程访问

  编辑： vim /usr/local/pgsql-16.4/data/pg_hba.conf
  ipv4 行添加如下： host all all 0.0.0.0/0 md5
  编辑： vim /usr/local/pgsql-16.4/data/postgres.conf
  添加： listen_addresses = '*'

• 1.10 使用systemd管理

  # /usr/local/pgsql/postgresql-16.service
  [Unit]
  Description=PostgreSQL-16.4
  After=network.target
  [Service]
  Type=forking
  User=postgres
  Group=postgres
  Environment=PGPORT=2345
  Environment=PGDATA=/usr/local/pgsql/data/
  OOMScoreAdjust=-1000
  ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
  ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
  ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
  TimeoutSec=300
  [Install]
  WantedBy=multi-user.target

  systemctl daemon-reload
systemctl enable postgresql-16 --now

二、 插件安装

2.1 内置扩展

扩展名称	用途
pg_stat_statements	收集并统计所有 SQL 查询的执行信息，帮助调优查询性能。
hstore	提供键值对存储功能，适合处理半结构化数据。
citext	提供不区分大小写的文本字段类型，适用于忽略大小写的字符串比较操作。
uuid-ossp	生成 UUID，适用于分布式系统中的唯一标识符生成。
pg_trgm	提供基于 trigram 的字符串相似度搜索功能，适用于模糊搜索。
fuzzystrmatch	提供字符串模糊匹配功能，如 Soundex 算法，适用于名字搜索等场景。
intarray	为整型数组提供额外的操作符和函数支持，适合处理数组类型的数据。
cube	提供对多维数据的支持，适用于几何计算和多维数据处理。
tablefunc	提供交叉表功能，适用于生成报表或统计结果时的行转列操作。
ltree	提供对树状结构数据的支持，适用于管理和查询层级结构的数据。
pgcrypto	提供数据加密和解密功能，适用于存储敏感数据。
plpgsql	提供编写存储过程和函数的内置过程语言，适用于复杂业务逻辑的实现。

内置扩展直接在源码目录下的 contribu 中对应的插件目录执行 make && make install .以 pg_stat_statements 为例:

cd postgresql-16.4/contrib/pg_stat_statements/
make && make install

psql命令行下：

create extension pg_statements;

2.2 非内置扩展

以安装 mysql_fdw 为例

• 依赖安装:

  yum install mariadb-devel

• 源码下载

  wget -O https://github.com/EnterpriseDB/mysql_fdw/archive/refs/tags/REL-2_9_2.tar.gz

• 导入pgsql和mysql的bin目录

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

• 编译安装

  tar -xzvf mysql_fdw-REL-2_9_2.tar.gz && cd mysql_fdw-REL-2_9_2
make USE_PGXS=1
make USE_PGXS=1 install

• 创建扩展

  postgres=# 
  postgres=# create extension mysql_fdw;
  ERROR:  failed to load the mysql query: 
  libmysqlclient.so: cannot open shared object file: No such file or directory
  HINT:  Export LD_LIBRARY_PATH to locate the library.
  postgres=# 

• 解决依赖问题

  将缺少的文件复制到 /usr/local/pgsql/lib 即可

  

2.3 特殊插件

uuid-ossp 作为内置插件， 该插件的安装比较特殊
uuid-ossp 依赖 uuid-devel， 所以先安装这个包 yum install uuid-devel

• 重新编译pgsql，在原来的编译选项中加上 --with-uuid-ossp

  ./configure --prefix=/usr/local/pgsql-16.4 --with-uuid-ossp

• 编译插件
  cd $PGHOME/contrib/uuid-ossp && make && make install

2.4 postGIS 插件安装

安装之前， 在这里查看 PostgreSQL和 PostGIS 的兼容性
https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

Build List：

• PostgreSQL 12.21
• postGIS 3.1.10
• geos 3.6.0

Build geos-3.6.0

# build from source
tar -xvf geos-3.6.0.tar.bz2 && cd geos-3.6.0
./configure --prefix=/usr/local/geos-3.6.0
make && make install

# add ldd_path
echo "/usr/local/geos-3.6.0/lib" > /etc/ld.so.conf.d/geos.conf
ldconfig

Build PostGIS-3.1.10

• Installation

  # Requirement 
  yum install proj proj-devel
  # build from source
  tar -zxvf postgis-3.1.10.tar.gz && cd postgis-3.1.10
  ./configure --with-pgconfig=/usr/local/pgsql-12/bin/pg_config \
  --with-geosconfig=/usr/local/geos-3.6.0/bin/geos-config \
  --without-raster --without-protobuf

• Enable on PostgreSQL

  /usr/local/pgsql-12/bin/psql -U postgres -d postgres -p 5432

  psql (12.21)
  Type "help" for help.
  
  postgres=# CREATE EXTENSION postgis;
  CREATE EXTENSION
  postgres=# 
  postgres=# 
  postgres=# \dx
                                  List of installed extensions
    Name   | Version |   Schema   |                        Description                         
  ---------+---------+------------+------------------------------------------------------------
   plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
   postgis | 3.1.10  | public     | PostGIS geometry and geography spatial types and functions
  (2 rows)