SonarQube 7.0社区版从Oracle DB迁移至PostgreSQL的流程咨询

Since SonarQube Community Edition doesn’t get the official DB Copy Tool that Enterprise Edition offers, you’ll have to handle the migration manually. Here’s a tried-and-true step-by-step process that other community users have successfully followed:

Pre-Migration Prep #

• Backup your Oracle database first: Always start with a full backup—you don’t want to risk losing data if something goes wrong. Use Oracle’s native tools like expdp or take a snapshot via your database admin tooling.
• Stop SonarQube: Shut down the SonarQube service completely before touching the database. No writes should happen during migration to avoid data inconsistencies.
• Set up your PostgreSQL environment:
  • Install a PostgreSQL version compatible with SonarQube 7.0 (officially supported: 9.3, 9.4, 9.5, 9.6, 10).
  • Create an empty database with UTF-8 encoding, plus a dedicated user with full ownership:

    CREATE USER sonar WITH PASSWORD 'your_secure_password';
    CREATE DATABASE sonar OWNER sonar ENCODING 'UTF8';
    

Export Data from Oracle #

Use Oracle’s Data Pump Export (expdp) to extract the entire SonarQube schema. Run this command on your Oracle server (adjust credentials and service name to match your setup):

expdp sonar_user/sonar_password@your_oracle_service schemas=sonar_schema dumpfile=sonar_oracle_dump.dmp logfile=sonar_export.log

If you prefer a SQL script export, you can use Oracle SQL Developer to generate a full schema export (make sure to include data, tables, sequences, indexes, and constraints).

Convert Oracle Data to PostgreSQL Format #

Oracle and PostgreSQL have syntax and data type differences, so you’ll need a conversion tool. Ora2Pg is a popular open-source tool built exactly for this use case:

1. Install Ora2Pg (follow the official installation steps for your OS—most package managers have it, or you can build from source).
2. Create a config file (ora2pg.conf) with your Oracle and PostgreSQL connection details, plus specify the SonarQube schema to migrate.
3. Run the conversion to generate a PostgreSQL-compatible SQL script:

   ora2pg -c ora2pg.conf -d sonar_schema -o sonar_postgres.sql
   

   • Double-check the generated script for SonarQube-specific quirks:
     • Ensure Oracle NUMBER types are converted to PostgreSQL NUMERIC or appropriate integer types.
     • Fix date/time functions (e.g., replace SYSDATE with CURRENT_TIMESTAMP).
     • Verify sequences are set up correctly (PostgreSQL uses nextval('sequence_name') instead of Oracle’s sequence.NEXTVAL).

Import Data into PostgreSQL #

1. Connect to your PostgreSQL database as the sonar user and run the converted SQL script:

   psql -U sonar -d sonar -f sonar_postgres.sql
   

2. If you hit constraint errors during import:
   • Temporarily disable foreign key checks before importing data, then re-enable them afterward:

     ALTER TABLE table_name DISABLE TRIGGER ALL;
     -- Run import here
     ALTER TABLE table_name ENABLE TRIGGER ALL;
     

   • Make sure sequences are initialized with the correct starting values (match the max ID from the imported data to avoid duplicate key errors).

Configure SonarQube to Use PostgreSQL #

1. Open SonarQube’s conf/sonar.properties file.
2. Comment out all Oracle-related JDBC settings, then add the PostgreSQL configuration:

   # Oracle settings (comment these out)
   # sonar.jdbc.url=jdbc:oracle:thin:@//your_oracle_host:1521/your_service
   # sonar.jdbc.username=sonar_user
   # sonar.jdbc.password=sonar_password
   
   # PostgreSQL settings
   sonar.jdbc.url=jdbc:postgresql://your_postgres_host:5432/sonar
   sonar.jdbc.username=sonar
   sonar.jdbc.password=your_secure_password
   sonar.jdbc.driverClassName=org.postgresql.Driver
   

3. Ensure the PostgreSQL JDBC driver is present in SonarQube’s lib/jdbc/postgresql directory. SonarQube 7.0 should include this by default, but if not, download a compatible driver version (e.g., 42.2.x) and place it there.

Test the Migration #

1. Start the SonarQube service.
2. Check the logs (logs/sonar.log) for any connection errors or data-related issues.
3. Log into the SonarQube web interface:
   • Verify all existing projects, historical scans, and metrics are present.
   • Run a test scan on a small project to confirm new data can be written to PostgreSQL without problems.
4. Spot-check key features: look at past code quality reports, vulnerability histories, and user permissions to ensure everything works as expected.

内容的提问来源于stack exchange，提问作者clausfod