PGFS: Using DB as FS
Recently, I received an interesting request from the Odoo community. They were grappling with a fascinating challenge: “If databases can do PITR (Point-in-Time Recovery), is there a way to roll back the file system as well?”
The Birth of “PGFS”
From a database veteran’s perspective, this is both challenging and exciting. We all know that in systems like Odoo, the most valuable asset is the core business data stored in PostgreSQL.
However, many “enterprise applications” also deal with file operations — attachments, images, documents, and the like. While these files might not be as “mission-critical” as the database, having the ability to roll back both the database and files to a specific point in time would be incredibly valuable from security, data integrity, and operational perspectives.
This led me to an intriguing thought: Could we give file systems PITR capabilities similar to databases? Traditional approaches often point to expensive and complex CDP (Continuous Data Protection) solutions, requiring specialized hardware or block-level storage logging. But I wondered: Could we solve this elegantly with open-source technology for the “rest of us”?
After much contemplation, a brilliant combination emerged: JuiceFS + PostgreSQL. By transforming PostgreSQL into a file system, all file writes would be stored in the database, sharing the same WAL logs and enabling rollback to any historical point. This might sound like science fiction, but hold on — it actually works. Let’s see how JuiceFS makes this possible.
Meet JuiceFS: When Database Becomes a File System
JuiceFS is a high-performance, cloud-native distributed file system that can mount object storage (like S3/MinIO) as a local POSIX file system. It’s incredibly lightweight to install and use, requiring just a few commands to format, mount, and start reading/writing.
For example, these commands will use SQLite as JuiceFS’s metadata store and a local path as object storage for testing:
juicefs format sqlite3:///tmp/jfs.db myjfs # Use SQLite3 for metadata, local FS for data
juicefs mount sqlite3:///tmp/jfs.db ~/jfs -d # Mount the filesystem to ~/jfs
The magic happens when you realize that JuiceFS also supports PostgreSQL as both metadata and object data storage backend! This means you can transform any PostgreSQL instance into a “file system” by simply changing JuiceFS’s backend.
So, if you have a PostgreSQL database (like one installed via Pigsty), you can spin up a “PGFS” with just a few commands:
METAURL=postgres://dbuser_meta:DBUser.Meta@:5432/meta
OPTIONS=(
--storage postgres
--bucket :5432/meta
--access-key dbuser_meta
--secret-key DBUser.Meta
${METAURL}
jfs
)
juicefs format "${OPTIONS[@]}" # Create a PG filesystem
juicefs mount ${METAURL} /data2 -d # Mount in background to /data2
juicefs bench /data2 # Test performance
juicefs umount /data2 # Unmount
Now, any data written to /data2 is actually stored in the jfs_blob table in PostgreSQL. In other words, this file system and PG database have become one!
PGFS in Action: File System PITR
Imagine we have an Odoo instance that needs to store file data in /var/lib/odoo or similar.
Traditionally, if you needed to roll back Odoo’s database, while the database could use WAL logs for point-in-time recovery, the file system would still rely on external snapshots or CDP.
But now, if we mount /var/lib/odoo to PGFS, all file system writes become database writes.
The database is no longer just storing SQL data; it’s also hosting the file system information.
This means: When performing PITR, not only does the database roll back to a specific point, but files instantly “roll back” with the database to the same moment.
Some might ask, “Can’t ZFS do snapshots too?” Yes, ZFS can create and roll back snapshots, but that’s still based on specific snapshot points. For precise rollback to a specific second or minute, you need true log-based solutions or CDP capabilities. The JuiceFS+PG combination effectively writes file operation logs into the database’s WAL, which is something PostgreSQL is naturally great at.
Let’s demonstrate this with a simple experiment. First, we’ll write timestamps to the file system while continuously inserting heartbeat records into the database:
while true; do date "+%H-%M-%S" >> /data2/ts.log; sleep 1; done
/pg/bin/pg-heartbeat # Generate database heartbeat records
tail -f /data2/ts.log
Then, let’s verify the JuiceFS table in PostgreSQL:
postgres@meta:5432/meta=# SELECT min(modified),max(modified) FROM jfs_blob;
min | max
----------------------------+----------------------------
2025-03-21 02:26:00.322397 | 2025-03-21 02:40:45.688779
When we decide to roll back to, say, one minute ago (2025-03-21 02:39:00), we just execute:
pg-pitr --time="2025-03-21 02:39:00" # Using pgbackrest to roll back to specific time, actual command:
pgbackrest --stanza=pg-meta --type=time --target='2025-03-21 02:39:00+00' restore
What? You’re asking where PITR and pgBackRest came from? Pigsty has already configured monitoring, backup, high availability, and more out of the box! You can set it up manually too, but it’s a bit more work.
Then when we check the file system logs and database heartbeat table, both have stopped at 02:39:00:
$ tail -n1 /data2/ts.log
02-38-59
$ psql -c 'select * from monitor.heartbeat'
id | ts | lsn | txid
---------+-------------------------------+-----------+------
pg-meta | 2025-03-21 02:38:59.129603+00 | 251871544 | 2546
This proves our approach works! We’ve successfully achieved FS/DB consistent PITR through PGFS!
How’s the Performance?
So we’ve got the functionality, but how does it perform?
I ran some tests on a development server with SSD using the built-in juicefs bench, and the results look promising — more than enough for applications like Odoo:
$ juicefs bench ~/jfs # Simple single-threaded performance test
BlockSize: 1.0 MiB, BigFileSize: 1.0 GiB,
SmallFileSize: 128 KiB, SmallFileCount: 100, NumThreads: 1
Time used: 42.2 s, CPU: 687.2%, Memory: 179.4 MiB
+------------------+------------------+---------------+
| ITEM | VALUE | COST |
+------------------+------------------+---------------+
| Write big file | 178.51 MiB/s | 5.74 s/file |
| Read big file | 31.69 MiB/s | 32.31 s/file |
| Write small file | 149.4 files/s | 6.70 ms/file |
| Read small file | 545.2 files/s | 1.83 ms/file |
| Stat file | 1749.7 files/s | 0.57 ms/file |
| FUSE operation | 17869 operations | 3.82 ms/op |
| Update meta | 1164 operations | 1.09 ms/op |
| Put object | 356 operations | 303.01 ms/op |
| Get object | 256 operations | 1072.82 ms/op |
| Delete object | 0 operations | 0.00 ms/op |
| Write into cache | 356 operations | 2.18 ms/op |
| Read from cache | 100 operations | 0.11 ms/op |
+------------------+------------------+---------------+
Another sample: Alibaba Cloud ESSD PL1 basic disk test results
+------------------+------------------+---------------+
| ITEM | VALUE | COST |
+------------------+------------------+---------------+
| Write big file | 18.08 MiB/s | 56.64 s/file |
| Read big file | 98.07 MiB/s | 10.44 s/file |
| Write small file | 268.1 files/s | 3.73 ms/file |
| Read small file | 1654.3 files/s | 0.60 ms/file |
| Stat file | 7465.7 files/s | 0.13 ms/file |
| FUSE operation | 17855 operations | 4.28 ms/op |
| Update meta | 1192 operations | 16.28 ms/op |
| Put object | 357 operations | 2845.34 ms/op |
| Get object | 255 operations | 327.37 ms/op |
| Delete object | 0 operations | 0.00 ms/op |
| Write into cache | 357 operations | 2.05 ms/op |
| Read from cache | 102 operations | 0.18 ms/op |
+------------------+------------------+---------------+
While the throughput might not match native file systems, it’s more than sufficient for applications with moderate file volumes and lower access frequencies. After all, using a “database as a file system” isn’t about running large-scale storage or high-concurrency writes — it’s about keeping your database and file system “in sync through time.” If it works, it works.
Completing the Vision: One-Click “Enterprise” Deployment
Now, let’s put this all together in a practical scenario — like one-click deploying an “enterprise-grade” Odoo with “automatic” CDP capabilities for files.
Pigsty provides PostgreSQL with external high availability, automatic backup, monitoring, PITR, and more. Installing it is a breeze:
curl -fsSL https://repo.pigsty.cc/get | bash; cd ~/pigsty
./bootstrap # Install Pigsty dependencies
./configure -c app/odoo # Use Odoo configuration template
./install.yml # Install Pigsty
That’s the standard Pigsty installation process. Next, we’ll use playbooks to install Docker, create the PGFS mount, and launch stateless Odoo with Docker Compose:
./docker.yml -l odoo # Install Docker module, launch Odoo stateless components
./juice.yml -l odoo # Install JuiceFS module, mount PGFS to /data2
./app.yml -l odoo # Launch Odoo stateless components, using external PG/PGFS
Yes, it’s that simple. Everything is ready, though the key lies in the configuration file.
The pigsty.yml configuration file would look something like this, with the only modification being the addition of JuiceFS configuration to mount PGFS to /data/odoo:
odoo:
hosts: { 10.10.10.10: {} }
vars:
# ./juice.yml -l odoo
juice_fsname: jfs
juice_mountpoint: /data/odoo
juice_options:
- --storage postgres
- --bucket :5432/meta
- --access-key dbuser_meta
- --secret-key DBUser.Meta
- postgres://dbuser_meta:DBUser.Meta@:5432/meta
- ${juice_fsname}
# ./app.yml -l odoo
app: odoo # specify app name to be installed (in the apps)
apps: # define all applications
odoo: # app name, should have corresponding ~/app/odoo folder
file: # optional directory to be created
- { path: /data/odoo ,state: directory, owner: 100, group: 101 }
- { path: /data/odoo/webdata ,state: directory, owner: 100, group: 101 }
- { path: /data/odoo/addons ,state: directory, owner: 100, group: 101 }
conf: # override /opt/<app>/.env config file
PG_HOST: 10.10.10.10 # postgres host
PG_PORT: 5432 # postgres port
PG_USERNAME: odoo # postgres user
PG_PASSWORD: DBUser.Odoo # postgres password
ODOO_PORT: 8069 # odoo app port
ODOO_DATA: /data/odoo/webdata # odoo webdata
ODOO_ADDONS: /data/odoo/addons # odoo plugins
ODOO_DBNAME: odoo # odoo database name
ODOO_VERSION: 18.0 # odoo image version
After this, you have an “enterprise-grade” Odoo running on the same server: backend database managed by Pigsty, file system mounted via JuiceFS, with JuiceFS’s backend connected to PostgreSQL. When a “rollback need” arises, simply perform PITR on PostgreSQL, and both files and database will “roll back to the specified moment.” This approach works equally well for similar applications like Dify, Gitlab, Gitea, MatterMost, and others.
Looking back at all this, you’ll realize: What once required expensive hardware and high-end storage solutions to achieve CDP can now be accomplished with a lightweight open-source combination. While it might have a “DIY for the rest of us” feel, it’s simple, stable, and practical enough to be worth exploring in more scenarios.