PostgreSQL数据库资料
0、介绍
PostgreSQL是一个功能强大的 开源 的关系型数据库。底层基于C实现。
PostgreSQL的开源协议和Linux内核版本的开源协议是一样的。。BDS协议，这个协议基本和MIT开源协议一样，说人话，就是你可以对PostgreSQL进行一些封装，然后商业化是收费。
PostgreSQL的名字咋来的。之前叫Ingres，后面为了解决一些ingres中的一些问题，作为后面的ingres，就起名叫postgre。
PostgreSQL版本迭代的速度比较快，现在最新的正式的发布版本，已经到了15.RELEASE。
PGSQL的版本选择一般有两种：
- 如果为了稳定的运行，推荐使用12.x版本。
- 如果想体验新特性，推荐使用14.x版本。
PGSQL允许跨版本升级，而且没有什么大问题。
PGSQL社区特别活跃，基本是三个月一发版。意味着很多常见的BUG都可以得到及时的修复。
PGSQL其实在国外使用的比较多，国内暂时还是以MySQL为主。
但是国内很多国产数据库都是基于PGSQL做的二次封装：比如华为GaussDB还有腾讯的Tbase等等。真实很多公司原来玩的Oracle，直接平转到PGSQL。同时国内的很多云产品都支持PGSQL了。
PGSQL因为开源，有很多做数据迁移的工具，可以让你快速的从MySQL，SQLServer，Oracle直接平转到PGSQL中内部，比如pgloader这样的数据迁移工具。
PGSQL的官方地址：https://www.postgresql.org/
PGSQL的国内社区：http://www.postgres.cn/v2/home
1、PostgreSQL和MySQL的区别
技术没有好坏之分，知识看一下是否符合你的业务，能否解决你的业务需求。其次也要查看社区的活跃度以及更新的频次。
MySQL不支持的几点内容：
- MySQL的数据类型不够丰富。
- MySQL不支持序列概念，Sequence。
- 使用MySQL时，网上比较好用的插件。
- MySQL的性能优化监控工具不是很多，定位问题的成本是比较高。
- MySQL的主从复制没有一个官方的同步策略，同步问题难以解决。
- MySQL虽然开源，but，不够彻底。
PostgreSQL相对MySQL上述问题的特点：
- PostgreSQL的数据类型嘎嘎丰富。
- PostgreSQL是有序列的概念的。
- PostgreSQL的插件特别丰富。
- PostgreSQL支持主从复制的同步操作，可以实现数据的0丢失。
- PostgreSQL的MVCC实现和MySQL不大一样。PostgreSQL一行数据会存储多个版本。最多可以存储40亿个事务版本。
2、安装
打开PostgreSQL官网下载页面，选择操作系统和数据库版本。本机操作系统是CentOS 7.9，x86架构，选择安装的数据库版本是PostgreSQL15这个版本。
17版本的操作系统得CentOS 8.x，CentOS 7.9最高支持建议安装15版本的
# 下载PGSQL的rpm包
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装PGSQL15的软件程序
yum install -y postgresql15-server
# 数据库初始化
/usr/pgsql-15/bin/postgresql-15-setup initdb
# 设置为开启自行启动
systemctl enable postgresql-15
# 启动PGSQL
systemctl start postgresql-15
# 使用清华源
cp pgdg-redhat-all.repo pgdg-redhat-all.repo.bak
sed 's/download.postgresql.org\/pub/mirrors.tuna.tsinghua.edu.cn\/postgresql/g' pgdg-redhat-all.repo > pgdg-tsinghua.repo
如果出错，可能是那些问题：（可以通过查看日志进行排错：日志文件路径：/var/lib/pgsql/15/data/log）
- 安装Linux的时候，一定要选择最小安装
- 你的Linux不能连接外网
- Linux中的5432端口，可能被占用了
安装如果报错如下：
错误：软件包：postgresql15-15.8-1PGDG.rhel7.x86_64 (pgdg15)
需要：libzstd >= 1.4.0
错误：软件包：postgresql15-15.8-1PGDG.rhel7.x86_64 (pgdg15)
需要：libzstd.so.1()(64bit)
错误：软件包：postgresql15-server-15.8-1PGDG.rhel7.x86_64 (pgdg15)
需要：libzstd.so.1()(64bit)
解决方案：
wget https://archives.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/l/libzstd-1.5.5-1.el7.x86_64.rpm
wget https://archives.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/l/llvm5.0-5.0.1-7.el7.x86_64.rpm
wget https://archives.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/l/llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
wget https://archives.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/l/llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
# 或者
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libzstd-1.5.5-1.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-5.0.1-7.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
yum install -y libzstd-1.5.5-1.el7.x86_64.rpm
yum install -y centos-release-scl-rh llvm5*
yum install -y epel-release
yum install -y ./libzstd-1.5.5-1.el7.x86_64.rpm
yum install -y llvm5*
或者采用如下办法：
cat >> /etc/yum.repos.d/centos-sclo.repo << "EOF"
[centos-sclo-sclo]
name=CentOS-7 - SCLo sclo
baseurl=https://mirrors.aliyun.com/centos/7/sclo/x86_64/sclo/
# mirrorlist=https://mirrors.aliyun.com?arch=$basearch&release=7&repo=sclo-sclo
gpgcheck=0
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo
[centos-sclo-rh]
name=CentOS-7 - SCLo rh
baseurl=https://mirrors.aliyun.com/centos/7/sclo/x86_64/rh/
#mirrorlist=https://mirrors.aliyun.com?arch=$basearch&release=7&repo=sclo-rh
gpgcheck=0
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo
EOF
yum -y install llvm5
# 登录验证，PostgreSQ 默认创建一个无密码用户：postgres
# 以超级用户身份登录L数据库
sudo -u postgres psql
# 查看有哪些库
\l
# 退出PostgreSQL
\q
# 修改密码,如果需要数据库连接工具连接，必须设置密码
ALTER USER postgres WITH PASSWORD '新密码';
3、修改配置文件方便远程连接
postgreSQL的核心文件，都属于postgres用户，所以，操作的时候，尽可能的别用root用户，容易出错，尽可能先切换到postgres用户，再去操作。
安装完成后，PostgreSQL的配置文件在目录 /var/lib/pgsql/15/data 下。配置的核心文件都属于postgres用户，修改时需要切换为postgres用户，一般比较常见的是修改 postgresql.conf 和 pg_hba.conf ，PostgreSQL默认情况下不支持远程连接的。
postgresql.conf是PostgreSQL的主要配置文件，它包含了许多数据库服务器的全局设置选项。通过修改这个文件，您可以配置数据库服务器的各种参数，如监听地址、端口号、最大连接数、日志记录等。这个文件的修改通常需要重启数据库服务器才能生效。
# 修改监听地址以允许远程连接
# vim postgresql.conf
# 监听所有IP，允许远程连接数据库
listen_addresses = '*'
# 修改默认端口号(不修改保持默认的5432端口也可以)
port = 55432
pg_hba.conf是PostgreSQL的身份验证配置文件，它定义了哪些客户端可以连接到数据库服务器以及如何进行身份验证。通过修改这个文件，您可以控制访问数据库的权限，配置不同用户的身份验证方法，以及定义访问控制规则。修改pg_hba.conf文件后，通常需要重新加载配置或重启数据库服务器才能使更改生效。
# 配置客户端认证
# vim pg_hba.conf
# 允许任意地址的全部用户连接所有数据库，配置如下：
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 scram-sha-256
模板参数说明：
- type：local代表本地连接，host代表可以指定连接的ADDRESS
- database：数据库名，如果写all，代表所有库都可以连接
- user：连接的用户，可以写all，代表所有用户
- address：代表那些IP地址可以连接
- method：加密方式，默认是scram-sha-256
注：配置尽量写在前面，因为是从上往下匹配的
修改完配置记得重启PostgreSQL
systemctl restart postgresql-15
4、添加到环境变量
编辑 /etc/profile 修改PATH环境变量，修改完成后执行 source /etc/profile 使之生效
# vim /etc/profile
export PATH=/usr/pgsql-15/bin/:$PATH
# source /etc/profile
5、设置密码
PostgreSQL不推荐使用root管理，在安装成功postgreSQL后，他默认会给你创建一个用户：postgres，无密码，
# 登录到PostgreSQL数据库：sudo -u postgres psql (直接一步到位)
注：先切换到：su postgres，再通过命令进入：psql，也是OK的，如下就是：
# 切换到postgres用户
su postgres
# 使用psql登录数据库，如果是默认5432端口，则不用携带-p 参数
psql -p 55432
# 通过以下命令修改postgres账号的密码
ALTER USER postgres WITH PASSWORD 'your password';
# 创建一个root超级管理员用户
# 登录进数据库中
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (15.12)
Type "help" for help.
# 创建一个root超级管理员用户
postgres=# create user root with SUPERUSER PASSWORD 'root';
CREATE ROLE
# 光有用户不让登录，得让用户有一个数据库，直接构建一个root库
postgres=# create database root;
CREATE DATABASE
# 可以在不退出psql的前提下，直接切换数据库
postgres=# \c root
You are now connected to database "root" as user "postgres".
# 用户提示符发生变化，不再是postgres=#
root=#
# 通过以下命令修改root账号的密码
ALTER USER root WITH PASSWORD 'your password';
# 查看全部用户信息
root=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser | {}
6、基本操作
1、登录数据库命令行参数
-h,--host=主机名，数据库服务器主机或socker目录(默认：本地接口)
-p,--port=端口，数据库服务器的端口(默认：5432)
-U,--username=用户名，指定数据库用户名(默认：postgres)
-w,--no-password，永远不提示输入口令
-W,--password，强制口令提示(自动)
# 登录到PostgreSQL数据库：sudo -u postgres psql (直接一步到位)
# 登录指定数据库中
# 切换到postgres用户
su postgres
psql -U your_username -d your_database -p port
# 不指定数据库登录，登录后切换数据库
# 切换到postgres用户
su postgres
psql -p port
\c your_database;
2、常用命令
# 查看有哪些库：\l
# 退出PostgreSQL：\q
# 查看全部用户：\du
# 查看到数据库级别的一些命令：\help
# 查看到服务级别的一些命令：\?
3、新建指定用户，赋予不同的权限
PGSQL一个数据库中有多个schema，在每个schema下都有自己的相应的库表信息，权限粒度会比MySQL更细一些
在PGSQL中，权限的管理分为很多层
server、cluster、tablespace级别：这个级别一般是基于pg_hba.conf去配置
database级别：通过命令级别操作，grant
namespace、schema级别：玩的不多……不去多了解这个~~
对象级别：通过grant命令去设置
新建用户命令
# 区别就是create user默认有连接权限，create role没有，不过可以基于选项去设置
CREATE USER 名称 [ [ WITH ] 选项 [ ... ] ]
create role 名称 [ [ WITH ] 选项 [ ... ] ]
# 修改用户，直接基于ALTER命令操作
# 删除用户，直接基于DROP命令操作
构建一个用户（你自己名字）
构建一个数据库
在这个数据库下构建一个schema（数据库默认有一个public的schema）
将这个schema的权限赋予用户
在这个schema下构建一个表
将表的select，update，insert权限赋予用户
-- 准备用户
create user laozheng with password 'laozheng';
-- 准备数据库
create database laozheng;
-- 切换数据库
\c laozheng;
-- 构建schema
create schema laozheng;
-- 将schema的拥有者修改为laozheng用户
alter schema laozheng owner to laozheng;
-- 将laozheng库下的laozheng的schema中的表的增，改，查权限赋予给laozheng用户
grant select,insert,update on all tables in schema laozheng to laozheng;
-- 用postgres用户先构建一张表
create table laozheng.test(id int);
-- 切换到laozheng用户。
\c laozheng -laozheng
-- 报错：
-- 致命错误: 对用户"-laozheng"的对等认证失败
-- Previous connection kept
-- 上述方式直接凉凉，原因是匹配连接方式时，基于pg_hba.conf文件去从上往下找
-- 找到的第一个是local，匹配上的。发现连接方式是peer。
-- peer代表用当前系统用户去连接PostgreSQL
-- 当前系统用户只有postgres，没有laozheng，无法使用peer连接
-- 想构建laozheng用户时，发现postgreSQL的所有文件拥有者和所属组都是postgres，并且能操作的只有拥有者
-- 基于上述问题，不采用本地连接即可。
-- 采用远程连接。
psql -h 192.168.11.32 -p 5432 -U laozheng -W
-- 这样依赖，跳过了local链接方式的匹配，直接锁定到后面的host，host的连接方式是md5，md5其实就是密码加密了。
-- 登录后，直接输入
\dn
-- 查看到当前database下有两个schema
这种权限的赋予方式，可以用管理员用户去构建整体表结构，如此一来，分配指定用户，赋予不同的权限，这样一来，就不怕用户误操了。
7、配置数据库的日志
/var/lib/pgsql/15/data/postgresql.conf里面有关于日志的配置， PostgreSQL默认情况下，只保存7天的日志，循环覆盖，默认参数如下：
# 代表日志是开启的。
logging_collector = on
# 日志存放的路径，默认放到当前目录下的log里
log_directory = 'log'
# 日志的文件名，默认是postgresql为前缀，%a表示把星期作为后缀
log_filename = 'postgresql-%a.log'
# 日志文件会被覆盖
log_truncate_on_rotation = on
# 一天一个日志文件
log_rotation_age = 1d
# 日志文件，没有大小限制
log_rotation_size = 0
8、pgvector插件安装
pgvector是一款开源的向量搜索引擎，除了具备所有Postgres数据库的特性外，最主要的特点是能在Postgres数据库存储和检索向量数据，支持向量的精确检索和模糊检索。向量格式除了传统embedding模型的单精度浮点数外，还支持半精度浮点数，二元向量或者稀疏向量。
1、首先需要安装postgresql15-devel
# 安装PostgreSQL15开发工具包
yum install -y postgresql15-devel
# 如果上一步执行时报错，那么请先安装centos-release-scl-rh包，再安装PostgreSQL开发工具包
Error: Package: postgresql15-devel-15.5-1PGDG.rhel7.x86_64 (pgdg15)
Requires: llvm-toolset-7-clang >= 4.0.1
# 安装centos-release-scl-rh包后，需要修改镜像源
yum install -y centos-release-scl-rh
cd /etc/yum.repos.d/
mv CentOS-SCLo-scl-rh.repo CentOS-SCLo-scl-rh.repo.bak
# vim CentOS-SCLo-scl-rh.repo
[centos-sclo-rh]
name=CentOS-7 - SCLo rh
baseurl=https://mirrors.aliyun.com/centos/7/sclo/x86_64/rh/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo
yum clean all
yum makecache
2、安装pgvector插件
方法一：yum方式安装
RPM packages are available from the PostgreSQL Yum Repository. Follow the setup instructions for your distribution and run:
yum install pgvector_15
方法二：源码方式安装
# 先安装git(如果已安装，跳过)
yum install -y git
# 切换到/tmp目录，下载源码包，我这里选择是目前最新版本0.5.1
cd /tmp
git clone --branch v0.5.1 https://mirrors.chenby.cn/https://github.com/pgvector/pgvector.git
# 进入/tmp/pgvector目录，进行编译安装
cd pgvector
make & make install
3、配置pgvector插件
登录PostgreSQL，选择数据库安装vector扩展。
# 创建demo数据库
create database demo;
# 切换到demo数据库
\c demo;
# 安装vector扩展，每次新建一个数据库都需要执行如下操作激活插件
CREATE EXTENSION vector;
# 可以通过查询pg_available_extensions表来验证扩展是否安装成功：
SELECT * FROM pg_available_extensions WHERE name='vector';
如果查询结果返回了vector扩展的信息，则说明安装成功。
demo=# SELECT * FROM pg_available_extensions WHERE name='vector';
name | default_version | installed_version | comment
--------+-----------------+-------------------+------------------------------------------------------
vector | 0.7.2 | 0.7.2 | vector data type and ivfflat and hnsw access methods
(1 row)
# 创建测试表
CREATE TABLE test (id bigserial PRIMARY KEY, embedding vector(3));
# 插入测试数据
INSERT INTO test (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
# 按与给定向量相似度(L2 distance)排序，显示前5条
SELECT * FROM test ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
9、图形化界面
-
pgAdmin
下载地址：https://www.pgadmin.org/download/
下载地址：https://dbeaver.io/
- navicat
下载地址：https://www.navicat.com.cn/products
- DataGrip
下载地址：https://www.jetbrains.com/zh-cn/datagrip/
10、数据类型
PGSQL支持的类型特别丰富，大多数的类型和MySQL都有对应的关系
| 名称 | 说明 | 对比MySQL |
|---|---|---|
| 布尔类型 | boolean，标准的布尔类型，只能存储true，false | MySQL中虽然没有对应的boolean，但是有替换的类型，数值的tinyint类型，和PGSQL的boolean都是占1个字节。 |
| 整型 | smallint（2字节），integer（4字节），bigint（8字节） | 跟MySQL没啥区别。 |
| 浮点型 | decimal，numeric（和decimal一样一样的，精准浮点型），real（float），double precision（double），money（货币类型） | 和MySQL基本也没区别，MySQL支持float，double，decimal。MySQL没有这个货币类型。 |
| 字符串类型 | varchar(n)（character varying），char(n)（character），text | 这里和MySQL基本没区别。 PGSQL存储的varchar类型，可以存储一个G。MySQL好像存储64kb（应该是）。 |
| 日期类型 | date（年月日），time（时分秒），timestamp（年月日时分秒）（time和timestamp可以设置时区） | 没啥说的，和MySQL基本没区别。 MySQL有个datetime。 |
| 二进制类型 | bytea-存储二进制类型 | MySQL也支持，MySQL中是blob |
| 位图类型 | bit(n)（定长位图），bit varying(n)（可变长度位图） | 就是存储0，1。MySQL也有，只是这个类型用的不多。 |
| 枚举类型 | enum，跟Java的enum一样 | MySQL也支持。 |
| 几何类型 | 点，直线，线段，圆………… | MySQL没有，但是一般开发也用不到 |
| 数组类型 | 在类型后，追加[]，代表存储数组 | MySQL没有~~~ |
| JSON类型 | json（存储JSON数据的文本），jsonb（存储JSON二进制） | 可以存储JSON，MySQL8.x也支持 |
| ip类型 | cidr（存储ip地址） | MySQL不支持 |
| 等等 | http://www.postgres.cn/docs/12/datatype.html |
11、备份&恢复
在PostgreSQL中，有三种备份方式：
1、逻辑备份（Logical Backup）
逻辑备份是通过导出数据库的 SQL 语句或数据文件来实现备份。它依赖于 PostgreSQL 的 pg_dump 和 pg_dumpall 工具。逻辑备份的优点是可以跨平台、跨版本还原，但备份速度相对较慢，适合备份数据量较小的场景。
优点：简单，方便操作，有手就行，还挺可靠。
缺点：数据数据量比较大，这种方式巨慢，可能导出一天，都无法导出完所有数据。
2、物理备份（Physical Backup）
其实就是找到当前数据库，数据文件在磁盘存储的位置，将数据文件直接复制一份或多份，存储在不同的物理机上。通常使用文件系统快照或 pg_basebackup 工具。物理备份的优点是备份速度快、还原效率高，但只能在同一版本的 PostgreSQL 上使用，且对硬件和存储要求较高。
优点：相比逻辑备份，恢复的速度快。
缺点：在备份数据时，可能数据还正在写入，一定程度上会丢失数据。 在恢复数据时，也需要注意数据库的版本和环境必须保持高度的一致。如果是线上正在运行的数据库，这种复制的方式无法在生产环境实现。
3、归档备份：（也属于物理备份）
Postgresql数据库在写入操作时，对数据文件做的任何修改都会写入WAL（预写日志）日志中，然后才会对数据文件做物理修改。当数据库服务器掉电或者意外宕机，Postgresql在启动时首先读取 WAL 日志，对数据文件进行恢复。
由于Postgresql数据库循环写WAL日志的机制，我们日常工作中要定期对WAL日志进行归档。为了减少数据恢复时间可以对数据库进行增量备份方式进行数据库备份。
wal日志的名称，是三块内容组成，每8个字符分成一组，用16进制标识的
时间线 逻辑id 物理id
00000001 00000000 0000000A
postgres=# select pg_current_wal_lsn(); # 查询当前库用的是哪个wal日志
pg_current_wal_lsn
--------------------
0/19C67F8
(1 row)
postgres=# select pg_walfile_name('0/19C67F8'); # 基于lsn查询具体的wal日志名称
pg_walfile_name
--------------------------
000000010000000000000001
(1 row)
postgres=#
# 批量造一批测试数据
CREATE TABLE t_test (
id integer NOT NULL,
name text NOT NULL,
age integer NOT NULL,
address character(50),
salary real
);
insert into t_test SELECT generate_series(1,5000000) as key,repeat( chr(int4(random()*26)+65),4), (random()*(6^2))::integer,null,(random()*(10^4))::integer;
1、逻辑备份&恢复
默认情况下，database_name是：postgres，schema_name是：public，table_name是自己在public下创建的表名
注意：还原的时候，若数据已存在，则不会覆盖原来的数据，而是新增新的数据进去
1.1 全库备份与还原
# 全库备份
pg_dumpall -U username -h host -p port > all_databases.sql
# 全库还原
psql -U username -h host -p port -f all_databases.sql
参数说明：
-U username：指定数据库用户。-h host：指定数据库主机。-p port：指定数据库端口。-f all_databases.sql：指定还原的 SQL 文件。
1.2 指定库备份与还原
# 指定库备份
pg_dump -U username -h host -p port -d database_name > database_name.sql
# 指定库还原
psql -U username -h host -p port -d database_name -f database_name.sql
参数说明：
-d database_name：指定要备份或还原的数据库名称。
1.3 指定表备份与还原
# 指定表备份
pg_dump -U username -h host -p port -d database_name -t schema_name.table_name > table_name.sql
# 指定表还原
psql -U username -h host -p port -d database_name -f table_name.sql
参数说明：
-t schema_name.table_name：指定要备份或还原的表。
2、物理备份（归档+物理）
这里需要基于前面的文件系统的备份和归档备份实现最终的操作，单独使用文件系统的方式，不推荐，毕竟数据会丢失。
这里直接用PostgreSQL提供的pg_basebackup命令来实现。
pg_basebackup会做两个事情：
- 会将内存中的脏数据落到磁盘中，然后将数据全部备份
- 会将wal日志直接做归档，然后将归档也备走。
提前准备出/pg_basebackup目录。记得将拥有者赋予postgres用户
mkdir /pg_basebackup
chown -R postgres.postgres /pg_basebackup/
在这里直接使用/var/lib/pgsql/15/backups路径
给postgres用户提供replication的权限，修改pg_hba.conf，记得重启生效
host replication postgres 0.0.0.0/0 scram-sha-256
systemctl restart postgresql-15
执行备份,需要输入postgres的密码
# 不带参数z进行备份，只打包
-bash-4.2$ pg_basebackup -D /var/lib/pgsql/15/backups -Ft -Pv -R -U postgres -h 127.0.0.1 -p 5432
Password: # 需要输入postgres的密码
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_7787"
23609/23609 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/3000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$
-bash-4.2$ ll /var/lib/pgsql/15/backups
total 7643636
-rw------- 1 postgres postgres 247545 Mar 18 15:10 backup_manifest
-rw------- 1 postgres postgres 7810051584 Mar 18 15:10 base.tar # 数据目录备份
-rw------- 1 postgres postgres 16778752 Mar 18 15:10 pg_wal.tar # 归档日志备份
# 带参数z进行备份，打包并压缩
-bash-4.2$ pg_basebackup -D /var/lib/pgsql/15/backups -Ft -z -Pv -R -U postgres -h 127.0.0.1 -p 5432
Password: # 需要输入postgres的密码
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_7815"
23610/23610 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/5000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$ ll /var/lib/pgsql/15/backups
total 3264
-rw------- 1 postgres postgres 138758 Mar 19 17:47 backup_manifest
-rw------- 1 postgres postgres 3178619 Mar 19 17:47 base.tar.gz
-rw------- 1 postgres postgres 17645 Mar 19 17:47 pg_wal.tar.gz
参数说明：
-
-D 指定备份文件的存储位置,/var/lib/pgsql/15/backups是自带的备份路径，权限都是postgres
-
-Ft 指定备份格式为 tar
-
-z 对备份文件进行压缩
-
-v 输出备份的详细信息
-
-P 显示备份进度
-
-U 用户名（要拥有备份的权限）
-
-h ip地址
-
-p 端口号
-
-R 复制写配置文件，在输出目录中（或者当使用 tar 格式时再基础归档文件中）建立 standby.signal 并附加连接设置到postgresql.auto.conf 来简化设置一个后备服务器。 postgresql.auto.conf文件将记录连接设置（如果有）以及pg_basebackup所使用的复制槽，这样流复制后面就会使用相同的设置。
3、物理恢复（归档+物理）
模拟数据库崩盘，先停止postgresql服务，然后直接删掉data目录下的全部内容
[root@localhost ~]# systemctl stop postgresql-15
[root@localhost ~]# su - postgres
Last login: Tue Mar 18 15:08:09 CST 2025 on pts/0
-bash-4.2$ ll
total 229636
drwx------ 4 postgres postgres 51 Mar 18 11:35 15
-bash-4.2$ cd 15/data/
-bash-4.2$ ll
total 88
drwxr-xr-x 2 postgres postgres 16384 Mar 18 15:15 archive
drwx------ 7 postgres postgres 63 Mar 18 15:10 base
-rw------- 1 postgres postgres 30 Mar 18 15:08 current_logfiles
drwx------ 2 postgres postgres 4096 Mar 18 15:09 global
drwx------ 2 postgres postgres 32 Mar 18 11:35 log
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_commit_ts
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_dynshmem
-rw------- 1 postgres postgres 4730 Mar 18 15:06 pg_hba.conf
-rw------- 1 postgres postgres 1636 Mar 18 11:35 pg_ident.conf
drwx------ 4 postgres postgres 68 Mar 18 15:15 pg_logical
drwx------ 4 postgres postgres 36 Mar 18 11:35 pg_multixact
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_notify
drwx------ 2 postgres postgres 6 Mar 18 15:10 pg_replslot
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_serial
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_snapshots
drwx------ 2 postgres postgres 25 Mar 18 15:15 pg_stat
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_stat_tmp
drwx------ 2 postgres postgres 18 Mar 18 11:35 pg_subtrans
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_tblspc
drwx------ 2 postgres postgres 6 Mar 18 11:35 pg_twophase
-rw------- 1 postgres postgres 3 Mar 18 11:35 PG_VERSION
drwx------ 3 postgres postgres 4096 Mar 18 15:15 pg_wal
drwx------ 2 postgres postgres 18 Mar 18 11:35 pg_xact
-rw------- 1 postgres postgres 88 Mar 18 11:35 postgresql.auto.conf
-rw------- 1 postgres postgres 29537 Mar 18 14:25 postgresql.conf
-rw------- 1 postgres postgres 58 Mar 18 15:08 postmaster.opts
-bash-4.2$ rm -rf *
-bash-4.2$ ls
-bash-4.2$
将之前备份的两个文件准备好，一个base.tar，一个pg_wal.tar
第一步：将base.tar中的内容，全部解压到 15/data 目录下
第二步：将pg_wal.tar中的内容，全部解压到 /archive 目录下 (这个是根据前面的备份而定的，在这里使用~/15/data/archive路径，下面类似)
-bash-4.2$ cd ~/15/backups/
-bash-4.2$ ll
total 7643636
-rw------- 1 postgres postgres 247545 Mar 18 15:10 backup_manifest
-rw------- 1 postgres postgres 7810051584 Mar 18 15:10 base.tar
-rw------- 1 postgres postgres 16778752 Mar 18 15:10 pg_wal.tar
-bash-4.2$ tar -xf base.tar -C ~/15/data
-bash-4.2$ tar -xf pg_wal.tar -C ~/15/data/archive
第三步：在postgresql.auto.conf文件中，指定归档文件的存储位置，以及恢复数据的方式
# 指定归档文件位置
restore_command = 'cp ~/15/data/archive/%f %p'
# 最大程度恢复
recovery_target = 'latest'
其中recovery_target_timeline 参数是默认恢复到最近时间点 latest。
第四步：启动postgresql服务
systemctl start postgresql-15
第五步：但是启动后的pg是只读模式，无法进行数据修改，出错(ERROR:connot execute INSERT in a read-only transaction)，不让写。需要执行一个函数，取消这种恢复数据后的状态，才允许正常的执行写操作。(需要删除一个文件，然后重启数据库就可以了)
postgres=# insert into t3 values (11111);
ERROR: cannot execute INSERT in a read-only transaction
postgres=# select pg_wal_replay_resume(); # 执行这个函数不生效的话则删除standby.signal这个文件
pg_wal_replay_resume
----------------------
(1 row)
postgres=# insert into t3 values (11111);
INSERT 0 1
# 在数据库data目录下有个文件：standby.signal
(这个文件是在从备份中恢复数据时必须存在的，否则无法回复数据。若恢复数据完毕后，执行上面的函数还是无法新增数据，则可以考虑删除这个文件然后重启数据库)
/var/lib/pgsql/15/data/standby.signal
cd /var/lib/pgsql/15/data/
rm -rf standby.signal
systemctl restart postgresql-15
4、增量备份和基于时间点恢复（PITR）（PITR-Point in time Recovery）
Postgresql数据库在写入操作时，对数据文件做的任何修改都会写入WAL（预写日志）日志中，然后才会对数据文件做物理修改。当数据库服务器掉电或者意外宕机，Postgresql在启动时首先读取 WAL 日志，对数据文件进行恢复。
由于Postgresql数据库循环写WAL日志的机制，我们日常工作中要定期对WAL日志进行归档。为了减少数据恢复时间可以对数据库进行增量备份方式进行数据库备份。
4.1 备份前的准备-数据库配置
4.1.1 创建归档目录
根据实际情况，在磁盘或者挂载的NFS目录上创建归档日志存放的目录，创建时要修改属主和属组为 postgres：
# mkdir -p /data/archive_wals
# cd /data
# chown postgres:postgres /archive_wals
或者直接在postgres目录下创建
su - postgres
mkdir -p /var/lib/pgsql/archive
4.1.2 修改参数
wal_level 的设置会影响 PostgreSQL 的日志记录级别，具体分为三种：
- minimal：记录最基本的 WAL 信息，仅用于崩溃恢复，无法支持逻辑复制
- replica：记录额外的 WAL 信息，支持物理复制，但仍不足以进行逻辑复制
- logical：记录全部 WAL 信息，包括逻辑复制所需的数据变更，适用于 Flink 和其他逻辑复制工具
修改参数文件 postgresql.conf 中的参数 wal_level 的值。可选有 minimal、replica和logical，从minimal到logical，WAL 日志级别依次增高，WAL 记录信息越多，越详细。 由于minimal 这一级别不包含从基本的备份和WAL日志中重建数据的足够信息，所以必须至少开启 wal_level 参数值为 replica ：
- 修改方式一
postgres=# ALTER SYSTEM SET wal_level = 'replica';
postgres=# ALTER SYSTEM SET archive_mode = 'on';
# 重启数据库服务
systemctl restart postgresql-15
# 修改archive_command 参数不需要重启数据库，reload即可。
postgres=# ALTER SYSTEM SET archive_command = 'test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f'
postgres=# select pg_reload_conf();
- 修改方式二
修改postgresql.conf文件
# 开启wal日志的内容，注释去掉即可
wal_level = replica
fsync = on
# 开启归档操作
archive_mode = on
# 修改存放归档日志的路径
archive_command = 'test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f'
# 重启数据库服务
systemctl restart postgresql-15
需要注意的是，archive_command 设定的归档命令是否成功执行，如果未成功，它会周期性的重试，在此期间已有的WAL日志将不会被覆盖重用，新的WAL日志信息会不断占用 pg_wal 的磁盘空间，直到pg_wal所在磁盘沾满后数据库关闭。由于参数 wal_level 与 archive_mode 需要重启数据库，可以在安装之初启动数据库之前，开启这两个参数，然后将 archive_command 的值设置为永远为真的值，例如：/bin/true。当需要开启归档时，只需要修改 archive_command，然后reload即可，省去重启数据库的步骤。
如果考虑归档所占空间较多，可以在归档时将WAL日志压缩后在归档，可以用gzip、bzip2、lz4等压缩工具。
例如：把 archive_command 设置为在归档目录使用 lz4 压缩 WAL 日志文件：
postgres=# ALTER SYSTEM SET archive_command = ‘/usr/bin/lz4 -q -z %p /data/archive_wals/%f.lz4’ postgres=# select pg_reload_conf();
4.1.3 使用pg_basebackup工具备份
在较低的版本中，使用 pg_start_backup 和 pg_stop_backup 创建基础备份。
从Postgresql 9开始有了 pg_basebackup 程序，用普通文件或创建tar包的方式进行基础备份。
pg_basebackup 命令已经整合了 pg_start_backup和pg_stop_backup命令。
模拟场景
场景：每天凌晨02:00，开始做全备（PBK），到了第二天，如果有人14:00分将数据做了误删，希望将数据恢复到14:00分误删之前的状态？
1、恢复全备数据，使用PBK的全备数据恢复到凌晨02:00的数据。（数据会丢失很多）
2、归档恢复：备份中的归档，有02:00~14:00之间的额数据信息，可以基于归档日志将数据恢复到指定的事务id或者是指定时间点，从而实现数据的完整恢复。
1、创建测试数据
-- 构建一张表
create table t3 (id int);
insert into t3 values (1);
insert into t3 values (11);
2、按照基础备份方式将数据库备份(凌晨02:00，开始做全备（PBK）)
按照前面介绍的pg_basebackup的方式使用最基础的备份方式备份，WAL日志没有写满16M不会进行归档，所以在执行完基础备份后需要手动进行一次 WAL 切换
-bash-4.2$ pg_basebackup -D /var/lib/pgsql/15/backups -Ft -z -Pv -Upostgres -h127.0.0.1 -p 5432 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/8000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_10376"
23538/23538 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/8000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$ ll /var/lib/pgsql/15/backups
total 3260
-rw------- 1 postgres postgres 137772 Mar 20 09:40 backup_manifest
-rw------- 1 postgres postgres 3175056 Mar 20 09:40 base.tar.gz
-rw------- 1 postgres postgres 17647 Mar 20 09:40 pg_wal.tar.gz
-bash-4.2$ psql
psql (15.12)
Type "help" for help.
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/9000078
(1 row)
3、删除数据库的数据目录，造成目录删除的故障
-- 凌晨2点已经全备完毕
-- 模拟第二天操作
insert into t3 values (111);
insert into t3 values (1111);
-- 误删操作 2023年3月20日20:13:26
systemctl stop postgresql-15
su - postgres
cd /var/lib/pgsql/15/data
rm -rf *
4、恢复数据（确认有归档日志）
将当前服务的数据全部干掉，按照之前的全备恢复的套路先走着,然后将全备的内容中的base.tar（base.tar.gz）扔data目录下，归档日志pg_wal.tar（pg_wal.tar.gz）也扔到/archive位置。
-bash-4.2$ pwd
/var/lib/pgsql/15/backups
-bash-4.2$ ll
total 3260
-rw------- 1 postgres postgres 137772 Mar 20 09:40 backup_manifest
-rw------- 1 postgres postgres 3175056 Mar 20 09:40 base.tar.gz
-rw------- 1 postgres postgres 17647 Mar 20 09:40 pg_wal.tar.gz
-bash-4.2$ tar -zxv -f base.tar.gz -C /var/lib/pgsql/15/data
-bash-4.2$ tar -zxv -f pg_wal.tar.gz -C /var/lib/pgsql/archive
5、查看归档日志，找到指定的事务id
查看归档日志，需要基于postgresql提供的一个命令
# 如果命令未找到，说明两种情况，要么没有这个可执行文件，要么是文件在，没设置环境变量
pg_waldump
# 也可以采用全路径的方式
/usr/pgsql-15/bin/pg_waldump
-bash-4.2$ pg_waldump 00000002000000010000008D
rmgr: Heap len (rec/tot): 59/ 59, tx: 778, lsn: 1/8C015DD0, prev 1/8C015DA8, desc: INSERT off 2 flags 0x00, blkref #0: rel 1663/5/16403 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 778, lsn: 1/8C015E10, prev 1/8C015DD0, desc: COMMIT 2025-03-18 16:02:09.579687 CST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 1/8C015E38, prev 1/8C015E10, desc: RUNNING_XACTS nextXid 779 latestCompletedXid 778 oldestRunningXid 779
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 1/8C015E70, prev 1/8C015E38, desc: RUNNING_XACTS nextXid 779 latestCompletedXid 778 oldestRunningXid 779
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 1/8C015EA8, prev 1/8C015E70, desc: CHECKPOINT_ONLINE redo 1/8C015E70; tli 2; prev tli 2; fpw true; xid 0:779; oid 24595; multi 1; offset 0; oldest xid 716 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 779; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 1/8C015F20, prev 1/8C015EA8, desc: RUNNING_XACTS nextXid 779 latestCompletedXid 778 oldestRunningXid 779
rmgr: Standby len (rec/tot): 42/ 42, tx: 779, lsn: 1/8C015F58, prev 1/8C015F20, desc: LOCK xid 779 db 5 rel 16395
rmgr: Standby len (rec/tot): 42/ 42, tx: 779, lsn: 1/8C015F88, prev 1/8C015F58, desc: LOCK xid 779 db 5 rel 16398
rmgr: Standby len (rec/tot): 42/ 42, tx: 779, lsn: 1/8C015FB8, prev 1/8C015F88, desc: LOCK xid 779 db 5 rel 16400
rmgr: Standby len (rec/tot): 42/ 42, tx: 779, lsn: 1/8C015FE8, prev 1/8C015FB8, desc: LOCK xid 779 db 5 rel 16399
rmgr: Heap len (rec/tot): 59/ 8191, tx: 779, lsn: 1/8C016030, prev 1/8C015FE8, desc: DELETE off 43 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/5/2610 blk 0 FPW
从上面可以看出，id是779的是删除操作，需要恢复到删除之前的id，也就是778.
6、修改data目录下的恢复数据的方式
修改postgresql.auto.conf文件
将之前的最大恢复，更换为指定的事务id恢复，基于提供的配置例子，如何指定事务id
修改postgresql.auto.conf文件指定好事务ID
restore_command = 'cp ~/15/data/archive/%f %p'
#recovery_target = 'latest'
recovery_target_xid='778'
7、启动postgreSQL服务，查看是否恢复到指定事务ID,可以通过日志查看到
systemctl start postgresql-15
2025-03-20 10:29:46.741 CST [10802] LOG: recovery stopping after commit of transaction 778, time 2025-03-20 10:17:11.780145+08
2025-03-20 10:29:46.741 CST [10802] LOG: pausing at the end of recovery
2025-03-20 10:29:46.741 CST [10802] HINT: Execute pg_wal_replay_resume() to promote.
8、记得执行会后的函数，避免无法执行写操作
postgres=# insert into t3 values (11111);
ERROR: cannot execute INSERT in a read-only transaction
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
postgres=# insert into t3 values (11111);
INSERT 0 1
12、数据迁移
PostgreSQL做数据迁移的插件非常多，可以从MySQL迁移到PostgreSQL，也可以基于其他数据源迁移到PostgreSQL，在这里使用插件：pgloader
以MySQL数据迁移到PostgreSQL为例，分为几个操作：
1、准备MySQL服务（防火墙问题，远程连接问题，权限问题）
2、准备PostgreSQL的服务
3、安装pgloader，pgloader可以安装在任何位置，比如安装在MySQL所在服务，或者PostgreSQL所在服务，再或者一个独立的服务都可以
# 使用源码方式安装，地址：https://github.com/dimitri/pgloader/releases
[root@localhost] # wget https://github.com/dimitri/pgloader/archive/refs/tags/v3.6.9.tar.gz
[root@localhost] # tar -zxv -f v3.6.9.tar.gz
[root@localhost] # cd pgloader-3.6.9
[root@localhost pgloader-3.6.9]# ll
total 88
-rw-rw-r-- 1 root root 387 Oct 24 2022 bootstrap-centos7.sh
-rw-rw-r-- 1 root root 725 Oct 24 2022 bootstrap-centos.sh
-rw-rw-r-- 1 root root 1893 Oct 24 2022 bootstrap-debian.sh
drwxrwxr-x 4 root root 49 Oct 24 2022 build
drwxrwxr-x 2 root root 71 Oct 24 2022 bundle
drwxrwxr-x 2 root root 119 Oct 24 2022 conf
drwxrwxr-x 5 root root 181 Oct 24 2022 debian
-rw-rw-r-- 1 root root 1111 Oct 24 2022 Dockerfile
-rw-rw-r-- 1 root root 1268 Oct 24 2022 Dockerfile.ccl
drwxrwxr-x 4 root root 255 Oct 24 2022 docs
-rw-rw-r-- 1 root root 4589 Oct 24 2022 INSTALL.md
-rw-rw-r-- 1 root root 2812 Oct 24 2022 ISSUE_TEMPLATE.md
-rw-rw-r-- 1 root root 1033 Oct 24 2022 LICENSE
-rw-rw-r-- 1 root root 9010 Oct 24 2022 Makefile
-rw-rw-r-- 1 root root 12976 Oct 24 2022 pgloader.asd
-rw-rw-r-- 1 root root 2602 Oct 24 2022 pgloader.spec
-rw-rw-r-- 1 root root 4827 Oct 24 2022 README.md
drwxrwxr-x 10 root root 244 Oct 24 2022 src
drwxrwxr-x 8 root root 4096 Oct 24 2022 test
-rw-rw-r-- 1 root root 2142 Oct 24 2022 TODO.md
-rw-rw-r-- 1 root root 1548 Oct 24 2022 Vagrantfile
[root@localhost pgloader-3.6.9]# chmod 777 bootstrap-centos7.sh
[root@localhost pgloader-3.6.9]# bash bootstrap-centos7.sh #安装依赖包
[root@localhost pgloader-3.6.9]# make pgloader
[root@localhost pgloader-3.6.9]# cp ./build/bin/pgloader /usr/bin/
[root@localhost pgloader-3.6.9]# pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 1.4.0-1.el7
4、准备pgloader需要的脚本文件，官方文档： https://pgloader.readthedocs.io/en/latest/
MySQL to Postgres 的文档地址：https://pgloader.readthedocs.io/en/latest/ref/mysql.html
# 简单的命令操作
$ pgloader mysql://myuser@myhost/dbname pgsql://pguser@pghost/dbname
# 脚本文件，my.load
LOAD DATABASE
FROM mysql://username:password@ip:port/database_a
INTO postgresql://username:password@ip:port/database_b
WITH include drop, create tables, create indexes, reset sequences,workers = 8, concurrency = 1
ALTER SCHEMA 'database_a' RENAME TO 'public'
;
记住，PostgreSQL的数据库（database_b）需要提前构建好才可以！！！！
5、执行脚本，完成数据迁移
# 显示迁移的详细日志
pgloader --verbose my.load
可能遇到的问题：
1、Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled
解决办法：
pgloader不支持caching_sha2_password身份验证插件,而这个是 MySQL 8 的默认设置，需要修改配置，如MySQL8.0前版本无需操作
在MySQL的配置文件：/etc/my.cnf 中增加 default-authentication-plugin=mysql_native_password
重启mysql
2、执行脚本的时候报错：(死活连不上postgresql数据库)
[root@localhost ~]# pgloader my.load
2025-03-20T05:15:20.028000Z LOG pgloader version "3.6.2"
KABOOM!
FATAL error: Failed to connect to pgsql at "10.16.16.22" (port 5432) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
An unhandled error condition has been signalled:
Failed to connect to pgsql at "10.16.16.22" (port 5432) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
What I am doing here?
Failed to connect to pgsql at "10.16.16.22" (port 5432) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
解决办法：
出现这种报错一般是采用yum方式安装的，建议使用源码方式进行安装
另一种解决办法：删除postgres密码，使用无密码模式 (未测试过)
3、MySQL to PostgreSQL执行脚本报错：
脚本内容：
LOAD DATABASE
FROM mysql://root:jddHLW\@jdd966@192.168.2.161:3306/oilx_ai
INTO postgresql://postgres:jddhlw966@127.0.0.1:5432/demo
WITH include drop, create tables, create indexes, reset sequences,workers = 8, concurrency = 1
ALTER SCHEMA 'oilx_ai' RENAME TO 'public'
;
报错信息：
[root@localhost ~]# pgloader my.load
2025-03-20T15:57:33.032000+08:00 LOG pgloader version "3.6.7~devel"
2025-03-20T15:57:33.157000+08:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///root/mysql:/root:jddHLW@jdd966@192.168.2.161:3306/oilx_ai.load {1008D74B23}>
2025-03-20T15:57:33.157000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@127.0.0.1:5432/demo {1008D75F23}>
2025-03-20T15:57:33.171000+08:00 ERROR sqlite: Failed to open sqlite file #P"/root/mysql:/root:jddHLW@jdd966@192.168.2.161:3306/oilx_ai.load": Could not open sqlite3 database /root/mysql:/root:jddHLW@jdd966@192.168.2.161:3306/oilx_ai.load
Code CANTOPEN: no message.
2025-03-20T15:57:33.171000+08:00 LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 0 0.000s
----------------- --------- --------- --------- --------------
----------------- --------- --------- --------- --------------
解决办法：尚未找到解决办法
LOAD DATABASE
FROM mysql://用户名:密码@主机(ip):3306/数据库名
INTO postgresql://用户名:密码@主机(ip):5432/数据库名
WITH
include drop,create tables, ---->这个是允许删除你目标库中的重名表，允许创建新表
create indexes,reset sequences, ---->创建索引相关，详细见文档
workers = 4,concurrency =1, ---->这里配置工作线程数，以及每个线程的并发量，有一个生效的最低值，我没仔细去研究，感兴趣的可以看文档
multiple readers per thread,rows per range = 1000 ---->这里的配置好像是关于每次读取多少行的，详细见文档
SET PostgreSQL PARAMETERS
maintenance_work_mem to '2048MB', ---->这边是设置工作内存大小的
work_mem to '521MB'
CAST ---->这个drop typemod 是舍去你原来配置的精度或者长度
type int to int drop typemod, ---->这边是具体的字段映射，可能你需要根据文档和你自己的数据库字段去进行配置，
type float to real drop typemod, ---->float默认会转成double
type int with extra auto_increment to serial,
type tinyint to smallint drop typemod, ----> tinyint你不设置的话默认是转成布尔类型
type datetime to timestamp ---->对于MySQL的时间类型基本在PostgreSQL里面都是转成timestamp，默认的字段映射会转成带时区的
---->下面这块一定要加上，因为pgloader默认的会放在同名数据库下的同名schema中，这块结构和MySQL不同，你需要把那个同名的设置成默认的查询schema,不然它默认的是public schema，那样你访问就需要数据库名.schema名才能访问到你那个迁移过来的数据库,加上下面这段，会让你迁移过来的schema直接成默认schema,这样你的后端代码不需要改动，就能直接默认操作迁移过来的数据。
BEFORE LOAD DO
$$ create schema if not exists iflytek;$$,
$$ alter database iflytek set search_path to iflytek,public;$$;
浙公网安备 33010602011771号