xiven.com stating the blatantly obvious since 2002

Step-by-step guide

This guide is written for JIRA admins who are using a PostgreSQL database as the backend of their JIRA instance. If you're using a different database engine you will need to make various adjustments.

1. First things first, get that database backup file and copy it to some server or workstation where you can safely mess around with a database without consequence.
2. On this safe place, import the file into your database: psql jira < 2017-08-31.sql
3. Connect to postgres: psql jira
4. Find the ID of your now-deleted field: select id from customfield where cfname = 'My custom field'; (for this hypothetical scenario, let's assume the ID was 10141).
5. You'll also need to find the ID of any entries in the fieldconfiguration table that reference your field: select * from fieldconfiguration where fieldid = 'customfield_10141'; (we'll assume we got an ID from this query of 10131)
6. Copy out the data from the relevant tables, filtered specifically to your custom field:

   
   create table cf_temp as select * from customfield where id = 10141;
   create table cfo_temp as select * from customfieldoption where customfield = 10141;
   create table cfv_temp as select * from customfieldvalue where customfield = 10141;
   create table fsli_temp as select * from fieldscreenlayoutitem where fieldidentifier = 'customfield_10141';
   create table cli_temp as select * from columnlayoutitem where fieldidentifier = 'customfield_10141';
   create table fli_temp as select * from fieldlayoutitem where fieldidentifier = 'customfield_10141';
   create table cc_temp as select * from configurationcontext where customfield = 'customfield_10141';
   create table fc_temp as select * from fieldconfiguration where fieldid = 'customfield_10141';
   create table gc_temp as select * from genericconfiguration where datatype = 'DefaultValue' and datakey = '10131';
   create table fcsit as select * from fieldconfigschemeissuetype where fieldconfigscheme = 10131;
   create table fcsit_temp as select * from fieldconfigschemeissuetype where fieldconfigscheme = 10131;
   

7. Now you'll want to extract this data from postgres in the form of SQL INSERT statements. From your shell command-line:

   
   pg_dump --data-only --inserts -t cf_temp jira > cf.sql
   pg_dump --data-only --inserts -t cfo_temp jira > cfo.sql
   pg_dump --data-only --inserts -t cfv_temp jira > cfv.sql
   pg_dump --data-only --inserts -t fsli_temp jira > fsli.sql
   pg_dump --data-only --inserts -t cli_temp jira > cli.sql
   pg_dump --data-only --inserts -t fli_temp jira > fli.sql
   pg_dump --data-only --inserts -t cc_temp jira > cc.sql
   pg_dump --data-only --inserts -t gc_temp jira > gc.sql
   pg_dump --data-only --inserts -t fc_temp jira > fc.sql
   pg_dump --data-only --inserts -t fcs_temp jira > fcs.sql
   pg_dump --data-only --inserts -t fcsit_temp jira > fcsit.sql
   

8. These SQL files are almost what you need, but they'll be using the wrong table names. Use some simple sed magic to clear those right up:

   
   sed s/cf_temp/customfield/ < cf.sql > cf-out.sql
   sed s/cfo_temp/customfieldoption/ < cfo.sql > cfo-out.sql
   sed s/cfv_temp/customfieldvalue/ < cfv.sql > cfv-out.sql
   sed s/fsli_temp/fieldscreenlayoutitem/ < fsli.sql > fsli-out.sql
   sed s/cli_temp/columnlayoutitem/ < cli.sql > cli-out.sql
   sed s/fli_temp/fieldlayoutitem/ < fli.sql > fli-out.sql
   sed s/cc_temp/configurationcontext/ < cc.sql > cc-out.sql
   sed s/gc_temp/genericconfiguration/ < gc.sql > gc-out.sql
   sed s/fc_temp/fieldconfiguration/ < fc.sql > fc-out.sql
   sed s/fcs_temp/fieldconfigscheme/ < fcs.sql > fcs-out.sql
   sed s/fcsit_temp/fieldconfigschemeissuetype/ < fcsit.sql > fcsit-out.sql
   

9. Copy these SQL files over to your JIRA server.
10. Back up your JIRA database! Trigger an extra special run of your trusty database backup script.
11. Stop your JIRA instance.
12. Run all of these shiny SQL files:

    
    psql jira < cf-out.sql
    psql jira < cfo-out.sql
    psql jira < cfv-out.sql
    psql jira < fsli-out.sql
    psql jira < cli-out.sql
    psql jira < fli-out.sql
    psql jira < cc-out.sql
    psql jira < gc-out.sql
    psql jira < fc-out.sql
    psql jira < fcs-out.sql
    psql jira < fcsit-out.sql
    

13. Start your JIRA instance up again.
14. Check to see if everything looks okay.
15. Hopefully breathe a huge sigh of relief.
16. Set a re-index going, just for good measure. This is JIRA we're talking about after all.