ARTIFACTORY: How to Resolve Database Migration Failures and "node_id" Errors by Enforcing a Single PostgreSQL Schema

Introduction 
During an Artifactory upgrade (e.g. upgrading to version 7.117.x or later), the application may fail to start, with the Access service refusing to initialize. Upon reviewing the application logs, you will see a database exception stating:

ERROR: column "node_id" does not exist

This article explains how this error is caused by a violation of the "Single Schema Requirement" in PostgreSQL databases, leading to a split where database migrations run in one schema while the application reads from another. It also provides steps to resolve the mismatch by strictly enforcing a single schema.


Problem

Access uses Flyway to manage its database migrations. A strict requirement for Access is that it must use the same PostgreSQL schema for Flyway migrations as it does for runtime operations.

If the schema used by Flyway differs from the schema where the application connects, the migrations will run in "Schema A" (updating the access_schema_version there), while the application attempts to use tables in "Schema B" that were never migrated.

In a real-world scenario, a database might inadvertently contain two schemas, such as artifactory (containing the historical data) and public. When Artifactory starts up, it determines which schema to use based on its configuration. If the currentSchema or search_path parameters are not explicitly defined in the JDBC URL, Access checks for the access_servers table in the public schema. If it fails to see the existing tables in the intended artifactory schema, the migration tool may assume it is a new installation and create a parallel set of tables in the public schema.

Consequently, the Flyway migration successfully executes the required upgrades—such as adding the node_id column to the access_servers table, which is introduced in Access migration during the 7.117.x version upgrade—but it applies them to the wrong schema. Meanwhile, the runtime application connects to the old artifactory schema, which is stuck on an older schema version and lacks the new column, resulting in the ‘column "node_id" does not exist’ startup failure.


Resolution
To resolve this issue, you must ensure that Artifactory is configured to use a single schema everywhere and that Flyway can run all pending migrations in that designated schema. Do not attempt to manually apply the missing column (e.g. adding node_id to the old table), as many other sequential migrations must also execute properly in the correct schema.

Step-by-Step Instructions

1. Verify the Schema Mismatch: Run the following query against your database to check which schemas currently contain the access_schema_version and access_servers tables:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name IN ('access_schema_version', 'access_servers')
ORDER BY table_schema, table_name;

If you see these tables in both your named schema (e.g. artifactory) and the public schema, a "split" has occurred.

2. Compare Migration Versions: Identify which schema is "stuck" by checking the latest successful migration in each:

-- Check the latest version in the named schema (e.g. artifactory)
SELECT 'artifactory' as schema, version, description, installed_on 
FROM artifactory.access_schema_version 
ORDER BY installed_rank DESC LIMIT 1;

-- Check the latest version in the public schema
SELECT 'public' as schema, version, description, installed_on 
FROM public.access_schema_version 
ORDER BY installed_rank DESC LIMIT 1;

How to Interpret the Results:

• The Historical Schema (Keep this one): This schema will show a long history of migrations but will be stuck on an older version, such as 7.1136.3 | 0  AddMlActionsToProjectAdmin. This is the schema that contains all your data.

• The Accidental Schema (Clean this one): This schema will likely have very few entries in its migration history, but its latest version will be greater than 7.1136.3 (e.g. 7.1145.0.0 | MigrateProjectResourcesAgain and higher, containing any version matching the newer Artifactory release you are installing). This schema was created automatically by Flyway during the failed upgrade and does not contain your real data.

3. Database Backup: Before making any structural changes to the database, perform a complete database backup.

4. To ensure one schema everywhere, choose the option below that fits your intended database design:

   1. Option A (tables in public):

      1. Do not add currentSchema or search_path to the JDBC URL.

      2. By leaving these unset, both Flyway and the application will default to using the public schema.

   2. Option B (named schema, e.g. artifactory):

      1. Set access.database.schemaName to the desired schema (e.g. artifactory) in system.yaml.

      2. Ensure the JDBC URL uses the same schema by appending ?currentSchema=artifactory to the connection string in system.yaml.

      3. Ensure all Access tables (including access_servers and access_schema_version) live in that schema. If Flyway mistakenly created an empty set of tables in public during the failed upgrade, you can clean the incorrect schema by running:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO public;

(Note: If your data is in the public schema, DO NOT run the commands above. Instead, follow Option A to point your configuration to the public schema.)

5. Restart Artifactory: Restart Artifactory to trigger a fresh startup. With the configuration strictly aligned so one schema is used everywhere, the migration process will attempt to run again within the correct, isolated schema without issues.