shayonj / pg_easy_replicate Goto Github PK

Hi,

running via docker :latest and basic pg_easy_replicate config_check

/usr/local/bundle/gems/pg_easy_replicate-0.2.2/lib/pg_easy_replicate.rb:106:in assert_config: undefined method split' for nil:NilClass (NoMethodError)
if tables.split(",").size > 0 && (schema_name.nil? || schema_name == "")
^^^^^^
from /usr/local/bundle/gems/pg_easy_replicate-0.2.2/lib/pg_easy_replicate/cli.rb:28:in `config_check'

I'm running into an issue where CREATE SUBSCRIPTION times out:

PG::QueryCanceled: ERROR:  canceling statement due to statement timeout: CREATE SUBSCRIPTION "pger_subscription_database_cluster_1"

I believe setting this higher (10s) should work, but realized that this is hardcoded in

$ docker run -it --rm shayonj/pg_easy_replicate:latest pg_easy_replicate
WARNING: The requested image's platform (linux/arm64/v8) does not match the detected host platform (linux/amd64/v4) and no specific platform was requested
exec /usr/local/bundle/bin/pg_easy_replicate: exec format error

Seems like it's trying to run an arm version.

Version:
Docker version 24.0.2, build cb74dfcd85

I'm on latest Arch Linux on an intel i7 machine.

Hi

My username is the following format my-user.

Bootstrapping fails with:

{"name":"pg_easy_replicate","hostname":"3d3214eab576","pid":1,"level":50,"time":"2023-08-01T05:13:31.277+00:00","v":0,"msg":"PG::SyntaxError: ERROR:  syntax error at or near \"-\"\nLINE 2: grant usage on schema pger to my-user;\n                                            ^: create schema if not exists pger;\ngrant usage on schema pger to my-user;\ngrant create on schema pger to my-user;\n","version":"0.1.8"}
Unable to bootstrap: Unable to setup schema: PG::SyntaxError: ERROR:  syntax error at or near "-"

Protecting username with quotes should fix this :)

due to original logical replication does not support ddl replicate ,can support ddl replicate before switching ?many thanks

I am upgrading from PG12 and I initially was trying to upgrade to PG15.

The error message I am seeing:

ERROR: CREATE SUBSCRIPTION ... WITH (create_slot = true) cannot run inside a transaction block

I enabled extra logging with the DEBUG=1 environment setting, and I can see that there we are not running the Query with a transaction.

Things I tried

I tried upgrading to PG 14 and PG 13 to see if there was an issue with postgres 15 creating the slot on postgres 12.

I tried using the deferred slot approach from the postgres docs:

1. Change CREATE SUBSCRIPTION to have connect = false.
2. Run pg_create_logical_replication_slot on the source db.
3. Run the ALTER SUBSCRIPTION ... ENABLE.
4. Run the `ALTER SUBSCRIPTION .. REFRESH PUBLICATION.

The REFRESH PUBLICATION failed with the same error message.

Questions:

1. Is this an issue that my source database version is too old? If so, what version of postgres is supported at minimum?
2. Is this an issue of a long outstanding transaction on the source database? I don't think so since I would expect an issue about locks expiring if so...
3. Any other ideas to consider or try?

(sorry, pressed enter to fast)

