我记不住的那些postgresql知识

背景：最近一直在使用postgresql数据库,之前都是增删改查数据，而没有涉及过建库、建用户、权限、访问白名单及相关事项，这里算是归纳总结知识也当做记录，以备不时之需。

问题一： 当你拿到一台空空如也的服务器后，如何进行安装以及启动数据库？ （这里暂且涉及单节点，三节点集群暂时略过）

首先，推荐源码安装，这样可忽略不同的CPU架构，直接通过编译器进行编译可执行文件。

1.下载源码并解压 
(略)

2. 编译和安装
 --prefix代表安装目录， 
 --with-<module>或--with-<module>=<某个厂商或某个组织>
 也就是说同一个模块，可能有多种厂商或组织提供，需要从中选择你所需要的那个厂商的模块。

# yum install uuid-dev
# ./configure --prefix=/pgsoft/pg15.6 --with-uuid=ossp
# make
# make install
# cd postgresql-15.6/contrib/uuid-ossp
# make && make install

3. 添加Linux系统postgres用户，并创建数据存储目录
# adduser postgres
# chown postgres:postgres /pgsoft/pg15.6
# su - postgres
$ mkdir -p /home/postgres/pgdata

4. 初始化数据库
$ /pgsoft/pg15.6/bin/initdb -D /home/postgres/pgdata

5. 增加访问白名单
$ vim /home/postgres/pgdata/pg_hba.conf

' 添加一行，格式为 TYPE  DATABASE  USER  ADDRESS  METHOD
' host代表 通过 TCP/IP 连接
' all代表所有库，
' all代表所有用户，
' 192.168.1.100/32 代表客户端的地址范围，例如：应用服务所在的地址
' md5代表连接方式

host  all  all  192.168.1.100/32  md5


6. 启动postgresl
$ /pgsoft/pg15.6/bin/pg_ctl -D /home/postgres/pgdata -l logfile start

7. 执行psql测试，并开启扩展
$ /pgsoft/pg15.6/bin/psql 
postgres=# CREATE EXTENSION "uuid-ossp";
查看你的扩展安装的情况
postgres=# \dx

问题二： 当你拿到一个运行中的postgresql且空空如也的数据库，如何去给所要使用的人建库建用户？

0. 以下都是使用数据库postgres用户进行操作

1. 创建一个dbuser用户，密码为 password
CREATE USER dbuser WITH PASSWORD 'password';

ps: postgresql 中的 user其实就是role，这两个都是一个意思，使用\du可以进行查看， 
或 SELECT rolname FROM pg_roles;

2. 创建一个属于dbuser用户的数据库exampledb
CREATE DATABASE exampledb OWNER dbuser;

3. 将数据库exampledb的所有权限都授权给dbuser
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

这里的ALL PRIVILEGES 包括:
权限名称	说明
CONNECT	允许用户连接到该数据库。
CREATE	允许用户在该数据库中创建新 schema（模式）。
TEMPORARY	允许用户在该数据库中创建临时表（TEMP TABLE）。

ALL PRIVILEGES 仅作用于数据库层级，不会自动授予用户对数据库内现有 schema、表、视图等对象的操作权限（如 SELECT, INSERT, UPDATE）。
若需用户操作表，需额外授权（例如在 schema 或表级别授予权限）

该命令是 数据库级权限的操作

4. 连接到example库
\c exampledb

5. 创建 Schema(可以理解为数据库的命名空间)
默认会存在一个public的schema，也就是无需手动创建，当创建库的时候会自动创建一个public的schema模式

CREATE SCHEMA sales;
CREATE SCHEMA hr;

6. 对所要使用的schema进行授权
-- 允许用户在数据库exampledb中的schema public 内查看对象
GRANT USAGE ON SCHEMA public TO dbuser;

-- 允许用户在数据库exampledb中的schema public 内创建表
GRANT CREATE ON SCHEMA public TO dbuser;

该命令是 schema级权限的操作

7. 然后再对表进行授权，依次进行，这才是正常流程

授权schema public模式下的 your_username用户可以对库的所有表进行增删查改
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_username;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_username;

具体来说，ALL PRIVILEGES 包括以下权限：
权限名称	说明
SELECT	允许查询表中的数据。
INSERT	允许向表中插入新数据。
UPDATE	允许修改表中的数据。
DELETE	允许删除表中的数据。
TRUNCATE	允许清空表（快速删除所有数据）。
REFERENCES	允许在表中创建外键约束（引用其他表的列）。
TRIGGER	允许在表上创建触发器。

