一、PostgreSQL基本知识

青啊青斯

已于 2024-04-14 19:58:54 修改

阅读量952

点赞数 7

分类专栏： # PostgreSQL 文章标签： postgresql 数据库

于 2024-04-14 19:58:25 首次发布

本文链接：https://blog.csdn.net/qq_37344058/article/details/137749059

版权

PostgreSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、简介

PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。用于安全地存储数据; 支持最佳做法，并允许在处理请求时检索它们。
特点：
- PostgreSQL可在所有主要操作系统(即Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru64)和Windows等)上运行。
- PostgreSQL支持文本，图像，声音和视频，并包括用于C/C++，Java，Perl，Python，Ruby，Tcl和开放数据库连接(ODBC)的编程接口。
- PostgreSQL支持SQL的许多功能，例如复杂SQL查询，SQL子选择，外键，触发器，视图，事务，多进程并发控制(MVCC)，流式复制(9.0)，热备(9.0))。
- 在PostgreSQL中，表可以设置为从“父”表继承其特征。
- 可以安装多个扩展以向PostgreSQL添加附加功能。
官网：https://www.postgresql.org/
中文社区：http://postgres.cn/v2/home
全球数据库排行：https://db-engines.com/en/ranking
国产数据库排行：https://www.modb.pro/dbRank
历史版本：
- 8.0版本：2005年发布，开始支持windows系统
- 9.0版本：支持64位windows，异步流数据复制、Hot Standby；
开源许可独特性：PG遵守BSD许可证发行：开发者们获取源代码并进一步开发系统，开发完也可以用于商用。
- 而MySQL是GPL的公共许可，如果改造了，需要开源才可以商用。

二、PostgreSQL和MySQL对比

PostgreSQL的优势：
- 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨。
- 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强
- PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
- PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
- PostgreSQL支持JSON和其他NoSQL功能，如本机XML支持和使用HSTORE的键值对。它还支持索引JSON数据以加快访问速度，特别是10版本JSONB更是强大。
- PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。相反，MySQL现在主要是被Oracle公司控制。
MySQL的优势：
- inodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。
- MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束。
- MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
- MySQL相对于PG在国内的流行度更高，PG在国内显得就有些落寞了。
- MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景
总结：从应用场景来说，PG更加适合严格的企业应用场景(比如金融、电信、ERP、CRM)，但不仅仅限制于此，PostgresQl的json，jsonb，hstore等数据格式，特别适用于一些大数据格式的分析；而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景(比如google、facebook、alibaba)，当然现在MySQL的在innodb引擎的大力发展，功能表现良好

三、PostgreSQL的安装

1.Docker安装

# 1.拉取镜像（下面两个命令二选一即可）
# 1.1 拉取最新的镜像
docker pull postgres
# 1.2 拉取指定版本的
docker pull postgres:14.2

# 2.创建挂载的文件夹
mkdir -p /data/postgresql

# 3.启动docker容器
docker run --name postgres \
    --restart=always \
    -e POSTGRES_PASSWORD=password \
    -p 5432:5432 \
    -v /data/postgresql:/var/lib/postgresql/data \
    -d postgres:14.2

启动命令参数解释：
- run: 创建并运行一个容器；
- –restart=always 表示容器退出时,docker会总是自动重启这个容器；
- –name: 指定创建的容器的名字；
- -e POSTGRES_PASSWORD=password: 设置环境变量，指定数据库的登录口令为password；
- -p 5432:5432: 端口映射将容器的5432端口映射到外部机器的5432端口；
- -v /data/postgresql:/var/lib/postgresql/data 将运行镜像的/var/lib/postgresql/data目录挂载到宿主机/data/postgresql目录
- -d postgres:11.4: 指定使用postgres:11.4作为镜像。

四、PostgreSQL相关工具

psql：它是一个命令行工具，也是管理PostgreSQL的主要工具。 pgAdmin是PostgreSQL的免费开源图形用户界面管理工具。
phpPgAdmin：它是用PHP编写的PostgreSQL的基于Web的管理工具。它基于phpMyAdmin工具管理MySQL功能来开发。它可以用作PostgreSQL的前端工具。
pgFouine：它是一个日志分析器，可以从PostgreSQL日志文件创建报告

# 1.命令行进入pg
# 语法：psql -h 服务器 -U 用户名 -d 数据库 -p 端口地址 
psql -h dbuser -d exampledb -h 127.0.0.1 -p 5432
# 注意：如果只输入 psql 连接数据库，默认用户和数据库都是postgres

五、数据库操作

