PgLogicalReplicator

PgLogicalReplicator is a Ruby gem that helps set up and manage logical replication for PostgreSQL databases. This gem was created so you can migrate databases with near 0 downtime. It uses the PG snapshot ability to create a backup of the current db and restores that backup to the target db. After the restore is done replication begins where the backup left off. This allows you to have a near 0 downtime migration.

Installation

Add this line to your application's Gemfile:

gem 'pg_logical_replicator'

And then execute:

$ bundle install

Or install it yourself as:

$ gem install pg_logical_replicator

Usage

PgLogicalReplicator provides a command-line interface (CLI) to set up and stop logical replication between PostgreSQL databases.

Creating a Replication User on the Target Database

Before setting up replication, you need to create a replication user on the target database. This user will have specific privileges to ensure that triggers and foreign keys are not enforced during replication. Use the following SQL commands to create and configure this user:

CREATE USER temp_replica_user WITH PASSWORD 'your_password';

GRANT ALL PRIVILEGES ON DATABASE your_target_database TO temp_replica_user;
GRANT ALL ON SCHEMA public TO temp_replica_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO temp_replica_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO temp_replica_user;

ALTER ROLE temp_replica_user SET session_replication_role = 'replica';

Transferring Schema

To transfer the schema from the source to the target database, use the following command:

pg_logical_replicator transfer_schema [OPTIONS]

• --source_host SOURCE_HOST : Source database host (required)
• --source_port SOURCE_PORT : Source database port (default: 5432)
• --source_database SOURCE_DATABASE : Source database name (required)
• --target_host TARGET_HOST : Target database host (required)
• --target_port TARGET_PORT : Target database port (default: 5432)
• --target_database TARGET_DATABASE : Target database name (required)
• --source_username SOURCE_USERNAME : Source database username (required)
• --source_password SOURCE_PASSWORD : Source database password (required)
• --target_username TARGET_USERNAME : Target database username (required)
• --target_password TARGET_PASSWORD : Target database password (required)

pg_logical_replicator transfer_schema \
  --source_host localhost \
  --source_database mydb \
  --source_username myuser \
  --source_password mypass \
  --target_host remotehost \
  --target_database targetdb \
  --target_username targetuser \
  --target_password targetpass

This command will transfer the schema from the source database to the target database.

Setting Up Replication

To set up logical replication, use the following command:

pg_logical_replicator setup [OPTIONS]

Options:

• --source_host SOURCE_HOST : Source database host (required)
• --source_port SOURCE_PORT : Source database port (default: 5432)
• --source_database SOURCE_DATABASE : Source database name (required)
• --target_host TARGET_HOST : Target database host (required)
• --target_port TARGET_PORT : Target database port (default: 5432)
• --target_database TARGET_DATABASE : Target database name (defaults to source database name)
• --source_username SOURCE_USERNAME : Source database username (required)
• --source_password SOURCE_PASSWORD : Source database password (required)
• --target_username TARGET_USERNAME : Target database username (defaults to source username)
• --target_password TARGET_PASSWORD : Target database password (defaults to source password)
• --target_rep_username REP_USERNAME : Target replication username (defaults to target username)
• --target_rep_password REP_PASSWORD : Target replication password (defaults to target password)
• --num_slots NUM_SLOTS : Number of replication slots (default: 10)
• --groups GROUP1,GROUP2,... : Groups to run (comma-separated list of numbers)

Example:

pg_logical_replicator setup \
  --source_host localhost \
  --source_database mydb \
  --source_username myuser \
  --source_password mypass \
  --target_host remotehost \
  --num_slots 5 \
  --groups 1,3,5

This command will set up logical replication for the specified groups of tables between the source and target databases.

Stopping Replication

To stop all replication, use the following command:

pg_logical_replicator stop_replication [OPTIONS]

Options:

• --source_host SOURCE_HOST : Source database host (required)
• --source_port SOURCE_PORT : Source database port (default: 5432)
• --source_database SOURCE_DATABASE : Source database name (required)
• --target_host TARGET_HOST : Target database host (required)
• --target_port TARGET_PORT : Target database port (default: 5432)
• --target_database TARGET_DATABASE : Target database name (defaults to source database name)
• --source_username SOURCE_USERNAME : Source database username (required)
• --source_password SOURCE_PASSWORD : Source database password (required)
• --target_username TARGET_USERNAME : Target database username (defaults to source username)
• --target_password TARGET_PASSWORD : Target database password (defaults to source password)

Example:

pg_logical_replicator stop_replication \
  --source_host sourcehost \
  --source_database mydb \
  --source_username sourceuser \
  --source_password sourcepass \
  --target_host targethost

This command will drop all publications on the source database and all subscriptions on the target database, effectively stopping all logical replication.

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and the created tag, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/yourusername/pg_logical_replicator.

License

The gem is available as open source under the terms of the MIT License.