Upgrading Babelfish to a new major version

For a major version upgrade, you need to first upgrade your Babelfish for Aurora PostgreSQL DB cluster to a version that supports the major version upgrade. To achieve this, apply patch updates or minor version upgrades to your DB cluster. For more information,see Upgrading Babelfish to a new minor version.

The following table shows Aurora PostgreSQL version and Babelfish version that can support a major version upgrade.

Before upgrading Babelfish to a new major version

An upgrade might involve brief outages. For that reason, we recommend that you perform or schedule upgrades during your maintenance window or during other periods of low usage.

Performing major version upgrade

After upgrading to a new major version

After a major version upgrade to a new Aurora PostgreSQL version, the IDENTITY value in tables with an IDENTITY column might be larger (+32) than the value was before the upgrade. The result is that when the next row is inserted into such tables, the generated identity column value jumps to the +32 number and starts the sequence from there. This condition won't negatively affect the functions of your Babelfish DB cluster. However, if you want, you can reset the sequence object based on the maximum value of the column. To do so, connect to the T-SQL port on your Babelfish writer instance using sqlcmd or another SQL Server client. For more information, see Using a SQL Server client to connect to your DB cluster.

sqlcmd -S bfish-db.cluster-123456789012.aws-region.rds.amazonaws.com,1433 -U
     sa -P ******** -d dbname

When connected, use the following SQL command to generate statements that you can use to seed the associated sequence object. This SQL command works for both single database and multiple database Babelfish configurations. For more information about these two deployment models, see Using Babelfish with a single database or multiple databases.

DECLARE @schema_prefix NVARCHAR(200) = ''
IF current_setting('babelfishpg_tsql.migration_mode') = 'multi-db'
    SET @schema_prefix = db_name() + '_'
SELECT 'SELECT setval(pg_get_serial_sequence(''' + @schema_prefix + schema_name(tables.schema_id)
    + '.' + tables.name + ''', ''' + columns.name + '''),(select max(' + columns.name + ')
    FROM ' + schema_name(tables.schema_id) + '.' + tables.name + '));
    'FROM sys.tables tables JOIN sys.columns
    columns ON tables.object_id = columns.object_id
    WHERE columns.is_identity = 1
GO

The query generates a series of SELECT statements that you can then run to reset the maximum IDENTITY value and close any gap. The following shows the output when using the sample SQL Server database, Northwind, running on a Babelfish cluster.

--------------------------------------------------------
SELECT setval(pg_get_serial_sequence('northwind_dbo.categories', 'categoryid'),(select max(categoryid)
    FROM dbo.categories));

SELECT setval(pg_get_serial_sequence('northwind_dbo.orders', 'orderid'),(select max(orderid)
    FROM dbo.orders));
                                                                                                                        
SELECT setval(pg_get_serial_sequence('northwind_dbo.products', 'productid'),(select max(productid)
    FROM dbo.products));
                                                                                                                        
SELECT setval(pg_get_serial_sequence('northwind_dbo.shippers', 'shipperid'),(select max(shipperid)
    FROM dbo.shippers));
                                                                                                                        
SELECT setval(pg_get_serial_sequence('northwind_dbo.suppliers', 'supplierid'),(select max(supplierid)
    FROM dbo.suppliers));
                                                                                                                        
(5 rows affected)

Run the statements one by one to reset the sequence values.

Example: Upgrading the Babelfish DB cluster to a major release

In this example, you can find the series of AWS CLI commands that explains how to upgrade an Aurora PostgreSQL 13.6.4 DB cluster running Babelfish version 1.2.2 to Aurora PostgreSQL 14.6. First, you create a custom DB cluster parameter group for Aurora PostgreSQL 14. Next, you modify the parameter values to match those of your Aurora PostgreSQL version 13 source. Finally, you perform the upgrade by modifying the source cluster. For more information, see DB cluster parameter group settings for Babelfish. In that topic, you can also find information about using the AWS Management Console to perform the upgrade.

Use the create-db-cluster-parameter-group CLI command to create the DB cluster parameter group for the new version.

For Linux, macOS, or Unix:

aws rds create-db-cluster-parameter-group \
    --db-cluster-parameter-group-name docs-lab-babelfish-apg-14 \
    --db-parameter-group-family aurora-postgresql14 \
    --description 'New custom parameter group for upgrade to new major version' \
    --region us-west-1

When you issue this command, the custom DB cluster parameter group is created in the AWS Region. You see output similar to the following.

{
    "DBClusterParameterGroup": {
        "DBClusterParameterGroupName": "docs-lab-babelfish-apg-14",
        "DBParameterGroupFamily": "aurora-postgresql14",
        "Description": "New custom parameter group for upgrade to new major version",
        "DBClusterParameterGroupArn": "arn:aws:rds:us-west-1:111122223333:cluster-pg:docs-lab-babelfish-apg-14"
    }
}

For more information, see Creating a DB cluster parameter group in Amazon Aurora.

Use the modify-db-cluster-parameter-group CLI command to modify the settings so that they match the source cluster.

For Windows:

aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name docs-lab-babelfish-apg-14 ^
  --parameters "ParameterName=rds.babelfish_status,ParameterValue=on,ApplyMethod=pending-reboot" ^
 "ParameterName=babelfishpg_tds.tds_default_numeric_precision,ParameterValue=38,ApplyMethod=pending-reboot" ^
 "ParameterName=babelfishpg_tds.tds_default_numeric_scale,ParameterValue=8,ApplyMethod=pending-reboot" ^
 "ParameterName=babelfishpg_tsql.database_name,ParameterValue=babelfish_db,ApplyMethod=pending-reboot" ^
 "ParameterName=babelfishpg_tsql.default_locale,ParameterValue=en-US,ApplyMethod=pending-reboot" ^
 "ParameterName=babelfishpg_tsql.migration_mode,ParameterValue=single-db,ApplyMethod=pending-reboot" ^
 "ParameterName=babelfishpg_tsql.server_collation_name,ParameterValue=sql_latin1_general_cp1_ci_as,ApplyMethod=pending-reboot"

The response looks similar to the following.

{
    "DBClusterParameterGroupName": "docs-lab-babelfish-apg-14"
}

Use the modify-db-cluster CLI command to modify the cluster to use the new version and the new custom DB cluster parameter group. You also specify the --allow-major-version-upgrade argument, as shown in the following sample.

aws rds modify-db-cluster \
--db-cluster-identifier docs-lab-bfish-apg-14 \
--engine-version 14.6 \
--db-cluster-parameter-group-name docs-lab-babelfish-apg-14 \
--allow-major-version-upgrade \
--region us-west-1 \
--apply-immediately

Use the reboot-db-instance CLI command to reboot the writer instance of the cluster, so that the parameter settings can take effect.

aws rds reboot-db-instance \
--db-instance-identifier docs-lab-bfish-apg-14-instance-1\
--region us-west-1