$ pg_easy_replicate stop_sync --group-name=dedic-migrate
/var/lib/gems/3.0.0/gems/pg_easy_replicate-0.1.4/lib/pg_easy_replicate/orchestrate.rb:172:in `stop_sync': wrong number of arguments (given 1, expected 0; required keywords: target_conn_string, source_conn_string, group_name) (ArgumentError)
        from /var/lib/gems/3.0.0/gems/pg_easy_replicate-0.1.4/lib/pg_easy_replicate/cli.rb:84:in `stop_sync'
        from /var/lib/gems/3.0.0/gems/thor-1.2.2/lib/thor/command.rb:27:in `run'
        from /var/lib/gems/3.0.0/gems/thor-1.2.2/lib/thor/invocation.rb:127:in `invoke_command'
        from /var/lib/gems/3.0.0/gems/thor-1.2.2/lib/thor.rb:392:in `dispatch'
        from /var/lib/gems/3.0.0/gems/thor-1.2.2/lib/thor/base.rb:485:in `start'
        from /var/lib/gems/3.0.0/gems/pg_easy_replicate-0.1.4/bin/pg_easy_replicate:6:in `<top (required)>'
        from /usr/local/bin/pg_easy_replicate:25:in `load'
        from /usr/local/bin/pg_easy_replicate:25:in `<main>'

and, of course,

$ pg_easy_replicate stop_sync
No value provided for required options '--group-name'

After I ran pg_easy_replicate switchover, it failed on full vacuum because of timeouts. I've manually removed vacuum from code and switchover completed successfully. I think adding flag that allows to skip vacuum or increase timeouts could be helpful

This work ideally involves
Setting up databases, populating and running data with pgbench, and performing a switchover. In the end we should expect all the data to be present in pgbench. Perhaps, even track total number of dropped requests. The client should retry when conns are dropped.

I ran across this project in the ever-expanding ecosystem of Postgres tools. Given it's a replication / sync tool, I took a look at the sequence handling code, as this is an area that's often covered improperly.

I noticed in your refresh_sequences method that you're only "refreshing" sequences which are directly associated with a table column, and doing so by implicitly using the max() function on the underlying table.column.

This does not cover cases where bare sequences are referenced by application code and are thus not associated with a table at all. A safer approach would be to obtain the current values of the upstream publishing system and adjust all sequences based on those values. You can do this by leveraging the last_value column in the sequence itself.

This ensures the planner is up to date and query planning performance is impacted on the new database

Your gemspec file puts the local bin directory into the global $PATH. This makes your common console and setup scripts conflict with all the other gem developers who do not properly configure their gemspec.

If it is your intent to place the local bin directory into the global $PATH then please choose different names for console and setup.

It'd be good if config_check

• can create and drop pger_su user successfully
• continue ensure wal_level and other parameters are present
• can create groups tables using the new user
• Has replication privileges

I'm attempting to get get working on a local macOS build. On my machine the following NoMethodError is raised running when running pg_easy_replicate config_check:

❯ pg_easy_replicate config_check
/usr/local/bin/pg_dump
/Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/pg_easy_replicate-0.1.8/lib/pg_easy_replicate.rb:32:in `config': undefined method `success?' for nil:NilClass (NoMethodError)

      pg_dump_exists = $CHILD_STATUS.success?
                                    ^^^^^^^^^
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/pg_easy_replicate-0.1.8/lib/pg_easy_replicate.rb:65:in `assert_config'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/pg_easy_replicate-0.1.8/lib/pg_easy_replicate/cli.rb:20:in `config_check'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/thor-1.2.2/lib/thor/command.rb:27:in `run'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/thor-1.2.2/lib/thor/invocation.rb:127:in `invoke_command'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/thor-1.2.2/lib/thor.rb:392:in `dispatch'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/thor-1.2.2/lib/thor/base.rb:485:in `start'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/pg_easy_replicate-0.1.8/bin/pg_easy_replicate:6:in `<top (required)>'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/bin/pg_easy_replicate:25:in `load'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/bin/pg_easy_replicate:25:in `<main>'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/bin/ruby_executable_hooks:22:in `eval'
        from /Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/bin/ruby_executable_hooks:22:in `<main>'❯ pg_easy_replicate config_check
/usr/local/bin/pg_dump
/Users/thomas.mcloughlinlifelenz.com/.rvm/gems/ruby-3.1.1@apidev/gems/pg_easy_replicate-0.1.8/lib/pg_easy_replicate.rb:32:in `config': undefined method `success?' for nil:NilClass (NoMethodError)

It seems lib/pg_easy_replicate is using the global variable $CHILD_STATUS which is not defined in my environment.

Adding require 'english' manually near the top of lib/pg_easy_replicate.rb to the fixes the issue:

❯ pg_easy_replicate config_check
/usr/local/bin/pg_dump
✅ Config is looking good.

I'm using:
pg_easy_replicate version: 0.1.8
ruby version: ruby 3.1.1p18 (2022-02-18 revision 53f5fc4236) [x86_64-darwin22]

During bootstrap phase, pg_easy_replicate fails with

Unable to bootstrap: PG::ConnectionBad: connection to server at "10.7.0.10", port 25432 failed: SSL error: certificate verify failed
connection to server at "10.7.0.10", port 25432 failed: FATAL:  password authentication failed for user "pger_su"

message. That is strange, because postgres://postgres:[email protected]:25432/database credentials are used.

How can this error be fixed?

On larger databases, the initial COPY can be slow, depending on the database engine and storage type this behavior may not be acceptable. Part of the reason why COPY can be slow is because each batched write id updating the index. So before creating the subscription we can capture all indices in groups table, drop it and create the subscription. Once all tables have replicating stage, we can re-add the indices.

Currently, stop_sync drops the pub/sub, rather than pausing it. It would be preferable to have this command pause replication (disable pub/sub), rather than reset it. Perhaps dropping pub/sub could be moved to the cleanup command.

Then, start_sync can resume the existing pub/sub or create it.

It'd be nice if as part of bootstrap the schema is copied to the new DB. Maybe using pg_dump or something similar.

Hi! I am attempting to use this tool on a replication setup. It has worked fine for one database, but another has been nothing but trouble. I have created new superusers on each side, I have confirmed that I can login with psql to each of the URL setups on the same system as the pg_easy_replicate install. But this particular configuration always returns: Unable to check config: Unable to check superuser conditions: PG::ConnectionBad: FATAL: password authentication failed for user $USERNAME

Is there something I'm doing wrong in the setup? Thanks for your time.

It'd be great to start feeding all the writes from the target database to the source database after switchover. In case the application requires a rollback. The replication can be maintained post switchover with bi-directional replication.

This will require re-plumbing the concept of source and target database in the code and always passing the conn strings as input to the various operations

Hi, I was wondering if support for azure postgres (Flexible Server) could also be added?

On Cloud SQL (GCP). The only super user is user cloudsqladmin.
It is not possible to log in as cloudsqladmin.

Problem is pg_easy_replicate then fails with:
User on source database should be a superuser

Superuser privileges should not be needed, as described here: https://cloud.google.com/sql/docs/postgres/replication/configure-logical-replication

All you need is REPLICATION.