Problem: We have datacenters with upwards of 25k instances each. We recently ran into an issue where Foreman couldn’t add data to the database (PostgreSQL 9.2) because we exhausted the digits for the primary key in the logs table. We had to change the data type to BIGINT from INT in order to keep computing.
Foreman and Proxy versions: 1.16 - Yes, I know it’s old, we’re working on it.
Distribution and version: OEL7.5
Other relevant data:
My questions are:
When we do the upgrades to newer Foreman versions, will having that table be a different data type cause problems with schema updates? If so, which files would I have to modify (change INT to BIGINT) to not cause schema update problems?
Has the data type for some of these tables been changed to BIGINT in newer versions of Foreman?
If the problem isn’t the data type, but something else causing some sort of cleaning/maintenance to work, what would it be so I can start looking into it?
I know this is not a final answer to your questions, but I just took a look at our production database (currently running 1.22), and I can confirm that the logs table’s id field (the pkey) has been changed to bigint somewhere on the way.
For the questions regarding upgrade problems, someone working on the database migrations themselfes is probably more suited to answer those.
the major issue here is not BIG vs BIGINT, but the way we store reports. These should have been stored in text/blob field rather than per-line which is extremely slow and it actually put huge stress on the SQL database. I’ve analyzed this here:
We haven’t implemented the changes yet tho. In the meantime, we bumped the primary keys for the most exposed tables (logs, fact_values) sometime in 2017:
You can easily backport this change into your Foreman without upgrading it up for now, just copy the file and perform migration. This should not break your migration when you choose to upgrade later on.
