New server add problem

[MakarovDmitri]

Hello! There is a repository server with a web interface. I need to add a remote server and I do it using the command SELECT powa_register_server(hostname => 'someserver.com', alias => 'someserver', password => 'mypassword', extensions => '{pg_stat_kcache,pg_qualstats,pg_wait_sampling}'); The remote server experiences an authentication error against itself.

[root@someserver ~]# /usr/bin/powa-collector.py
2024-06-10 17:58:11,192 - INFO  : Starting powa-collector...
2024-06-10 17:58:11,192 - DEBUG : Connecting on repository...
2024-06-10 17:58:11,198 - DEBUG : Connected.
2024-06-10 17:58:11,200 someserver:5432 DEBUG : Creating worker someserver:5432: {'dsn': {'host': 'someserver', 'port': 5432, 'user': 'powa', 'password': 'XXX', 'dbname': 'powa'}, 'frequency': 300, 'srvid': 2}
2024-06-10 17:58:11,200 someserver:5432 INFO  : Starting worker
2024-06-10 17:58:11,200 someserver:5432 DEBUG : Connecting on repository...
2024-06-10 17:58:11,200 - INFO  : List of workers:
2024-06-10 17:58:11,201 - INFO  : someserver:5432: {'host': 'someserver', 'port': 5432, 'user': 'powa', 'password': 'XXX', 'dbname': 'powa'} (no connection to remote server)
2024-06-10 17:58:11,205 someserver:5432 DEBUG : Connected.
2024-06-10 17:58:11,207 someserver:5432 DEBUG : Connecting on remote database...
2024-06-10 17:58:11,225 someserver:5432 ERROR : Error connecting on {'host': 'someserver', 'port': 5432, 'user': 'powa', 'password': 'XXX', 'dbname': 'powa'}:
connection to server at "someserver" (10.10.10.10), port 5432 failed: FATAL:  LDAP authentication failed for user "powa"

2024-06-10 17:58:11,227 someserver:5432 DEBUG : Checking postgres and dependencies versions
2024-06-10 17:58:11,227 someserver:5432 ERROR : Could not check PoWA
2024-06-10 17:58:11,227 someserver:5432 DEBUG : Retrieved last snapshot time: Decimal('-Infinity')
2024-06-10 17:58:11,228 someserver:5432 DEBUG : Connecting on remote database...
2024-06-10 17:58:11,245 someserver:5432 ERROR : Error connecting on {'host': 'someserver', 'port': 5432, 'user': 'powa', 'password': 'XXX', 'dbname': 'powa'}:
connection to server at "someserver" (10.10.10.10), port 5432 failed: FATAL:  LDAP authentication failed for user "powa"

Also when new servers are added, they all need access to each other. It doesn't seem like it should work like that. In theory, I just have to register a new remote server on the repository and register pg_hba only between the two of them. Please confirm whether I really need access to pg_hba on all servers or only between the repository and the remote one

[rjuju]

Hi,

First I removed the password that was in the log extract. I don't know if that's your real password or not but better be safe. Note that the password is not emitted anymore in those logs, but the feature hasn't been released yet, I will try to take care of that soon.

For the rest:

The remote server experiences an authentication error against itself.

What do you mean? Is someserver the same server where powa-collector is running? In any case it's just doing a normal connection, and would fail the same way if you tried psql or any other client:

FATAL: LDAP authentication failed for user "powa"

maybe the pg_hba should be updated or something?

Also when new servers are added, they all need access to each other.

They don't. The requirement is that powa-collector should be able to access all the remote servers and the repository server. Optionally, powa-web can connect to the remote server, but that's not mandatory. The remote servers never need to access anything.

Please confirm whether I really need access to pg_hba on all servers or only between the repository and the remote one

I confirm that only the server where powa-collector is running should be able to connect everywhere.

[MakarovDmitri]

Thanks for help, we have resolved the question above. Now we have another one. Where can we find in the database the suggested index in the attached screenshot?

[rjuju]

That index doesn't exist. What happened is that a hypothetical with this definition was created using hypopg, and then a query associated with this qual was checked (using EXPLAIN) with and without the hypothetical index to check if it did help. There should be the result of that step a bit below. If you agree with the results, it's up to you to create the index.

[MakarovDmitri]

Yes, we understand that. But I would like to know in which database table the hypothetical indexes are stored. The goal is to select a list of hypothetical indexes from the database.

[rjuju]

Powa doesn't store the list of hypothetical indexes it tried, it's only output on the powa-web page when running the wizard.

[MakarovDmitri]

Ok, thank you!