pg_easy_replicate

pg_easy_replicate is a CLI orchestrator tool that simplifies the process of setting up logical replication between two PostgreSQL databases. pg_easy_replicate also supports switchover. After the source (primary database) is fully replicated, pg_easy_replicate puts it into read-only mode and via logical replication flushes all data to the new target database. This ensures zero data loss and minimal downtime for the application. This method can be useful for performing minimal downtime (up to <1min, depending) major version upgrades between a Blue/Green PostgreSQL database setup, load testing and other similar use cases.

Battle tested in production at Tines 🚀

• Installation
• Requirements
• Limits
• Usage
• CLI
• Replicating all tables with a single group
  • Config check
  • Bootstrap
  • Bootstrap and Config Check with special user role in AWS or GCP
    • Config Check
    • Bootstrap
  • Start sync
  • DDL Changes Management
    • Listing DDL Changes
    • Applying DDL Changes
  • Stats
  • Notify
  • Performing switchover
• Replicating single database with custom tables
• Exclude tables from replication
  • Cleanup
• Switchover strategies with minimal downtime
  • Rolling restart strategy
  • DNS Failover strategy
• FAQ
  • Adding internal user to pg_hba or pgBouncer userlist
• Contributing

Add this line to your application's Gemfile:

gem "pg_easy_replicate"

And then execute:

$ bundle install

Or install it yourself as:

$ gem install pg_easy_replicate

This will include all dependencies accordingly as well. Make sure the following requirements are satisfied.

Or via Docker:

docker pull shayonj/pg_easy_replicate:latest

https://hub.docker.com/r/shayonj/pg_easy_replicate

• PostgreSQL 10 and later
• Ruby 3.0 and later
• Database users should have SUPERUSER permissions, or pass in a special user with privileges to create the needed role, schema, publication and subscription on both databases. More on --special-user-role section below.
• See more on FAQ below

All Logical Replication Restrictions apply.

Ensure SOURCE_DB_URL and TARGET_DB_URL are present as environment variables in the runtime environment.

• SOURCE_DB_URL = The database that you want to replicate FROM.
• TARGET_DB_URL = The database that you want to replicate TO.

The URL should be in postgres connection string format. Example:

$ export SOURCE_DB_URL="postgres://USERNAME:PASSWORD@localhost:5432/DATABASE_NAME"
$ export TARGET_DB_URL="postgres://USERNAME:PASSWORD@localhost:5433/DATABASE_NAME"

Optional

You can extend the default timeout by setting the following environment variable

$ export PG_EASY_REPLICATE_STATEMENT_TIMEOUT="10s" # default 5s

You can get additional debug logging by adding the following environment variable

$ export DEBUG="true"

Any pg_easy_replicate command can be run the same way with the docker image as well. As long the container is running in an environment where it has access to both the databases. Example

docker run -e SOURCE_DB_URL="postgres://USERNAME:PASSWORD@localhost:5432/DATABASE_NAME"  \
  -e TARGET_DB_URL="postgres://USERNAME:PASSWORD@localhost:5433/DATABASE_NAME" \
  -it --rm shayonj/pg_easy_replicate:latest \
  pg_easy_replicate config_check

$  pg_easy_replicate
pg_easy_replicate commands:
  pg_easy_replicate bootstrap -g, --group-name=GROUP_NAME    # Sets up temporary tables for information required during runtime
  pg_easy_replicate cleanup -g, --group-name=GROUP_NAME      # Cleans up all bootstrapped data for the respective group
  pg_easy_replicate config_check                             # Prints if source and target database have the required config
  pg_easy_replicate help [COMMAND]                           # Describe available commands or one specific command
  pg_easy_replicate start_sync -g, --group-name=GROUP_NAME   # Starts the logical replication from source database to target database provisioned in the group
  pg_easy_replicate stats  -g, --group-name=GROUP_NAME       # Prints the statistics in JSON for the group
  pg_easy_replicate notify  -g, --group-name=GROUP_NAME,  -u --url=URL_TO_NOTIFY    # Sends notifications of all stats values for a group to a specified url
  pg_easy_replicate stop_sync -g, --group-name=GROUP_NAME    # Stop the logical replication from source database to target database provisioned in the group
  pg_easy_replicate switchover  -g, --group-name=GROUP_NAME  # Puts the source database in read only mode after all the data is flushed and written
  pg_easy_replicate version                                  # Prints the version

You can create as many groups as you want for a single database. Groups are just a logical isolation of a single replication.

$ pg_easy_replicate config_check

✅ Config is looking good.

Every sync will need to be bootstrapped before you can set up the sync between two databases. Bootstrap creates a new super user to perform the orchestration required during the rest of the process. It also creates some internal metadata tables for record keeping.

