We need some considerations to have in order to change in a productive environment of APM, the server of the APM database by other with a new APM database.
CA APM 10.5.2.15 with SP2Linux server Rhel 6.4
We are changing the postgreSQL server. Since we have too many issues about the current APM database, which causes the cemdb to increase its size considerably day by day and the maintenance processes do not end properly, preventing physical space from being released from the filesystem. The objective is to start with a clean database where we ensure its initial integrity.
We want to be clear about the steps to make the change.
You say you want a clean database so it sounds like there will be no postgres cemdb backup/restore from existing APM DB/cemdb to the new one. However to minimise rework to recreate Business Transaction definitions you can take the APM DB configuration from the old system using the configexport/configimport utilities as follows:
Manage the APM Database - CA Application Performance Management - 10.5 - CA Technologies Documentation
The steps to create the new APM DB and transition the EMs would be as follows:
a. Run the 10.5.2.0 EM Installer on the new server that will host Postgres and use the "Database Only" option. That will install Postgres software and new APM DB (cemdb):
Install and Configure the APM Database - CA Application Performance Management - 10.5 - CA Technologies Documentation
b. Point all of your EMs at the new APM DB via changes to EM_HOME/config/tess-db-cfg.xml file (start with plaintext option & passwords). EMs need to be stopped of course before doing this:
NOTE: The install of the EM 10.5.2 SP2 (10.5.2.52) patch update does not update the APM DB schema so your 10.5.2 SP2 EMs will be compatible with the new 10.5.2 GA APM DB.
Hope that helps
Thank you very much Lynn for your answer, the new server is already with the clean database deployed. Now we will import the configuration from the current database.
It is normal that the process configexport.sh delays so much in its execution, takes more than two hours and still does not finish, and the output .xml already weighs 1.2GB
I had to stop the process of configexport, which came to process the ts_user_logins_map table, which in our case weighs more than 20GB, and the output xml already weighed 1.3GB. Given this situation I think it is necessary before exporting again to see the way to lower the volumeb data of this and other tables ts_user_*** that have volume of several GB.
I know it's complex to tell me more details if you do not have much context for this environment, but I will appreciate any information that allows me to know how to eliminate data from these tables and if there are, the risks that this action can produce to transmit to our client. the options.
Thank you very much.
It seems the configexport is also capturing user data?
Is the blow out in ts_user_* table sizes possibly due to inactive users and Fernando can run this SQL to check:
select count(*) from ts_users; - total usersselect count(*) from ts_users where ts_active = true; - active usersselect count(*) from ts_users where ts_active = false; - Inactive usersselect count(*) from ts_users where ts_soft_delete = true; - deleted users
A long time ago, I went through the config export and listed what it captured. It should capture EVERYTHING but the statistics tables. It is not just the definitions only
select count(*) from ts_users; --result 70644384
select count(*) from ts_users where ts_active = true; --result 57525
select count(*) from ts_users where ts_active = false; --result 70586785
select count(*) from ts_users where ts_soft_delete = true; --result 7
Looks like you have 70 million inactive users and we can supply a script utility to remove them. Please create a support case, refer to this Community thread and request the delete inactive users script for postgres for APM 10.5.2. After you run the script hopefully the related tables will much reduced in size and configexport will run more quickly/efficiently.
Open support case No.1161210
CA Technician name Christian Debernardi
some times ago I migrate/move one of our APM Environments to a new location.In the past we use a new/empty DB all the time (the Triage Map DB contain no settings or configuration data), but the DB used by the Teamcenter contain several user specific and public Perspectives, Universes and several Experience View Cards, Attribute Rules and Security Token.
Unfortunately there is no way to export all these settings, hence I spent some time to have a look to the DB initialization scripts and the DB.
In a sort summary, I use "pg_dump" to export the tables "public.appmap_settings" and "public.appmap_api_keys" in the "insert" format and insert these data into the new/clean DB used by the new environment.It is important to start (and stop) the APM environment (EM, WV, PGSQL) before you import the old data, because some settings are stored/created in an empty database only at the first startup.
This was the most easy way to get all the data back in the new environment and the used "insert" format was easy to handle, I do some manual modifications thereby. I removed the defaults are created automatic all the time the EM find a new DB, these are two Experience View Cards, eight default Perspectives and a default Universe (and I removed all the data deleted in the past, see column "deleted_at" in table "public.appmap_settings").
We got no problems, the Security Token are useable, but I find error messages like this in the EM log.
6/10/17 08:36:34.217 PM MESZ [ERROR] [btpool0-7] [Manager.AppMap] Cannot create sample for tokenId 100
But, in general it is not necessary to import the "public.appmap_api_keys" tables, you can create new keys.
This was the most easy way to move an environment.
Have a look to the post from Lynn, he add some usefull links and some more detail information.
In our case, in our client's environment we have deployed almost all the components of APM, however, our client does not use them in its entirety, for this reason, at least in a first revision, it will not be affected if some data of Settings are refreshed from scratch.
Thanks a lot for sharing your experience.
I also want to mention that this was a good discussion and would like to see more of these in the community.
Can Tech Info work with someone to add what's generally helpful from this to the doc if that content isn't there?
Let's sched 30 mins