# Managing Babelfish for Aurora PostgreSQL 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 [https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/Welcome.html](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/Welcome.html). **Note** Babelfish DB clusters running on any version of Aurora PostgreSQL 13 can't be upgraded to Aurora PostgreSQL 14.3, 14.4, and 14.5. Also, Babelfish doesn't support a direct upgrade from 13.x to 15.x. You must first upgrade your 13.x DB cluster to 14.6 and higher version and then upgrade to 15.x version. For a list of supported functionality across different Babelfish releases, see [Supported functionalities in Babelfish by version](babelfish-compatibility.supported-functionality-table.md). For a list of currently unsupported functionality, see [Unsupported functionalities in Babelfish](babelfish-compatibility.tsql.limitations-unsupported.md). You can get a list of Aurora PostgreSQL versions that support Babelfish by querying your AWS Region using the [https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-engine-versions.html](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-engine-versions.html) AWS CLI command, as follows. For Linux, macOS, or Unix: ``` $ aws rds describe-db-engine-versions --region us-east-1 \ --engine aurora-postgresql \ --query '*[]|[?SupportsBabelfish==`true`].[EngineVersion]' \ --output text ``` For more information, see [https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-engine-versions.html](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-engine-versions.html) in the *AWS 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. **Contents** + [Identifying your version of Babelfish](babelfish-information-identify-version.md) + [Upgrading your Babelfish cluster to a new version](babelfish-information-upgrading.md) + [Upgrading Babelfish to a new minor version](babelfish-information-upgrading-minor.md) + [Upgrading Babelfish to a new major version](babelfish-information-upgrading-major.md) + [Before upgrading Babelfish to a new major version](babelfish-information-upgrading-major.md#babelfish-information-upgrading-preliminary) + [Performing major version upgrade](babelfish-information-upgrading-major.md#babelfish-performing-major-version-upgrade) + [After upgrading to a new major version](babelfish-information-upgrading-major.md#babelfish-information-upgrading-post-upgrade) + [Example: Upgrading the Babelfish DB cluster to a major release](babelfish-information-upgrading-major.md#babelfish-information-upgrading-example) + [Using Babelfish product version parameter](babelfish-guc-version.md) + [Configuring Babelfish product version parameter](babelfish-guc-version.md#babelfish-guc-version-setvalues) + [Affected queries and parameter](babelfish-guc-version.md#babelfish-guc-version-affects) + [Interface with babelfishpg\$1tsql.version parameter](babelfish-guc-version.md#babelfish-guc-version-tsql) # 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. + [To use the TDS port to query for version information](#apg-version-info-tds) + [To use the PostgreSQL port to query for version information](#apg-version-info-psql) **To use the TDS port to query for version information** 1. Use `sqlcmd` or `ssms` to connect to the endpoint for your Babelfish DB cluster. ``` sqlcmd -S bfish_db.cluster-123456789012.aws-region.rds.amazonaws.com,1433 -U login-id -P password -d db_name ``` 1. To identify the Babelfish version, run the following query: ``` 1> SELECT CAST(serverproperty('babelfishversion') AS VARCHAR) 2> GO ``` The query returns results similar to the following: ``` serverproperty ------------------------------ 3.4.0 (1 rows affected) ``` 1. To identify the version of the Aurora PostgreSQL DB cluster, run the following query: ``` 1> SELECT aurora_version() AS aurora_version 2> GO ``` The query returns results similar to the following: ``` aurora_version ------------------------------------------------- 15.5.0 (1 rows affected) ``` 1. To identify the compatible Microsoft SQL Server version, run the following query: ``` 1> SELECT @@VERSION AS version 2> GO ``` The query returns results similar to the following: ``` Babelfish for Aurora PostgreSQL with SQL Server Compatibility - 12.0.2000.8 Dec 7 2023 09:43:06 Copyright (c) Amazon Web Services PostgreSQL 15.5 on x86_64-pc-linux-gnu (Babelfish 3.4.0) (1 rows affected) ``` 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. **To use the PostgreSQL port to query for version information** 1. Use `psql` or `pgAdmin` to connect to the endpoint for your Babelfish DB cluster. ``` psql host=bfish_db.cluster-123456789012.aws-region.rds.amazonaws.com port=5432 dbname=babelfish_db user=sa ``` 1. Turn on the extended feature (`\x`) of `psql` for more readable output. ``` babelfish_db=> \x babelfish_db=> SELECT babelfish_db=> aurora_version() AS aurora_version, babelfish_db=> version() AS postgresql_version, babelfish_db=> sys.version() AS Babelfish_compatibility, babelfish_db=> sys.SERVERPROPERTY('BabelfishVersion') AS Babelfish_Version; ``` The query returns output similar to the following: ``` -[ RECORD 1 ]-----------+----------------------------------------------------------------------------------------------- aurora_version | 15.5.0 postgresql_version | PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 9.5.0, 64-bit babelfish_compatibility | Babelfish for Aurora Postgres with SQL Server Compatibility - 12.0.2000.8 + | Dec 7 2023 09:43:06 + | Copyright (c) Amazon Web Services + | PostgreSQL 15.5 on x86_64-pc-linux-gnu (Babelfish 3.4.0) babelfish_version | 3.4.0 ``` # 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](babelfish-create.md), **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. **Major version upgrades** You must upgrade the following Aurora PostgreSQL versions to Aurora PostgreSQL 14.6 and higher version before upgrading to Aurora PostgreSQL 15.2 version. + Aurora PostgreSQL 13.8 and all higher versions + Aurora PostgreSQL 13.7.1 and all higher minor versions + Aurora PostgreSQL 13.6.4 and all higher minor versions You can upgrade Aurora PostgreSQL 14.6 and higher versions to Aurora PostgreSQL 15.2 and higher versions. Upgrading an Aurora PostgreSQL DB cluster to a new major version involves several preliminary tasks. For more information, see [Performing a major version upgrade](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md). To successfully upgrade your Babelfish for Aurora PostgreSQL DB cluster, you need to create a custom DB cluster parameter group for the new Aurora PostgreSQL version. This new parameter group must contain the same Babelfish parameter settings as that of the cluster that you're upgrading. For more information and for a table of major version upgrade sources and targets, see [Upgrading Babelfish to a new major version](babelfish-information-upgrading-major.md). **Minor version upgrades and patches** Minor versions and patches don't require the creation of a new DB cluster parameter group for the upgrade. Minor versions and patches can use the minor version upgrade process, whether that's applied automatically or manually. For more information and a table of version sources and targets, see [Upgrading Babelfish to a new minor version](babelfish-information-upgrading-minor.md). **Note** Before performing a major or a minor upgrade, apply all pending maintenance tasks to your Babelfish for Aurora PostgreSQL cluster. **Topics** + [Upgrading Babelfish to a new minor version](babelfish-information-upgrading-minor.md) + [Upgrading Babelfish to a new major version](babelfish-information-upgrading-major.md) # Upgrading Babelfish to a new minor version A minor version update aims to maintain backward compatibility. However, in some cases, critical security fixes or important bug fixes may not be fully 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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html#AuroraPostgreSQL.Updates.20180305.143X) 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](USER_UpgradeDBInstance.Maintenance.md#Aurora.Maintenance.AMVU). 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 AWS 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. ![\[Minor version upgrade available for an Aurora PostgreSQL DB cluster with Babelfish.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/apg-minor-upgrade-available-bfish-cluster.png) 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\$1version](aurora_version.md) in the [Aurora PostgreSQL functions reference](Appendix.AuroraPostgreSQL.Functions.md). You can find an example of using the function in the [To use the PostgreSQL port to query for version information](babelfish-information-identify-version.md#apg-version-info-psql) procedure in [Identifying your version of Babelfish](babelfish-information-identify-version.md). The following table shows Aurora PostgreSQL and Babelfish version and the available target versions that can support the minor version upgrade process. | Current source versions | Newest upgrade targets | | --- | --- | | Aurora PostgreSQL (Babelfish) | Aurora PostgreSQL (Babelfish) | | 17.7 (5.4.0) | 17.9 (5.5.0) | | 17.6 (5.3.0) | 17.9 (5.5.0), 17.7 (5.4.0) | | 17.5 (5.2.0) | 17.9 (5.5.0), 17.7 (5.4.0), 17.6.0 (5.3.0) | | 17.4 (5.1.0) | 17.9 (5.5.0), 17.7 (5.4.0), 17.6.0 (5.3.0), 17.5 (5.2.0) | | 16.11 (4.8.0) | 16.13 (4.9.0) | | 16.10 (4.7.0) | 16.13 (4.9.0), 16.11 (4.8.0) | | 16.9 (4.6.0) | 16.13 (4.9.0), 16.11 (4.8.0), 16.10 (4.7.0) | | 16.8 (4.5.0) | 16.13 (4.9.0), 16.11 (4.8.0), 16.10 (4.7.0), 16.9 (4.6.0) | | 16.6 (4.4.0) | 16.13 (4.9.0), 16.11 (4.8.0), 16.10 (4.7.0), 16.9 (4.6.0), 16.8 (4.5.0) | | 16.4 (4.3.0) | 16.13 (4.9.0), 16.11 (4.8.0), 16.10 (4.7.0), 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0) | | 16.3 (4.2.0) | 16.13 (4.9.0), 16.11 (4.8.0), 16.10 (4.7.0), 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0) | | 16.2 (4.1.0) | 16.13 (4.9.0), 16.11 (4.8.0), 16.10 (4.7.0), 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0) | | 16.1 (4.0.0) | 16.13 (4.9.0), 16.11 (4.8.0), 16.10 (4.7.0), 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0) | | 15.15 (3.12.0) | 15.17 (3.13.0) | | 15.14 (3.11.0) | 15.17 (3.13.0), 15.15 (3.12.0) | | 15.13 (3.10.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0) | | 15.12 (3.9.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0) | | 15.10 (3.8.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0) | | 15.8 (3.7.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0) | | 15.7 (3.6.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0) | | 15.6 (3.5.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0) | | 15.5 (3.4.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0) | | 15.4 (3.3.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0) | | 15.12 (3.9.0), 15.3 (3.2.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0), 15.4 (3.3.0) | | 15.2 (3.1.0) | 15.17 (3.13.0), 15.15 (3.12.0), 15.14 (3.11.0), 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0), 15.4 (3.3.0), 15.3 (3.2.0) | | 14.20 (2.15.0) | 14.22 (2.16.0) | | 14.19 (2.14.0) | 14.22 (2.16.0), 14.20 (2.15.0) | | 14.18 (2.13.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0) | | 14.17 (2.12.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0) | | 14.15 (2.11.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0) | | 14.13 (2.10.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0) | | 14.12 (2.9.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0) | | 14.11 (2.8.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0), 14.12 (2.9.0) | | 14.10 (2.7.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0), 14.12 (2.9.0), 14.11 (2.8.0) | | 14.9 (2.6.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0), 14.12 (2.9.0), 14.11 (2.8.0), 14.10 (2.7.0) | | 14.8 (2.5.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0), 14.12 (2.9.0), 14.11 (2.8.0), 14.10 (2.7.0), 14.9 (2.6.0) | | 14.7 (2.4.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0), 14.12 (2.9.0), 14.11 (2.8.0), 14.10 (2.7.0), 14.9 (2.6.0), 14.8 (2.5.0) | | 14.6 (2.3.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0), 14.12 (2.9.0), 14.11 (2.8.0), 14.10 (2.7.0), 14.9 (2.6.0), 14.8 (2.5.0), 14.7 (2.4.0) | | 14.5 (2.2.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.17 (2.12.0), 14.15 (2.11.0), 14.13 (2.10.0), 14.12 (2.9.0), 14.11 (2.8.0), 14.10 (2.7.0), 14.9 (2.6.0), 14.8 (2.5.0), 14.7 (2.4.0), 14.6 (2.3.0) | | 14.3 (2.1.0) | 14.22 (2.16.0), 14.20 (2.15.0), 14.19 (2.14.0), 14.18 (2.13.0), 14.6 (2.3.0) | | 13.8 (1.4.0) | 13.9 (1.5.0) | | 13.7 (1.3.0) | 13.9 (1.5.0), 13.8 (1.4.0) | # 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](babelfish-information-upgrading-minor.md). The following table shows Aurora PostgreSQL version and Babelfish version that can support a major version upgrade. | Current source versions | Newest upgrade targets | | --- | --- | | Aurora PostgreSQL (Babelfish) | Aurora PostgreSQL (Babelfish) | | 16.9 (4.6.0) | 17.5 (5.2.0) | | 16.8 (4.5.0) | 17.5 (5.2.0), 17.4 (5.1.0) | | 16.6 (4.4.0) | 17.5 (5.2.0), 17.4 (5.1.0) | | 16.4 (4.3.0) | 17.5 (5.2.0), 17.4 (5.1.0) | | 16.3 (4.2.0) | 17.5 (5.2.0), 17.4 (5.1.0) | | 16.2 (4.1.0) | 17.5 (5.2.0), 17.4 (5.1.0) | | 16.1 (4.0.0) | 17.5 (5.2.0), 17.4 (5.1.0) | | 15.13 (3.10) | 17.5 (5.2.0) 16.9 (4.6.0) | | 15.12 (3.9.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0) | | 15.10 (3.8.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0) | | 15.8 (3.7.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0) | | 15.7 (3.6.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0) | | 15.6 (3.5.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0) | | 15.5 (3.4.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) | | 15.4 (3.3.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) | | 15.3 (3.2.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) | | 15.2 (3.1.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) | | 14.18 (2.13.0) | 17.5 (5.2.0) 16.9 (4.6.0) 15.13 (3.10.0) | | 14.17 (2.12.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0) 15.13 (3.10.0), 15.12 (3.9.0) | | 14.15 (2.11.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0) | | 14.13 (2.10.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0) | | 14.12 (2.9.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0) | | 14.11 (2.8.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0) | | 14.10 (2.7.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0) | | 14.9 (2.6.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0), 15.4 (3.3.0) | | 14.8 (2.5.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0), 15.4 (3.3.0), 15.3 (3.2.0) | | 14.7 (2.4.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0), 15.4 (3.3.0), 15.3 (3.2.0), 15.2 (3.1.0) | | 14.6 (2.3.0) | 17.5 (5.2.0), 17.4 (5.1.0) 16.9 (4.6.0), 16.8 (4.5.0), 16.6 (4.4.0), 16.4 (4.3.0), 16.3 (4.2.0), 16.2 (4.1.0), 16.1 (4.0.0) 15.13 (3.10.0), 15.12 (3.9.0), 15.10 (3.8.0), 15.8 (3.7.0), 15.7 (3.6.0), 15.6 (3.5.0), 15.5 (3.4.0), 15.4 (3.3.0), 15.3 (3.2.0), 15.2 (3.1.0) | | 13.9 (1.5.0) | 14.6 (2.3.0) | | 13.8 (1.4.0) | 14.6 (2.3.0) | | 13.7 (1.3.0) | 14.6 (2.3.0) | ## 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. **Before you perform a major version upgrade** 1. Identify the Babelfish version of your existing Aurora PostgreSQL DB cluster by using the commands outlined in [Identifying your version of Babelfish](babelfish-information-identify-version.md). The Aurora PostgreSQL version and Babelfish version information is handled by PostgreSQL, so follow the steps detailed in the [To use the PostgreSQL port to query for version information](babelfish-information-identify-version.md#apg-version-info-psql) procedure to get the details. 1. Verify if your version supports the major version upgrade. For the list of versions that support the major version upgrade feature, see [Upgrading Babelfish to a new minor version](babelfish-information-upgrading-minor.md) and perform the necessary pre-upgrade tasks. For example, if your Babelfish version is running on an Aurora PostgreSQL 13.5 DB cluster and you want to upgrade to Aurora PostgreSQL 15.2, then first apply all the minor releases and patches to upgrade your cluster to Aurora PostgreSQL 14.6 or higher version. When your cluster is at version 14.6 or higher, continue with the major version upgrade process. 1. Create a manual snapshot of your current Babelfish DB cluster as a backup. The backup lets you restore the cluster to its Aurora PostgreSQL version, Babelfish version, and restore all data to the state before the upgrade. For more information, see [Creating a DB cluster snapshot](USER_CreateSnapshotCluster.md). Be sure to keep your existing custom DB cluster parameter group to use again if you decide to restore this cluster to its pre-upgraded state. For more information, see [Restoring from a DB cluster snapshot](aurora-restore-snapshot.md) and [Parameter group considerations](aurora-restore-snapshot.md#aurora-restore-snapshot.Parameters). 1. Prepare a custom DB cluster parameter group for the target Aurora PostgreSQL DB version. Duplicate the settings for the Babelfish parameters from your current Babelfish for Aurora PostgreSQL DB cluster. To find a list of all Babelfish parameters, see [DB cluster parameter group settings for Babelfish](babelfish-configuration.md). For a major version upgrade, the following parameters require the same settings as the source DB cluster. For the upgrade to succeed, all the settings must be the same. + rds.babelfish\$1status + babelfishpg\$1tds.tds\$1default\$1numeric\$1precision + babelfishpg\$1tds.tds\$1default\$1numeric\$1scale + babelfishpg\$1tsql.database\$1name + babelfishpg\$1tsql.default\$1locale + babelfishpg\$1tsql.migration\$1mode + babelfishpg\$1tsql.server\$1collation\$1name **Warning** If the settings for the Babelfish parameters in the custom DB cluster parameter group for the new Aurora PostgreSQL version don't match the parameter values of the cluster that you're upgrading, the `ModifyDBCluster` operation fails. An `InvalidParameterCombination` error message appears in the AWS Management Console or in the output from the `modify-db-cluster` AWS CLI command. 1. Use the AWS Management Console or the AWS CLI to create the custom DB cluster parameter group. Choose the applicable Aurora PostgreSQL family for the version of Aurora PostgreSQL that you want for the upgrade. **Tip** Parameter groups are managed at the AWS Region level. When you work with AWS CLI, you can configure with a default Region instead of specifying the `--region` in the command. To learn more about using the AWS CLI, see [Quick setup](https://docs.aws.amazon.com/cli/latest/userguide/getting-started-quickstart.html) in the *AWS Command Line Interface User Guide*. ## Performing major version upgrade 1. Upgrade Aurora PostgreSQL DB cluster to a new major version. For more information, see [Upgrading the Aurora PostgreSQL engine to a new major version](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.Upgrading.Manual). 1. Reboot the writer instance of the cluster, so that the parameter settings can take effect. ## 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 (\$132) 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 \$132 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](babelfish-connect-sqlserver.md). ``` 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](babelfish-architecture.md#babelfish-single_vs_multi_db). ``` 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](babelfish-configuration.md). 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](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster-parameter-group.html) 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 groupin Amazon Aurora](USER_WorkingWithParamGroups.CreatingCluster.md). Use the [modify-db-cluster-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster-parameter-group.html) 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](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html) 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](https://docs.aws.amazon.com/cli/latest/reference/rds/reboot-db-instance.html) 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 ``` # Using Babelfish product version parameter A new Grand Unified Configuration (GUC) parameter called `babelfishpg_tds.product_version` is introduced from Babelfish 2.4.0 and 3.1.0 versions. This parameter allows you to set the SQL Server product version number as the output of Babelfish. The parameter is a 4-part version ID string, and each part should be separated by “.”. Syntax ``` Major.Minor.Build.Revision ``` + Major Version: A number between 11 and 16. + Minor Version: A number between 0 and 255. + Build Version: A number between 0 and 65535. + Revision: 0 and any positive number. ## Configuring Babelfish product version parameter You must use the cluster parameter group to set the babelfishpg\$1tds.product\$1version parameter in the console. For more information on how to modify the DB cluster parameter, see [Modifying parameters in a DB cluster parameter groupin Amazon Aurora](USER_WorkingWithParamGroups.ModifyingCluster.md). When you set the product version parameter to an invalid value, the change will not take effect. Although the console might show you the new value, the parameter retains the previous value. Check the engine log file for details about the error messages. For Linux, macOS, or Unix: ``` aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name mydbparametergroup \ --parameters "ParameterName=babelfishpg_tds.product_version,ParameterValue=15.2.4000.1,ApplyMethod=immediate" ``` For Windows: ``` aws rds modify-db-cluster-parameter-group ^ --db-cluster-parameter-group-name mydbparametergroup ^ --parameters "ParameterName=babelfishpg_tds.product_version,ParameterValue=15.2.4000.1,ApplyMethod=immediate" ``` ## Affected queries and parameter | Query/Parameter | Result | Effective time | | --- | --- | --- | | SELECT @@VERSION | Returns user defined SQL Server version (babelfishpg\$1tsql.version value = Default) | Immediately | | SELECT SERVERPROPERTY('ProductVersion') | Returns user defined SQL Server version | Immediately | | SELECT SERVERPROPERTY('ProductMajorVersion') | Returns Major Version of the user defined SQL Server version | Immediately | | VERSION tokens in PRELOGIN Response Message | Server returns PRELOGIN messages with user defined SQL Server version | Takes effect when a user creates a new session | | SQLServerVersion in LoginAck when using JDBC | DatabaseMetaData.getDatabaseProductVersion() returns user defined SQL Server version | Takes effect when a user creates a new session | ## Interface with babelfishpg\$1tsql.version parameter You can set the output of the @@VERSION using the parameters babelfishpg\$1tsql.version and babelfishpg\$1tds.product\$1version. The following examples show how these two parameters interface. + When babelfishpg\$1tsql.version parameter is "default" and babelfishpg\$1tds.product\$1version is 15.0.2000.8. + Output of @@version – 15.0.2000.8. + When babelfishpg\$1tsql.version parameter is set to 13.0.2000.8 and babelfishpg\$1tds.product\$1version parameter is 15.0.2000.8. + Output of @@version – 13.0.2000.8.