Migrating d/b on upgrade is problematic

SonarQube Server / Community Build
1

Currently running 7.9.1 SonarQube CE from docker image, using postgres in AWS. I did a dump of the d/b, loading it into a fresh AWS RDS postgres, and brought up the new 8.1 SonarQube docker in a different account. I’ve found some issues with the import, namely a column in the analysis_properties table that changed name from snapshot_uuid to analysis_uuid.
When importing, I changed the sql dump to reflect these changes, yet I still got errors when loading. From the dump file:

    --
    -- TOC entry 201 (class 1259 OID 16425)
    -- Name: analysis_properties; Type: TABLE; Schema: public; Owner: sonar
    --

    CREATE TABLE IF NOT EXISTS public.analysis_properties (
        uuid character varying(40) NOT NULL,
        analysis_uuid character varying(40) NOT NULL,
        kee character varying(512) NOT NULL,
        text_value character varying(4000),
        clob_value text,
        is_empty boolean NOT NULL,
        created_at bigint NOT NULL
    );

    ALTER TABLE public.analysis_properties OWNER TO sonar;

And further down:

--
-- TOC entry 4417 (class 0 OID 16425)
-- Dependencies: 201
-- Data for Name: analysis_properties; Type: TABLE DATA; Schema: public; Owner: sonar
--

COPY public.analysis_properties (uuid, analysis_uuid, kee, text_value, clob_value, is_empty, created_at) FROM stdin;
AWqiGgaD5fQDTP-yEBmB	AWqiGgNg5fQDTP-yEBl_	sonar.analysis.scm_revision_id	\N	\N	t	1557497775747
AWlzCERkNmwozKgA79ON	AWlzCDc2NmwozKgA79Nf	sonar.analysis.scm_revision_id	8c5b2339490b119ce9d60f9cdc72d519131b0d53	\N	f	1552413115492
AWjO3-0ZzbffL5qVMmeb	AWjO3-HqzbffL5qVMmea	sonar.analysis.buildId	${ENV.JOB_NAME}	\N	f	1549659008282
AWjfDHVpzbffL5qVMmyM	AWjfDGhdzbffL5qVMmyL	sonar.analysis.analysisId	${ENV.JOB_NAME}	\N	f	1549930362218
AWlByBwTNmwozKgA4wOo	AWlByBCENmwozKgA4wOh	sonar.analysis.scm_revision_id	aa3742e8dd0e74c61c337dea804bbc29a37e73fd	\N	f	1551586827284
AWlByTNBNmwozKgA4x4f	AWlByS3DNmwozKgA4x4e	sonar.analysis.scm_revision_id	aa3742e8dd0e74c61c337dea804bbc29a37e73fd	\N	f	1551586898753

Yet the log reported:

...
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 224
COPY 3773
COPY 0
psql:/tmp/sonarqube.dump:6340: ERROR:  column "analysis_uuid" of relation "analysis_properties" does not exist
psql:/tmp/sonarqube.dump:6341: error: invalid command \N
psql:/tmp/sonarqube.dump:6342: error: invalid command \N
psql:/tmp/sonarqube.dump:6343: error: invalid command \N
(and so on...)
2

Hi,

I’m going to paraphrase a little to make sure I understand.

You made a backup of your 7.9.1 DB, restored it to a different instance, then pointed 8.1 at it to test the upgrade.

Assuming I’ve got that right, here are the things that are throwing me:

It sounds to me like you manually manipulated your DB (either before or after restoring the backup) and got errors… during the DB restore? …during SQ’s upgrade of its schema?

From here I have two thoughts to offer.

  1. The DB should be treated like a black box. Leave it all to SQ and report the errors to us.
  2. If the errors you encountered were during SQ’s upgrade of its schema, then please give us the errors from SonarQube’s logs (web.log IIRC).

 
Ann

3

I’m not finding any errors in web.log or archived web.2020-01-24.log

4

Hi,

Did you manually manipulate your DB? Could you check your other logs?

 
Ann

5

I did nothing to the DB other than run the import. No errors found in any of the logs (sonar, web, ce, access or es)

6

Hi,

Where did the log snippet you provided in the initial post come from? And can you provide more context around it?

 
Ann

7

I started over with this instance, discovered an assumption about table definition was wrong. Import seems to have gone well, reloaded now.
But I don’t see any historical data, there are currently only 11 projects analyzed, which I ran during testing. Yet the original instance has 142 projects, some going back 18 months or more. Doesn’t this detail migrate with the DB?

8

Hi,

If you tested using the embedded database and then pointed to your production database, your ES indices likely reflect only the test data. If this is what you did, then you need to purge the ES indices so they’ll be rebuilt:

  • stop the server
  • delete $SONARQUBE-HOME/data/es6
  • restart the server

 
HTH,
Ann

9

Yes, I’ve done that step now, but I am still not seeing all 142 projects; unless the re-index process takes some significant time to complete.

10

Hi,

The reindex would be part of the startup. Are you absolutely sure you’re pointed at the right DB?

 
Ann

11

The new instance is NOT running on the same DB as the old instance; I did postgres dump on old instance, then psql import to new.

12

I re-created the AWS RDS DB, and imported my SQL dump, now I am seeing all projects migrated. My issues previously were due to importing into a polluted DB from starting Sonar with no data at all. This time I got directed to ‘Upgrade database’ page following import.

1 Like
13

Hi Guys,
I am facing the same issue i.e. getting error: invalid command \N when trying to restore the postgres DB from backup.sql file (created this using pgdump). I am trying to upgrade from bitnami sonarqube 8.9 to sonarqube 9.9.