Data replication / subscription disappear after configuration without error

[jf-baltazar]

Hello,

I'm currently setting up manageiq with several servers, one of them have to be configured to get data from others, i'm trying to configure data replication, others are set as remote.
First I try to have one subscription working.
I fail to configure the server to be configured to fetch data. I set data replication as global, try to add 1 server as subscription but task is defined as "successful" unfortunately when i go back to data replication tab, my subscription is not there. no log with error regarding subscription but log with success there.
I didn't find clear documentation about this. do you have more information about how to set subscription.
fqdn/ip is remote database server or remote manageiq server ?
username/password is database server password, ssh password of remote manageiq server or manageiq account on remote server ?
the kind of network flow ? https from Global to remote ?
Some prerequisite to have it working ?
A set data collection roles on my servers

Regards

[Fryguy]

hi @jf-baltazar ...I'd like to clarify - Are you using replication for a multi-region setup, or for HA, or for some other specific purpose? I'm assuming a multi-region setup, but it's hard to tell. If you have any error logs or screenshots or something that would be helpful. cc @jrafanie

[jrafanie]

I assume this is using publications and subscriptions for multi-region setup.

@jf-baltazar can you provide what version you're using?
Also, if you look at the postgresql log, you may see some errors. Please provide more information and if you don't see anything there, you can try looking at the evm.log and production.log.

If you're using an appliance that wasn't already configured for region replication, you may have to modify your postgresql.conf, setting at least these values:

wal_level = 'logical'
max_worker_processes = 10

We also have two other settings we use elsewhere:

max_replication_slots = 10
max_wal_senders = 10

Seen here: https://github.com/ManageIQ/pg-logical_replication/blob/14b3869ec87c2316f29bf90986a50b70b8e24f8e/.github/ci/postgresql-cfg/01_overrides.conf#L1-L4

Appliances in the past few years have logical configured along with max_worker_processes:
https://github.com/ManageIQ/manageiq-appliance/blob/d023fc0b1b425f2038cf5aa7daecc21d7c37af3e/COPY/etc/manageiq/postgresql.conf.d/01_miq_overrides.conf#L30-L36

[jf-baltazar]

Hello,

I'm using morphy-1 release.
I have a region 99 and from it I want to get data from our region 0 and 1 in order to produce reports with region 99. But i don't really understand how to setup it using documentation.

Regards,

[jrafanie]

You'll need to configure 3 appliances. 1 as region 99, 1 as 0 and 1 as 1.

Once those are configured in appliance console with the right regions, you'll need to configure the publication databases (region 1 and 0). I believe it's in Settings -> Application Settings. In the settings accordion, click the region (1/0) you want to configure. Click the replication tab. In there, you'll select type: "remote" and save. do this for both 1 and 0.

Then, in region 99, you'll need to do the same but select "global". Then, you'll need to add subscriptions for each of the regions you want to replicate to the 99 region, so the database connection details for region 0 and 1. Then, save.

Of course, before you do that, you'll need to follow the configuration I mentioned above to ensure you have "logical" and the max processes at the bare minimum otherwise, it will try to start processes and exit because each connection to the remote databases will consume a process. See #22276 (reply in thread)

If you did all of that correctly, you should start seeing the admin users from the different regions replicated to the 99 region users table.

[jf-baltazar]

Hello,

We reconfigured our database.

When I add connection details in a new subscription on region 99 to connect to the region 0 db.
The validate button reply :

Subscription Credentials validated successfully

