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:
- Navigate to the following location on the computer where you have upgraded the Application Server:
<InstallationDirectory>/tools/postgres10_upgrade - Copy the contents of this folder to a temporary directory on the TrueSight Capacity Optimization Database Server that runs the PostgreSQL 9.x database.
- Extract the contents of the pg10upgrade_tool.tar file, and provide read, write, and execute permissions of these files to a postgres user.
- Edit the customenv.sh file and set all the necessary variables.
- 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.
- If you select Yes, the script will run and prepare the database for upgrade.
- If you select No, you can use pgsql or a DB client connected to the PostgreSQL 9.x instance to run the script.
- As a root user, stop the PostgreSQL 9.x instance.
For example:systemctl stop postgresql-9.6
- As a root user, install and configure the PostgreSQL 10.x database. For more information, see
Installing PostgreSQL 10.x
.
- As a root user, initiate the PostgreSQL 10.x database using the
initdb
command:
For example:/usr/pgsql-10/bin/postgresql-10-setup initdb
- 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
- 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 thepg_upgrade
command, see pg_upgrade.
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.
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
- 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:
- Navigate to the following location on the computer where you have upgraded the Application Server:
<InstallationDirectory>\tools\postgres10_upgrade - Copy the contents of this folder to a temporary directory on the TrueSight Capacity Optimization Database Server that runs the PostgreSQL 9.x database.
- 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.
- Take a backup of all existing SQL views.
- 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 ..." - Copy the result.
- 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.
- Check that the file obtained with pg_dump contains all the SQL views of your environment.
- Run the following query:
- Run the SQL scripts in the pg10upgrade_tool/sql folder in the following order:
- drop_all_views.sql
- change_table_column_types.sql
- recreate_views.sql
- drop_casts.sql
- As an administrator, click Start > Run > services.msc and stop the PostgreSQL 9.x instance.
- As an administrator, install and configure the PostgreSQL 10.x database. For more information, see
Installing PostgreSQL 10.x
.
- As an administrator, initiate the PostgreSQL 10.x database using the
initdb
command:
For example: \usr\pgsql-10\bin\postgresql-10-setup initdb
- If the database instance has already started, as an Administrator, click Start > Run > services.msc and stop the PostgreSQL 10.x instance.
- 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 thepg_upgrade
command, see pg_upgrade.
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.
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
- As an administrator, click Start > Run > services.msc and start the PostgreSQL 10.x instance.
Comments