Introduction
How do you migrate table grid data to a new JIRA instance (or from production to test, for example) ?From a customer case
Hi, I am refreshing our test environment with data from the production environment. I synchronised the plugin versions, did an xml export in production and imported the xml in test.
I see not all data of the Table Grid plugin is copied. What is best practice to have a complete test environment ? Is it enough to copy some (which) database tables ?
Solution
The grid tables are not part of the JIRA database, and taking an xml backup is not going to include the data. There are a couple of reasons of this behaviour
a) Grid tables can be stored on external databases. In that case an XML backup would not incorporate the data anyway
b) Atlassian is advising to use native database tools to backup JIRA data, as the XML backup might be inconsistent (and in some cases completely wrong) as some of the data might be in transition while the xml backup is created.
A good approach to reset your test database to the latest version is to do a database copy. This should also include the grid tables.
If you are restoring your JIRA xml backup on the new JIRA instance you also have to take care of tables used in your Table Grid Editor configurations. Follow these steps:
- check all the custom fields of type "Table Grid Editor" and locate all the database tables which are used to store grid data. Check article Where does Table Grid Editor store the data? for details.
- migrate the data to the new database. The simplest thing to do is to do that via csv files, i.e. first load database data into csv file from the original database. Then load the data to the new database from the csv file. The syntax of loading the csv files data into the databse table can differ from one database to another, but most database vendors allow that
If you store the TGE data in the external database (not JIRA database) you don't need to migrate the data even if you move to another JIRA instance. Just make sure that the database connection to the external database is still possible.
PostgreSQL hint
If you are using PostgreSQL and your grids use 'textarea' column type - the data from 'pg_largeobject' table has to be restored as well as the grid tables. This is due to the fact that PostgreSQL stores the CLOB column data in pg_largeobject table.