PostgreSql的备份和升级

原创已于 2025-06-24 17:37:32 修改 · 2.3k 阅读

11 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#postgresql #数据库

于 2024-10-12 22:54:29 首次发布

设计模式与项目优化同时被 3 个专栏收录

44 篇文章

订阅专栏

31 篇文章

订阅专栏

数据库

6 篇文章

订阅专栏

问题背景

版本概述

跨大版本数据迁移

QProcess 调用相关进程进行冷备份和恢复

表的定期备份与恢复

问题背景

随着现在网络安全意识的逐渐加强，越来越多的机构加强了对终端上软件的安全漏洞扫描，由于项目历史悠久，初始选择的PostgreSql版本目前来看比较老旧，且常常会扫描出很多安全漏洞，项目软件铺设较广、运行时间也比较久远，因此需要进行数据库高版本平滑迁移。

客户端长时间运行数据库由于断电或磁盘坏道等问题常常崩溃不可恢复，虽然一些重要数据可能会在云端存储，但是对于一些局域网单位内部项目往往没有云的接入，此时对数据的备份和恢复有一定要求。

版本概述

该数据库版本主要分为主要版本和次要版本，大版本基本每年发布一次，小版本则每几个月即发布，更新较快。在10.0之前所使用的数据库版本由三个数字组成，如9.5.2，主要版本为9.5，次要版本为2，即9.5版本第二次发布；在10.0之后版本由两位数字组成，如10.16，

10为主要版本，16为次要版本，同一个主版本由于数据的存储结构相同，且总是前后版本兼容，因此升级只需要将旧版本的data文件替换到新的data文件即可；而跨越主版本升级则会因为存储结构的改变而不能简单的进行data替换。

跨大版本数据迁移

通过pg_dump进行升级，该方法主要是将原有的数据库转存为文本，该文本主要包含数据库重新生成的sql语句，后续再新版本中配合pg_restore或pg_sql进行数据库重新生成。

pg_dump -h 127.0.0.1 -U dbusername -p 5432 -d dbname -f sqlfilename .sql

psql -h 127.0.0.1 -U dbusername -p 5432 -d dbname -f sqlfilename.sql

pg_dump -h 127.0.0.1 -U dbusername -p 5432 -d dbname -F c -f sqlfilename.backup

pg_restore -h 127.0.0.1 -U dbusername -p 5432 -d dbname -1 backup_file.backup

不指定输出格式则输出纯文本，即sql文件；恢复时需要psql程序执行sqlfilename 中sql语句恢复
指定自定义格式或其他格式，恢复时必须用pg_restore读取sqlfilename进行恢复

纯文本格式恢复效率一般比自定义格式低。

-h, --host=HOSTNAME：指定数据库服务器的主机名或 IP 地址。
-p, --port=PORT：指定数据库服务器的端口号。
-U, --username=NAME：指定连接数据库时使用的用户名。
-d, --dbname=NAME：指定要备份的数据库名称。
输出格式参数：
-F, --format=FORMAT：指定导出文件的格式。常见的格式有：
c：自定义格式（compressed），这是默认格式，支持压缩和并行恢复。
d：目录格式，将数据库导出为多个文件，存储在一个目录中。
t：纯文本格式（SQL 脚本），可读性强，但恢复时可能较慢。
C：自定义格式（不压缩）。
输出文件参数：
-f, --file=FILENAME：指定导出文件的路径和名称。
备份内容参数：
-n, --schema=SCHEMA：指定要导出的架构名称。
-t, --table=TABLE：指定要导出的表名称。
--exclude-table=TABLE：排除某张表不导出。
-a, --data-only：只导出数据，不导出结构（表定义）。
-s, --schema-only：只导出结构，不导出数据。

QProcess 调用相关进程进行冷备份和恢复

