Archiving issues for the upgrade to 8.13

Still need help?

The Atlassian Community is here for you.

Ask the community

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

Read more about this approach...

Since Issue archiving is not available in Jira 7.x, you will archive issues by modifying the database. In Jira, your issues won’t change in any way until you upgrade to the new version. After upgrading, you will perform the initial reindexing, which will be much shorter without the archived issues. Once you have a working index and open Jira to your users, you can restore as many of the archived issues as you need, and reindex again. The second reindexing will take longer to complete, but you will perform it on a single node, without shutting down Jira to your users.

After the upgrade, archives issues will be read-only and accessible via a direct link. Your users won’t be able to easily browse or search them (until the second reindexing), that’s why you’ll need to identify issues that can be archived and aren’t critical. If you’d like to learn more about how archived issues will behave after the upgrade, see Archiving an issue.

Before you begin

You can complete these steps without shutting down your Jira Data Center.

Identifying issues to be archived

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

What issues should I archive?

It’s not only about the number of issues, but mostly about their content — attachments, work logs, custom fields, history, comments. The “heavier” the issue is, the more it affects reindexing. What issues you choose is really up to you, but the easiest way would be to identify issues that your users absolutely need right after the upgrade, and archive all the rest.

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

Example 1: All issues created before...

This query will archive all issues created before :

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'	
);
Example 2: All issues with no recent updates...

This query will archive all issues that haven’t been updated for 2 years:

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')	
);
Example 3: Issues from a project, with label, created between…

This query will archive issues from project Jira, with label archive, created between  and :

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

This is where you’ll need to shut down your Jira Data Center and start the upgrade.

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.

Last modified on Oct 20, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.