Planning database migrations: A SQL Server to Snowflake Postgres case study

Moving an application from SQL Server to Snowflake Postgres involves more than converting SQL or changing a JDBC URL. Configuration can still carry assumptions from the previous database host: a provider-specific wrapper, a pinned driver, TLS settings, and writer-routing behavior. This case study makes that hidden work concrete with Keycloak, an identity server that already supports PostgreSQL and creates its target schema with Liquibase. That removes dialect conversion from the critical path and lets us focus on finding, scoping, and tracking the work that can still break the cutover.

Keycloak's Aurora guide contains exactly those assumptions. Snowflake is the target in this scenario, not a Codemod customer, and we did not cut a cluster over. On codemod/keycloak-java at ebde947d, Codemod Grep is how we investigated them, and Codemod Insights is how we kept the resulting scope visible and owned.

The migration risk is host-specific configuration and session loss, not SQL compatibility

Keycloak selects PostgreSQL in configuration: db=postgres, the KC_DB* settings, and kc.sh build --db=postgres. Liquibase in model/jpa creates the schema on the target. SQL Server support can stay in the tree. Nobody has to rewrite the dialect to move one cluster.

kc.sh export moves realms, clients, roles, groups, and users. It does not move sessions. Users log in again. That, plus the host contract above, is the project to staff.

Separate upstream examples from production configuration before estimating the migration

File counts in this repository are not running services. The deployment you cut over is the Helm values, Operator CRs, and images you actually run, often in another repo. This tree is upstream Keycloak: high-availability examples, the operator, Quarkus mappers, and CI jobs that provision Aurora.

Codemod Grep runs ast-grep against ebde947d. A document rule can require two strings in the same YAML file. A block_mapping_pair rule can match a key and skip a Java local that happens to use the same word. Every hit below is still inside upstream Keycloak.

None of that is a cluster you run. Point the same rules at the repo that holds production values. If that list is empty, the config work is a small allowlist, and the weeks go to rehearsal.

The first two rows are separate string counts. The migration-sized question is whether one YAML document sets both the URL and the driver. Two independent counts will call a driver in a test and a URL in a doc two projects.

id: yaml-wrapper-url-with-aws-driver
language: yaml
rule:
  kind: document
  all:
    - has:
        regex: "jdbc:aws-wrapper:postgresql:"
        stopBy: end
    - has:
        regex: "software\\.amazon\\.jdbc\\.Driver"
        stopBy: end

This search is the wrapper URL alone, in the generated high-availability examples. The pairing rule also matches the Aurora job in ci.yml.

A text search for usernameSecret also hits ClientIdAndSecretAuthenticator, which parses a client Basic-auth header. The block_mapping_pair rule leaves that class out. Of the seven YAML keys, four are examples and three are tests or CRD schema. I would not open a secret-rotation program on them.

Treat the JDBC driver swap as a routing change, not dependency cleanup

docs/guides/high-availability/examples/generated/keycloak.yaml sets vendor: postgres, a wrapper URL, software.amazon.jdbc.Driver, and db-tls-mode: verify-server. Changing the hostname leaves the AWS driver in place. Switching to the stock PostgreSQL driver, which is the right call for a host that is not Aurora, wakes a different default.

DatabasePropertyMappers does this for the primary datasource:

fromOption(SYNTHETIC_RUNTIME_DB_OPTION).mapFrom(DB, (name, value, context) -> "primary")
        .to(PG_TARGET_SERVER_TYPE)
        .isEnabled(DatabasePropertyMappers::isPostgresqlTargetServerTypeEnabled)
        .build(),

PG_TARGET_SERVER_TYPE is quarkus.datasource.jdbc.additional-jdbc-properties.targetServerType. The mapper sets primary when the vendor is Postgres, the driver is the standard one, and the URL does not already contain targetServerType. A custom driver disables it. The comment names the AWS JDBC wrapper. ConfigurationTest checks both: --db=postgres yields primary; --db-driver=software.amazon.jdbc.Driver yields nothing. Aurora deployments that pin the AWS driver never exercised this default. The first boot on the standard driver is the one that does.

Keycloak's server guide records the failure mode. After a failover, PgBouncer can keep reporting in_hot_standby=on. The PostgreSQL driver then refuses the connection:

Could not find a server with specified targetServerType: primary

The guide's remedy is targetServerType=any, and only if something else already sends sessions to the writable primary. For Snowflake Postgres, set any or set the parameter yourself, so the mapper stays quiet. We have not opened that connection. The error string is a test you still owe.

A Grep rule for targetServerType= on a string concatenation finds the wrong file: DefaultJpaConnectionProviderFactory.addPostgreSQLKeywords under testsuite/.../connections/jpa/. The mapper above never builds that string, so the rule stops at the testsuite. An absence check on the YAML misses it too. The parameter is absent from the manifest because the mapper adds primary later, and only after the driver is the standard one.

Leave the four native queries that only run on MySQL

Under model/jpa/ the createNativeQuery rule returns 9 calls in 5 files: cluster events, JpaRealmProvider, JpaUtils, revoked tokens, and single-use objects. That is a staging queue, not a rewrite list. An agent that "fixes" every call will invent work for the wrong database.

