JIRA 4.0 : Making databases read-only for upgrade backups
This page last changed on Jun 18, 2009 by rosie@atlassian.com.
For medium to large databases, backing up JIRA data can take a long time, during which it's possible for users and JIRA services to alter the database, possibly resulting in inconsistent data in your backup. For automated daily backups, there is no solution to this, and it's a good reason to prefer native database backup utilities, as the documentation suggests. For upgrading, though, you need to generate an XML backup, and this problem needs to be solved to ensure a consistent backup. One way to ensure a consistent backup is to shut down JIRA, start it on a private port and do the backup there, knowing that there are no existing or new users doing things. This causes downtime though. A simpler approach is to make JIRA read-only for the duration of the backup, just before you take it down for the upgrade. The process would be:
Users are still able to search and view issues, but creating, editing or commenting on an issue will fail with an error. This is admittedly a bit ugly, but setting a banner explaining the situation ameliorates this. The process for making a database read-only for a particular user is very database-specific. PostgreSQLLog in to the JIRA database with psql, and run: REVOKE INSERT, DELETE, UPDATE on project,jiraaction,jiraissue from jira; where jira is the Postgres user JIRA uses. For instance, on a linux system this might look as follows: jturner@psyche:~$ sudo su - postgres Password: postgres@psyche:~$ psql jiradb_391 Welcome to psql 8.2.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit jiradb_391=# \du List of roles Role name | Superuser | Create role | Create DB | Connections | Member of -----------+-----------+-------------+-----------+-------------+----------- jira | no | no | no | no limit | jturner | yes | yes | yes | no limit | postgres | yes | yes | yes | no limit | (3 rows) jiradb_391=# REVOKE INSERT, DELETE, UPDATE on project,jiraaction,jiraissue from jira; REVOKE jiradb_391=# MySQLThis is very similar to PostgreSQL, except that specifying the user has to include a hostname component. First find the relevant username and host: jturner@psyche:~$ mysql -u root -p mysql Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 303 Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select user,host from user; +------------------+-----------+ | user | host | +------------------+-----------+ | bugzilla | % | | debian-sys-maint | % | | jirauser | % | | root | % | | bugzilla | localhost | | debian-sys-maint | localhost | | jira | localhost | | root | teacup | +------------------+-----------+ 8 rows in set (0.00 sec) The relevant user is 'jira', valid from host 'localhost'. Now run: mysql> REVOKE INSERT,DELETE,UPDATE on jiradb_391.* from 'jira'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) Unlike PostgreSQL, you will need to restart JIRA for this change to take effect. Other databases.To help grow this page, please add a description in a comment if you have successfully done this on another database. |
![]() |
Document generated by Confluence on Oct 06, 2009 00:26 |