PostgreSQL学习之部署与简单使用

转载说明：如果您喜欢这篇文章并打算转载它，请私信作者取得授权。感谢您喜爱本文，请文明转载，谢谢。

---

PostgreSQL是一个开源的、功能强大的对象关系型数据库系统。它的起源可以追溯到 1986 年。PostgreSQL可以在所有主要操作系统上运行。

PostgreSQL的优点

1）历史悠久，架构久经考验；

2）免费、开源且社区较为活跃；

3）具有高度扩展性，可以自定义数据类型、构建自定义函数等，且无需重新编译数据库；

4）从 2021 年 9 月发布的第 14 版开始，PostgreSQL 至少符合 SQL:2016 Core 一致性的 179 个强制性特性中的 170 个；

PostgreSQL的功能

以下是 PostgreSQL 中各种功能的详尽列表，每个主要版本中都会添加更多功能：

• 数据类型

• • 基元：整数、数字、字符串、布尔值
  • 结构化：日期/时间、数组、范围/多范围、UUID
  • 文档：JSON/JSONB、XML、键值（Hstore）
  • 几何：点、线、圆、多边形
  • 自定义：复合，自定义类型

• 数据的完整性

• • 唯一，不为空
  • 主键
  • 外键
  • 排除约束
  • 显式锁、咨询锁

• 并发、性能

• • 索引：B-tree、多列、表达式、部分
  • 高级索引：GiST、SP-Gist、KNN Gist、GIN、BRIN、覆盖索引、布隆过滤器
  • 复杂的查询计划器/优化器、仅索引扫描、多列统计信息
  • 事务、嵌套事务（通过保存点）
  • 多版本并发控制 (MVCC)
  • 读取查询的并行化和构建 B-tree 索引
  • 表分区
  • SQL 标准中定义的所有事务隔离级别，包括 Serializable
  • 表达式的即时 (JIT) 编译

• 可靠性，灾难恢复

• • 预写式日志记录 (WAL)
  • 复制：异步、同步、逻辑
  • 时间点恢复 (PITR)，活动备用
  • 表空间

• 安全

• • 身份验证：GSSAPI、SSPI、LDAP、SCRAM-SHA-256、证书等
  • 强大的门禁系统
  • 列和行级别的安全性
  • 使用证书和其他方法的多因素身份验证

• 可扩展性

• • 存储函数和过程
  • 过程语言：PL/PGSQL、Perl、Python（等等）
  • SQL/JSON 路径表达式
  • 外部数据包装器：使用标准 SQL 接口连接到其他数据库或流
  • 可定制的表格存储界面
  • 许多提供附加功能的扩展，包括 PostGIS

• 国际化，文本搜索

• • 支持国际字符集，例如通过 ICU 排序规则
  • 不区分大小写和不区分重音的排序规则
  • 全文搜索

PostgreSQL的安装

系统版本：centos7

PostgreSQL版本：postgresql-14(目前最新版）

一、在线yum安装步骤

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
sudo yum install -y postgresql14-server  #这一步会自动创建默认用户postgres

#create your own datadir
sudo mkdir /home/Data/postgres -p
sudo chown postgres:postgres -R /home/Data/postgres

#change configfi of /usr/lib/systemd/system/postgresql-14.service:
Environment=PGDATA=/home/Data/postgres

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-14/bin/postgresql-14-setup initdbsudo systemctl enable postgresql-14sudo systemctl start postgresql-14

添加远程连接的自定义配置

#1. change the bind IP:
#编辑 /home/Data/postgres/postgresql.conf ，约61行添加这行，增加5432端口绑定IP：
listen_addresses = '*'  #or listen_addresses = '${IP}'

#2. add IPv4 local connections:
#编辑 /home/Data/postgres/pg_hba.conf，约92行“IPv4 local connections”模块添加这行，授权远程连接：
host    all             all             0.0.0.0/0               md5

#3. restart postgresql:
systemctl daemon-reloadsystemctl restart postgresql-14

二、离线源码安装

源码包下载路径：https://www.postgresql.org/ftp/source/

安装包：postgresql-14.4.tar.gz

源码安装的依赖条件：

1. 需要GNU make版本 3.80 或更新版本(命令行执行“make --version”查看GNU版本)；

2. 安装gcc，推荐使用最新版本的GCC

3. 安装tar命令，用以解压压缩包

4. 安装GNU Readline 库，用以psql记住您键入的每个命令，并允许您使用箭头键来调用和编辑以前的命令。

5. 安装 zlib 压缩库

6. 其余信息见：https://www.postgresql.org/docs/current/install-requirements.html

离线源码安装步骤

# yum -y install make openssl-devel gcc tar readline-devel zlibe-devel 
# mkdir -p /home/data/pgsql  #创建数据目录
# mkdir -p /home/app #创建安装目录
# tar xf postgresql-14.4.tar.gz
# cd postgresql-14.4/
# ./configure --prefix=/home/app/pgsql-14  #编译安装，可参考官网设置更多自定义参数
# make
# make install
# adduser postgres  #创建pgsql管理员账号postgres
# chown postgres:postgres /home/data/pgsql   #修改数据目录权限
# su - postgres #切换到普通用户进行后边的初始化数据及启动操作
$ 
$ /home/app/pgsql-14/bin/initdb -D /home/data/pgsql   #初始化数据
$ /home/app/pgsql-14/bin/pg_ctl -D /home/data/pgsql -l /home/data/pgsql/logfile start  #启动postgresql应用，logfile如果不指定路径，就会在当前目录下生成logfile
$ /home/app/pgsql-14/bin/createdb test  #测试创建test库
$ /home/app/pgsql-14/bin/psql test  # 测试登录psql并进入test库

自定义配置：

1、change the bind IP:

vim /home/data/pgsql/postgresql.conf ，约61行添加这行：

listen_addresses = '*'  #or listen_addresses = '${IP}'

2、 添加IPv4 local connections:

vim/home/data/pgsql/pg_hba.conf，约92行IPv4 local connections模块添加这行，授权远程连接：

host    all             all             0.0.0.0/0               md5

postgresql语句操作示例

进入数据库

# su postgres
$ /home/app/pgsql-14/pgsql/bin/psql

给postgres用户设置密码

postgres=# \password postgres
Enter new password for user "postgres": 
Enter it again: 
postgres=#

创建sre库

postgres=# create database sre;
CREATE DATABASE
postgres=#

列出当前所有database

postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sre       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)
postgres=#

创建新的用户并设置密码

postgres=# create user sre with password 'sre';
CREATE ROLE
postgres=#

列出当前所有用户

postgres=# \du   #查看指定用户则跟上用户名，如: \du sre
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 sre       |                                                            | {}
postgres=#

给用户sre添加权限

postgres=# alter role sre with CREATEDB; #给用户创建db的权限
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 sre       | Create DB                                                  | {}
postgres=#

将数据库sre的所有权限都授权给新用户sre

postgres=# GRANT ALL PRIVILEGES ON DATABASE sre to sre; #前一个sre是库，后一个sre是用户
GRANT
postgres=#

删除sre库

postgres=# drop database sre;
DROP DATABASE
postgres=#

删除指定用户

postgres=# drop user sre; #删除用户前先确保其名下没有关联的database，否则无法删除
DROP ROLE
postgres=#

使用pgAdmin连接和使用postgresql

pgAdmin连接pgsql配置步骤

连接成功

（本文完）