Migrating the data from PostgreSQL 9.x to 10.x

You can upgrade PostgreSQL version 9.x to 10.x. In the process of upgrading, you need to migrate PostgreSQL 9.x database and configuration information to PostgreSQL 10.x.

Migrating is especially useful because it allows you to reuse configured information from the earlier version and saves time in getting started with the new version.

You can migrate the PostgreSQL data using the following approach:

• Linux - Migrate the data using the pre-upgrade check utility 
• Microsoft Windows - Migrate the data manually 

Before you begin

Before you migrate the data, ensure that the following requirements are met:

• You have administrator privileges for accessing TrueSight Capacity Optimization.
• TrueSight Capacity Optimization is upgraded to version 11.3.
• All TrueSight Capacity Optimization services are stopped.
• You have a backup of your PostgreSQL 9.x instance.
• The PostgreSQL 9.x instance is up and running.
• The PostgreSQL 10.x installation files are downloaded .

Migrating data on Linux computers

On Linux computers, prepare the database schema using the pre-upgrade check utility and perform the migration using the following procedure: 

1. Navigate to the following location on the computer where you have upgraded the Application Server: 
   <InstallationDirectory>/tools/postgres10_upgrade
2. Copy the contents of this folder to a temporary directory on the TrueSight Capacity Optimization Database Server that runs the PostgreSQL 9.x database.
3. Extract the contents of the pg10upgrade_tool.tar file, and provide read, write, and execute permissions of these files to a postgres user.
4. Edit the customenv.sh file and set all the necessary variables.
5. As a postgres user, run the prepare_for_pg_upgrade.sh command to prepare the database for upgrade. The pre_upgrade_script.sql script is created, and you will be prompted whether to run this SQL script.
   1. If you select Yes, the script will run and prepare the database for upgrade.
   2. If you select No, you can use pgsql or a DB client connected to the PostgreSQL 9.x instance to run the script.
6. As a root user, stop the PostgreSQL 9.x instance. 
   For example:  systemctl stop postgresql-9.6
7. As a root user, install and configure the PostgreSQL 10.x database. For more information, see  Installing PostgreSQL 10.x .
8. As a root user, initiate the PostgreSQL 10.x database using the initdb command:
   For example: /usr/pgsql-10/bin/postgresql-10-setup initdb
9. If the database instance is already running, as a root user, stop PostgreSQL 10.x instance by using the following command: 
   For example: systemctl stop postgresql-10
10. As the postgres user, perform the upgrade using the pg_upgrade command:   
    For example: /usr/pgsql-10/bin/pg_upgrade -b /usr/pgsql-9.6/bin -B /usr/pgsql-10/bin -d /var/lib/pgsql/9.6/data -D /var/lib/pgsql/10/data -k –v
    
    For more information about the pg_upgrade command, see  pg_upgrade .

11. Copy the Client Authentication configuration file (pg_hba.conf) from the <PostgreSQL 9.x installation directory>/data folder to the <PostgreSQL 10.x installation directory>/data folder.

12. Copy the following configuration parameter information from the <PostgreSQL 9.x installation directory>/data/postgresql.conf file and add it to the <PostgreSQL 10.x installation directory>/data/postgresql.conf file.
    For example:

    listen_addresses = '*'
    max_connections = 300
    default_statistics_target = 50
    constraint_exclusion = on
    wal_buffers = 8MB
    checkpoint_segments = 32
    checkpoint_timeout = 15min
    checkpoint_completion_target = 0.9
    log_min_messages = fatal
    log_min_error_statement = fatal
    #following parameters should be tuned according to actual memory available to Database server machine
    #example of configuration for 8GB RAM 
    
    maintenance_work_mem = 512MB
    
    effective_cache_size = 5GB
    work_mem = 48MB
    shared_buffers = 2GB

13. As a root user, start the PostgreSQL 10.x instance. 
    For example: systemctl start postgresql-10   

Migrating data on Windows computers

On Microsoft Windows computers, prepare the database schema manually and perform the migration using the following procedure:

1. Navigate to the following location on the computer where you have upgraded the Application Server: 
   <InstallationDirectory>\tools\postgres10_upgrade
2. Copy the contents of this folder to a temporary directory on the TrueSight Capacity Optimization Database Server that runs the PostgreSQL 9.x database.
3. Extract the contents of the pg10upgrade_tool.tar file, and provide read, write, and execute permissions of these files to a postgres user. The ‘sql’ folder in the extracted folder includes the set of SQL scripts needed to prepare for the upgrade.
4. Take a backup of all existing SQL views.
   1. Run the following query: 
      get_view_names.sql
      It returns a list of arguments such as "-t public.v_bpa_metric_dataflow -t public.er_v_dep_2002 ..." 
   2. Copy the result.
   3. Use the pg_dump utility to dump all views in the recreate_views.sql file by using the parameters that you copied in the previous step. 
      For more information, see  Installing PostgreSQL 10.x .
   4. Check that the file obtained with pg_dump contains all the SQL views of your environment.
5. Run the SQL scripts in the pg10upgrade_tool/sql folder in the following order:
   1. drop_all_views.sql
   2. change_table_column_types.sql
   3. recreate_views.sql 
   4. drop_casts.sql
6. As an administrator, click Start > Run > services.msc and stop the PostgreSQL 9.x instance.
7. As an administrator, install and configure the PostgreSQL 10.x database. For more information, see  Installing PostgreSQL 10.x .
8. As an administrator, initiate the PostgreSQL 10.x database using the initdb command:
   For example: \usr\pgsql-10\bin\postgresql-10-setup initdb
9. If the database instance has already started, as an Administrator, click Start > Run > services.msc and stop the PostgreSQL 10.x instance. 
10. As a postgres user, perform the upgrade using the pg_upgrade command:   
    For example: \usr\pgsql-10\bin\pg_upgrade -b \usr\pgsql-9.6\bin -B \usr\pgsql-10\bin -d \var\lib\pgsql\9.6\data -D \var\lib\pgsql\10\data -k –v
    For more information about the pg_upgrade command, see  pg_upgrade .

11. Copy the Client Authentication configuration file (pg_hba.conf) from the <PostgreSQL 9.x installation directory>\data\ folder to the <PostgreSQL 10.x installation directory>\data\ folder.

12. Copy the following configuration parameter information from the <PostgreSQL 9.x installation directory>\data\postgresql.conf file and add it to the <PostgreSQL 10.x installation directory>\data\postgresql.conf file.
    For example:

    listen_addresses = '*'
    max_connections = 300
    default_statistics_target = 50
    constraint_exclusion = on
    wal_buffers = 8MB
    checkpoint_segments = 32
    checkpoint_timeout = 15min
    checkpoint_completion_target = 0.9
    log_min_messages = fatal
    log_min_error_statement = fatal
    #following parameters should be tuned according to actual memory available to Database server machine
    #example of configuration for 8GB RAM 
    
    maintenance_work_mem = 512MB
    
    effective_cache_size = 5GB
    work_mem = 48MB
    shared_buffers = 2GB

13. As an administrator, click Start > Run > services.msc and start the PostgreSQL 10.x instance.   

Related topic

Database requirements