# 1.创建数据库
CREATE DATABASE mydb;
# 2.删除数据库
DROP DATABASE mydb;
# 3.切换数据库（相当于mysql的use dbname）
\c dbname
# 4.列举数据库（相当于mysql的show databases）
\l
# 5.列举表（相当于mysql的show tables）
\dt
# 6.查看表结构（相当于desc tblname,show columns from tbname）
\d tblname
# 7.查看索引
\di

六、数据库表操作

1.数据类型

以下是PostgreSQL中主要有三种类型的数据类型：
- 数值数据类型
- 字符串数据类型
- 日期/时间数据类型

1.1 数值数据类型

名字	存储长度	描述	范围
smallint	存储整数，小范围	2字节	-32768 至 +32767
integer	存储整数。使用这个类型可存储典型的整数	4字节	-2147483648 至 +2147483647
bigint	存储整数，大范围。	8字节	-9223372036854775808 至 9223372036854775807
decimal	用户指定的精度，精确	变量	小数点前最多为131072个数字; 小数点后最多为16383个数字。
numeric	用户指定的精度，精确	变量	小数点前最多为131072个数字; 小数点后最多为16383个数字。
real	可变精度，不精确	4字节	6位数字精度
double	可变精度，不精确	8字节	15位数字精度
serial	自动递增整数	4字节	1 至 2147483647
bigserial	大的自动递增整数	8字节	1 至 9223372036854775807

1.2 字符串数据类型

String数据类型用于表示字符串类型值

数据类型	描述
char(size)	这里size是要存储的字符数。固定长度字符串，右边的空格填充到相等大小的字符。
character(size)	这里size是要存储的字符数。固定长度字符串。右边的空格填充到相等大小的字符。
varchar(size)	这里size是要存储的字符数。可变长度字符串。
character varying(size)	这里size是要存储的字符数。可变长度字符串。
text	可变长度字符串。

1.3 日期/时间数据类型

日期/时间数据类型用于表示使用日期和时间值的列。

名称	描述	存储大小	最小值	最大值	解析度
timestamp [ (p) ] [不带时区 ]	日期和时间(无时区)	8字节	4713 bc	294276 ad	1微秒/14位数
timestamp [ (p) ]带时区	包括日期和时间，带时区	8字节	4713 bc	294276 ad
date	日期(没有时间)	4字节	4713 bc	5874897 ad	1微秒/14位数
time [ (p) ] [ 不带时区 ]	时间(无日期)	8字节	00:00:00	24:00:00	1微秒/14位数
time [ (p) ] 带时区	仅限时间，带时区	12字节	00:00:00+1459	24:00:00-1459	1微秒/14位数
interval [ fields ] [ (p) ]	时间间隔	12字节	-178000000年	178000000年	1微秒/14位数

1.4 布尔类型

名称	描述	存储大小
boolean	它指定true或false的状态。	1字节

2.相关命令

# 1.创建表
# 语法：create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 
CREATE TABLE test(id serial primary key, name varchar(255));

# 2.删除一个表
drop table [表名]; 

# 3.重命名一个表
alter table [表名A] rename to [表名B]; 

# 4.在已有的表里添加字段 
alter table [表名] add column [字段名] [类型]; 

# 5.删除表中的字段
alter table [表名] drop column [字段名]; 

# 6.重命名一个字段
alter table [表名] rename column [字段名A] to [字段名B];

# 7.给一个字段设置缺省值
alter table [表名] alter column [字段名] set default [新的默认值]; 

# 8.去除缺省值
alter table [表名] alter column [字段名] drop default; 

# 9.在表中插入数据
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 

# 10.修改表中的某行某列的数据
update [表名] set [目标字段名]=[目标值] where [该行特征]; 

# 11.删除表中某行数据
delete from [表名] where [该行特征]; 

# 12.删空整个表 
delete from [表名];

七、Schema

1.介绍

PG的Schema可以看成是一个表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。
一个数据库包含一个或多个模式，模式中包含着表。模式还包含其他类型的命名对象，包括数据类型、函数和操作符。相同的对象名称可以被用于不同的模式中而不会出现冲突，例如schema1和myschema都可以包含名为mytable的表。和数据库不同，模式并不是被严格地隔离：一个用户可以访问他们所连接的数据库中的所有模式内的对象，只要他们有足够的权限。
优点：
- 允许多个用户使用一个数据库并且不会互相干扰。
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

2.相关操作

# 1.创建Schema
CREATE SCHEMA myschema;

# 2.在myschema中创建表cities
CREATE TABLE db3.myschema.cities (
    name            varchar(80),
    location        point
);

# 3.删除Schema
DROP SCHEMA myschema;

