Verifying local development logical replication works and then testing the replication settings in the UI for both remote and global

readme.md

1. Determine your data directory:

psql -U root vmdb_development -c "show data_directory;"

2. Edit: DATA_DIRECTORY_FROM_STEP1/pg_hba.conf

Ensure this line is added:

local   replication     all                                     trust

3. Edit: DATA_DIRECTORY_FROM_STEP1/postgresql.conf

Ensure these values are set:

wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10

4. Save both files and restart postgresql or reboot your machine.

5. Try the automated setup of logical replication on manageiq master branch. This will exercise everything and provide psql commands to verify things.

bundle exec rake development:replication:setup

This will create 3 databases:

development_replication_99
development_replication_1
development_replication_2

99 is global and 1 and 2 are "remote" regions. It takes a few minutes, but when it's done, it will verify you can run replication locally. When it's done, you should verify you have all 3 regions represented as it will instruct you:

Local replication is setup... try checking for users in all regions: psql postgres://root:smartvm@localhost:5432/development_replication_99 -c "SELECT id FROM users;"

% psql postgres://root:smartvm@localhost:5432/development_replication_99 -c "SELECT id FROM users;"
       id
----------------
 99000000000001
  1000000000001
  2000000000001
(3 rows)

6. Now that you know it works, blow away that setup

bundle exec rake development:replication:teardown

7. Let's setup a second database for you to try to use as a remote or global:

NOTE: Whenever you switch database.yml settings, you MUST kill your rails server and rails console or other workers. simulate_queue_worker started with one database configuration will not update itself when you change the configuration. You must exit and start it again.

8. If you want to use your normal db as the global, you need to setup a remote:

From manageiq directory: (make sure you pick a region number (1 below) that is different than your region number)

bundle exec rails r "TaskHelpers::Development::Replication.send(:setup_remote, 1)"

At this point, start your local rails server and you should be able to add a subscription to this remote region using DATABASE_URL information in the connection string.

Settings => Application Settings => Your region column in the tree Go to replication tab and choose global. Then, add the remote with that connection information outputted above.

Make sure you have other workers running or your simulate_queue_worker is running in rails console.

After initiating it in the UI, the background worker will do the work and replication should be done quickly.

If you set it up correctly, you can now check your rails server database and see the region 1 user added:

% psql postgres://root:smartvm@localhost:5432/vmdb_development -c "SELECT id, name FROM users;"
      id       |     name
---------------+---------------
             1 | Administrator
 1000000000001 | Administrator

When you're done, you can remove your subscription to this database through the UI. Again, make sure a worker or simulate_queue_worker is running.

This will drop the subscription and remove any data from the remote region in your local database.

Then, drop that remote region setup:

bundle exec rake development:replication:teardown

9. To add a new global database, you'll need to configure your normal rails server as a remote in the UI.

Settings => Application Settings => Your region column in the tree Go to replication tab and choose remote.

Then, you'll need to create the global database:

bundle exec rails r "TaskHelpers::Development::Replication.send(:create_region, 99)"
bundle exec rails db:seed

Then, you'll need to switch your database.yml to this new region. This prior command will print the database URL when it finishes, it should be: development_replication_99.

Then, you'll need to rails your rails server with this global region. Don't forget to restart your rails console running simulate_queue_worker.

Settings => Application Settings => region 99 column in the tree Go to replication tab and choose global. Then, add the remote with your "normal" database connection information.

After you validate and save, a background worker (simulate_queue_worker) will get things started. You can revisit the page and see it gets to replicating status.

At that point, you'll see that your users have been replicated to the new global:

% psql postgres://root:smartvm@localhost:5432/development_replication_99 -c "SELECT id, name FROM users;"
       id       |     name
----------------+---------------
 99000000000001 | Administrator
              1 | Administrator

Make sure you switch back to the normal database in config/database.yml.

Start up your rails server and remove the setting for "remote".

Settings => Application Settings => Your region column in the tree Go to replication tab and choose none and save with a background process running.

When you're done, blow away this global region and the subscription to your database: bundle exec rake development:replication:teardown