int AsyUpradeFileObj::pg_dump_sqlfile(const QString&DbBinDirStr, const QString&sqlfilePath)
{
	cLogger("PG")->info(tr("开始执行pg_dump命令备份数据").toStdString().c_str());
	QProcess pgProcess;
	QStringList ArgumentsList;
	ArgumentsList << "-h" << "127.0.0.1" << "-U" << "username" << "-p" << "5432" << "-d" << "dbname" << "-f" << sqlfilePath;
	pgProcess.setProgram(QDir::cleanPath(DbBinDirStr + QDir::separator() + "pg_dump.exe"));
	pgProcess.setArguments(ArgumentsList);
	QProcessEnvironment env = QProcessEnvironment::systemEnvironment();
    //数据库密码写在进程的环境变量中
	env.insert("PGPASSWORD", MyPassWord);
	pgProcess.setProcessEnvironment(env);
	pgProcess.start();
	QObject::connect(&pgProcess, &QProcess::readyReadStandardOutput, [&]() {
		QString msg = pgProcess.readAllStandardOutput();
		cLogger("PG")->info(msg.toStdString());
	});
	QObject::connect(&pgProcess, &QProcess::readyReadStandardError, [&]() {
		QString msg = pgProcess.readAllStandardError();
		cLogger("PG")->info(msg.toStdString());
	});
	if (!pgProcess.waitForStarted())
	{
		cLogger("PG")->error(tr("执行pg_dump命令失败").toStdString().c_str());
		return -1;
	}
	if (pgProcess.waitForFinished(-1))
	{
		cLogger("PG")->info(tr("执行pg_dump命令备份数据成功").toStdString().c_str());
		return 0;
	}
	else
	{
		return -1;
	}
}

int AsyUpradeFileObj::pg_restore_fromFile(const QString&DbBinDirStr, QString&sqlfilePath)
{
	cLogger("PG")->info(tr("开始执行psql命令执行数据备份sql进行恢复").toStdString().c_str());
	QProcess pgProcess;
	QStringList ArgumentsList;
	ArgumentsList << "-h" << "127.0.0.1" << "-U" << "pms_db_role" << "-p" << "5432" << "-d" << "pms_db_client" << "-f" << sqlfilePath;
	pgProcess.setProgram(QDir::cleanPath(DbBinDirStr + QDir::separator() + "psql.exe"));
	pgProcess.setArguments(ArgumentsList);
	pgProcess.start();
	if (!pgProcess.waitForStarted())
	{
		cLogger("PG")->error(tr("执行psql命令失败").toStdString().c_str());
		return -1;
	}
	if (pgProcess.waitForFinished(-1))
	{
		cLogger("PG")->info(tr("执行psql命令执行数据备份sql进行恢复成功").toStdString().c_str());
		return 0;
	}
	else
	{
		return -1;
	}
}

该程序除了用于备份和恢复，也适合用于数据跨版本迁移；在使用文本格式的备份文件时，备份文件是将数据以sql的形式存储在文件中，在高版本中调用psql实际是在将该sql执行一遍在恢复数据，相较于自定义格式效率较低。

表的定期备份与恢复

程序中对于重要的表可以定期进行冷备份，若数据库由于断电或磁盘原因崩溃不可恢复，至少在重建时可以恢复重要的数据。

数据库崩溃无法修复时对于重要的表有INSERT 或 COPY备份便于数据库重装后可以执行这些sql语句恢复重要数据(单纯的SQL可以忽略版本等其他因素)

以insert语句单独备份数据：
pg_dump -U username -h hostname -d dbname -t tablename1 -t tablename2 -a --inserts -f outputfile.sql
--data-only(或者-a) 确保只导出数据。
--inserts 使用 INSERT 语句导出数据。
-f outputfile.sql 指定输出文件。

以copy语句备份数据：
pg_dump -U username -d dbname -t tablename1 -t tablename2 -f outputfile.sql

psql恢复：恢复纯文本格式
psql -U username -d database -f outputfile.sql

自定义备份：
pg_dump -U username -d database -t table -Fc -f table_backup.dump
-Fc:
指定了备份文件格式。c 表示自定义格式（Custom format），这种格式是 PostgreSQL 特有的一种压缩格式，可以包含整个数据库、单个表或模式等。
-f my_table_backup.dump:
指定输出文件的名称和路径：table_backup.dump 是生成的备份文件的名称。

pg_restore恢复：自定义格式恢复
pg_restore -U username -d database table_backup.dump