3 Upgrading ARCO

Note:

The ARCo upgrade is in fact re-installation of the dbwriter and reporting modules, during which you supply the parameters of your existing database and database users.

During the installation of dbwriter, the existing database schema version is checked and updated, if a newer version is available.

During the installation of reporting, the following actions occur:

Predefined queries in the reporting spool directory (default: /var/spool/arco) are overwritten.

Note:
Only the predefined queries will be overwritten, none of your custom queries will be modified.
The reporting module is unregistered from the Sun Java Web Console and deployed again.

If you specify a different spool directory during re-installation of reporting, you will need to move your custom queries and results from the spool directory of your previous installation to the new directory, so that they appear in the Sun Java Web Console. Before proceeding with the upgrade, read all the steps in:

How to Upgrade the ARCo Software
How to Migrate a PostgreSQL Database to a Different Schema

3.1 How to Migrate a PostgreSQL Database to a Different Schema

If you do not plan to perform cross-cluster queries, follow the standard ARCo upgrade procedure. If you have an existing ARCo installation and want to use the multi-cluster features, follow these steps to migrate existing PostgreSQL ARCo databases to the schema configuration.

Prepare a database to which to migrate. Follow How to Configure the ARCo Database with Multiple Schemas on PostgresSQL.

Note:
Throughout this section, in the code snippets and accompanying text, the following values need to be replaced by your appropriate names.
- postgres - the database superuser
- arco - the database you are migrating to and that has schemas configured
- filename - path to a file where all output from the database console will be redirected. The postgres user must have write privileges to the file.
- arco_write_london - the schema name you are migrating to.
- arco_read_london - is the user used by reporting application to access the database; search_path of this user is set to arco_write_london.
- multi_read - is a a user used to perform cross-cluster queries; it must be able to access all schemas and read all object in the schemas.
Restore data from your first database backup file into arco database. See http://www.postgresql.org/docs/8.1/interactive/backup.html.

Note:
After restoring a database backup into a new database with schemas, database object are restored into the default public schema. Hence, you need to restore one backup at a time and only after moving objects to a different schema, you can restore the next backup.
Change to the database superuser.
```
# su - postgres
```
Log in to the arco database.
```
> psql arco
```
Change the output display, so that column name headings and row count footer are not shown.
```
arco=# \t
 Showing only tuples.
```
Redirect the output of a query to a file. The postgres user must have write privileges to the file.
```
arco=# \o filename
```

Execute the following command to generate commands for moving each table to a different schema.

arco=# select 'alter table ' || tablename || ' set schema arco_write_london;' 
       from pg_tables where schemaname='public';

Execute the following command to generate commands for moving each view to a different schema.

arco=# select 'alter table ' || viewname || ' set schema arco_write_london;' 
       from pg_views where schemaname='public';

Execute the following commands to generate commands for granting arco_read_london select privileges on all the database objects in the specified schema.

arco=# select 'grant select on ' ||schemaname||'.'||tablename|| ' to arco_read_london;' 
       from pg_tables where schemaname='arco_write_london';
arco=# select 'grant select on ' ||schemaname||'.'||viewname|| ' to arco_read_london;' 
       from pg_views where schemaname='arco_write_london';

Reset the psql console to the default state.
```
arco=# \o
arco=# \t
```
Run all the commands from the created file.
```
arco=# \i <filename>
```
Restore the next backup, and follow the steps 3 - 9, changing the schema and user names appropriately.

Note:
Remember to use a different output filename or delete the previous file.

Create the multi_read user.

arco=# CREATE USER multi_read WITH PASSWORD 'your_password';

Grant multi_read usage on all schemas.

arco=# GRANT USAGE ON SCHEMA arco_write_london TO multi_read;

Repeat the previous step for each schema, changing the schema name.
Execute steps 5 - 6.

Note:
Remember to use a different output filename or delete the previous file.

Execute the following commands to generate commands granting multi_read select privilege on all database object in all the schemas.

arco=# select 'grant select on ' ||schemaname||'.'||tablename|| ' to multi_read;' 
       from pg_tables where schemaname in ('arco_write_london', 'next_schema_name', ...);
arco=# select 'grant select on ' ||schemaname||'.'||viewname||' to multi_read;' 
       from pg_views where schemaname in ('arco_write_london', 'next_schema_name', ...);

Execute steps 10 - 11.
Reinstall dbwriter.

Note:
If upgrading from version < 6.2, you must run the installations script with option -upd. This will remove existing RC scripts. During the installation, point each dbwriter to the newly created database with multiple schemas and specify appropriate arco_write_cluster user and schema. See How to Install dbwriter.
Reinstall reporting.

Note:
During the re-installation of the reporting module, enter the required information for all the configured database schemas. See How to Install Reporting.

3.2 How to Upgrade the ARCo Software

Ensure that there are no running or pending jobs.
Follow information for shutting down the cluster. See Upgrading From a Previous Release of the Grid Engine Software.
Ensure that the reporting file has been completely processed by dbwriter, so all the job information from the previous Grid Engine installation has been inserted into the database. There should be no reporting or reporting.processing file in the $SGE_ROOT/$SGE_CELL/common directory.
Once the reporting file has been processed, do the following on the dbwriter host:
1. Source the cluster settings.sh (or .csh) file.
2. Stop the dbwriter
```
> $SGE_ROOT/$SGE_CELL/common/sgedbwriter stop
```
Finish upgrading Grid Engine. See Upgrading From a Previous Release of the Grid Engine Software.

Note:
After finishing the Grid Engine upgrade, qmaster can be started and jobs submitted to minimize the downtime, as long as dbwriter is not started.
Back up the existing ARCo databases. Refer to your database manuals on how to backup a database.
(Optional) If you plan to perform cross-cluster queries and have PostgreSQL, go to How to Migrate a PostgreSQL Database to a Different Schema; otherwise, continue with the next step.

Note:
You might want to migrate your existing PostgreSQL databases under a single one with multiple schemas, even if you do not plan to perform cross-cluster queries, but you simply like to consolidate all under one roof.
Reinstall dbwriter.

Note:
If upgrading from version < 6.2, you must run the installations script with option -upd. This will remove existing RC scripts. See How to Install dbwriter.
Reinstall reporting. See How to Install Reporting.