pg_orphaned
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_orphaned | 1.0 | ADMIN | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 5200 | pg_orphaned | No | Yes | No | Yes | No | No | - |
| Related | pg_dirtyread pg_surgery amcheck pageinspect pg_visibility pg_checksums pg_catcheck pg_repack |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PIGSTY | 1.0 | 1817161514 | pg_orphaned | - |
| RPM | PIGSTY | 1.0 | 1817161514 | pg_orphaned_$v | - |
| DEB | PIGSTY | 1.0 | 1817161514 | postgresql-$v-pg-orphaned | - |
Build
You can build the RPM / DEB packages for pg_orphaned using pig build:
pig build pkg pg_orphaned # build RPM / DEB packages
Install
You can install pg_orphaned directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_orphaned; # Install for current active PG version
pig ext install -y pg_orphaned -v 18 # PG 18
pig ext install -y pg_orphaned -v 17 # PG 17
pig ext install -y pg_orphaned -v 16 # PG 16
pig ext install -y pg_orphaned -v 15 # PG 15
pig ext install -y pg_orphaned -v 14 # PG 14
dnf install -y pg_orphaned_18 # PG 18
dnf install -y pg_orphaned_17 # PG 17
dnf install -y pg_orphaned_16 # PG 16
dnf install -y pg_orphaned_15 # PG 15
dnf install -y pg_orphaned_14 # PG 14
apt install -y postgresql-18-pg-orphaned # PG 18
apt install -y postgresql-17-pg-orphaned # PG 17
apt install -y postgresql-16-pg-orphaned # PG 16
apt install -y postgresql-15-pg-orphaned # PG 15
apt install -y postgresql-14-pg-orphaned # PG 14
Create Extension:
CREATE EXTENSION pg_orphaned;
Usage
pg_orphaned provides functions to detect and manage orphaned data files in PostgreSQL. It handles corner cases like in-progress transactions that could cause false positives by using a dirty snapshot.
List Orphaned Files
-- List orphaned files (default: older than 1 day marked as "older")
SELECT * FROM pg_list_orphaned();
-- Custom age threshold
SELECT * FROM pg_list_orphaned('10 seconds');
SELECT * FROM pg_list_orphaned('1 minute');
Returns: dbname, path, name, size, mod_time, relfilenode, reloid, older (boolean).
Move Orphaned Files to Backup
-- Move files older than the threshold to orphaned_backup directory
SELECT pg_move_orphaned('1 minute');
List Moved Files
SELECT * FROM pg_list_orphaned_moved();
Move Files Back (if still orphaned)
SELECT pg_move_back_orphaned();
Remove Moved Files
SELECT pg_remove_moved_orphaned();
Typical Workflow
-- 1. Check for orphaned files
SELECT * FROM pg_list_orphaned('1 minute');
-- 2. Move them to backup (only those older than threshold)
SELECT pg_move_orphaned('1 minute');
-- 3. Verify what was moved
SELECT * FROM pg_list_orphaned_moved();
-- 4. After confirming, remove the backup files
SELECT pg_remove_moved_orphaned();
Note: functions operate on orphaned files for the database you are connected to. Always double-check carefully before moving or removing files.
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.