该命令是 表级权限 的批量操作

ps:   实例（Instance） → Database（数据库） → Schema（模式） → 表（Table）、视图（View）、函数等对象， 这里的schema是我本次学习postgresql遇到最大的知识盲区，而不是通常认为的Database下面就是表table。

也就是说 Database    数据库是数据的最高层级容器，用于逻辑隔离数据（如 app_db 和 analytics_db）。
Schema 是数据库内的命名空间，用于组织表、视图等对象（如 public、finance）

问题三： 如何进行数据迁移

所谓的数据迁移，其实就是 将所需的数据从一个环境导出，然后到另一个环境进行导入，例如：将dev环境的数据库数据进行导出，并放置一个U盘中或其他存储设备，然后进入到prod环境进行导入数据。

首先导入的数据根据你的需求分为不同的情况，例如：

第一种情况： 一开始我的prod环境数据库空空如也，我需要迁移dev下某个库的所有表的所有数据

第二种情况：我后续存在迭代变更，我需要将某个库中的某个表的所有数据进行重新灌装(即删除这个表的所有数据，再重新导入新的数据)。

第三种情况：我后续存在调整参数，我需要调整这个库的某个表的某几行数据，即对表中的数据进行增删查改。

这三种不同的情况，对应了三个维度，分别是 库、表、数据，它们的颗粒度不一样，也对应于开发、测试及投产过程中。

postgresql中pg_dump和pg_restore是官方推荐的备份和恢复工具。

第一种情况：

导出数据

# 针对第一种情况，我们直接导出库的数据
pg_dump -U 用户名 -h 主机地址 -p 端口 -d 数据库名 > 导出路径/文件名.sql

# 针对第一种情况，-Fc 表示生成自定义压缩格式，适合大数据库
pg_dump -U 用户名 -h 主机地址 -p 端口 -Fc -d 数据库名 > 导出路径/文件名.dump

# 仅导出当前库的表结构（不含数据）：
pg_dump -U 用户名 -h 主机地址 -p 端口 -s -d 数据库名 > 导出路径/文件名.sql


导入数据
psql -U 用户名 -h 主机地址 -p 端口 -d 目标数据库名 -f 文件路径/文件名.sql

pg_restore -U postgres -h 主机地址 -p 端口 -d 目标数据库名 文件路径/文件名.dump

第二种情况：

# 针对第二种情况，我们直接导出库的数据
pg_dump -U 用户名 -h 主机地址 -p 端口 -d 数据库名 -t 表名 > 导出路径/文件名.sql

# 针对第一种情况，-Fc 表示生成自定义压缩格式，适合大数据库
pg_dump -U 用户名 -h 主机地址 -p 端口 -Fc -d 数据库名 -t 表名 > 导出路径/文件名.dump

# 仅导出当前库的某个表结构（不含数据）：
pg_dump -U 用户名 -h 主机地址 -p 端口 -s -d 数据库名 -t 表名 > 导出路径/文件名.sql


导入数据
psql -U 用户名 -h 主机地址 -p 端口 -d 目标数据库名 -f 文件路径/文件名.sql

pg_restore -U postgres -h 主机地址 -p 端口 -d 目标数据库名 -t 表名 文件路径/文件名.dump

如果想加速导入，使用 -j 4或者8
pg_restore -U postgres -h 主机地址 -p 端口 -d 目标数据库名 -t 表名 -j 8 文件路径/文件名.dump

如果仅恢复表结构，不导入数据  --schema-only
如果仅导入数据，不恢复表结构  --data-only

第三种情况：

写脚本进行导入数据

# 数据导出






# 数据导入

PGPASSWORD="${DB_PASSWORD}" psql -U ${DB_USER} -d ${DB_NAME} -h ${DB_HOST} -p ${DB_PORT} -c "\copy  apps FROM '$HOME_PATH/apps.csv' WITH (FORMAT csv, HEADER true);"


PGPASSWORD="${DB_PASSWORD}" 密码
-U 数据库用户
-h 主机地址
-p 端口
-c 执行的命令

COPY命令和\copy命令区别：

COPY是一种sql命令，类似 SELECT/UPDATE/INSERT/DELETE

\copy 是psql的一种指令，类似于

\l (列出数据库)  、 \c (连接数据库)  、 \d (显示表的列表)  、\dx (显示扩展)  、\x () 、\t (是否有表头或表框)