Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Babelfish version updates

Babelfish is an option available with Aurora PostgreSQL version 13.4 and higher releases. Updates to Babelfish become available with certain new releases of the Aurora PostgreSQL database engine. For more information, see the Release Notes for Aurora PostgreSQL.

For a list of supported functionality across different Babelfish releases, see Supported functionality in Babelfish by version.

For a list of currently unsupported functionality, see Unsupported functionality in Babelfish.

You can use the describe-db-engine-versions Amazon CLI command to get a list of Aurora PostgreSQL versions in your Amazon Web Services Region that support Babelfish, as shown in the following example.

For Linux, macOS, or Unix:

$ aws rds describe-db-engine-versions --region us-east-1 \
    --engine aurora-postgresql \
    --query '*[]|[?SupportsBabelfish==`true`].[EngineVersion]' \
    --output text
13.4
13.5
13.6
13.7
13.8
14.3
14.4
14.5
14.6

For more information, see describe-db-engine-versions in the Amazon CLI Command Reference.

In the following topics, you can learn how to identify the version of Babelfish running on your Aurora PostgreSQL DB cluster, and how to upgrade to a new version.

Identifying your version of Babelfish

You can query Babelfish to find details about the Babelfish version, the Aurora PostgreSQL version, and the compatible Microsoft SQL Server version. You can use the TDS port or the PostgreSQL port.

As an example that shows one minor difference between Babelfish and Microsoft SQL Server, you can run the following query. On Babelfish, the query returns 1, while on Microsoft SQL Server, the query returns NULL.

SELECT CAST(serverproperty('babelfish') AS VARCHAR) AS runs_on_babelfish

You can also use the PostgreSQL port to obtain version information, as shown in the following procedure.

Upgrading your Babelfish cluster to a new version

New versions of Babelfish become available with some new releases of the Aurora PostgreSQL database engine after version 13.4. Each new release of Babelfish has its own version number. As with Aurora PostgreSQL, Babelfish uses the major.minor.patch naming scheme for versions. For example, the first Babelfish release, Babelfish version 1.0.0, became available as part of Aurora PostgreSQL 13.4.0.

Babelfish doesn't require a separate installation process. As discussed in Creating a Babelfish for Aurora PostgreSQL DB cluster, Turn on Babelfish is an option that you choose when you create an Aurora PostgreSQL DB cluster.

Likewise, you can't upgrade Babelfish independently from the supporting Aurora DB cluster. To upgrade an existing Babelfish for Aurora PostgreSQL DB cluster to a new version of Babelfish, you upgrade the Aurora PostgreSQL DB cluster to a new version that supports the version of Babelfish that you want to use. The procedure that you follow for the upgrade depends on the version of Aurora PostgreSQL that's supporting your Babelfish deployment, as follows.

Upgrading Babelfish to a new minor version

A new minor version includes only changes that are backward compatible. A patch version includes important fixes for a minor version after its release. For example, the version label for the first release of Aurora PostgreSQL 13.4 was Aurora PostgreSQL 13.4.0. Several patches for that minor version have been released to date, including Aurora PostgreSQL 13.4.1, 13.4.2, and 13.4.4. You can find the patches available for each Aurora PostgreSQL version in the Patch releases list at the top of the Aurora PostgreSQL release notes for that version. For an example, see PostgreSQL 14.3 in the Release Notes for Aurora PostgreSQL.

If your Aurora PostgreSQL DB cluster is configured with the Auto minor version upgrade option, your Babelfish for Aurora PostgreSQL DB cluster is upgraded automatically during the cluster's maintenance window. To learn more about auto minor version upgrade (AMVU) and how to use it, see Automatic minor version upgrades for Aurora DB clusters . If your cluster isn't using AMVU, you can manually upgrade your Babelfish for Aurora PostgreSQL DB cluster to new minor versions either by responding to maintenance tasks, or by modifying the cluster to use the new version.

When you choose an Aurora PostgreSQL version to install and when you view an existing Aurora PostgreSQL DB cluster in the Amazon Web Services Management Console, the version displays the major.minor digits only. For example, the following image from the Console for an existing Babelfish for Aurora PostgreSQL DB cluster with Aurora PostgreSQL 13.4 recommends upgrading the cluster to version 13.7, a new minor release of Aurora PostgreSQL.

To get complete version details, including the patch level, you can query the Aurora PostgreSQL DB cluster using the aurora_version Aurora PostgreSQL function. For more information, see aurora_version in the Aurora PostgreSQL functions reference. You can find an example of using the function in the To use the PostgreSQL port to query for version information procedure in Identifying your version of Babelfish.

The following table shows Aurora PostgreSQL and Babelfish version and the available target versions that can support the minor version upgrade process.

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 Amazon 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 Amazon Web Services 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 Amazon Web Services 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.

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