PostgreSQL基础（一）

简介

PostgreSQL是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。
PostgreSQL的Slogan是“世界上最先进的开源关系型数据库” 号称是“开源界的Oracle”,去O首选
PostgreSQL官网
https://www.postgresql.org/
PostgreSQL中文社区
http://www.postqres.cn/v2/ home
全球数据库排行
https://db-engines.com/en/
国产数据库排行
https://www.modb.pro/dbRank

PostgreSQL与Mysql对比

PostgreSQL的优势：
1)在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨
2)对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强。
3)PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
4)PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
5)PostgreSQL支持ISON和其他NOSQL功能(文档存储)，如本机XML支持和使用HSTORE的键值对。它还支持索引SON数据以加快访问速度，特别是10版本JSONB更是强大
6)PostareSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱也没有人管你，这一点很重要，这表明了PostgreSOl数据库不会被其它公司控制。相反，MySQL现在主要是被Oracle公司控制。
PostgreSQL的劣势：
1)innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制是占优的。
新老数据一起存放，需要定时触发VACUUM，会带来多余的I0和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。
2)MSQL采用索引组织表，这种存储方式非常适合基于主键匹配的査询、删改操作，但是对表结构设计存在约束。
3)MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。

总结：
从应用场景来说PG更加适合严格的企业应用场景(比如金融、电信、ERP、CRM)。
不仅仅限制于此，PostareSQl的json，isonb，bstore等数据格式，特别适用于一些大数据格式的分析。
而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景(比如google、facebook、alibaba)，
当然现在MySQL的在innodb引擎的大力发展，功能表现良好

下载及安装

访问官网下载地址
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

• windos
  安装路径:尽量避免存在中文的情况
  数据目录:，默认路径就可以
  postgres密码（类似超级管理员root）
  安装后通过pgAdmin4图形化界面客户端登录数据，使用安装设置的密码。
  
  也可以通过SQL Shell命令行登录。
  开启远程访问:在安装路径找到data目录下的pg_hba文件

修改pg_hba.conf文件:在IPV4部分添加新的一行:
host all all 0.0.0.0/0 md5
修改后重启postgres
在服务中查询postgres服务进行重启

• Linux

#安装yum源
sudo yum install -y https: //download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#安装pg服务
sudo yum install y postgresql14-server
#初始化数据库
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

#设置PostgreSQL服务为开机启动
sudo systemctl enable postgresql-14
#启动PostgreSQL服务
sudo systemctl start postgresql-14

PostgreSQL安装成功后会默认创建一个名为postgres的用户，初始化数据库后会有名为postgres的数据库来存储数据库的基础信息。例如用户信息等等，相当于MySQL中默认的名为mysql数据库。
postgres数据库中会初始化一名超级用户postgres为了方便我们使用postqres账号进行管理，我们可以修改该账号的密码

#进入命令行
su postgres
#启动SQL Shell
psql
#修改postgre账号密码
alter user postgres with password 'postgres';
#开启端口配置远程访问
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload
#修改配置文件
vi /var/lib/pgsql/14/data/postgresql.conf
#将监听地址修改为*
#默认1isten_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen addresses='*'
允许所有IP访问
#修改配置文件
vi /var/lib/pgsq1/14/data/pg_hba.conf
#在尾部加入
host all all 0.0.0.0/0 md5
重启数据库
sudo systemctl restart postgresql-14

创建用户及配置权限

#查看所有用户和权限
\du
#创建用户并设置密码
CREATE USER '用户名' WITH PASSWORD '密码';
#修改用户密码
ALTER USER '用户名' WITH PASSWORD '密码';
#数据库授权,赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE '数据库名' To '用户名';
#但此时用户还是没有读写权限，需要继续授权表(这条sql语句必须在所要操作的数据库里执行)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;
#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb from test;
#指定用户指定表指定权限 [权限]可以是SELECT、INSERT、UPDATE、DELETE等操作的组合，用逗号分隔
GRANT [权限] ON [表名] TO [用户名];
#删除用户
drop user test;

#指定用户登录
psql -U '用户名' -d '要连接的数据库名'

表空间

使用CREATE TABLE语句创建新的表时,PostgreSQL帮我们完成了所有操作。包括更新系统表中的信息和在文件系统中生成相应的文件。
那么PostgreSQL怎么知道应该在什么位置创建系统文件呢?
答：PostgreSQL通过表空间(Tablespaces)来实现逻辑对象(表、索引等)与物理文件之间的映射。
那当我们查询表的数据时,它如何知道数据文件所在的物理位置呢?
答：创建数据库或者数据表(包括索引)的时候，可以为其指定一个表空间(tablespace)。表空间决定了这些对象在文件系统中的存储路径

在PostgreSQL中，表空间(tablespace)表示数据文件的存放目录，这些数据文件代表了数据库的对象，例如表或索引。
当我们访问表时，系统通过它所在的表空间定位到对应数据文件所在的位置。

• 表空间的优点
  1、如果数据库集群所在的初始磁盘分区或磁盘卷的空间不足，又无法进行扩展，可以在其他分区上创建一个新的表空间以供使用。
  2、管理员可以根据数据库对象的使用统计优化系统的性能。
  例如，可以将访问频繁的索引存放到一个快速且可靠的磁盘上，比如昂贵的固态硬盘。与此同时，将很少使用或者对性能要求不高的归档数据表存储到廉价的低速磁盘上。

PostgreSQL 在集群初始化时将所有的数据文件和配置文件存储到它的数据目录中，通常是环境变量 PGDATA的值。默认创建了两个表空间:
pg_default,template1和template0默认的表空间,也是创建其他数据库时的默认表空间。对应的目录为PGDATA/base。
pg_global，用于存储一些集群级别的共享系统表(system catalogs)，例如 pg_database、pg_control。对应的目录为PGDATA/global。

关于表空间相关的sql

#查看默认表空间
\db
#创建表空间 (表空间名称不能是pg_开头)
CREATE TABLESPACE '指定拥有者用户名' LOCATION '存储的磁盘地址';
#普通用户需要授予表空间上的对象创建权限才能使用该表空间。
GRANT CREATE ON TABLESPACE '表空间名称' To '用户名';

#创建表并指定对应表空间
建表语句  TABLESPACE '表空间名称';
#将表移动到指定表空间
ALTER TABLE IF EXISTS public.t1 SET TABLESPACE pg_default;

有一种情况当表空间所在的磁盘或空间不足或性能比较低想换表空间时

1、停止PostgreSQL服务
2、移动文件系统的数据文件地址
mv /path/源目录 /path/指定目录
3、修改PGDATA/pg_tblspc目录中的符号链接文件(需要提前获取文件名)，指向新的目录;
ln -snf /var/lib/pgsql/源地址 /var/lib/pgsq1/xxx/xxx/连接后地址
4、启动 PostgreSQL
sudo systemctl start postgresql-14