Migrate and Upgrade

Steps to restore the CollectionSpace database files from the old server onto the new server. I am using v4.1 on the old server and v5.2 on the new server.
CollectionSpace should first be installed on the new server and the tenant ‘your_tenant' configured there. For the tenant I use the profile FCART for fine and contemporary art. The installer and tenant creation process and are described here:

CollectionSpace Current Documentation

https://collectionspace.atlassian.net/wiki/spaces/DOC/pages/2593661043

The instructions for creating the new tenant specify that the file /home/cspace/collectionspace-source/application/tenant.properties needs to be edited for these values:

template.tenant.shortname
tenant.shortname
tenant.id
tenant.internet.domain

For the tenant use:

template.tenant.shortname=fcart
tenant.shortname=your_tenant
tenant.id=110
tenant.internet.domain=your_domain

After installation the new tenant should be available here: http://ip_address:8180/cspace/your_tenant/

It’s important to log on to your new tenant and create at least one media record. This will establish the media directories on the new server.

Overall the steps to migrate and upgrade are:

Backup and restore roles
Backup and restore the cspace database
Backup and restore the nuxeo database and data files
Backup and restore your tenant database
Fix-up permissions and ownership in the three databases
Run 4.1 upgrade sql script in cspace database
Ant undeploy deploy
Ant import

