Pigsty, FOSS Postgres RDS

FerretDB is an open-source MongoDB wire protocol compatible middleware that allows you to use PostgreSQL as a drop-in replacement for MongoDB. It enables applications that rely on MongoDB's wire protocol to work seamlessly with PostgreSQL, providing a bridge between the two databases.

To enable FerretDB, you'll need the FerretDB patched documentdb extension, which is also available in the Pigsty repository. The latest combo is FerretDB 2.4 and DocumentDB 0.105.0.

Get Started

Use Pigsty’s standard installation process with the mongo configuration template.

./configure -c mongo    # Use FerretDB / DocumentDB config template
./install.yml           # Install, for production deployment please modify passwords in pigsty.yml first

For production deployment, please ensure to modify the password parameters in the pigsty.yml configuration file before running the installation playbook.

Configuration

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: mongod      ,password: DBUser.Mongo  ,pgbouncer: true ,roles: [dbrole_admin   ] ,comment: ferretdb super user ,superuser: true }
      - { name: dbuser_meta ,password: DBUser.Meta   ,pgbouncer: true ,roles: [dbrole_admin   ] ,comment: pigsty admin user }
      - { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer  }
    pg_databases:
      - {name: meta, owner: mongod ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [ documentdb, postgis, vector, pg_cron, rum ]}
    pg_hba_rules:
      - { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
      - { user: mongod      , db: all ,addr: world ,auth: pwd ,title: 'mongodb password access from everywhere' }
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # DocumentDB Settings
    pg_extensions: [ documentdb, citus, postgis, pgvector, pg_cron, rum ]
    pg_libs: 'pg_documentdb, pg_documentdb_core, pg_cron, pg_stat_statements, auto_explain'  # add timescaledb to shared_preload_libraries
    pg_parameters: { cron.database_name: meta }

Usage

Check the FERRET docs for the details.

Install Client Tools

You can use MongoDB’s command-line tool MongoSH to access FerretDB.

Use the pig command to add MongoDB repository, then install mongosh using yum or apt:

pig repo add mongo -u
yum install mongodb-mongosh
apt install mongodb-mongosh

Connect to FerretDB

You can access FerretDB using MongoDB connection strings with any MongoDB driver in any language. Here’s an example using the mongosh CLI tool:

$ mongosh
Current Mongosh Log ID:	67ba8c1fe551f042bf51e943
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.4.0
Using MongoDB:		7.0.77
Using Mongosh:		2.4.0

For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/

test>

Authentication

You can log in with different users. See FerretDB: Authentication for details.

mongosh 'mongodb://dbuser_meta:DBUser.Meta@10.10.10.10:27017/meta'      # Business admin user
mongosh 'mongodb://dbuser_view:DBUser.Viewer@10.10.10.10:27017/meta'    # Read-only user

Quick Start

You can connect to FerretDB and use it as if it were a MongoDB cluster.

$ mongosh 'mongodb://dbuser_meta:DBUser.Meta@10.10.10.10:27017/meta'

MongoDB commands are translated to SQL and executed in the underlying PostgreSQL:

use test                            // CREATE SCHEMA test;
db.dropDatabase();                  // DROP SCHEMA test;
db.createCollection('posts');       // CREATE TABLE posts(_data JSONB,...)
db.posts.insertOne({                // INSERT INTO posts VALUES(...);
    title: 'Post One',body: 'Body of post one',category: 'News',tags: ['news', 'events'],
    user: {name: 'John Doe',status: 'author'},date: Date()}
);
db.posts.find().limit(2).pretty();  // SELECT * FROM posts LIMIT 2;
db.posts.createIndex({ title: 1 })  // CREATE INDEX ON posts(_data->>'title');

If you’re not familiar with MongoDB, here’s a quick tutorial that works with FerretDB: Perform CRUD Operations with MongoDB Shell

To generate sample workload, you can use this simple test script with mongosh:

cat > benchmark.js <<'EOF'
const coll = "testColl";
const numDocs = 1000;

for (let i = 0; i < numDocs; i++) {  // insert
  db.getCollection(coll).insertOne({ num: i, name: "MongoDB Benchmark Test" });
}

for (let i = 0; i < numDocs; i++) {  // select
  db.getCollection(coll).find({ num: i });
}

for (let i = 0; i < numDocs; i++) {  // update
  db.getCollection(coll).updateOne({ num: i }, { $set: { name: "Updated" } });
}

for (let i = 0; i < numDocs; i++) {  // delete
  db.getCollection(coll).deleteOne({ num: i });
}
EOF

mongosh 'mongodb://dbuser_meta:DBUser.Meta@10.10.10.10:27017' benchmark.js

You can check FerretDB’s supported MongoDB commands and known differences. For basic usage, these differences are usually not significant.

FerretDB