Home

Welcome to the pg2mongo wiki!

Replicate one single PostgreSQL instance

To replicate PostgreSQL database using the extension Logical Decoding Output Plugin wal2mongo in MongoDB, build on postgres container, i.e. pg1, and then build one mongo container, i.e. mongo following below commands.

• build p2m-network

$ docker network create --driver bridge p2m-network

• build volumes for postgres and mongo containers

$ docker volume create pg1data
$ docker volume create mgdata

• build containers pg and mongo, bind to p2m-network, and map the corresponding volume.

$ docker run --name pg1 --network p2m-network -v pg1data:/var/lib/pgsql/12/data/ -d postgres:12.2
$ docker run --name mongo --network p2m-network -v mgdata:/data/db -d mongo:4.2.5

• login mongo and setup a replication slot, then start to steam the changes

$ docker exec -it mongo bash
[root@90294ef67a55 /]# pg_recvlogical -d wal2mongo -h pg1 -U postgres --slot w2m_slot1 --create-slot --plugin=wal2mongo 
[root@90294ef67a55 /]# pg_recvlogical -d wal2mongo -h pg1 -U postgres --slot w2m_slot1 --start -f -

• login pg1 and create a table and perform

$ docker exec -it pg1 bash
wal2mongo=# CREATE TABLE tbl_test (id SERIAL PRIMARY KEY, data TEXT);
wal2mongo=# ALTER TABLE tbl_test REPLICA IDENTITY FULL;

wal2mongo=# INSERT INTO tbl_test (data) VALUES ('Hello wal2mongo');
wal2mongo=# UPDATE tbl_test SET data = 'Hello, \`wal2mongo!\`' WHERE id = 1;
wal2mongo=# DELETE FROM tbl_test WHERE id = 1;

• back to mongo console an the changes should be something like below

use mycluster_wal2mongo_w2m_slot1;
db.tbl_test.insertOne( { id: NumberInt("1"), data:"Hello wal2mongo" } );
use mycluster_wal2mongo_w2m_slot1;
db.tbl_test.updateOne( { id: NumberInt("1") }, { $set:  { id: NumberInt("1"), data:"Hello, \`wal2mongo!\`" } } );
use mycluster_wal2mongo_w2m_slot1;
db.tbl_test.deleteOne( { id: NumberInt("1") } );

• terminate pg_recvlogical and login mongoDB

[root@90294ef67a55 /]# mongo
MongoDB shell version v4.2.5

• copy the insert output and check result

> use mycluster_wal2mongo_w2m_slot1;
switched to db mycluster_wal2mongo_w2m_slot1
> db.tbl_test.insertOne( { id: NumberInt("1"), data:"Hello wal2mongo" } );
{
	"acknowledged" : true,
	"insertedId" : ObjectId("5e91f55b6ad977fab8aa3659")
}
> 
> db.tbl_test.find();
{ "_id" : ObjectId("5e91f55b6ad977fab8aa3659"), "id" : 1, "data" : "Hello wal2mongo" }
> 

• copy the update output and check the result

> use mycluster_wal2mongo_w2m_slot1;
switched to db mycluster_wal2mongo_w2m_slot1
> db.tbl_test.updateOne( { id: NumberInt("1") }, { $set:  { id: NumberInt("1"), data:"Hello, \`wal2mongo!\`" } } );
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> 
> db.tbl_test.find();
{ "_id" : ObjectId("5e91f55b6ad977fab8aa3659"), "id" : 1, "data" : "Hello, `wal2mongo!`" }
> 

• copy the delete output and check the result

> db.tbl_test.deleteOne( { id: NumberInt("1") } );
{ "acknowledged" : true, "deletedCount" : 1 }
> 
> db.tbl_test.find();
>