So I add it then save, unfortunately when I refresh the page it disapear and "No replication role has been set" message is present:(

But in task I have :
Finished | Task completed successfully | Save subscriptions for global region
Note : If i use wrong credential, message is "Failed to save subscription...." in tasks

Of course nothing is replicated on region 99.

nothing relevant in logs found.

Region 0 is properly configured as remote, all appliance has been restarted too after DB reconfiguration.

Did you already seen this issue ?

[jrafanie]

That error comes from here.

The UI thinks you've not set the replication types to remote and global as described in #22276 (reply in thread). Try setting the replication types to remote for region 0, 1 and global for 99 as described above. Then, try again.

[jf-baltazar]

Currently I tried only with region 0 and region 99, i just tried to set as remote region 1
region 0 set as remote : OK
region 1 set as remote : replication save initiated and in task : task completed succesfully. But when I come back to repliacation tab, configuration is set again to None
region 99 : same thing, no error and conf is blocked on None

between region 0 and region 1 my serveurs have the same server roles

I really don't understand why it fail to set Region 1 as remote with success message. (And region 99 as global)

All server are installed from same ova

[jrafanie]

You'll have to look at the logs in the failing region 1 and 99. Try /var/www/miq/vmdb/log/evm.log on older appliances and journalctl for evmserverd on newer ones. Good luck! Let me know if you find any errors. I don't have an environment to test it but maybe I'll be able to understand any errors.

Note, morphy uses components that have been upgraded on master/oparin. We've upgraded to rails 6.1, ruby 2.7, and postgresql 13 subsequently. Note, ruby 2.6 and postgresql 10, which are what is on morphy, are both end of life.

[jf-baltazar]

My cmpadmin user has Create DB and Create Role permissions on my external DB. If I add superuser permission it work. Is superuser permission mandatory ?

[jrafanie]

Yes! Sorry, I missed this update. To create a subscription you need to be a superuser. I believe this is because it's a postgresql cluster wide object. See #21372 and for PG 13 https://www.postgresql.org/docs/13/logical-replication-security.html

In order to be able to copy the initial table data, the role used for the replication connection must have the SELECT privilege on a published table (or be a superuser).

To create a publication, the user must have the CREATE privilege in the database.

To add tables to a publication, the user must have ownership rights on the table. To create a publication that publishes all tables automatically, the user must be a superuser.

To create a subscription, the user must be a superuser.

[jf-baltazar]

The guy who installed our manageiq used PG10. documentation talk about PGSQL 10, https://www.manageiq.org/docs/reference/latest/installing_on_red_hat_virtualization/ , a v13 migration may help ?

[jf-baltazar]

Oh I forgot to update, I still have replication issue :
Enabling Remote is now OK.
But global is KO :
Failed to save subscription to XXXX.local: ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host "Y.Y.Y.Y", user "cmpadmin", SSL on FATAL: no pg_hba.conf entry for replication connection from host "Y.Y.Y.Y", user "cmpadmin", SSL off

Our pg_hba.conf is OK with replication auth on IP. and restarted. Seem to be on psql side.

[jrafanie]

Is it still a problem? Did you add the missing line to the pg_hba.conf? It's saying your host is not allowed for the replication keyword (instead of a database) with that user.

You likely don't have remote connections enabled. You'll need to edit your pg_hba.conf found in the data directory. psql -U postres postgres. Then show DATA_DIRECTORY;. That will show the data directory.

host   replication  cmpadmin  ADDRESS  METHOD

or maybe this if using SSL where you setup certificates on each:

hostssl   replication  cmpadmin ADDRESS  METHOD

Replace address with the IP address of the other database, all, etc.
Replace METHOD with trust, md5, etc.

See: https://www.postgresql.org/docs/13/auth-pg-hba-conf.html

Change these settings based on your security situation and how you desire to run.

[jrafanie]

The guy who installed our manageiq used PG10. documentation talk about PGSQL 10, manageiq.org/docs/reference/latest/installing_on_red_hat_virtualization , a v13 migration may help ?

Postgresql 10 is end of life. I suggest you upgrade your manageiq to a version with postgresql 13, oparin. There are prior suggestions around this upgrade here and @tjyang wrote it up in https://docs.google.com/document/d/1BJL1NG3Htvu8SIRakFlFZvtbhwXU2EPBfJ34AOsrpDk/edit?usp=sharing.

Make sure you have backups of your appliances and database before all upgrades.

[jf-baltazar]

It now work ! What i had to do :

cmpadmin user as superadmin on PG database.

in postgresql.conf :
wal_level = logical
max_wal_sender = 10
max_replication_slots = 10
max_worker_processes = 10

in pg_hba.conf :
host replication cmpadmin IP_SERVER/32 md5
host vmdb_production cmpadmin IP_SERVER/32 md5