Change the type of column geo_event_log.geo_event_id from integer to bigint

added devopssystems groupgeo maintenancescalability sectioncore platform labels

assigned to @ulisesf

added 1 deleted label

The following customer is interested in this capability

Subscription: GitLab Premium
Product: self-managed & ~"SaaS"
SFDC Link: https://gitlab.my.salesforce.com/00161000004bZPDAA2
Priority: customer priority::High
Customer Comment::needed as an option for a work around to prevent downtime
CSM: @ericstrait1
PM to mention: @sranasinghe

added customer label

Marking this as a bug since a customer has now hit this and we need to resolve it ASAP. Since we know what needs to be done I've put a workflowready for development and geoactive label.

added bugfunctional typebug workflowready for development + 1 deleted label and removed typemaintenance label

removed maintenancescalability label

added 1 deleted label and removed 1 deleted label

changed milestone to %16.7

Marking this as a severity1 priority1 until we can confirm a workaround.

added priority1 severity1 labels

assigned to @aakriti.gupta

added workflowin dev label and removed workflowready for development label

mentioned in merge request !138646 (merged)

I am in favour of truncating the event related tables as a rake task, as @ulisesf suggested on Slack:

TRUNCATE geo_cache_invalidation_events,
         geo_repository_created_events,
         geo_repository_updated_events,
         geo_repository_deleted_events,
         geo_repository_renamed_events,
         geo_repositories_changed_events,
         geo_hashed_storage_migrated_events,
         geo_hashed_storage_attachments_events,
         geo_lfs_object_deleted_events,
         geo_job_artifact_deleted_events,
         geo_upload_deleted_events,
         geo_reset_checksum_events,
         geo_event_log RESTART IDENTITY RESTRICT;

With the sql TRUNCATE command we can't choose to limit how many records we delete.

Do we need to keep events until a certain point @mkozono @dbalexandre ?

Ideally we want to truncate upto the point where we safely don't need those events - that could be as late as the last backup or the last time secondary was fully synced. But do we need that for a subsequent successful sync?

Do we need to keep events until a certain point @mkozono @dbalexandre ?

@aakriti.gupta It seems we need to keep until the minimum value of all cursor_last_event_ids. See https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/workers/geo/prune_event_log_worker.rb#L31.

Will trancating the table allow Geo secondaries to work?

@sranasinghe Yes, I think truncating the table will allow the customer to return the Geo secondaries online since we are restarting the auto-increment counters. The other option is to manually migrate the column to bigint if the customer is okay with some downtime/long locks, which would fix the issue quickly.

@dbalexandre (the customer here) - I did actually force the geo_event_log:geo_event_id column to bigint (by adding the new column, copying over values from the old column and renaming columns inside a transaction) but it didn't resolve the problem. That might be because a trigger or foreign key was still referencing the old column, not sure.

I'm in favor of truncating the geo_events and geo_event_log tables, if that works, since we've removed all Geo config entirely so starting from scratch has the benefit of pruning all the unnecessary history from these tables.

There was more discussion in Slack. I'm attempting to write down a workaround until a migration for this is released. I have not tested these steps, so only attempt them first on a staging environment, and let me know if I made a mistake or how this can be improved.

If you are encountering this issue and your primary is down

To initially get back up:

