Migrate Data Between PostgreSQL Implementations

If you want to migrate Tenable Security Center Director data between PostgreSQL implementations, Tenable offers a script to simplify the process.

The script is supported for the following use cases:

Existing Tenable Security Center Director installs that are licensed and configured. For new installs, follow the directions in Connect an External PostgreSQL Server.
Migrating from an internal to an external PostgreSQL.
Migrating from an external PostgreSQL 16 to an internal or external PostgreSQL 16 or 17.
Migrating from an external PostgreSQL 17 to an external PostgreSQL 17. In this scenario, the script will only modify the PostgreSQL configuration. You will need to migrate your data manually after running the script.

Note: The following migration paths are not supported:

External PostgreSQL 17 to external PostgreSQL 16.
External PostgreSQL 17 to internal PostgreSQL.

This page includes two sets of instructions:

Migrate data from an internal PostgreSQL or a PostgreSQL version 16 implementation

Use these steps if you are migrating from an internal PostgreSQL to an external PostgreSQL, or if you are migrating from an external PostgreSQL 16 to an internal or external PostgreSQL 16 or 17.
Migrate data from a PostgreSQL version 17 implementation

Use these steps if you are migrating from an external PostgreSQL 17 to an external PostgreSQL 17.

Note: In the following instructions, the source server is the PostgreSQL that you are migrating data from, and the destination server is the PostgreSQL that you are migrating data to.

Before you begin

The destination PostgreSQL must be a supported version. For information about supported PostgreSQL versions, see External PostgreSQL Requirements.
For Tenable Security Center Director 6.5 and 6.6, download the script php file from the Tenable downloads site to the server where you have installed Tenable Security Center Director. Move the script file to /opt/sc/src/tools.
Ensure that both the source PostgreSQL server and the destination PostgreSQL server are currently running.
On the destination PostgreSQL server, you must have the permission to create a database. If the database is already created, then you must have read and write permissions.

Migrate data from an internal PostgreSQL or a PostgreSQL version 16 implementation

Stop Tenable Security Center Director with the following command:
Copy
```
# /bin/systemctl stop SecurityCenter
```
Set the environment variables for the destination PostgreSQL server.

Note: You must set the environment variables with a root or tns user account. Use the same account to run the script in step 4. If you are using the tns user account to run the script, switch to the tns user using the command su - tns.
Run the following script on the server where you have installed Tenable Security Center Director:
Copy
```
/opt/sc/support/bin/php /opt/sc/src/tools/MovePostgresDB.php
```
The data on the source server is copied to the destination server.
Start Tenable Security Center Director with the following command:
Copy
```
# /bin/systemctl start SecurityCenter
```

Migrate data from a PostgreSQL version 17 implementation

Stop Tenable Security Center Director with the following command:
Copy
```
# /bin/systemctl stop SecurityCenter
```
Set the environment variables for the destination PostgreSQL server.

Note: You must set the environment variables with a root or tns user account. Use the same account to run the script in step 4.
Run the following script on the server where you have installed Tenable Security Center Director:
Copy
```
/opt/sc/support/bin/php /opt/sc/src/tools/MovePostgresDB.php config-only
```
Migrate your data from the source server to the destination server.
1. Log in to your source PostgreSQL server with the following command, where <username> is your PostgreSQL username:
  Copy
```
sudo su - <username>
```
2. Dump the contents of the source server to a directory with the following command, where <dbname> is the name of the source PostgreSQL server, and <backup_name> is the name of the backup file:
  Copy
```
pg_dump <dbname> -O -F d -f <backup_name> -j 5 2>&1
```
3. Restore the dumped contents to the destination server with the following command, where <admin:admin@[IP address of new PG]:port/dbname> is the path to the destination PostgreSQL server, and <backup_name> is the name of the backup file you created in the previous step:
  Copy
```
pg_restore -d postgresql://<admin:admin@[IP address of new PG]:port/dbname> -O -F d <backup_name> -j 5 2>&1
```
  The data on the source server is copied to the destination server.
Start Tenable Security Center Director with the following command:
Copy
```
# /bin/systemctl start SecurityCenter
```