目录
前言
一、什么是PostgreSQL
1.PostgreSQL的定义
2.PostgreSQL功能和特性
2.1数据类型
2.2数据完整性
2.3并发性、性能
2.4可靠性、灾难恢复
2.5安全
2.6扩展
2.7国际化、文本搜索
二、部署PostgreSQL
1.下载与安装
2.配置数据库
3.配置远程访问
4.修改配置文件
5.常用命令
5.1数据库
5.2数据表
5.3数据库备份与还原
前言
PostgreSQL，通常简称为Postgres，是一款强大且开源的关系型数据库管理系统（RDBMS），它在数据存储和处理方面提供了广泛的功能和灵活性。
一、什么是PostgreSQL
1.PostgreSQL的定义
PostgreSQL 是一款开源的、免费的关系型数据库管理系统。它具有以下特点：
- 高度可扩展： PostgreSQL 允许在不同规模的应用程序中灵活扩展，从小型网站到大型企业级系统都可以使用。
- 支持复杂数据类型： 除了常见的数据类型外，PostgreSQL 还支持 JSON、数组、范围、几何图形、全文搜索等多种复杂数据类型。
- ACID 兼容： 它严格遵循 ACID（原子性、一致性、隔离性、持久性）属性，确保数据的完整性和一致性。
- 丰富的扩展支持： PostgreSQL 提供了丰富的扩展支持，允许开发人员自定义和添加功能，以满足各种需求。
- 高级优化器： 它具有强大的查询优化器，能够自动优化查询以提高性能。
官网：PostgreSQL: The world's most advanced open source database
2.PostgreSQL功能和特性
- 多版本并发控制（MVCC） PostgreSQL 使用多版本并发控制来管理事务，这意味着它可以支持高并发访问，每个事务都可以看到一致的快照数据。
- 外键支持 PostgreSQL 提供了完整的外键支持，可以维护表之间的关联关系，并确保数据的完整性。
- 复杂查询 PostgreSQL 支持复杂查询，包括联接、子查询、窗口函数等，使您能够灵活地查询和分析数据。
- 全文搜索 它内置了全文搜索功能，允许您执行高效的文本搜索和分析。
- 复制和高可用性 PostgreSQL 提供了复制和高可用性解决方案，包括流复制、逻辑复制和自动故障转移。
2.1数据类型
- 基元：整数、数字、字符串、布尔值
- 结构化：日期/时间、数组、范围/多范围、UUID
- 文档：JSON/JSONB、XML、KEY-VALUE （HSTORE）
- 几何：点、线、圆、多边形
- 自定义：复合、自定义类型
2.2数据完整性
- 唯一，不为空
- 主键
- 外键
- 排除约束
- 显式锁、咨询锁
2.3并发性、性能
- 索引：B 树、多列、表达式、部分索引
- 高级索引：GiST，SP-Gist，KNN Gist，GIN，BRIN，覆盖索引，布隆过滤器
- 复杂的查询规划器/优化器，仅索引扫描，多列统计信息
- 事务，嵌套事务（通过保存点）
- 多版本并发控制 （MVCC）
- 并行化读取查询和构建 B 树索引
- 表分区
- SQL 标准中定义的所有事务隔离级别，包括可序列化
- 表达式的实时 （JIT） 编译
2.4可靠性、灾难恢复
- 预写日志记录 （WAL）
- 复制：异步、同步、逻辑
- 时间点恢复 （PITR），活动备用
- 表空间
2.5安全
- 身份验证：GSSAPI、SSPI、LDAP、SCRAM-SHA-256、证书等
- 强大的门禁系统
- 列级和行级安全性
- 使用证书和其他方法进行多重身份验证
2.6扩展
存储函数和过程
过程语言：PL/pgSQL、Perl、Python 和 Tcl。还有其他语言可以通过扩展使用，例如Java，JavaScript（V8），R，Lua和Rust。
SQL/JSON 路径表达式
外部数据包装器：使用标准 SQL 接口连接到其他数据库或流
可定制的表存储界面
许多提供附加功能的扩展模块，包括 PostGIS
2.7国际化、文本搜索
- 支持国际字符集，例如通过 ICU 排序规则
- 不区分大小写和不区分重音的排序规则
- 全文搜索
二、部署PostgreSQL
1.下载与安装
// 下载
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
// 安装
yum install -y postgresql15-server
// 初始化数据库
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
// 设置开机启动
sudo systemctl enable postgresql-15
安装完成后会默认创建账户postgresql，默认数据库postgres 存储数据库的基础信息
[root@localhost ~]#tail -n1 /etc/passwd
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
[root@localhost ~]#grep postgres /etc/shadow
postgres:!!:19801::::::
2.配置数据库
[root@localhost ~]#su postgres
#切换到postgres用户
bash-4.2$
bash-4.2$
bash-4.2$
bash-4.2$
bash-4.2$ psql
#切换SQL模式
could not change directory to "/root"
psql (9.2.24, server 15.6)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
postgres=# alter user postgres with password 'postgres123';
#修改密码
ALTER ROLE
postgres=# create user test with password 'test';
#创建test用户
CREATE ROLE
postgres=# \q
#退出SQL模式
bash-4.2$ exit
#退出postgres用户
[root@localhost ~]#3.配置远程访问
将5432端口添加到防火墙列表
// 打开防火墙端口
sudo firewall-cmd --add-port=5432/tcp --permanent
// 刷新防火墙
sudo firewall-cmd --reload
[root@localhost ~]#systemctl start firewalld
[root@localhost ~]#sudo firewall-cmd --add-port=5432/tcp --permanent
success
[root@localhost ~]#sudo firewall-cmd --reload
success4.修改配置文件
修改监听地址为任意地址，即修改postgresql.conf文件
// 打开配置文件
vi /var/lib/pgsql/15/data/postgresql.conf
// 打开监听注释，监听地址改为*
listen_addresses = '*'
// 保存退出 esc
:wq
[root@localhost ~]#vim /var/lib/pgsql/
15/ .bash_history .bash_profile .psql_history
[root@localhost ~]#vim /var/lib/pgsql/15/data/postgresql.conf
[root@localhost ~]#sed -n '60p' /var/lib/pgsql/15/data/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
5.常用命令
5.1数据库
// 切换到postgres用户
su postgres
// 创建数据库 mydb
create database mydb;
// 查看所有数据库
\l
// 切换到mydb数据库
\c mydb
// 删除
drop database mydb;
[root@localhost ~]#su postgres
bash-4.2$ psql
could not change directory to "/root"
psql (9.2.24, server 15.6)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
postgres=# create database mydb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privil
eges
-----------+----------+----------+-------------+-------------+----------------
-------
mydb | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
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/po
stgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/po
stgres
(4 rows)
postgres=# create database mydb;
错误: 数据库 "mydb" 已经存在
postgres=# \c mydb
psql (9.2.24, server 15.6)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
You are now connected to database "mydb" as user "postgres".
mydb=# drop database mydb;
错误: 无法删除当前使用的数据库
mydb=# \q
bash-4.2$ psql
could not change directory to "/root"
psql (9.2.24, server 15.6)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
postgres=# drop database mydb;
DROP DATABASE
postgres=# \c mydb;
连接到套接字"/run/postgresql/.s.PGSQL.5432"上的服务器失败:致命错误: 数据库 "mydb" 不存在
Previous connection kept
postgres=# \q
bash-4.2$ exit5.2数据表
// 创建表
create table business_order(id serial primary key,order_no varchar(255),sku_name varchar(255));
// 插入数据
insert into business_order(order_no,sku_name)values('20221126000001','iphone14 plus');
// 查看表内容
select * from business_order;
// 查看表结构
\d business_order
// 查看所有表，序列等
\d
[root@localhost ~]#su postgres
bash-4.2$ psql
could not change directory to "/root"
psql (9.2.24, server 15.6)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
postgres=# create table business_order(id serial primary key,order_no varchar(255),sku_name varchar(255));
CREATE TABLE
postgres=# insert into business_order(order_no,sku_name)values('20240319000001','Huawei p60');
INSERT 0 1
postgres=# select * from business_order;
id | order_no | sku_name
----+----------------+------------
1 | 20240319000001 | Huawei p60
(1 row)
postgres=# \d business_order
错误: 字段 c.relhasoids 不存在
第1行..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi...
^
postgres=# \d business_order;
错误: 字段 c.relhasoids 不存在
第1行..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi...
^
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+----------
public | business_order | table | postgres
public | business_order_id_seq | sequence | postgres
(2 rows)
postgres=# \q
bash-4.2$ exit
5.3数据库备份与还原
实际工作中会对数据库进行备份和还原，备份主要有三种格式
- .bak 即压缩的二进制
- .sql 即明文存储
- .tar 即tarball压缩格式
数据库备份分单数据库备份，使用 pg_dump 命令；所有数据库备份，使用 pg_dumpall 命令。
// 切换到postgres用户
su postgres
// 单数据库备份-导出到当前目录
pg_dump mydb > mydb.bak
// 所有数据库备份,
pg_dumpall > backup.bak
// 整个数据库备份到指定位置
pg_dump -f /tmp/mydb.bak mydb
// 备份postgres数据库中business_order表
pg_dump -U postgres -f /tmp/mydb.sql -t business_order postgres
// 数据库恢复-直接恢复，注意先新增空数据库
psql -f /tmp/mydb.bak mydb
// 数据库恢复
pg_restore -U postgres -d business_order /temp/mydb.bak
// 查看目录
ls -l
[root@localhost ~]#su postgres
bash-4.2$ psql
could not change directory to "/root"
psql (9.2.24, server 15.6)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
postgres=# pg_dump mydb > mydb.bak
postgres-# pg_dumpall > backup.bak
postgres-# pg_dump -f /tmp/mydb.bak mydb
postgres-# pg_dump -U postgres -f /tmp/mydb.sql -t business_order postgres
postgres-# psql -f /tmp/mydb.bak mydb
postgres-# pg_restore -U postgres -d business_order /temp/mydb.bak
postgres-# ls -l
postgres-# ll
postgres-# \q
bash-4.2$ exit
异常处理：Navicat连接报错
错误代码：authentication method 10 not supported
处理方案：在 pg_hba.conf 文件中可将新增的 0.0.0.0/0 的模式改为 trust
