Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,295,926
Community Members
 
Community Events
165
Community Groups

SQL-error during import of data from production

About

During dump of data from prod to test in JIRA failes. JIRA won't start due to errors found in the log such as 

2022-06-14 08:31:51,601+0200 JIRA-Bootstrap ERROR      [c.a.jira.startup.LauncherContextListener] Unable to start JIRA.
com.atlassian.jira.exception.DataAccessException: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key
	at com.atlassian.jira.startup.DatabaseLauncher.cleanupDatabaseTableName(DatabaseLauncher.java:254)
	at com.atlassian.jira.startup.DatabaseLauncher.cleanupDatabaseTableNames(DatabaseLauncher.java:210)
	at com.atlassian.jira.startup.DatabaseLauncher.start(DatabaseLauncher.java:73)
	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:139)
	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:307)
	at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:202)
	at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:135)
	at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:102)
	at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31)
	at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:100)
	at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key

 

Versions

Production is currently hosted on Win 2012, but we are about to migrate to Win 2019. Therefore we need the dump/import of data to be working without issues. 

JIRA

8.20.2#820002-sha1:829506d

Postgres

PS C:\Windows\system32> psql --version
psql (PostgreSQL) 10.19

Export

On the production server the export is done using command

d:\postgresql\bin\pg_dump.exe  -U postgres -d jiradb --format=c --file=e:\backups\dbs\jiradb_20220614_020008.pgdump

Import

Import script has done the following preparations before import

DROP DATABASE jiradb;
CREATE DATABASE jiradb;
grant all privileges on database jiradb to jira_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jira_user;

On the test instance the data is being imported using command 

d:\postgresql\bin\pg_restore.exe -U postgres -d jiradb --no-privileges --no-owner -v -1 d:\temp\latest-backup.pgdump

Errors being logged

JIRA won't start, 5 errors are being logged, in brief

2022-06-14 08:51:18,645+0200 localhost-startStop-2 ERROR [o.a.c.c.C.[Catalina].[localhost].[/]] Exception sending context destroyed event to listener instance of class [com.atlassian.jira.startup.LauncherContextListener]
... 130 lines omitted ...
... 98 more
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation propertyentry
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
Show all 143 lines
2022-06-14 08:53:44,979+0200 JIRA-Bootstrap ERROR [c.a.jira.startup.ComponentContainerLauncher] A fatal error occurred during initialisation. JIRA has been locked.
... 151 lines omitted ...
... 117 more
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation propertyentry
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
Show all 164 lines
2022-06-14 08:53:45,042+0200 JIRA-Bootstrap ERROR [c.a.jira.health.HealthChecks] We couldn't start JIRA
2022-06-14 08:53:45,042+0200 JIRA-Bootstrap ERROR [c.a.jira.health.HealthChecks] An error occurred while trying to start JIRA. We can't give you any more detail right now, we suggest checking the logs for more detail and contacting our support team.
See our documentation for more information on contacting our support team and creating a support zip.
2022-06-14 08:53:45,261+0200 JIRA-Bootstrap ERROR [c.a.jira.startup.LauncherContextListener] Unable to start JIRA.
com.atlassian.jira.exception.DataAccessException: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key
at com.atlassian.jira.startup.DatabaseLauncher.cleanupDatabaseTableName(DatabaseLauncher.java:254)
... 11 lines omitted ...
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation moved_issue_key
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
Show all 27 lines

2 answers

1 accepted

0 votes
Answer accepted

The below command resolved the issue for me, got help from a colleague of mine. 

d:\postgresql\bin\pg_restore.exe --username jirs_user -d jiradb -O -W --format=c d:\temp\latest-backup.pgdump


Beats me, but it works

0 votes

It's pretty clear that this is a permissions issue in the database.

Could you try re-running

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jira_user;


after the import (and also check that Jira is definitely using jira_user for access - look at Jira-home/dbconfig.xml 

It is without doubt my DBA skills that is not up to date

C:\Windows\system32>psql -U postgres -c "grant all privileges on database jiradb to jira_user;"
GRANT

C:\Windows\system32>psql -U postgres -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jira_user;"
GRANT

C:\Windows\system32>psql -U postgres -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO jira_user;"
GRANT

C:\Windows\system32>psql -U postgres -c "GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO jira_user;"
GRANT

C:\Windows\system32>psql -U jira_user -d jiradb -c "select O_S_PROPERTY_ENTRY.property_key, O_S_PROPERTY_ENTRY.propertytype from public.propertyentry O_S_PROPERTY_ENTRY"
ERROR: permission denied for relation propertyentry

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Software

Upcoming changes to epic fields in company-managed projects

👋 Hi there Jira Community! A few months ago we shared with you plans around renaming epics in your company-managed projects. As part of these changes, we highlighted upcoming changes to epics on...

14,492 views 37 44
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you