PostgreSQL 15.6 Database Migration Guide

This article covers the in-product migration procedure that allows you to upgrade your Security Console database to PostgreSQL version 15.6. Read the following sections to understand why this migration is necessary, be informed about important dates during the availability period, and learn how to start the process.

Why Migration is Important

The Security Console stores your assessment data and reports in a PostgreSQL database. The application has been using version 11.17 of the PostgreSQL database up to this point. However, PostgreSQL ended official support for this version on November 9, 2023. You can read more about this date and others in this PostgreSQL versioning policy resource:

https://www.postgresql.org/support/versioning/

Running an officially supported version of the database ensures that the Security Console can receive security updates from PostgreSQL as they become available. This migration procedure upgrades the database to version 15.6 of PostgreSQL, which will continue to receive official support through November 11, 2027.

In accordance with our commitment to the security and integrity of your data, Rapid7 requires that all Security Console databases migrate to PostgreSQL 15.6 during the availability period.

How Migration Works

The database migration procedure involves 3 steps:

1. Recent backup verification - Before you can start migrating data to the new 15.6 database, the migration service will check if a recent backup already exists. This backup must have been created within the last 3 days to be valid.
   • The migration requires a valid backup for precautionary reasons. It will not be used during the migration procedure itself.

     Constraint validation scenario

     If the migration service determines that your database did not undergo constraint validation and remediation at the time that your backup was created, it will attempt to validate the constraints in your database before allowing you to proceed. If your backup falls into this scenario, this validation service will run as soon as the migration interface loads.

     If this validation check finds any constraint violations in your database, you will have to create a new backup with the constraint validation and remediation service enabled to proceed. See the Database Backup, Restore, and Data Retention page for instructions on how to do this.

2. The migration starts - The procedure runs a preliminary disk space check to ensure that your Security Console host has sufficient storage to complete the process. Migration will not take place if the host does not have enough storage. Assuming you have sufficient disk space, the procedure will begin copying your data to a new PostgreSQL 15.6 database.
   • If you are experiencing disk space limitations, see the I don’t have enough disk space to start the migration. What should I do? FAQ.

     How much free disk space does my host machine need?

     This will vary widely depending on the size of your database, but the migration service calculates this disk space requirement with the following formula:

     DB_size * 1.3 + 1.6GB

3. The migration finishes - Your migration will conclude when all data has successfully copied over to the new 15.6 database.

How to Start the Migration Procedure

To start your migration:

1. Navigate to the migration service in your Security Console. You can access this interface directly from corresponding items in your Notifications dropdown, or manually through Administration > Console > PostgreSQL Migration. The migration interface contains all 3 steps of the procedure and a time estimation feature.
2. If the migration service does not detect an existing valid backup, click Backup Now. This will take you to the Backup/Restore tab on the Maintenance screen where you can create your backup. Give your backup a suitable description (such as “15.6 migration backup” or similar) and click Start Backup. Return to the migration interface when your backup is ready.
   • You can read more about the backup creation process on the Database Backup, Restore, and Data Retention page.
