PostgreSQL pg_orphaned扩展

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 由于种种原因,PostgreSQL可能会产生一些孤儿文件,这些文件会占用磁盘空间,手工查找费时费力还容易出错,pg_orphaned扩展很好的解决了这个问题。

一、介绍

虽然可以通过查询获得孤儿文件列表,但由于存在正在创建文件(如CREATE TABLE、Relation重写等)的事务(意味着已启动且尚未提交或回滚),因此可能会得到不正确的结果。

pg_orphaned扩展通过在pg_class中查找relfilnode(s)时使用脏快照来处理此类情况。

有关孤独文件,可参考 PostgreSQL孤儿文件

二、安装

$ git clone https://github.com/bdrouvot/pg_orphaned.git
$ cd pg_orphaned
$ make$ make install
$ psql DB -c"CREATE EXTENSION pg_orphaned;"

三、功能

pg_orphaned扩展提供了以下函数去维护孤独文件:

pg_list_orphaned(interval)

列出孤独文件。当"older"字段为true时,仅列出比interval参数(缺省1天)老的孤儿文件。

pg_move_orphaned(interval)

移动孤儿文件到"orphaned_backup" 目录。 当"older"字段为true时,仅移动比interval参数(缺省1天)老的孤儿文件。

pg_list_orphaned_moved()

列出已经被移动到"orphaned_backup" 目录的孤儿文件。

pg_move_back_orphaned()

将已经被移到 "orphaned_backup" 目录的孤儿文件移回其原始位置。

pg_remove_moved_orphaned()

删除"orphaned_backup" 目录中的孤独文件。

四、用法举例

例一:

