# PostgreSQL 12 to 16 Schema Drift

In Sourcegraph versions `5.10.x` and `5.11.x` we support both PostgreSQL 12 and 16. However, Sourcegraph's database management tool `migrator` expects the database schema of the various Sourcegraph databases to be in an exact expected state. The upgrade from PostgreSQL 12 to 16 is opinionated and automatically mutates the schema without running our application defined migrations. Starting in Sourcegraph `5.10.0` we expect databases to be in PostgresSQL 16 and as such our tooling will identify schema drift in PostgreSQL 12 databases. This drift does not impact the functionality of the Sourcegraph instance but will stop migrator's multiversion `upgrade` command from executing. Note that starting in [Sourcegraph 7.0.0](https://sourcegraph.com/changelog/releases/7.0), the entrypoint script that performs the automatic PostgreSQL 12 to 16 upgrade has been removed. Users must complete this upgrade on a 6.x release.

> **Note:** The autoupgrade feature has been removed in Sourcegraph 6.12.0 and later. The instructions below for autoupgrade only apply to versions prior to 6.12.0.

The drift takes the following general form, dropping table prefixes to columns in views, and changing `uuid` types to `gen_random_uuid()`:

```diff
Schema drift detected in 8 objects:

webhooks.uuid:
Problem: Unexpected properties of column webhooks."uuid"
Solution: alter the column
Statement: ALTER TABLE webhooks ALTER COLUMN uuid SET DEFAULT public.gen_random_uuid();

batch_spec_workspace_execution_queue:
Problem: Unexpected definition of view "batch_spec_workspace_execution_queue"
Solution: redefine the view
Diff:
  (
  	"""
  	... // 6 identical lines
  	          ORDER BY (rank() OVER (PARTITION BY queue.user_id ORDER BY exec.created_at, exec.id)), queue.latest_dequeue NULLS FIRST
  	        )
- 	 SELECT id,
+ 	 SELECT queue_candidates.id,
  	    row_number() OVER () AS place_in_global_queue,
- 	    place_in_user_queue
+ 	    queue_candidates.place_in_user_queue
  	   FROM queue_candidates;
  	"""
  )

codeintel_configuration_policies:
Problem: Unexpected definition of view "codeintel_configuration_policies"
Solution: redefine the view
Diff:
  (
  	"""
- 	 SELECT id,
- 	    repository_id,
- 	    name,
- 	    type,
- 	    pattern,
- 	    retention_enabled,
- 	    retention_duration_hours,
- 	    retain_intermediate_commits,
- 	    indexing_enabled,
- 	    index_commit_max_age_hours,
- 	    index_intermediate_commits,
- 	    protected,
- 	    repository_patterns,
- 	    last_resolved_at,
- 	    embeddings_enabled
+ 	 SELECT lsif_configuration_policies.id,
+ 	    lsif_configuration_policies.repository_id,
+ 	    lsif_configuration_policies.name,
+ 	    lsif_configuration_policies.type,
+ 	    lsif_configuration_policies.pattern,
+ 	    lsif_configuration_policies.retention_enabled,
+ 	    lsif_configuration_policies.retention_duration_hours,
+ 	    lsif_configuration_policies.retain_intermediate_commits,
+ 	    lsif_configuration_policies.indexing_enabled,
+ 	    lsif_configuration_policies.index_commit_max_age_hours,
+ 	    lsif_configuration_policies.index_intermediate_commits,
+ 	    lsif_configuration_policies.protected,
+ 	    lsif_configuration_policies.repository_patterns,
+ 	    lsif_configuration_policies.last_resolved_at,
+ 	    lsif_configuration_policies.embeddings_enabled
  	   FROM lsif_configuration_policies;
  	"""
  )

codeintel_configuration_policies_repository_pattern_lookup:
Problem: Unexpected definition of view "codeintel_configuration_policies_repository_pattern_lookup"
Solution: redefine the view
Diff:
  (
  	"""
- 	 SELECT policy_id,
- 	    repo_id
+ 	 SELECT lsif_configuration_policies_repository_pattern_lookup.policy_id,
+ 	    lsif_configuration_policies_repository_pattern_lookup.repo_id
  	   FROM lsif_configuration_policies_repository_pattern_lookup;
  	"""
  )

lsif_dumps:
Problem: Unexpected definition of view "lsif_dumps"
Solution: redefine the view
Diff:
  strings.Join({
  	" SELECT ",
+ 	"u.",
  	"id,\n    ",
+ 	"u.",
  	"commit,\n    ",
- 	"root,\n    queued_at,\n    uploaded_at,\n    state,\n    ",
+ 	"u.root,\n    u.queued_at,\n    u.uploaded_at,\n    u.state,\n    u.",
  	"failure_message,\n    ",
+ 	"u.",
  	"started_at,\n    ",
+ 	"u.",
  	"finished_at,\n    ",
+ 	"u.",
  	"repository_id,\n    ",
+ 	"u.",
  	"indexer,\n    ",
+ 	"u.",
  	"indexer_version,\n    ",
+ 	"u.",
  	"num_parts,\n    ",
+ 	"u.",
  	"uploaded_parts,\n    ",
+ 	"u.",
  	"process_after,\n    ",
+ 	"u.",
  	"num_resets,\n    ",
+ 	"u.",
  	"upload_size,\n    ",
+ 	"u.",
  	"num_failures,\n    ",
+ 	"u.",
  	"associated_index_id,\n    ",
+ 	"u.",
  	"expired,\n    ",
+ 	"u.",
  	"last_retention_scan_at,\n    ",
+ 	"u.",
  	"finished_at AS processed_at\n   FROM lsif_uploads u\n  WHERE ((",
+ 	"u.",
  	"state = 'completed'::text) OR (",
+ 	"u.",
  	"state = 'deleting'::text));",
  }, "")

outbound_webhooks_with_event_types:
Problem: Unexpected definition of view "outbound_webhooks_with_event_types"
Solution: redefine the view
Diff:
  (
  	"""
- 	 SELECT id,
- 	    created_by,
- 	    created_at,
- 	    updated_by,
- 	    updated_at,
- 	    encryption_key_id,
- 	    url,
- 	    secret,
+ 	 SELECT outbound_webhooks.id,
+ 	    outbound_webhooks.created_by,
+ 	    outbound_webhooks.created_at,
+ 	    outbound_webhooks.updated_by,
+ 	    outbound_webhooks.updated_at,
+ 	    outbound_webhooks.encryption_key_id,
+ 	    outbound_webhooks.url,
+ 	    outbound_webhooks.secret,
  	    array_to_json(ARRAY( SELECT json_build_object('id', outbound_webhook_event_types.id, 'outbound_webhook_id', outbound_webhook_event_types.outbound_webhook_id, 'event_type', outbound_webhook_event_types.event_type, 'scope', outbound_webhook_event_types.scope) AS json_build_object
  	           FROM outbound_webhook_event_types
  	... // 2 identical lines
  	"""
  )

site_config:
Problem: Unexpected definition of view "site_config"
Solution: redefine the view
Diff:
  (
  	"""
- 	 SELECT site_id,
- 	    initialized
+ 	 SELECT global_state.site_id,
+ 	    global_state.initialized
  	   FROM global_state;
  	"""
  )
```

## Solutions for Handling Schema Drift

If you're confident that your instance is seeing database drift associated with the PG12 to PG16 upgrade, you can run a multiversion upgrade via migrator `upgrade` using the `--skip-drift-check` flag.

To run migrator's `upgrade` command add the `--skip-drift-check` flag to migrator's entrycommand as below:

```yaml
command: ['upgrade', '-from', '5.5.0', '-to', '5.10.0', '--skip-drift-check=true']             
```

If you have any concerns about the drift, please reach out to us at support@sourcegraph.com