Archiving issues for the upgrade to 8.13

To minimize downtime for your users, you’ll reduce the size of the index by archiving issues, which essentially excludes them from indexing. Archived issues won’t be available to users right after the upgrade, but you will restore them later, on a working Jira.

Overview

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.

Upgrading Jira Data Center

Perform a regular upgrade of your whole Jira Data Center cluster by following the steps from Upgrading Jira Data Center. 

What about reindexing?

During the upgrade procedure described above, you will perform a reindex on the first node that you’ve upgraded and then copy the index (along with the installation directory) to the remaining nodes. This initial reindexing will be much faster, because it doesn’t include issues that were marked as archived. After the upgrade, you can already open Jira to your users — they will have access to issues that haven’t been archived, and will be able to create new issues.

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. To  perform a reindex, go to  > System > Indexing (you can search for it by pressing '.')

3. 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.