线上版本升级 — — pg数据库备份

线上版本升级 — — pg数据库备份

在版本升级之前，我们通常为了保险都需要将数据库里的数据结构备份一份，防止升级失败之后数据丢失。（根据业务而来，并非所有业务都需要备份）

1 备份

1.1 pg_dump：备份指定数据

只能备份单个数据库，不会导出角色和表空间相关的信息。因此恢复数据库或者表之前，需要保证之前的数据库已经被创建。否则会报数据库not exist.

①参数解析

Usage:
  pg_dump [OPTION]... [DBNAME] 数据库名放最后，不指定默认是系统变量PGDATABASE指定的数据库。


General options:(一般选项)

  -f, --file=FILENAME          导出后保存的文件名

  -F, --format=c|d|t|p         导出文件的格式(custom, directory, tar, plain, text(default))

  -j, --jobs=NUM               并行任务数

  -v, --verbose                详细信息

  -V, --version                版本信息

  -Z, --compress=0-9           压缩格式的压缩级别

  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败

  -?, --help                   帮助信息


Options controlling the output content:(控制输出的选项)

  -a, --data-only              只导出数据，不包括模式

  -b, --blobs                  在转储中包括大对象

  -c, --clean                  在重新创建之前，先清除（删除）数据库对象

  -C, --create                 在转储中包括命令,以便创建数据库（包括建库语句，无需在导入之前先建数据库）

  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据

  -n, --schema=SCHEMA          只转储指定名称的模式

  -N, --exclude-schema=SCHEMA  不转储已命名的模式

  -o, --oids                   在转储中包括 OID

  -O, --no-owner               在明文格式中, 忽略恢复对象所属者

  -s, --schema-only            只转储模式, 不包括数据(不导出数据)

  -S, --superuser=NAME         在转储中, 指定的超级用户名

  -t, --table=TABLE            只转储指定名称的表

  -T, --exclude-table=TABLE    不转储指定名称的表

  -x, --no-privileges          不转储权限 (grant/revoke)

  --binary-upgrade             只能由升级工具使用

  --column-inserts             以带有列名的insert命令形式转储数据

  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号

  --disable-triggers           在恢复数据的过程中禁用触发器

  --exclude-table-data=TABLE   不转储指定表的数据

  --inserts                    将数据转储为insert命令，而不是copy命令

  --no-security-labels         不分配安全标签进行转储

  --no-synchronized-snapshots  不在并行任务中使用同步快照

  --no-tablespaces             不转储表空间分配信息

  --no-unlogged-table-data     不转储未标记的表数据

  --quote-all-identifiers      引用所有标识符( 不是关键字 )

  --section=SECTION            转储命名部分(pre-data, data, or post-data)

  --serializable-deferrable    等待没有异常的情况下进行转储

  --use-set-session-authorization  使用SET SESSION AUTHORIZATION命令而不是ALTER OWNER命令来设置所有权


Connection options:(控制连接的选项)

  -d, --dbname=DBNAME      转储的数据库名

  -h, --host=HOSTNAME      数据库服务器的主机名或IP

  -p, --port=PORT          数据库服务器的端口号

  -U, --username=NAME      指定数据库的用户联接

  -w, --no-password        不显示密码提示输入口令

  -W, --password           强制口令提示 (自动)

  --role=ROLENAME          转储前设置角色


如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量的数值.

②使用

1 备份指定表:pg_dump -U xxx -f xxx.sql -t tableName

• 数据准备

-- 创建account表
create table account(id int primary key,name varchar(20), description varchar(100));

-- 手动插入数据
insert into account(id,name,description) values (1,'a1',''),(2,'a2',''),(3,'a2','');

1. 备份account表

-- 备份指定表
pg_dump -U postgres -f /postgres_account.sql -t account postgres; -- (数据库名)

2. 清空表并通过psql进行数据恢复

-- 模拟线上升级故障：手动drop table
drop table account;


 -- psql 恢复数据
psql -U postgres -f /postgres_account.sql postgres 

2 备份整个数据库：pg_dump -U postgres -f /postgres.sql postgres(数据库名)

1. 数据准备：创建多张表并插入数据

-- 创建account、goods表
create table account(id int primary key,name varchar(20), description varchar(100));

create table goods(id int primary key,name varchar(20), price float);


-- 手动插入数据
insert into account(id,name,description) values (1,'a1',''),(2,'a2',''),(3,'a2','');

insert into goods(id,name,price) values (1,'banana',20.0),(2,'apple',15.0),(3,'tomato',7.0);

2. 备份整个数据库

-- -U ziyi指定用户名
-- -f指定存储文件格式以及存储位置
-- testdb指定数据库
pg_dump -U ziyi -f /testdb.sql  testdb

3. 删除数据库并尝试恢复

-- 删除数据库
drop database testdb;

-- 查看数据库列表
\list