Route all user requests to the primary site.
Disable Geo by deleting the geo_nodes records (don't need to remove PG replication slots or edit configuration, since we will be reenabling Geo soon):
1. In the primary site, in Rails console or runner, GeoNode.delete_all
2. In the primary site, restart Puma and Sidekiq in all nodes where they are running gitlab-ctl restart puma; gitlab-ctl restart sidekiq

To resolve the ID limit, and get the secondary site back up

In the primary site, SSH into the PG node (the leader node if running a cluster). Truncate the Geo events tables and change the affected column to bigint so that it can handle very high IDs. Note that after commit, the statement might look blocked for quite some time (no return prompt) until the data is freed. This doesn't mean that the table will be locked.
```
begin;
truncate table geo_event_log;
ALTER TABLE geo_event_log ALTER COLUMN geo_event_id TYPE bigint;
truncate table geo_events cascade;
commit;
```
Reset replication at the secondary site: https://docs.gitlab.com/ee/administration/geo/replication/troubleshooting.html#resetting-geo-secondary-site-replication
Add the primary geo_nodes record back. In the primary site, in a node which is running Puma or Sidekiq:
```
sudo gitlab-ctl set-geo-primary-node
```
In every node of the primary site which is running Puma or Sidekiq, restart GitLab services: gitlab-ctl restart.
Add the secondary geo_nodes record back: https://docs.gitlab.com/ee/administration/geo/replication/configuration.html#step-3-add-the-secondary-site
Restart GitLab on primary and secondary sites. The main services we want to restart are Puma/Sidekiq/Geo Log Cursor, though it's not a bad idea to restart everything.

If you are not yet affected by this issue, but your `geo_event_log.geo_event_id` is nearing the limit

If your geo_event_log table happens to be small (we don't have a precise size at this time), then you can mitigate this issue in advance by running ALTER TABLE geo_event_log ALTER COLUMN geo_event_id TYPE bigint; on the primary site's Postgres database. This statement locks the table but it may be fast enough if the table is small.
If the tables are large, then this command may lock the table for too long, and the statement may timeout. In that case, you may need to perform the full workaround which disables Geo and resets Geo replication. Truncating the tables in order to alter the column loses Geo events, which may lead to data loss and orphan data on secondaries.

How to determine if your `geo_event_log.geo_event_id` is nearing the limit

Visit Admin Area > Geo > Sites
In the primary site, look at Last event ID
Compare that number with the limit of a Postgres int: 2,147,483,647

Now that we have a workaround, I am lowering the severity and priority of this issue to severity2 priority2. Thank you to everyone who helped identify a workaround.

added priority2 severity2 labels and removed priority1 severity1 labels

mentioned in issue gitlab-org/geo-team/discussions#5066 (closed)

mentioned in issue gitlab-org/geo-team/discussions#5101 (closed)

changed milestone to %16.8

added missed:16.7 label

Step 1 out of 4 is done for %16.8

There are 3 more steps to finish this issue, which will be released in consecutive releases. I will keep updating the milestone as they are done.

After Step 2 in %"16.9" the problem will be resolved already

Reopening this because step 2: !143652 (merged) is in review now and will hopefully be merged for %16.9

changed milestone to %16.9

added workflowverification label and removed workflowin dev label

👋 @juan-silva, @sranasinghe and @nwestbury,

This groupgeo bug has at most 50% of the SLO duration remaining and is an SLONear Miss breach. Please consider taking action before this becomes an SLOMissed in 29 days (2024-02-03).

changed due date to February 03, 2024

added SLONear Miss label

mentioned in issue gitlab-org/geo-team/discussions#5107 (closed)

closed

The workflow label was automatically updated to workflowcomplete because you closed the issue while in workflowverification.

If this is not the correct label, please update.

To avoid this message, update the workflow label as you close the issue. This message was generated automatically. You're welcome to improve it.

added workflowcomplete label and removed workflowverification label

removed 1 deleted label

mentioned in merge request !143652 (merged)

reopened

removed workflowcomplete label

added 1 deleted label

added SLOMissed label and removed SLONear Miss label

mentioned in issue gitlab-org/geo-team/discussions#5110 (closed)

added workflowvalidation backlog label

changed milestone to %16.10

added missed:16.9 label

@aakriti.gupta any more work to be done on this? Looks like the last MR was merged in 16.9.

Only a couple of clean up tasks are remaining: https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#remove-the-trigger-and-old-integer-columns-release-n--2

mentioned in issue gitlab-org/geo-team/discussions#5111 (closed)

added workflowready for development label and removed workflowvalidation backlog label

added workflowin dev label and removed workflowready for development label

mentioned in issue gitlab-org/geo-team/discussions#5112 (closed)

added workflowready for development label and removed workflowin dev label

changed milestone to %16.11

changed milestone to %17.0

unassigned @aakriti.gupta

mentioned in issue gitlab-org/geo-team/discussions#5115 (closed)

changed milestone to %17.1

added missed:17.0 label

changed milestone to %17.2

added missed:17.1 label

Now that there are only clean-up tasks remaining I am lowering the severity and priority of this issue to severity3 priority3

I opened !180270 (merged) to clean up the bigint conversion for geo_event_log.geo_event_id.

@aakriti.gupta with Douglas's cleanup MR above, are we able to close this bug or are there more cleanup to do here? Can you also help me confirm the weight I've put on this bug issue?