Error while restoring backup from cloud to server.

Question

Our goal is to create a backup of our cloud instance (backup for server), and restore the same on a datacenter instance hosted on AWS. The projects in the cloud instance contain barely any data at all(only a few issues, attachments and some blank projects) since we're trying to test out the backup & restore process from cloud to datacenter. Here's what we've done so far : * Create a backup for server from the cloud instance. * Move the backup to the correct directory in the datacenter for restoration (i.e. /media/atl/jira/shared/import). * Attempt to restore the backup. At the last step, we're getting the following error : There was an error during import/export with : ERROR: relation "public.PROJECT" does not exist. Here's a snippet from the generated log : 2021-06-15 14:07:58,027+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 68% complete... 2021-06-15 14:07:58,028+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 69% complete... 2021-06-15 14:07:58,029+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 70% complete... 2021-06-15 14:07:58,030+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 71% complete... 2021-06-15 14:07:58,030+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 72% complete... 2021-06-15 14:07:58,031+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 73% complete... 2021-06-15 14:07:58,033+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 74% complete... 2021-06-15 14:07:58,035+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 75% complete... 2021-06-15 14:07:58,036+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 76% complete... 2021-06-15 14:07:58,038+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 77% complete... 2021-06-15 14:07:58,039+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 78% complete... 2021-06-15 14:07:58,041+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 79% complete... 2021-06-15 14:07:58,043+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 80% complete... 2021-06-15 14:07:58,044+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 81% complete... 2021-06-15 14:07:58,049+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 82% complete... 2021-06-15 14:07:58,050+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 83% complete... 2021-06-15 14:07:58,051+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 84% complete... 2021-06-15 14:07:58,052+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 85% complete... 2021-06-15 14:07:58,053+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 86% complete... 2021-06-15 14:07:58,054+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 87% complete... 2021-06-15 14:07:58,055+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 88% complete... 2021-06-15 14:07:58,058+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 89% complete... 2021-06-15 14:07:58,060+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 90% complete... 2021-06-15 14:07:58,497+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Finished storing Generic Values. 2021-06-15 14:07:58,575+0000 JiraImportTaskExecutionThread-1 DEBUG utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.activeobjects.osgi.ActiveObjectsServiceFactory] startCleaning 2021-06-15 14:07:58,578+0000 JiraImportTaskExecutionThread-1 DEBUG utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.activeobjects.osgi.ActiveObjectsServiceFactory] stopCleaning 2021-06-15 14:08:04,483+0000 JiraImportTaskExecutionThread-1 ERROR utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Error during ActiveObjects restore com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with :Error executing update for SQL statement 'ALTER TABLE public."AO_2C4E5C_MAILBLOCKLISTS" ADD CONSTRAINT "fk_ao_2c4e5c_mailblocklists_project_id" FOREIGN KEY ("PROJECT_ID") REFERENCES public."PROJECT"("ID")' at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportSqlException(ImportExportErrorServiceImpl.java:26) at com.atlassian.activeobjects.backup.SqlUtils.onSqlException(SqlUtils.java:46) at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:37) at com.atlassian.activeobjects.backup.ActiveObjectsForeignKeyCreator.create(ActiveObjectsForeignKeyCreator.java:43) at com.atlassian.activeobjects.backup.ForeignKeyAroundImporter.after(ForeignKeyAroundImporter.java:26) at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:47) at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:70) at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:151) at com.atlassian.jira.bc.dataimport.DefaultDataImportService.restoreActiveObjects(DefaultDataImportService.java:574) at com.atlassian.jira.bc.dataimport.DefaultDataImportService.performImport(DefaultDataImportService.java:749) at com.atlassian.jira.bc.dataimport.DefaultDataImportService.doImport(DefaultDataImportService.java:323) at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.unsafeCall(DataImportAsyncCommand.java:82) at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:64) at com.atlassian.jira.web.action.setup.DataImportAsyncCommand.call(DataImportAsyncCommand.java:30) at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:177) at com.atlassian.jira.task.ImportTaskManagerImpl$TaskCallableDecorator.call(ImportTaskManagerImpl.java:149) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.PROJECT" does not exist at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:297) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274) at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:246) at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:234) at com.atlassian.jira.ofbiz.sql.StatementWrapper.executeUpdate(StatementWrapper.java:29) at com.atlassian.jira.diagnostic.connection.DiagnosticStatement.lambda$executeUpdate$8(DiagnosticStatement.java:70) at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:69) at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55) at com.atlassian.jira.diagnostic.connection.DiagnosticStatement.executeUpdate(DiagnosticStatement.java:70) at com.atlassian.activeobjects.backup.SqlUtils.executeUpdate(SqlUtils.java:34) ... 19 more 2021-06-15 14:08:04,485+0000 JiraImportTaskExecutionThread-1 WARN utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.p.d.dao.impl.CachingOrganizationDaoImpl] onImportCompleted - clearing cache !! 2021-06-15 14:08:04,494+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.index.ha.DefaultIndexCopyService] Index backup started. Requesting node: ANY, currentNode: i-0d561038a0848d396 2021-06-15 14:08:04,495+0000 JiraImportTaskExecutionThread-1 WARN utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.index.ha.DefaultIndexCopyService] Index backup failed - latest index operation not found. Requesting node: ANY, currentNode: i-0d561038a0848d396 2021-06-15 14:08:04,560+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] Importing data is 100% complete... 2021-06-15 14:08:04,560+0000 JiraImportTaskExecutionThread-1 INFO utkarshkosta 847x2327x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore.jspa [c.a.j.bc.dataimport.DefaultDataImportService] JIRA Data Import has finished. 2021-06-15 14:08:19,116+0000 heartbeat-scheduler-0 ERROR anonymous 590x117x1 1r0yitl 182.237.135.181,10.0.143.215 /secure/SetupLicense.jspa [c.a.j.cluster.lock.StartableClusterNodeHeartbeatService] Unhandled exception occurred in heartbeat service java.lang.NullPointerException at com.atlassian.jira.cluster.service.NodeAutoShutdownIfOfflineService.checkCurrentNodeState(NodeAutoShutdownIfOfflineService.java:61) at com.atlassian.jira.cluster.lock.ClusterHeartbeatJob.run(ClusterHeartbeatJob.java:36) at com.atlassian.jira.cluster.lock.StartableClusterNodeHeartbeatService.lambda$createHeartbeatJobWrapper$0(StartableClusterNodeHeartbeatService.java:113) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) 2021-06-15 14:08:19,288+0000 http-nio-8080-exec-10 WARN utkarshkosta 848x2335x1 11g4fbk 182.237.135.181,10.0.157.98 /secure/admin/XmlRestore!finish.jspa [webresource] exception thrown in `addToUrl` during condition evaluation com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: Database: - name:PostgreSQL - version:11.10 - minor version:10 - major version:11 Driver: - name:PostgreSQL JDBC Driver - version:42.2.6 org.postgresql.util.PSQLException: ERROR: relation "public.AO_21F425_MESSAGE_AO" does not exist Position: 28 at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:152) at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:302) ... 2 filtered at java.lang.reflect.Method.invoke(Method.java:498) After this error, we can no longer login to the datacenter instance. Attempting to access the service URL gives an error saying "The license is invalid." Any ideas to resolve this issue? Thank you for any potential solutions.

Philip Armour · Answer

Please also note that when I raised this problem with Atlassian support, they recommended the steps mentioned here:

https://jira.atlassian.com/browse/JRASERVER-72543

Sazz_0w21tf · Answer

@Utkarsh Kosta  your issue can be resolved by installing DB and APP on the same server and increasing the import threads and some postgres and dbconfig.xml tuning. But note, DB and App has to be on the same server for that 90% error not to show up.

My error message, although related to yours was because of a Cloud Export Additional table that got introduced very recently. That table should not be included as part of the export. The fix was to remove a rogue line in the activeobjects,xml file and then re-zipping it.

Please note, we have the same issue with Confluence as well of 90% stoppage. and we bypassed it via app and db on the same server.

Hope the above helps.

Philip Armour · Answer

Hi,

We have recently encountered the same problem with exactly the same error message which sazz reports.

We tried using the internal H2 database instead of Postgres as a workaround and the import was successful.

So although not a proper solution - this may provide a way forward in some cases