-- 恢复数据库，需要我们手动创建testdb
CREATE DATABASE testdb OWNER ziyi;
psql -U ziyi -f /testdb.sql testdb 

重新创建数据库，执行恢复脚本：

进入数据库查看数据是否恢复：

远程备份：pg_dump -U ziyi -h localhost -p 5432 -f /testdb.sql testdb

-- 1. 备份数据
pg_dump -U ziyi -h localhost -p 5432  -f /testdb.sql testdb
#-d 数据库名称
#-h ip地址
#-p 端口号
#-U 用户
#-f 保存路径
#testdb  数据库名称


-- 2. 恢复数据
psql -U ziyi -h localhost -d testdb -p 5432 -f /testdb.sql

#-d 数据库名称
#-h ip地址
#-p 端口号
#-U 用户
#-f sql文件路径

1.2 pg_dumpall：备份所有

-- 备份
pg_dumpall > pg_all.sql

-- 恢复
psql -f pg_all.sql

2 恢复

2.1 psql：psql -U postgres -f /postgres_account.sql postgres

-- 以postgres的角色在postgres数据库下执行/postgres_account.sql文件
psql -U postgres -f /postgres_account.sql postgres 

2.2 pg_restore：从非文本格式（例：xxx.tar）中恢复数据

pg_restore 是一个用来从 pg_dump 创建的非文本格式文件中恢复 PostgreSQL 数据库的工具。
部分参数：
-v : 输出详细信息
-F : 指定备份文件的格式，例如 -Ft 表示以 tar 形式备份的文件
-c : 在还原之前清空数据库
-O : 不还原对象所有者
-j : 并行执行的进程数
-n : 单独指定还原的模式
-t : 单独指定还原的表

--备份数据库,-F t：指定归档格式为tar，注意：默认情况下，pg将忽略备份
--过程中发生的任何错误，这可能导致备份不完整。可以使用-1选项运
-- 行pg_dump，这将会让整个备份过程编程单个事务。

-- pg_dump的备份格式有几种选择
			-- *.bak 压缩二进制格式
			-- *.sql 明文转储
			-- *.tar tarball

pg_dump testdb -c -Ft -f testdb.tar 

-- 删除数据库
drop database testdb

-- 恢复数据到testdb数据库（需要先保证testdb已创建好）
pg_restore -d testdb -c testdb.tar

3 实战

3.1 docker搭建pg

docker run -d \
-p 5432:5432 \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=postgres \
-v /Users/ziyi2/docker-home/pg:/var/lib/postgresql/data \
--name pg \
--restart always \
docker.io/postgres:9.6-alpine


# -p port 映射端口，可以通过宿主机的端口访问到容器内的服务
# -d 是detach 保持程序后台运行的意思
# -e environment 设置环境变量
# -v volume 文件或者文件夹的挂载

3.2 数据准备：创建数据库、创建表

1. 进入pg容器并创建数据库

# 过滤出容器并以postgres角色进入postgres数据库
docker ps | grep pg
docker exec -it container_id /bin/bash
psql -U postgres postgres

# 创建用户并为用户创建数据库
CREATE USER ziyi WITH PASSWORD 'postgres';
create database testdb owner ziyi;

2. 创建表

-- 以ziyi用户切换到testdb数据库
\c testdb ziyi

-- 创建account、goods表
create table account(id int primary key,name varchar(20), description varchar(100));

create table goods(id int primary key,name varchar(20), price float);

3. 插入数据

-- 手动插入数据
insert into account(id,name,description) values (1,'a1',''),(2,'a2',''),(3,'a2','');

insert into goods(id,name,price) values (1,'banana',20.0),(2,'apple',15.0),(3,'tomato',7.0);

3.3 pg_dump：升级前备份数据库

-- 退出pg，来到容器的bash控制台
\q

-- -U ziyi指定数据库owner
-- -f /testdb.sql：指定转储文件路径
-- testdb指定要备份的数据库名
pg_dump -U ziyi -f /testdb.sql  testdb

3.4 删除数据库：模拟数据库异常、数据丢失等

-- 回到容器内部删除数据库
psql -U postgres postgres

drop database testdb;

3.5 psql：通过备份的文件恢复数据库数据

-- 恢复数据之前需要手动创建数据库
CREATE DATABASE testdb owner ziyi;

-- 在docker bash执行恢复命令
psql -U postgres -f /testdb.sql testdb 

执行恢复过程中可能出现一些报错，比如：数据库中已经存在部分func函数等，那么在执行恢复脚本之前，需要我们手动先把对应函数删除

• 数据恢复前：testdb数据库下还没有对应的表

• 数据恢复后：

如果是通过k8搭建的pg，可以直接通过如下命令备份：

source /etc/profile && mkdir -p /ziyi/backup && cd /ziyi/backup && kubectl exec -it pod_name -- pg_dumpall -U postgres > pro_db_20230421.sql

参考文章：https://www.cnblogs.com/nhdlb/p/14960000.html