进击的PostgreSQL

目录

前言

一、什么是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
success

4.修改配置文件

修改监听地址为任意地址，即修改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=# \lList of databasesName    |  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
stgrestemplate1 | 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$ exit

5.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=# \dList of relationsSchema |         Name          |   Type   |  Owner   
--------+-----------------------+----------+----------public | business_order        | table    | postgrespublic | 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