Copying of 3 huge tables getting failed while migrating to postgresDB

  • which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension)
    Sonarqube version is 9.2 ( eplus edition without support )
    postgresDB : 13.4
  • what are you trying to achieve
    Existing sonarqube is running in a VM in GCP with PostgresDB in GCP. We are moving the sonarqube to AWS EKS with a new Postgres RDS in AWS.
  • what have you tried so far to achieve this
    We have done a clean installation of sonarqube in EKS with helmchart with new DB and did a functional test.
    Then we wanted to migrate the data, which is above 1Tb, from existing DB to the new DB.
    I used the latest backup taken by the existing DB and trying to restore it in the new DB.
    I was able to copy all the tables except the following 3 tables :
    1. file_sources - 50 GB
    2. issue_changes - 887 GB
    3. live_measures - 71 GB
    As the size of these tables are huge, I am not able complete the DB restore task.
    Is there any chance that I can ignore these tables ? Or anything can be done to reduce the size ?

Regards,
Sarath

You really can’t ignore these tables.

issue_changes is huge (and with SONAR-15321 in SonarQube v9.5, we hope that overtime we new analyses the size of that table will decrease).

But it seems you’re having trouble copying fairly reasonably sized tables (50 GB, 71 GB), so I would try and figure out why you can’t restore backups of tables this size.

And, never a bad idea to run a VACUUM to make sure free space is reclaimed (maybe it will help a little).

Hello Colin,

Thanks for the reply. We managed to copy the data from old DB to the new one. But now when I try to deploy sonarqube with this data in the new DB, I am getting the below error and sonarqube will not come up.

sonarqube 2022.08.04 06:37:37 ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube                                                                                                                                           │
│ sonarqube java.lang.IllegalStateException: 2 rows retrieved for single property server.idChecksum

It looks like the new DB has the serverID from the old DB. How can I remove that and apply for a new License for this new deployment ? Please remember, we have sonarqube UI up and running with the old DB with enterpriseplus license. The above error is for the new deployment with the new DB which has the data copied from the old DB.

An error like that makes me suspicious that data was actually migrated to an empty database, rather than (perhaps) one that had already been started up against a SonarQube server. Do you see any other duplicate data in the internal_properties table?

Hello @Colin,

I tried as you suggested.
I brought up Sonarqube in the new database. Then I restored the old DB data to this new DB keeping the sonarqube running.
After the restore, I restarted sonarqube and it didnt come up with the following logs:

│ sonarqube 2022.08.08 04:59:52 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 9000                                                                                                                                                      │
│ sonarqube 2022.08.08 04:59:52 INFO  web[][o.s.s.p.DetectPluginChange] Detect plugin changes                                                                                                                                                                │
│ sonarqube 2022.08.08 04:59:52 ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube                                                                                                                                           │
│ sonarqube org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2                                                                                                                  │
│ sonarqube     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:80)                                                                                                                                                 │
│ sonarqube     at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)                                                                                                                                                                      │
│ sonarqube     at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)                                                                                                                                                     │
│ sonarqube     at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)                                                                                                                                                                         │
│ sonarqube     at com.sun.proxy.$Proxy15.selectByKey(Unknown Source)                                                                                                                                                                                        │
│ sonarqube     at org.sonar.db.property.PropertiesDao.selectGlobalProperty(PropertiesDao.java:144)                                                                                                                                                          │
│ sonarqube     at org.sonar.server.platform.serverid.ServerIdManager.readCurrentServerId(ServerIdManager.java:121)                                                                                                                                          │
│ sonarqube     at org.sonar.server.platform.serverid.ServerIdManager.start(ServerIdManager.java:66)                                                                                                                                                         │
│ sonarqube     at org.sonar.core.platform.StartableCloseableSafeLifecyleStrategy.start(StartableCloseableSafeLifecyleStrategy.java:40)                                                                                                                      │
│ sonarqube     at org.picocontainer.injectors.AbstractInjectionFactory$LifecycleAdapter.start(AbstractInjectionFactory.java:84)                                                                                                                             │
│ sonarqube     at org.picocontainer.behaviors.AbstractBehavior.start(AbstractBehavior.java:169)                                                                                                                                                             │
│ sonarqube     at org.picocontainer.behaviors.Stored$RealComponentLifecycle.start(Stored.java:132)                                                                                                                                                          │
│ sonarqube     at org.picocontainer.behaviors.Stored.start(Stored.java:110)                                                                                                                                                                                 │
│ sonarqube     at org.picocontainer.DefaultPicoContainer.potentiallyStartAdapter(DefaultPicoContainer.java:1016)                                                                                                                                            │
│ sonarqube     at org.picocontainer.DefaultPicoContainer.startAdapters(DefaultPicoContainer.java:1009)                                                                                                                                                      │
│ sonarqube     at org.picocontainer.DefaultPicoContainer.start(DefaultPicoContainer.java:767)                                                                                                                                                               │
│ sonarqube     at org.sonar.core.platform.ComponentContainer.startComponents(ComponentContainer.java:136)                                                                                                                                                   │
│ sonarqube     at org.sonar.server.platform.platformlevel.PlatformLevel.start(PlatformLevel.java:90)                                                                                                                                                        │
│ sonarqube     at org.sonar.server.platform.platformlevel.PlatformLevel3.start(PlatformLevel3.java:66)                                                                                                                                                      │
│ sonarqube     at org.sonar.server.platform.PlatformImpl.start(PlatformImpl.java:213)                                                                                                                                                                       │
│ sonarqube     at org.sonar.server.platform.PlatformImpl.startLevel34Containers(PlatformImpl.java:186)                                                                                                                                                      │
│ sonarqube     at org.sonar.server.platform.PlatformImpl$1.lambda$doRun$0(PlatformImpl.java:120)                                                                                                                                                            │
│ sonarqube     at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.runIfNotAborted(PlatformImpl.java:370)                                                                                                                                         │
│ sonarqube     at org.sonar.server.platform.PlatformImpl$1.doRun(PlatformImpl.java:120)                                                                                                                                                                     │
│ sonarqube     at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.run(PlatformImpl.java:354)                                                                                                                                                     │
│ sonarqube     at java.base/java.lang.Thread.run(Thread.java:829)                                                                                                                                                                                           │
│ sonarqube 2022.08.08 04:59:52 INFO  web[][o.s.p.ProcessEntryPoint] Hard stopping process                                                                                                                                                                   │
│ sonarqube 2022.08.08 04:59:53 INFO  app[][o.s.a.SchedulerImpl] Process[web] is stopped                                                                                                                                                                     │
│ sonarqube 2022.08.08 04:59:53 INFO  app[][o.s.a.SchedulerImpl] Process[es] is stopped                                                                                                                                                                      │
│ sonarqube 2022.08.08 04:59:53 WARN  app[][o.s.a.p.AbstractManagedProcess] Process exited with exit value [es]: 143                                                                                                                                         │
│ sonarqube 2022.08.08 04:59:53 INFO  app[][o.s.a.SchedulerImpl] SonarQube is stopped