$ pg_easy_replicate bootstrap --group-name database-cluster-1 --copy-schema

{"name":"pg_easy_replicate","hostname":"PKHXQVK6DW","pid":21485,"level":30,"time":"2023-06-19T15:51:11.015-04:00","v":0,"msg":"Setting up schema","version":"0.1.0"}
...

If you don't want your primary login user to have superuser privileges or you are on AWS or GCP, you will need to pass in the special user role that has the privileges to create role, schema, publication and subscription. This is required so pg_easy_replicate can create a dedicated user for replication which is granted the respective special user role to carry out its functionalities.

For AWS the special user role is rds_superuser, and for GCP it is cloudsqlsuperuser. Please refer to docs for the most up to date information.

Note: The user in the connection url must be part of the special user role being supplied.

$ pg_easy_replicate config_check --special-user-role="rds_superuser" --copy-schema

✅ Config is looking good.

$ pg_easy_replicate bootstrap --group-name database-cluster-1 --special-user-role="rds_superuser" --copy-schema

{"name":"pg_easy_replicate","hostname":"PKHXQVK6DW","pid":21485,"level":30,"time":"2023-06-19T15:51:11.015-04:00","v":0,"msg":"Setting up schema","version":"0.1.0"}
...

Once the bootstrap is complete, you can start the sync. Starting the sync sets up the publication, subscription and performs other minor housekeeping things.

NOTE: Start sync by default will drop all indices in the target database for performance reasons. And will automatically re-add the indices during switchover. It is turned on by default and you can opt out of this with --no-recreate-indices-post-copy

$ pg_easy_replicate start_sync --group-name database-cluster-1 [-d <track-ddl>]

{"name":"pg_easy_replicate","hostname":"PKHXQVK6DW","pid":22113,"level":30,"time":"2023-06-19T15:54:54.874-04:00","v":0,"msg":"Setting up publication","publication_name":"pger_publication_database_cluster_1","version":"0.1.0"}
...

pg_easy_replicate now supports tracking and applying DDL (Data Definition Language) changes between the source and target databases. To track DDLs you can pass --track-ddl to start_sync.

This feature ensures that most schema changes made to the source database tables that are being replicated during the replication process are tracked, so that you can apply them at your will before or after switchover.

To view the DDL changes that have been tracked:

$ pg_easy_replicate list_ddl_changes -g <group-name> [-l <limit>]

This command will display a list of DDL changes in JSON format;

[
  {
    "id": 1,
    "group_name": "cluster-1",
    "event_type": "ddl_command_end",
    "object_type": "table",
    "object_identity": "public.pgbench_accounts",
    "ddl_command": "ALTER TABLE public.pgbench_accounts ADD COLUMN test_column VARCHAR(255)",
    "created_at": "2024-08-31 15:42:33 UTC"
  }
]

pg_easy_replicate won't automatically apply the changes for you. To apply the tracked DDL changes to the target database:

$ pg_easy_replicate apply_ddl_change -g <group-name> [-i <change-id>]

If you specify a change ID with the -i option, only that specific change will be applied. If you don't specify an ID, you'll be prompted to apply all pending changes.

$ pg_easy_replicate apply_ddl_change -g cluster-1
The following DDL changes will be applied:
ID: 1, Type: table, Command: ALTER TABLE public.pgbench_accounts ADD COLUMN test_column VARCHAR(255)...

Do you want to apply all these changes? (y/n): y
...
All pending DDL changes applied successfully.

You can inspect or watch stats any time during the sync process. The stats give you an idea of when the sync started, current flush/write lag, how many tables are in replicating, copying or other stages, and more.

You can poll these stats to perform any other after the switchover is done. The stats include a switchover_completed_at which is updated once the switch over is complete.

$ pg_easy_replicate stats --group-name database-cluster-1