# 4.删除一个模式及其中包含的所有对象
DROP SCHEMA myschema CASCADE;

# 5.查看Schema
\dn

八、备份

PostgreSQL提供了pg dump 实用程序来简化备份单个数据库的过程。必须以对要备份的数据库具有读取权限的用户身份运行此命令。

1.单数据库备份

# 1.以postgres用户登录
sudo su - postgres
# 2.备份dbname数据库（默认遇到错误，还会继续备份，加上-1的话 会回滚）
pg_dump -l dbname > dbname.bak
# 3.恢复数据库
psql dbname < dbname.bak

备份格式有几种：
- *.bak：压缩二进制格式
- *.sql：明文存储
- *.tar：tarball
注意：默认情况下，PostgreSQL将忽略备份过程中发生的任何错误。这可能导致备份不完整，要防止这种情况，您可以使用-1选项运行pg_dump命令。这会将整个备份过程视为单个事务，这将在发生错误时阻止部分备份。

# 以下是案例
# 导出postgres数据库保存为postgre.sql
pg_dump -U postgres -f /tmp/postgres.sql postgres 

# 导出postgres数据库中表test的数据
pg_dump -U postgres -f /tmp/postgres.sql -t test postgres

# 导出postgres数据库以tar形式压缩保存为postgres.tar
pg_dump -U postgres -F t -f /tmp/postgres.tar postgres

2.所有数据库备份

# 1.备份所有数据库
pg_dumpall > pg_backup.bak

# 2.从备份还原所有数据库
psql -f pg_backup.bak postgres

# 以下是案例
# 恢复postgres.sql数据到bk01数据库
psql -U postgres -f /tmp/postgres.sql bk01
# 恢复postgres.tar数据到bk01数据库
pg_restore -U postgres -d bk01 /tmp/postgres.tar

九、用户/角色管理

在PostgreSQl 里没有区分用户和角色的概念， CREATE USER 为 CREATE ROLE 的别名，这两个命令几乎是完全相同的，唯一的区别是"CREATE USER"命令创建的用户默认带有LOGIN属性，而“CREATE ROLE"命令创建的用户默认不带LOGIN属性。

1.角色操作

# 创建david角色：默认不带LOGIN属性
CREATE ROLE david;
# 创建sandy用户：默认具有LOGIN属性
CREATE USER sandy;

# 查看有哪些用户/角色
\du

在这里插入图片描述

1.1 角色属性

属性	说明
login	只有具有LOGIN属性的角色可以用做数据库连接的初始角色名
superuser	数据库超级用户
createdb	创建数据库权限
createrole	允许创建或删除其他普通的用户角色（超级用户除外）
replication	做流复制的时候用到的一个用户属性，一般单独设定
password	在登录时要求指定密码时才会起作用，比如md5或者password模型，跟客户端的连接认证方式有关
inherit	用户组对组员的一个继承标识，成员可以继承用户组的权限特性

2.用户操作

# 1.创建用户并设置密码
CREATE USER 'username' WITH PASSWORD 'password';

# 2.修改用户密码
ALTER USER 'username' WITH PASSWORD 'password';

# 3.数据库授权，赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE 'dbname' TO 'username';
# 将数据库mydb权限授予test
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;
# 此时用户没有读写权限，需要继续授权表
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test;
# 注意，该sql语句必须要在所要操作的数据库里执行

# 4.移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM test;

# 5.删除用户
DROP USER test;

# 6.创建用户时赋予角色属性（创建bella并赋予创建数据库权限）
CREATE ROLE bella CREATEDB;

# 7.给用户赋予权限
# 赋予登录权限
ALTER ROLE username WITH LOGIN;
# 赋予创建角色权限
ALTER ROLE username WITH CREATEROLE;

十、控制台常用命令总结

\password：设置密码
\q：推出
\h：查看SQL命令的解释，比如：\h select
\?：查看psql命令列表
\l：列出所有数据库
\c [database_name]：切换数据库
\d：列出当前数据库所有的表
\d [table_name]：列出某一个表的结构
\du：列出所有用

青啊青斯

关注

7
点赞
踩
22

收藏

觉得还不错? 一键收藏
1
评论
一、PostgreSQL基本知识

PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。用于安全地存储数据;支持最佳做法，并允许在处理请求时检索它们。特点：PostgreSQL可在所有主要操作系统(即Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru64)和Windows等)上运行。PostgreSQL支持文本，图像，声音和视频，并包括用于C/C++，Java，Perl，Python，Ruby，Tcl和开放数据库连接(ODBC)的编程接口。
复制链接

扫一扫