Also I am sharing the contents of the internal_properties table

Regards,
Sarath

hi @Colin
Is there anyway to change the log level to debug or trace so that we may be able to find the query where we are getting duplicate or multiple entry instead of the expected null or 1.
We are using helm charts. So please let me know where can I add the log level in the values.yaml file.

Regards,
Sarath

Hi Sarath,

Since you are migrating Postgres to Postgres, the process should be to restore the backup to an empty schema in GCP, then start your SonarQube instance.

I’ve sent you a private message to request a file. Please respond with the information so I can review it.

Brian

hi @bcipollone,

Thanks for reply. But as I mentioned, our new DB is in AWS rds and its postgres.
Let me explain our requirement and the steps I did so far again:

  • Our new sonarqube is using helm installation in AWS EKS. The existing one is running in VM in GCP.
  • Existing sonarqube DB is in GCP. We wanted to migrate this to AWS rds.
  1. Using helm chart, we deployed sonarqube in aws eks with a fresh DB and schema in aws rds. And sonarqube came up and we used one of our staging license to do some sanity tests.
  2. Now we wanted to migrate the data in the GCP DB to this new DB. For that deleted the helm installation and deleted the new sonarqube DB in AWS.
  3. Restored the db, schema and tables from GCP DB to the new AWS DB.
    4 Deployed sonarqube using the helm deployments and given the new DB details.
    On startup, we got the error
sonarqube 2022.08.04 06:37:37 ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube                                                                                                                                           │
│ sonarqube java.lang.IllegalStateException: 2 rows retrieved for single property server.idChecksum
  1. Then as your colleague @Colin suggested, I deleted the restored DB, schema and tables and again did the step 1 which is the fresh installation without any content in the DB. Sonarqube came up but it again asked for new License and the serverID is changed. And then as @Colin said, I restored the old DB over this freshly created DB. And then I restarted sonarqube and then I was hit with the latest error mentioned in the thread. Also sonarqube url is not accessible. returning 503.
sonarqube 2022.08.08 04:59:52 ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube                                                                                                                                           │
│ sonarqube org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2                                                                                                                  │

And regarding the system info you asked privately, I cannot provide it now as sonarqube is returning 503.

Hi Sarath,

Thank you for the background.

The request for the file was from the SonarQube server as it exists in GCP, not the new one in AWS. Please provide this in response to my private message.

The correct procedure here is to restore the database to an empty schema. If you start SonarQube, the schema will get populated so you should not start it up after you empty it and SonarQube must be shut down while the restore is running. Also, make sure you are restoring all tables and indexes.

I suggest you empty the schema and go through the restore without starting the EKS SonarQube. If, after the restore is complete and you startup, you face issues, let me know and we’ll investigate further.

Let me know if any of the above needs clarification.

Brian