"Error: Database replication for RBAC is currently down" while setting up a replica for high availability in PE 2016.5.1 to 2019.1

I'm trying to set up HA (high availability), but setting up a replica isn't working. At the top of the page in the PE console, I see: Error: Database replication for RBAC is currently down. How do I resolve this?

Error messages and logs

• In the PE console, at the top of the page: Error: Database replication for RBAC is currently down.

  Note: This issue can affect the other PE databases: (pe-activity, pe-classifier, pe-puppetdb, and pe-orchestrator).

• Errors in the PE PostgreSQL log /var/log/puppetlabs/postgresql/pgstartup.log similar to:

  ERROR: subscriber s60dd73aa6f16b0 initialization failed during nonrecoverable step (c), please try the setup again

You might also see the following:

• Errors in the PE PostgreSQL log /var/log/puppetlabs/postgresql/pgstartup.log at the time the replica was provisioned similar to:

  ERROR: could not create unique index "tokens_id_key" DETAIL: Key (id)=(b6eccc79-d3c4-4210-8ae5-7f7dd8ccc12a) is duplicated

• Errors in the console services log /var/log/puppetlabs/console-services.log similar to:

  org.postgresql.util.PSQLException: ERROR: function show_subscription_status() does not exist

Version and installation information

PE version: 2016.5.1 to 2019.1
OS: RedHat 7, CentOS 7, Ubuntu 16.04
Installation type: monolithic

Solution

Initial replication of databases by the pglogical extension fails when two database rows have the same primary key.

In PE 2017.3.2 to 2019.1

To resolve the issue, reinitialize the replica using instructions in our documentation.

In PE 2016.5.1 to 2017.3.1

To resolve the issue, drop the affected database and run Puppet to recreate the database and re-establish the replication.

Note: These steps fix the issue for the RBAC database (pe-rbac). If the issue affects a different PE database (pe-activity, pe-classifier, pe-puppetdb, or pe-orchestrator), replace pe-rbac with the affected database in the following steps.

On the replica:

1. Stop the console service: puppet resource service pe-console-services ensure=stopped

2. Open psql: sudo -u pe-postgres /opt/puppetlabs/server/bin/psql

3. Connect to the RBAC database: \c pe-rbac

4. Find the subscription name causing the issue: table pglogical.subscription;

5. pglogical is running in the background and has a lock on the database. To remove the extension, run:

   select pglogical.drop_subscription(<SUBSCRIPTION NAME FROM STEP FOUR IN SINGLE QUOTES>, true);

   Then run:

   drop extension pglogical cascade;

6. Exit psql: \q

7. Restart PE PostgreSQL: service pe-postgresql restart

8. Open psql: sudo -u pe-postgres /opt/puppetlabs/server/bin/psql

9. Drop the affected database: drop database "pe-rbac";

10. Exit psql: \q

11. Run Puppet: puppet agent -t