This task describes how to connect to the database servers and to backup databases from the source and to restore databases at the target. The term source server is defined as the existing cspace server you are moving from, and the term target server is defined as the new cspace server you are moving into. The database technology is postgresql and referred to as postgres. The command psql connects to the database server and allows postgres specific work to be done with more commands. The linux bash environment is used in ssh sessions connected to the source and target servers from the operator workstation (that's you and your computer) and is where you run commands on the source and target. User accounts with sudo privilege include root plus any accounts you added to the group sudo.

The following steps are for the target server. (This is after you have installed your new tenant there and created at least one media record to validate the installation worked.)

Start a session as user with sudo privilege to the target server using ssh.
Run the command sudo su postgres to change to the user postgres which is allowed to connect to the postgres environment.
Run the command cd ~ to change to the home directory.
Run the psql command to connect to postgres.
Inside the psql prompt run the command \l to list the databases so you are familiar with them.
Inside the psql prompt run the \du to list the user roles so you are familiar with them.

The following steps are done on the source server

Create backup files.
1. Create the database backup from the source server which will be used to restore the database at the target. At the source server in the ssh session change to user postgres with the command sudo su postgres then command cd ~ to change to the home directory. Run the command:
  pg_dump -Ft -f./yourtenant_domain.tar yourtenant_domain
  This created the backup file yourtenant_domain.tar in the current directory in a compressed format for the database yourtenant_domain. The current directory for user postgres is /var/lib/postgresql/
2. Repeat the pg_dump for databases nuxeo_default and cspace_default. They may be named differently on your source server.
3. Create the backup of the roles needed from the source environment that will be needed to restore the database at the target. Still at the source server run the command:
  Exit the user session postgres. This created the backup file dump_roles.bak in the current directory.
4. Create a new folder in your home directory to receive the nuxeo media files. Copy the files at $CATALINA_HOME/nuxeo-server/data/yourtenant_domain/data/ into this new folder.This backed up the media files.

The following steps are to copy the files to the target server.

The exact steps to accomplish the copy are not described here. It may be easiest to copy files from the source server to the your workstation and then from that workstation to the target server.
1. Copy the yourtenant_domain.tar and cspace and nuxeo database backup files to the target server directory /var/lib/postgresql/
2. Copy the media backup folder to /usr/local/share/apache-tomcat-8.5.40/nuxeo-server/data/yourtenant/data/
  Here is an example copy command from the root account at the following directory: root@yourserver:~/20191124backupCatalina/nuxeo-server/data/yourtenant_domain/data#
  cp ./* /usr/local/share/apache-tomcat-8.5.40/nuxeo-server/data/yourtenant/data/ -r
  Follow this with the command to fix the ownership of the data files on the target server:
  chown cspace:cspace /usr/local/share/apache-tomcat-8.5.40/nuxeo-server/data/yourtenant/data/* --recursive
3. Restore the postgres roles at the target server. Use command sudo su postgres and then cd ~ to go to the home directory of the postgres user. Start a psql session. Run the command \i DUMPROLES.BAK which will process the file previously copied to that directory. Exist psql with command \q
4. Drop the databases on the target server and in prepararation to create blank databases to accept the source databases that you are restoring. The commands to do this are: sudo su postgres to change to the database user, and psql to enter the postgresql environment. Then issue three psql commands drop database cspace_default; drop database nuxeo_default; drop database yourtenant_default; The semicolon is part of the command.
5. Create the databases in psql: Three commands again: create database cspace_default; create database nuxeo_default; create database yourtenant_default;
6. Fix-up the permissions with the psql command GRANT. Notice we omit the cspace_default as it appears to use different default permissions and not need any fix-up. The command reference is here: https://www.postgresql.org/docs/current/sql-grant.html
  1. ALTER DATABASE nuxeo_default OWNER TO nuxeo_default;
  2. ALTER DATABASE yourtenant_default OWNER TO nuxeo_default;
  3. GRANT TEMP ON DATABASE nuxeo_default TO nuxeo_default;
  4. GRANT TEMP ON DATABASE yourtenant_default TO nuxeo_default;
  5. GRANT CONNECT ON DATABASE nuxeo_default TO reader_default;
  6. GRANT CONNECT ON DATABASE yourtenant_default TO reader_default;
Restore the databases nuxeo_default, yourtenant_default, cspace_default at the target with bash and user postgres. These are bash and not psql commands. Your will need to refine the data filenames for your case. (You created these data files at the source server and copied them to the target.)
1. pg_restore --clean --dbname=nuxeo_default nuxeo.tar
2. pg_restore --clean --dbname=yourtenant_default yourtenant.tar
3. pg_restore --clean --dbname=cspace_default cspace.tar
Upgrade the new cspace_default database from v4.1, so as user postgres in bash download the upgrade script and in psql run the upgrade script as follows.
1. In bash in the home directory for postgres run the following command (note: if you receive “No such file or directory” you will need to carefully type it into bash because it doesn’t seem to copy and paste correctly).
  wget https://raw.githubusercontent.com/cspace-deployment/Tools/master/scripts/db/upgrade_4.1_to_5.0.sql
2. In psql connect to database cspace_default with command \c cspace_default and run the upgrade script with command: \i upgrade_4.1_to_5.0.sql
Fix up the schema permissions.The nuxeo_default db restore leaves many items owned by the old user 'nuxeo' and not the current 'nuxeo_default'. Here are bash (not psql) scripts to fix the ownership.
In bash exit postgres and change users sudo su cspace. Go to the directory ~/collectionspace-source/services and run these commands (you ran them previously to deploy your new tenant). Both commands should complete with the message BUILD SUCCESSFUL.
- ant undeploy deploy
- ant import
Start up CollectionSpace (done as user cspace with command $CSPACE_JEESERVER_HOME/bin/startup.sh) Validate your tenant. The records should all be migrated and upgraded.

Useful commands in psql include:

\l to list the databases
\q to exit psql
\c to connect to a database
\dt to display tables in the connected database
\du to display users in the connected database
\ds to display sequences in the connected database
\? to show a list of commands (it’s a long list and you will see the : so use the up or down arrow to scroll and type q to exit the list)

Validation of the top five tables by size can be done with this code in psql first connecting to a database with \c yourtenant_default or \c nuxeo_default or \c cspace_default

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 5;

Basic troubleshooting: Check the logs. Slow down, do one operation at a time and check the logs at /usr/local/share/apache-tomcat-8.5.40/logs. Focus there on cspace-services.log and catalina.out.

Is the application startup healthy? Compare it to a pristine startup log.
Can the services be called ? Compare a successful call to an unsuccessful call.
Try probing this logon service:
curl -i http://localhost:8180/cspace-services/collectionobjects -u admin@core.collectionspace.org:Administrator
curl -i http://localhost:8180/cspace-services/collectionobjects -u admin@core.collectionspace.org:Administrator