3. With your precautionary backup in place, you’re ready to start the migration. If desired, you can generate a time estimate for the procedure by clicking the Estimate now link. Click Migrate Now when ready.
   • While migration time estimates are based on the amount of data you have stored in your database, actual migration times can vary depending on other conditions in your environment.
   • Be aware that this estimate does not account for time spent on server restarts. Depending on your pre-migration state, your Security Console may restart up to 3 times: Once during the creation of your recent backup (if you don't have one already), once for the actual database migration itself, and once for your new baseline backup that you may elect to perform in step 7 of this procedure.
4. When your migration finishes, the Security Console temporarily retains a compressed copy of your pre-migration database. You have the option to automatically delete this compressed copy along with other temporary files to save disk space on your Security Console host.

   Recommendation for compressed pre-migration database copy

   As a best practice, Rapid7 recommends that you relocate this compressed pre-migration database to external media (instead of deleting it) in case you need to roll back to the pre-migration state. However, rolling back should not be taken lightly and should not be used as a general troubleshooting step for issues that may arise post-migration. Be mindful that we still require all Security Console databases to upgrade to PostgreSQL 15.6 eventually, and rolling back afterwards means that you’ll have to complete the process again later. Rolling back should only ever be considered in exceptional cases.

   You can find this compressed database copy in the following location:

   • Linux - /opt/rapid7/nexpose/nsc/nxpgsql-backup-dd-MM-yyyy-HH-MM
   • Windows - C:\Program Files\Rapid7\NeXpose\nsc\nxpgsql-backup-dd-MM-yyyy-HH-MM
5. Verify that the migration was successful by checking the database version number. You can do this by navigating through Administration > Database > Settings.
6. Next, verify that your data is consistent with what existed prior to the start of your migration procedure. If you notice inconsistencies between your pre- and post-migration data sets, contact Rapid7 Support.
7. Finally, as best practice, run a new database backup operation to preserve a baseline instance of your post-migration database.

Migration complete - Next steps

First, verify that your PostgreSQL database is on the correct version. You can check this by opening the nsc.log file located in the following directory:

• Linux - /opt/rapid7/nexpose/nsc/logs
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\logs

Search for the PostgreSQL string. You will find the active PostgreSQL version number with an instance of that string, which should be 15.6. Alternatively, you can check for this version number in the Security Console by navigating to Administration > Administer > Database.

Next, check your Security Console interface and confirm that your data is consistent with what existed prior to migration. Note these items in particular:

• reports
• sites
• assets
• asset groups
• vulnerabilities
• user-added tags

If you notice any discrepancies in your data after the migration, contact Rapid7 Support.

After you confirm that the migration was successful, perform the following tasks:

1. As a failsafe, save the /nxpgsql-backup-dd-MM-yyyy-HH-MM directory to an external location in case you need to roll back your migration to 9.4, 11.7, or 11.17. This directory contains a compressed copy of the pre-migration database. If you choose to skip this step, this directory will be deleted in step 3 of the overall migration procedure.
2. Move back any Security Console files or directories that you may have relocated earlier for disk space purposes when you were preparing for your migration.

Frequently Asked Questions (FAQs)

See the following FAQs for common troubleshooting scenarios and other cases that you may encounter during your upgrade.

Why do I need to migrate?

Upgrading the database to version 15.6 ensures that it will continue to receive security updates from PostgreSQL going forward.

Why do I need to create a backup before migrating?

Backing up your database is a best practice that protects your files. Although the backup process is designed to be safe for your files (even in the case of backup failure), we require that you create a backup first as a precaution given the sensitive information that is critical to your security and business needs. Your backup must have taken place within the last 3 days before you can start the migration process.

Does migration happen automatically when I update my Security Console?

No. Updating to console product version 6.6.230 (and any other product version included in this grace period) merely makes the migration service available in your Administration page. The Security Console will not force you to take immediate action beyond informing you that the service is available.

Is the migration process the same as the Security Console update process?

No. The database migration is completely separate from the product version update that your Security Console goes through every week. As stated previously, product version 6.6.230 is just the first of several versions that include the migration service. As a result, all product versions from 6.6.230 onward that fall within the migration grace period are technically capable of running either pre-migration PostgreSQL versions or 15.6.

Do I still need to migrate to 15.6 if I’m just going to move my Security Console deployment to another host?

Yes. Starting with product version 6.6.230, new installations of the Security Console already include a PostgreSQL 15.6 database, but you still need to migrate your original database to 15.6 in order to create a compatible backup file. Backups created from a pre-migration database are not restorable on 15.6.

If you are intending to move your Security Console to a new server or host, it is recommended that you migrate your database to PostgreSQL 15.6 beforehand and make a backup.

If you are in the midst of migrating to a new host, have not upgraded your database, and have made a backup of your original database, then you must re-install your Security Console with pre-PostgreSQL 15.6, product version 6.6.229 installers. Once you have reinstalled and you can restore your old database before commencing upgrade to PostgreSQL 15.6.

Pre-PostgresSQL 15.6 Installers (Product Version 6.6.229)

Select one of the following links according to your operating system:

• Linux installer
• Windows installer

Checksum files

Use one of the following checksum files to verify the integrity of your installer and ensure that it wasn’t corrupted during the download process:

• sha512sum for Linux download
• sha512sum for Windows download

What happens during migration?

After starting the migration, the Security Console goes into Maintenance Mode while it copies data from the pre-migration database to the new 15.6 database. The migration process will check to make sure there is sufficient disk space to complete this action beforehand. Global Administrators can log in to monitor the status of the migration during this period.

After the migration completes, the Security Console backs up the pre-migration database and stores it in the following directory:

• Linux - /opt/rapid7/nexpose/nsc/nxpgsql-backup-dd-MM-yyyy-HH-MM
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\nxpgsql-backup-dd-MM-yyyy-HH-MM

After all migration processes finish, the Security Console restarts and you can resume normal operations.

If the migration fails, your current version of the PostgreSQL database will remain intact and you can continue using the Security Console without interruption. See the FAQ What should I do if the migration fails? if this occurs.

If my Security Console host is isolated in an offline environment, will that affect my migration?

The migration procedure will remain the same.

Will the migration affect my data warehousing capabilities?

Not unless your warehouse database runs a version of PostgreSQL that predates 8.2. The new 15.6 version of the Security Console database does include an updated warehouse export driver, but this will not affect your current data warehousing capabilities as long as your warehouse database runs version 8.2 of PostgreSQL or later.

If your warehouse database does not run at least version 8.2 of PostgreSQL, you will also need to upgrade it to a supported version before you can resume exporting.

How long does migration take?

You can generate an estimate of your migration time using the estimate function shown in the Migrate PostgreSQL to 15.6 step on this screen. Although this estimate is based on the size of your database, actual migration times may vary depending on other conditions in your environment. The estimate does not account for time spent during server restarts.

If I authenticate to the Security Console with an external source like LDAP or Kerberos, will I be able to monitor the migration?

No. The database stores information about external authentication sources and they won't be operational during the migration. As an alternative, you can monitor the progress of your migration in the following ways:

• Console-authenticated users with Global Administrator privileges can log in while the Security Console is in Maintenance Mode to monitor new status messages as they appear.
• If a console-authenticated Global Administrator user is not available, you can also monitor the progress of your migration by reading the nsc.log file located in the following directory:
  • Linux - /opt/rapid7/nexpose/nsc/logs
  • Windows - C:\Program Files\Rapid7\NeXpose\nsc\logs

My PostgreSQL database is on an external server. Can I migrate it?

The external database configuration is currently not supported. Contact Rapid7 Support for more information.

The migration fails because Port 50432 is in use. What should I do?

Port 50432 is reserved for the migration, so you will have to make it available. Contact your system administrator to determine what service is running on port 50432 and shut that service down until the migration is complete.

I don't have enough disk space to start the migration. What should I do?

If this problem occurs after a previous migration attempt failed, see the FAQ What should I do if the migration fails? Otherwise, take the following steps:

1. Move the following InsightVM directories from the host system, and restore them after the migration is complete. These directories and files take up increasing amounts of disk space over time as the Security Console accumulates data.

Backup files

• Linux - /opt/rapid7/nexpose/nsc/*.bak and /opt/rapid7/nexpose/nsc/*.zip
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\*.bak and C:\Program Files\Rapid7\NeXpose\nsc\*.zip

Reports directory

• Linux - /opt/rapid7/nexpose/nsc/htroot/reports
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\htroot/reports

Access log directory, including the Tomcat log subdirectory

• Linux - /opt/rapid7/nexpose/nsc/logs
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\logs

Scan event data files directory

• Linux - /opt/rapid7/nexpose/nse/scans
• Windows - C:\Program Files\Rapid7\NeXpose\nse\scans

Security Console logs

• Linux - /opt/rapid7/nexpose/nsc/logs/*.log*
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\logs\*.log*

PostgreSQL log

• Linux - /opt/rapid7/nexpose/nsc/nxpgsql/nxpgsql.log
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\nxpgsql\nxpgsql.log

2. Move any files or directories from the host system that are not in the Security Console installation directory and not required by other applications to run. You can restore them after the migration is complete.
3. Delete the contents of the java.io.tmpdir directory on the host system, located at /var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/T/.

After taking the preceding steps, try starting the migration again. If you still don't have enough disk space, contact Rapid7 Support.

The migration is taking a long time with no new status messages. Is this normal?

Depending on the amount of data, the migration to 15.6 can take several hours. Therefore, a long migration time is not unusual, and extended periods without new status messages do not necessarily indicate that the migration is "hanging." You can perform some quick checks to confirm that the migration is still proceeding even when no status messages are visible:

• Run the top command in Linux or open the Task Manager in Windows. Check if a PostgreSQL process is running and using CPU resources.
• You can also check migration log files for messages about database tables being copied:
  • Linux - /opt/rapid7/nexpose/nsc/nxpgsql/pgsql/bin/pg_upgrade_*.log
  • Windows - C:\Program Files\Rapid7\NeXpose\nsc\nxpgsql\pgsql\bin\pg_upgrade_*.log

When migration processes complete, the Security Console restarts with a success message.

What happens if I stop the migration before it completes?

If you choose to cancel the migration before it completes, the Security Console will discontinue the migration process and delete any artifacts that were created up to that point. You can then restart the Security Console in its normal operational mode.

What should I do if the migration fails?

Typically, your current version of the PostgreSQL database will remain intact if the migration fails. Simply restart the Security Console and resume normal operations. If you retry the migration and it fails a second time, contact Rapid7 Support for assistance.

If the migration fails due to a system failure or power outage and you attempt to run the migration again, you may encounter a disk space limitation issue. This is caused by the Security Console creating an nxpgsql-temp directory during the interrupted migration. To resolve this issue, delete this directory and start the migration again. The temporary directory is located in the following location:

• Linux - /opt/rapid7/nexpose/nsc/
• Windows - C:\Program Files\Rapid7\NeXpose\nsc\