Combined: Archiving issues and reindexing outside of the cluster

To minimize downtime for your users, you’ll reduce the size of the index by archiving issues, essentially excluding them from indexing, and create the index outside of the production cluster. With a working index in place, you’ll be able to open Jira for your users. Later on, you’ll restore and reindex all archived issues, this time on a working Jira.

Overview

In this approach, you’ll combine issue archiving and reindexing outside of the production cluster.

Before you begin

Identifying issues to be archived

In this step, you need to identify issues you won’t need right after the upgrade.

Creating an SQL query to archive issues

Once you’ve identified issues to be archived, you need to create an SQL query that will archive them. Here are some example queries that we’ve built (based on PostgreSQL database).

update jiraissue 
	set 
		archived='Y', 
		archivedby='admin', 
		archiveddate=current_timestamp 
	where id in (
		select 
			i.id 
		from jiraissue i 
		where i.created < timestamp '2019-01-01'	
);

update jiraissue 
	set 
		archived='Y', 
		archivedby='admin', 
		archiveddate=current_timestamp 
	where id in (
		select 
			i.id 
		from jiraissue i 
		where i.updated < (current_timestamp - interval '2 year')	
);

update jiraissue 
	set 
		archived='Y', 
		archivedby='admin', 
		archiveddate=current_timestamp 
	where id in (
		select 
			i.id 
		from jiraissue i 
		inner join project p on i.project = p.id
		inner join label l on l.issue = i.id
		where UPPER(p.pkey) = 'JIRA' 
			and l.label = 'archive'
			and i.created  between timestamp '2019-01-01' and timestamp '2019-10-01'	
);

Modifying the database

To minimize downtime, you can complete these steps on a live database.

These steps will modify the database schema to include columns required for Issue archiving.

1. Modify the database schema to make it support issue archiving. The following SQL query will add three new columns to the jiraissue table. 

   • PostgreSQL:

     alter table jiraissue
             add archived char default null,
             add archivedby varchar(255) default null,
             add archiveddate timestamp with time zone default null;

   • Oracle:

     alter table jiraissue add (
         archived CHAR default null,
         archivedby varchar2(255) default null,
         archiveddate date default null);

2. Once the new columns have been added, run another SQL query that will set the status of all your issues to “not archived”. 

   update jiraissue set archived='N', archivedby=null, archiveddate=null;

3. Just to be safe, open Jira and try creating a few issues to check if everything looks right.

4. Run the SQL query that archives the issues you’ve chosen. You should’ve prepared it in Creating an SQL query to archive issues. The “archived” issues won’t change in any way in your source version, 7.x.

Creating the index outside of the cluster

You’ll make a copy of your database and connect Jira 8.5 to it. This will allow you to create an index that is compatible with Jira 8 outside of your production cluster.

1. Copying the NodeIndexCounter table

• Make a copy of the NodeIndexCounter table from your database.

Why do I need this?

The NodeIndexCounter table records the changes that were applied by individual nodes to their copies of the index. By copying it now, and restoring it later to your original database (during the upgrade), you’ll make sure that the upgraded nodes include all possible changes made by your users from this point on.

2. Copying the database

• Make a copy of your live database. Don’t worry about users adding new data, because you’ll switch back to the original database later on.

3. Connecting the database copy to Jira 8.5

You’ll need a separate Jira 8.5 environment that you’ll connect your database copy to. Here are requirements for this environment:

• Must be Jira 8.5.

• Must have the same plugins as your production instance (plugins can provide data coming from custom fields, and add data to the index during reindexing).

• Must have a Data Center license (Server wouldn’t recognize issues as archived), but you can use a single node.

The easiest way to set up this environment is to complete these steps:

1. Follow Upgrade Jira on the first node from Upgrading Jira Data Center. When configuring Jira 8.5, make sure to connect it to the copy of your database that you created in the previous steps.

2. Follow Post-upgrade steps on the first node from Upgrading Jira Data Center. 

Result

After completing the manual reindex, you should have a Jira 8 index that’s based on the information from the copy of your database. It’s still missing all the information that was added to the original database since you’ve made the copy, but it will be easier for your nodes to add missing entries for these changes than create the whole index from scratch.

Copying the index to your nodes

The Jira 8 index is stored in a different directory than the old one, so it won’t affect your existing cluster. You can already copy the new index to each of the nodes, so they don’t have to download it.

1. Copy the Jira 8 index to the following directory on each node: 

   <local-home-directory>/caches/indexesV1

Upgrading Jira Data Center

Upgrading the first node

We recommend that you upgrade Jira Data Center by choosing a single node, upgrading it, and then copying the installation directory to other nodes, just to save time. You can choose any node here.

1. Stop the cluster.

2. Restore the NodeIndexCounter table, which you copied at the beginning of this procedure, to your original database. This will make sure that the nodes create entries for all the information added to your original database since you’ve started the upgrade.

3. Upgrade the first node. Follow Upgrade Jira on the first node from Upgrading Jira Data Center. Before you start, make sure to read the note below. 

   Disabling automatic reindex

   The last step of the linked procedure will tell you to disable automatic reindex (Step 5: Disabling automatic reindex). Make sure to complete it, as otherwise Jira will initiate automatic reindex, something you’re trying to avoid.

4. Complete post-upgrade steps on the first node. Follow Post-upgrade steps on the first node from Upgrading Jira Data Center. 

   One of the steps will tell you to rebuild index. Make sure to omit this step, as you have already created the index outside of the cluster, so there’s no need to create it again.

What happens here?

After you start the first node, the database will be upgraded. As the next step, the node would check the state of the index, and reindex if needed. But, since you’ve already copied a compatible index, the node will detect it and only add entries for the missing changes — the difference between you original and cloned database that you used to create the Jira 8 index. From the node’s perspective, it looks like it was offline for a short while.

Optional: Requesting a snapshot of the index

This optional step can make your nodes download a snapshot of the index from the first node instead of replaying the updates from the database.

Upgrading remaining nodes

You can now copy the installation directory to remaining nodes to upgrade them.

1. Upgrade remaining nodes. Follow Upgrade remaining nodes from Upgrading Jira Data Center.

Restoring the archived issues and reindexing again

Now that your Jira Data Center is upgraded and functional, it’s time to restore the archived issues and reindex again. You don’t have to close Jira to your users, because reindexing will be performed on a single node.

1. Run a reverse SQL query to change the status of archived issues to “not archived”. To make it simple, you can use the following query to change the status for all issues.

2. Perform a reindex.

   1. Go to  > System > Indexing (you can search for it by pressing '.')

   2. Choose Full reindex. 

This reindexing will take more time to complete, but it doesn’t block your users from using Jira. Once it’s complete, the new index will be automatically distributed to the remaining nodes.