Making databases read-only for upgrade backups

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:

Set a banner warning users that JIRA is going read-only before an upgrade.
In the database, remove write permissions from the database user JIRA uses.
Generate an XML backup.
Shut down JIRA.
Proceed with the upgrade (start the new JIRA pointing at a new empty database, and import the XML backup).

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.

PostgreSQL

Log 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=#

MySQL

This 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.

Making databases read-only for upgrade backups

PostgreSQL

MySQL

Other databases.

Labels

Comments (3)

Matt Doar says:

huasoon says:

Grégory Joseph says: