进击的PostgreSQL

2024-03-20 14:52
文章标签 postgresql 进击

本文主要是介绍进击的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

这篇关于进击的PostgreSQL的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/829820

相关文章

PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL有什么优点? 开源和免费 PostgreSQL是一个开源的数据库管理系统,可以免费使用和修改。这降低了企业的成本,并为开发者提供了一个活跃的社区和丰富的资源。 高度兼容 PostgreSQL支持多种操作系统(如Linux、Windows、macOS等)和编程语言(如C、C++、Java、Python、Ruby等),并提供了多种接口(如JDBC、ODBC、ADO.NET等

PostgreSQL中的多版本并发控制(MVCC)深入解析

引言 PostgreSQL作为一款强大的开源关系数据库管理系统,以其高性能、高可靠性和丰富的功能特性而广受欢迎。在并发控制方面,PostgreSQL采用了多版本并发控制(MVCC)机制,该机制为数据库提供了高效的数据访问和更新能力,同时保证了数据的一致性和隔离性。本文将深入解析PostgreSQL中的MVCC功能,探讨其工作原理、使用场景,并通过具体SQL示例来展示其在实际应用中的表现。 一、

PostgreSQL入门介绍

一、PostgreSQL 背景及主要功能介绍 1、背景 PG数据库,全称为PostgreSQL数据库,是一款开源的关系型数据库管理系统(RDBMS)。其起源可以追溯到20世纪80年代末和90年代初,由加拿大的计算机科学家Michael Stonebraker及其团队在加州大学伯克利分校启动。该项目旨在创建一个强大的、开源的关系型数据库管理系统,作为早期关系型数据库系统Ingres的继承者。Mi

PostgreSQL索引介绍

梦中彩虹   博客园首页新随笔联系管理 随笔 - 131  文章 - 1  评论 - 14 PostgreSQL索引介绍 INDEX 索引是增强数据库性能的常用方法。索引使得数据库在查找和检索数据库的特定行的时候比没有索引快的多。但索引也增加了整个数据库系统的开销,所以应该合理使用。 介绍 假设我们有一个类似这样的表: CREATE TABLE test1 (id integ

PostgreSQL分区表(partitioning)应用实例详解

https://www.jb51.net/article/97937.htm   PostgreSQL分区表(partitioning)应用实例详解  更新时间:2016年11月22日 10:25:58   作者:小灯光环    我要评论   这篇文章主要为大家详细介绍了PostgreSQL分区表(partitioning)应用实例,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

PostgreSql中WITH语句的使用

https://blog.csdn.net/chuan_day/article/details/44809125 PostgreSql中WITH语句的使用 With语句是为庞大的查询语句提供了辅助的功能。这些语句通常是引用了表表达式或者CTEs(一种临时数据的存储方式),可以看做是一个查询语句的临时表。在With语句中可以使用select,insert,update,delete语句。当然wit

PostgreSQL 17即将发布,新功能Top 3

按照计划,PostgreSQL 17 即将在 2024 年 9 月 26 日发布,目前已经发布了第一个 RC 版本,新版本的功能增强可以参考 Release Notes。 本文给大家分享其中 3 个重大的新增功能。 MERGE 语句增强 MERGE 语句是 PostgreSQL 15 增加的一个新功能,它可以在单个语句中实现 INSERT、UPDATE 以及 DELETE 操作,非常适合数据

Peewee+Postgresql+PooledPostgresqlDatabase重连机制

需求: Postgresql数据库服务重启后,需要业务代码正常读写数据库 方案: 通过继承playhouse.shortcuts.ReconnectMixin和playhouse.pool.PooledPostgresqlDatabase来创建一个新的ReconnectPooledPostgresqlDatabase类修改reconnect_errors属性来适配Postgresql

【PostgreSQL教程】PostgreSQL 高级篇之 LOCK(锁)

博主介绍:✌全网粉丝20W+,CSDN博客专家、Java领域优质创作者,掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌ 技术范围:SpringBoot、SpringCloud、Vue、SSM、HTML、Nodejs、Python、MySQL、PostgreSQL、大数据、物联网、机器学习等设计与开发。 感兴趣的可以先关注收藏起来,在工作中、生活上等遇到相关问题都可以给我

【PostgreSQL教程】PostgreSQL 高级篇之 TRANSACTION(事务)

博主介绍:✌全网粉丝20W+,CSDN博客专家、Java领域优质创作者,掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌ 技术范围:SpringBoot、SpringCloud、Vue、SSM、HTML、Nodejs、Python、MySQL、PostgreSQL、大数据、物联网、机器学习等设计与开发。 感兴趣的可以先关注收藏起来,在工作中、生活上等遇到相关问题都可以给我