Migrate Data Between PostgreSQL Implementations

If you want to migrate Tenable Security Center 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 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 version 13 through 16 to an internal or external PostgreSQL version 13 through 17.
Migrating from an external PostgreSQL version 17 to an external PostgreSQL version 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:

Migrating from an external PostgreSQL to an external PostgreSQL of an earlier version. For example, migrating from an external PostgreSQL version 17 to an external PostgreSQL version 16.
Migrating from an external PostgreSQL version 17 to internal PostgreSQL.

This page includes two sets of instructions:

Migrate data from an internal PostgreSQL or a PostgreSQL version 13 through 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 version 13 through 16 to an internal or external PostgreSQL 13 through 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 6.5.x and 6.6.x, download the script php file from the Tenable downloads site to the server where you have installed Tenable Security Center. 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 13 through 16 implementation

Stop Tenable Security Center 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:
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 with the following command:
Copy
```
# /bin/systemctl start SecurityCenter
```

Migrate data from a PostgreSQL version 17 implementation

Stop Tenable Security Center 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:
Copy
```
/opt/sc/support/bin/php /opt/sc/src/tools/MovePostgresDB.php config-only
```

Log in to your destination PostgreSQL server with the following command, where <username> is your PostgreSQL username:
Copy
```
sudo su - <username>
```

Clear data by running the following SQL commands in the PostgreSQL terminal:

Copy

CREATE OR REPLACE FUNCTION truncate_public_schema_tables()
RETURNS void AS $$
DECLARE
    table_name text;
BEGIN
    FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'TRUNCATE TABLE public.\"' || table_name || '\" CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;
        
SELECT truncate_public_schema_tables();

DROP FUNCTION IF EXISTS truncate_public_schema_tables();

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 -a -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 -a -F d <backup_name> -j 5 2>&1
```
  The data on the source server is copied to the destination server.
Start Tenable Security Center with the following command:
Copy
```
# /bin/systemctl start SecurityCenter
```