PostgreSQL + Removing old Host from DB not succeeded

mike_bronson7 — Thu, 15 Feb 2024 17:02:54 GMT

We have HDP cluster with 152 workers machines - `worker1.duplex.com` .. `worker152.duplex.com` , While all machines are installed on RHEL 7.9 version

We are trying to delete the last host - `worker152.duplex.com` from Ambari server or actually from PostgreSQL DB as the following

First we need to find the `host_id`

select host_id from hosts where host_name='worker152.duplex.com';

and host_id is:

host_id
---------
51
(1 row)

Now we are deletion this `host_id` - 51

delete from execution_command where task_id in (select task_id from host_role_command where host_id in (51));
delete from host_version where host_id in (51);
delete from host_role_command where host_id in (51);
delete from serviceconfighosts where host_id in (51);
delete from hoststate where host_id in (51);
delete from kerberos_principal_host WHERE host_id='worker152.duplex.com';
delete from hosts where host_name in ('worker152.duplex.com');
delete from alert_current where history_id in ( select alert_id from alert_history where host_name in ('worker152.duplex.com'));

Now we verify that `host_id` - 51 that represented the host - `worker152.duplex.com` isn't exists By the following verification

ambari=> select host_name, public_host_name from hosts;
host_name | public_host_name
--------------------------+--------------------------
worker1.duplex.com
.
.
.
worker151.duplex.com

As we can see above the host `worker151.duplex.com` not exist and that's fine , and indeed seems That host - `worker151.duplex.com` was deleted from PostgreSQL DB

Now we restarting the `Ambari-server` in order to take affect ( its also restart the PostgreSQL service )

ambari-server restart
Using python /usr/bin/python
Restarting ambari-server
Waiting for server stop...
Ambari Server stopped
Ambari Server running with administrator privileges.
Organizing resource files at /var/lib/ambari-server/resources...
Ambari database consistency check started...
Server PID at: /var/run/ambari-server/ambari-server.pid
Server out at: /var/log/ambari-server/ambari-server.out
Server log at: /var/log/ambari-server/ambari-server.log
Waiting for server start.........................
Server started listening on 8080

DB configs consistency check: no errors and warnings were found.

After Ambari server started , we are surprised because the `host_id` - 51 or host - `worker152.duplex.com` , is still exist as the following

ambari=> select host_name, public_host_name from hosts;
host_name | public_host_name
--------------------------+--------------------------
worker1.duplex.com
.
.
.
worker152.duplex.com

We not understand why this host back again in spite we delete this record

We also tried to delete historical data by the following but this isn't help

ambari-server db-purge-history --cluster-name hadoop7 --from-date 2024-01-01

Using python /usr/bin/python
Purge database history...
Ambari Server configured for Embedded Postgres. Confirm you have made a backup of the Ambari Server database [y/n]yes
ERROR: The database purge historical data cannot proceed while Ambari Server is running. Please shut down Ambari first.
Ambari Server 'db-purge-history' completed successfully.

1. Why host returned after `Ambari-server` restart ?

2. what is wrong with out deletion process?

PostgreSQL Version:

postgres=# SHOW server_version;
server_version
----------------
9.2.24
(1 row)

links:

https://www.andruffsolutions.com/removing-old-host-data-from-ambari-server-and-tuning-the-database/

https://community.cloudera.com/t5/Support-Questions/how-to-remove-old-registered-hosts-from-DB/m-p/217524/highlight/true

Re: PostgreSQL + Removing old Host from DB not succeeded

Rajat_710 — Mon, 22 Apr 2024 06:30:27 GMT

Hi, @mike_bronson7 did you try to delete the host from Ambari UI? if not please try that out first.

You might need to stop the ambari server first delete entries from the backed DB commit the changes then start your ambari server. But I would recommend deleting if from Ambari UI and checking.

question PostgreSQL + Removing old Host from DB not succeeded in Support Questions

PostgreSQL + Removing old Host from DB not succeeded

Re: PostgreSQL + Removing old Host from DB not succeeded