Portal Pitr: Point-in-Time Recovery for MySQL and PostgreSQL

Portal Pitr is a Point-in-Time Recovery (PITR) tool for MySQL and PostgreSQL databases. It allows you to create database snapshots, continuously replicate changes, and restore your database to a specific moment, minimizing data loss.

How it Works

Pitr uses logical replication to capture changes from your database in real-time. These changes are stored on disk, and can be used to restore the database to any point in time since the replication started.

For PostgreSQL, Pitr uses the pg_logical_replication protocol. For MySQL, it uses the binary log (binlog).

Installation

1. Clone the repository:

   git clone https://github.com/octave2000/pitr.git

2. Install the dependencies:

   bun install

Configuration

PostgreSQL

In your postgresql.conf file, you need to enable logical replication: by default those options are not enabled

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

You will also need a user with the REPLICATION privilege.

MySQL

In your my.cnf or my.ini file, you need to configure the following settings:

[mysqld]
log_bin=/var/log/mysql/mysql-bin.log
binlog_format=ROW                  # row-based events
binlog_row_image=FULL              # include full row before/after
binlog_expire_logs_seconds=604800  # e.g. 7 days retention (tune as needed)
gtid_mode=ON
enforce_gtid_consistency=ON

You will also need a user with the REPLICATION SLAVE and REPLICATION CLIENT privileges. For example:

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Usage

Starting the Server

To start the Pitr server and all the replication workers, run the following command:

bun start

This will start the following processes:

• API Server: The main server that handles API requests.
• PostgresReplication: The worker that replicates changes from PostgreSQL databases.
• mysqlConsumer: The worker that replicates changes from MySQL databases.
• ddlWatcher: The worker that watches for DDL changes in PostgreSQL databases.

Adding a Database Credential

To start replicating a database, you need to add its credentials to Pitr. You can do this by sending a POST request to the appropriate endpoint.

PostgreSQL:

curl -X POST http://localhost:3000/add-credential/postgres \
     -H "Content-Type: application/json" \
     -d '{
           "name": "my-postgres-db",
           "host": "localhost",
           "port": 5432,
           "database": "mydatabase",
           "user": "myuser",
           "password": "mypassword"
         }'

MySQL:

curl -X POST http://localhost:3000/add-credential/mysql \
     -H "Content-Type: application/json" \
     -d '{
           "name": "my-mysql-db",
           "host": "localhost",
           "port": 3306,
           "database": "mydatabase",
           "user": "repl",
           "password": "password"
         }'

Restoring a Database

To restore a database to a specific point in time, you need to send a POST request to the appropriate endpoint.

PostgreSQL:

curl -X POST http://localhost:3000/restore/postgres \
     -H "Content-Type: application/json" \
     -d '{
           "name": "my-postgres-db",
           "targetTime": "2025-10-04T10:00:00.000Z"
         }'

MySQL:

curl -X POST http://localhost:3000/restore/mysql \
     -H "Content-Type: application/json" \
     -d '{
           "name": "my-mysql-db",
           "targetTime": "2025-10-04T10:00:00.000Z"
         }'

API Endpoints

• POST /add-credential/postgres: Add a new PostgreSQL database to replicate.
• POST /restore/postgres: Restore a PostgreSQL database to a specific point in time.
• POST /add-credential/mysql: Add a new MySQL database to replicate.
• POST /restore/mysql: Restore a MySQL database to a specific point in time.