Four of the nine sit inside JpaUtils.isUpsertRowCountUnreliable, which is true only for MySQLDialect, in JpaSingleUseObjectProvider and JpaRevokedTokenProvider. The INSERT IGNORE statements and the deletes beside them do not run on PostgreSQL. Leave them. Rehearse the rest. JpaRealmProvider builds the table name at runtime:

String clientScopeMapping = JpaUtils.getTableNameForNativeQuery("SCOPE_MAPPING", em);
em.createNativeQuery("delete from " + clientScopeMapping + " where ROLE_ID = :role")

createQuery and createNamedQuery are JPQL, so they stay off this list. A miner that also counts prepareStatement will print a larger number. The upstream queue on this commit is these nine calls. Extensions get their own pass for TOP, NEWID, GETDATE, and custom Liquibase. That is where a SQL Server converter belongs. Keycloak's own SQL Server changelogs are dialect support.

Save the Grep questions on an Insights board, and keep the counts separate

Grep is the search you throw away when the rule was wrong. Insights is the one you keep. The read-only package snowflake-postgres-discovery-bundle feeds this demo board: Snowflake → Postgres migration planning (Keycloak) on keycloak-java. The board tracks. It does not edit the repo.

The pairing widget is not a second copy of the Grep figure. The document rule found 3 YAML files with both the wrapper URL and the AWS driver, including a GitHub Actions workflow. This widget reports wrapper-url-with-aws-driver as 1 XML file, and keeps aws-driver-only visible. Do not add 3 and 1. The burn-down is the pairing column, on the repos you have decided are in scope.

default-append is the testsuite concatenation. property-key is the Quarkus mapper. Judge TLS against one rule, sslmode=verify-full plus a truststore, not against every sslmode row. The native-SQL panel is wider on purpose: it includes prepareStatement. The queue to assign this week is still the nine createNativeQuery calls under model/jpa/.

On this board the one XML pairing sits with @keycloak/core-protocols-maintainers. That names an owner in the product repo. It does not assign your Helm chart.

Agents should open this board and these Grep rules, or every session starts from a fresh JDBC regex. A clean board is one acceptance check. Pod config and a login smoke are the other. A flat chart means the metric held still, not that the cutover is underway.

Decide driver, TLS, and routing before you staff the window

On this migration, fill that motion in before anyone edits a URL.

• Driver: jdbc:postgresql: and the PostgreSQL driver. Keep the AWS wrapper only where the host is still Aurora.
• TLS: Snowflake Postgres requires SSL and certificate checks. Keycloak's mapper can set sslmode=verify-full and pass a truststore as sslrootcert. The high-availability example sets db-tls-mode: verify-server. disabled, a missing truststore, or a raw JDBC URL that skips the mapper fails the bar you pick.
• Routing: targetServerType=any, or an explicit parameter, so DatabasePropertyMappers does not inject primary. Try it against the Snowflake Postgres endpoint in non-prod. Until that attempt, the decision is unsigned.
• Pool size: take the configured maximums into a capacity review. Aurora's pool numbers are not a forecast for this host.

For one cluster, at most about a million users, and few extensions, we would staff roughly 8 to 13 weeks: classify the hits, change the allowlisted config and image, stand up non-prod, rehearse export and import at least twice, take one production window, then decommission SQL Server. That is a staffing shape. We have not run it.

kc.sh export and kc.sh import move realms, clients, roles, groups, and users. Events and sessions do not. Tell relying parties and the helpdesk before the window, or Monday is an incident. Copy EVENT_ENTITY and ADMIN_EVENT_ENTITY out first when audit retention needs the history. Table copy is the exception, when sessions have to survive inside Keycloak or export is too slow. Rollback is revert KC_DB* and the image, same Keycloak version on both sides, while SQL Server is still read-only.

I would not call the window done without all of these:

• In-scope deployment files classified, and every wrapper-plus-driver pair either gone or explicitly accepted
• targetServerType tried against the Snowflake Postgres host
• TLS matching the agreed rule on the paths that actually connect
• The native-SQL queue owned, and a staging run that covers the calls which execute on PostgreSQL
• Two clean rehearsals, matching row counts, login latency inside the target, rollback rehearsed, relying parties signed off

Payback is removed SQL Server licenses, host, backup, and DBA time, minus Snowflake Postgres compute (double it for a high-availability deployment), storage, transfer, and an optional event archive. Check that forecast against account usage once an instance exists. The room is the IAM platform team, a DBA, SRE, security, network, every relying party, helpdesk, whoever reads Keycloak tables, and finance.

A clean dashboard cannot replace an end-to-end migration rehearsal

The two rehearsals are where you learn export duration, whether the row counts match, and what login latency does. The Insights board will not tell you. PostgresTestDatabase and PostgreSQLContainer only prove a local PostgreSQL.

SQL Server support stays. A later config codemod, if you write one, edits the deployment allowlist, keeps JDBC parameters it was not asked to touch, and can be run twice. It leaves upstream Keycloak and createNativeQuery alone.

If the deployment repo and the extensions repo are clean, what remains is rehearsal, the session-reset notice, and the window. If extensions contain T-SQL, that repo is the critical path and the 8 to 13 weeks move. Keep one Grep pass and one Insights pairing column. Adding those numbers together invents a project.