{
  "lag_stats": [
    {
      "pid": 66,
      "client_addr": "192.168.128.2",
      "user_name": "jamesbond",
      "application_name": "pger_subscription_database_cluster_1",
      "state": "streaming",
      "sync_state": "async",
      "write_lag": "0.0",
      "flush_lag": "0.0",
      "replay_lag": "0.0"
    }
  ],
  "message_lsn_receipts": [
    {
      "received_lsn": "0/1674688",
      "last_msg_send_time": "2023-06-19 19:56:35 UTC",
      "last_msg_receipt_time": "2023-06-19 19:56:35 UTC",
      "latest_end_lsn": "0/1674688",
      "latest_end_time": "2023-06-19 19:56:35 UTC"
    }
  ],
  "sync_started_at": "2023-06-19 19:54:54 UTC",
  "sync_failed_at": null,
  "switchover_completed_at": null

  ....

You can send stats to an endpoint on an interval using notify. This can be configured to receieve the stats to this url on a frequency (default 10s). A timeout can also be configured for the request to the endpoint (default 10s). This gives you greater control over processing different events in the replication cycle in your workflow.

$ pg_easy_replicate notify --group-name database-cluster-1 --url https://example.com/webhook --frequency 10 --timeout 10

pg_easy_replicate doesn't kick off the switchover on its own. When you start the sync via start_sync, it starts the replication between the two databases. Once you have had the time to monitor stats and any other key metrics, you can kick off the switchover.

switchover will wait until all tables in the group are replicating and the delta for lag is <200kb (by calculating the pg_wal_lsn_diff between sent_lsn and write_lsn) and then perform the switch.

Additionally, switchover will take care of re-adding the indices (it had removed in start_sync) in the target database before hand. Depending on the size of the tables, the recreation of indexes (which happens CONCURRENTLY) may take a while. See start_sync for more details.

The switch is made by putting the user on the source database in READ ONLY mode, so that it is not accepting any more writes and waits for the flush lag to be 0. It’s up to the user to kick off a rolling restart of their application containers or failover DNS (more on these below in strategies) after the switchover is complete, so that your application isn't sending any read + write requests to the old/source database.

$ pg_easy_replicate switchover  --group-name database-cluster-1

{"name":"pg_easy_replicate","hostname":"PKHXQVK6DW","pid":24192,"level":30,"time":"2023-06-19T16:05:23.033-04:00","v":0,"msg":"Watching lag stats","version":"0.1.0"}
...

By default all tables are added for replication but you can create multiple groups with custom tables for the same database. Example

$ pg_easy_replicate bootstrap --group-name database-cluster-1 --copy-schema
$ pg_easy_replicate start_sync --group-name database-cluster-1 --schema-name public --tables "users,posts,events"

...

$ pg_easy_replicate bootstrap --group-name database-cluster-2 --copy-schema
$ pg_easy_replicate start_sync --group-name database-cluster-2 --schema-name public --tables "comments,views"

...
$ pg_easy_replicate switchover  --group-name database-cluster-1
$ pg_easy_replicate switchover  --group-name database-cluster-2
...

By default all tables are added for replication but you can exclude tables if necessary. Example

...
$ pg_easy_replicate bootstrap --group-name database-cluster-1 --copy-schema
$ pg_easy_replicate start_sync --group-name database-cluster-1 --schema-name public --exclude_tables "events"
...

Use cleanup if you want to remove all bootstrapped data for the specified group. Additionally you can pass -e or --everything in order to clean up all schema changes for bootstrapped tables, users and any publication/subscription data.

$ pg_easy_replicate cleanup  --group-name database-cluster-1 --everything

{"name":"pg_easy_replicate","hostname":"PKHXQVK6DW","pid":24192,"level":30,"time":"2023-06-19T16:05:23.033-04:00","v":0,"msg":"Dropping groups table","version":"0.1.0"}

{"name":"pg_easy_replicate","hostname":"PKHXQVK6DW","pid":24192,"level":30,"time":"2023-06-19T16:05:23.033-04:00","v":0,"msg":"Dropping schema","version":"0.1.0"}
...

For minimal downtime, it'd be best to watch/tail the stats and wait until switchover_completed_at is updated with a timestamp. Once that happens you can perform any of the following strategies. Note: These are just suggestions and pg_easy_replicate doesn't provide any functionalities for this.

In this strategy, you have a change ready to go which instructs your application to start connecting to the new database. Either using an environment variable or similar. Depending on the application type, it may or may not require a rolling restart.

Next, you can set up a program that watches the stats and waits until switchover_completed_at is reporting as true. Once that happens it kicks off a rolling restart of your application containers so they can start making connections to the DNS of the new database.

In this strategy, you have a weighted based DNS system (example AWS Route53 weighted records) where 100% of traffic goes to a primary origin and 0% to a secondary origin. The primary origin here is the DNS host for your source database and secondary origin is the DNS host for your target database. You can set up your application ahead of time to interact with the database using DNS from the weighted group.

Next, you can set up a program that watches the stats and waits until switchover_completed_at is reporting as true. Once that happens it updates the weight in the DNS weighted group where 100% of the requests now go to the new/target database. Note: Keeping a low ttl is recommended.

pg_easy_replicate sets up a designated user for managing the replication process. In case you handle user permissions through pg_hba, it's necessary to modify this list to permit sessions from pger_su_h1a4fb. Similarly, with pgBouncer, you'll need to authorize pger_su_h1a4fb for login access by including it in the userlist.

PRs most welcome. You can get started locally by

• docker compose down -v && docker compose up --remove-orphans --build
• Install ruby 3.3.6 using RVM (instruction)
• bundle exec rspec for specs