postgres=# create database test;CREATE DATABASE
postgres=# \c test
You are now connected to database "test"as user "postgres".
test=# createtable bdt asselect*from generate_series(1,40000000);SELECT40000000test=# select*from pg_list_orphaned()orderby relfilenode; dbname | path | name | size | mod_time | relfilenode | reloid
--------+------+------+------+----------+-------------+--------(0 rows
test=# begin;BEGINtest=# createtable bdtorph asselect*from generate_series(1,40000000);SELECT40000000test=# create index orphidx on bdtorph(generate_series);CREATE INDEX
test=# select pg_relation_filepath ('bdtorph'); pg_relation_filepath
---------------------- base/294991/294997(1 row)test=# select pg_relation_filepath ('orphidx'); pg_relation_filepath
---------------------- base/294991/295000(1 row)$ backend has been killed -9test=# select pg_relation_filepath ('orphidx');server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
$ reconnect and search for orphaned files
test=# select pg_relation_filepath ('orphidx');ERROR:  relation "orphidx" does not exist
LINE 1:select pg_relation_filepath ('orphidx');test=#
test=# select pg_relation_filepath ('bdtorph');ERROR:  relation "bdtorph" does not exist
LINE 1:select pg_relation_filepath ('bdtorph');test=# select*from pg_list_orphaned()orderby relfilenode; dbname |    path     |   name   |    size    |        mod_time        | relfilenode | reloid
--------+-------------+----------+------------+------------------------+-------------+-------- test   | base/294991|294997.1|376176640|2020-05-0316:18:36+00|294997|0 test   | base/294991|294997|1073741824|2020-05-0316:16:03+00|294997|0 test   | base/294991|295000|898490368|2020-05-0316:20:16+00|295000|0(3 rows)

例二:

test=# CREATE TABLESPACE bdttbs location '/usr/local/pgsql12.2-orphaned/bdttbs';CREATE TABLESPACE
test=# begin;BEGINtest=# createtable bdtorph tablespace bdttbs asselect*from generate_series(1,40000000);SELECT40000000test=# select pg_relation_filepath ('bdtorph');              pg_relation_filepath
------------------------------------------------ pg_tblspc/303184/PG_12_201909212/303183/303185(1 row)$ backend has been killed -9test=# select pg_relation_filepath ('bdtorph');server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
$ reconnect and search for orphaned files
test=# select pg_relation_filepath ('bdtorph');ERROR:  relation "bdtorph" does not exist
LINE 1:select pg_relation_filepath ('bdtorph');test=# select*from pg_list_orphaned()orderby relfilenode; dbname |                  path                   |   name   |    size    |        mod_time        | relfilenode | reloid
--------+-----------------------------------------+----------+------------+------------------------+-------------+-------- test   | pg_tblspc/303184/PG_12_201909212/303183|303185|1073741824|2020-05-0317:28:49+00|303185|0 test   | pg_tblspc/303184/PG_12_201909212/303183|303185.1|376176640|2020-05-0317:30:18+00|303185|0(2 rows)

例三:

test=# begin;BEGINtest=# create temp table bdtorphtemp asselect*from generate_series(1,40000000);SELECT40000000test=# select pg_relation_filepath ('bdtorphtemp'); pg_relation_filepath
----------------------- base/311377/t4_311380
(1 row)$ backend has been killed -9test=# select pg_relation_filepath ('bdtorphtemp');server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
$ 重连查找孤独文件
test=# select pg_relation_filepath ('bdtorphtemp');ERROR:  relation "bdtorphtemp" does not exist
LINE 1:select pg_relation_filepath ('bdtorphtemp');test=# select*from pg_list_orphaned()orderby relfilenode; dbname |    path     |    name     |    size    |        mod_time        | relfilenode | reloid
--------+-------------+-------------+------------+------------------------+-------------+-------- test   | base/311377| t4_311380.1|376176640|2020-05-0317:35:03+00|311380|0 test   | base/311377| t4_311380   |1073741824|2020-05-0317:34:59+00|311380|0(2 rows)

例四(处理进行中的交易):

Session 1:postgres=# begin;BEGINpostgres=*# createtable bdtinpgro (a int);CREATETABLE如果使用类似下面的查询,Session 2 会列出一个假的孤儿文件:postgres=# select*from pg_ls_dir ('/home/postgres/pgorph/pg_installed/data/base/13580')as file where file ~'^[0-9]*$'and file::textnotin(select oid::textfrom pg_class ); file
-------16408(1 row)使用扩展不会列出这个假孤儿文件:postgres=# select*from pg_list_orphaned(); dbname | path | name | size | mod_time | relfilenode | reloid
--------+------+------+------+----------+-------------+--------(0 rows)

例五(从10/28/2021开始):

  • pg_list_orphaned() 增加interval参数 (缺省 1 天)。
  • 它是个布尔值,用于填充新增加的“older”列,以指示孤儿文件是否比interval参数指定的值旧。
postgres=# select now();              now
-------------------------------2021-10-2813:20:24.734192+00(1 row)postgres=# select*from pg_list_orphaned();  dbname  |    path    | name  |  size  |        mod_time        | relfilenode | reloid | older
----------+------------+-------+--------+------------------------+-------------+--------+------- postgres | base/13214|16391|106496|2021-10-2813:19:56+00|16391|0| f
 postgres | base/13214|16388|147456|2021-10-2813:19:56+00|16388|0| f
(2 rows)postgres=# select*from pg_list_orphaned('10 seconds');  dbname  |    path    | name  |  size  |        mod_time        | relfilenode | reloid | older
----------+------------+-------+--------+------------------------+-------------+--------+------- postgres | base/13214|16391|106496|2021-10-2813:19:56+00|16391|0| t
 postgres | base/13214|16388|147456|2021-10-2813:19:56+00|16388|0| t
(2 rows)

例六(从11/26/2021开始):

列出1分钟前的孤儿文件
postgres=# select*from pg_list_orphaned('1 minute');  dbname  |    path    |   name   |  size   |        mod_time        | relfilenode | reloid | older
----------+------------+----------+---------+------------------------+-------------+--------+------- postgres | base/13892|987654|8192000|2021-11-2615:01:46+00|987654|0| t
 postgres | base/13892|145676.2|8192000|2021-11-2614:54:44+00|145676|0| t
 postgres | base/13892|145676|8192000|2021-11-2614:54:30+00|145676|0| t
 postgres | base/13892|145676.1|8192000|2021-11-2614:54:40+00|145676|0| t
(4 rows)移动1分钟前的孤儿文件到备份目录
postgres=# select pg_move_orphaned('1 minute'); pg_move_orphaned
------------------4(1 row)列出备份目录中的孤儿文件
postgres=# select*from pg_list_orphaned_moved();  dbname  |               path               |   name   |  size   |        mod_time        | relfilenode | reloid
----------+----------------------------------+----------+---------+------------------------+-------------+-------- postgres | orphaned_backup/13892/base/13892|987654|8192000|2021-11-2615:01:46+00|987654|0 postgres | orphaned_backup/13892/base/13892|145676.2|8192000|2021-11-2614:54:44+00|145676|0 postgres | orphaned_backup/13892/base/13892|145676|8192000|2021-11-2614:54:30+00|145676|0 postgres | orphaned_backup/13892/base/13892|145676.1|8192000|2021-11-2614:54:40+00|145676|0(4 rows)移除备份目录中的孤独文件
postgres=# select pg_remove_moved_orphaned(); pg_remove_moved_orphaned
--------------------------(1 row)列出备份目录中的孤独文件
postgres=# select*from pg_list_orphaned_moved(); dbname | path | name | size | mod_time | relfilenode | reloid
--------+------+------+------+----------+-------------+--------(0 rows)列出1分钟前的孤独文件
postgres=# select*from pg_list_orphaned('1 minute'); dbname | path | name | size | mod_time | relfilenode | reloid | older
--------+------+------+------+----------+-------------+--------+-------(0 rows)


相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
存储 NoSQL 关系型数据库
PostgreSQL列存扩展hydra简单测试
Hydra是一款PostgreSQL的扩展,为PostgreSQL增加了列存引擎,使得PostgreSQL的olap性能大幅提升,本文介绍Hydra基本的使用方法。
|
存储 安全 关系型数据库
PostgreSQL物化视图增量更新扩展 -- pg_ivm
PostgreSQL不支持物化视图增量更新,需要定期执行REFRESH MATERIALIZED VIEW命令刷新物化视图。Incremental View Maintenance (IVM)是一种使物化视图保持最新的方法,其中只计算增量更改并将其应用于视图,而不是REFRESH MATERIALIZED VIEW那样从头开始重新计算内容。当只更改视图的一小部分时,IVM可以比重新计算更高效地更新物化视图。
|
3月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
SQL 关系型数据库 Go
《增强你的PostgreSQL:最佳扩展和插件推荐》
《增强你的PostgreSQL:最佳扩展和插件推荐》
982 0
|
7月前
|
SQL 关系型数据库 C语言
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
97 0
|
7月前
|
SQL 关系型数据库 数据库
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
226 0
|
7月前
|
SQL 关系型数据库 编译器
PostgreSQL SQL扩展 ---- C语言函数(二)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
7月前
|
SQL 存储 关系型数据库
PostgreSQL SQL扩展 ---- C语言函数(一)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
SQL 存储 缓存
「PostgreSQL技术」扩展Postgresql到TB
「PostgreSQL技术」扩展Postgresql到TB
|
SQL 监控 关系型数据库
「性能调优」PostgreSQL扩展到12亿条/月的经验教训
「性能调优」PostgreSQL扩展到12亿条/月的经验教训

相关产品

  • 云原生数据库 PolarDB