Migration Help from 6.3 to 7.5

[Michel Montenegro]

Where can I find documentation that shows how to migrate from 6.3 to 7.5 or how we can migrate?

All documentation I found was out of date or apparently incomplete.

[Mark H. Wood]

The official upgrade documentation is at:

https://wiki.lyrasis.org/display/DSDOC7x/Upgrading+DSpace

If that is incorrect or incomplete, please tell us about the specific
problem(s) you found.

--
Mark H. Wood
Lead Technology Analyst

University Library
Indiana University - Purdue University Indianapolis
755 W. Michigan Street
Indianapolis, IN 46202
317-274-0749
www.ulib.iupui.edu

[Michel Montenegro]

I will detail our case:
we have version 6.3 (All data) and 7.5 (Clean) running on different servers, we simply want to transfer only the data from version 6.3 to 7.5.

We don't understand how to do that even from the documentation you indicated (We had already seen it). What exactly do we need to do to export the data from 6.3 to 7.5? (Assuming 7.5 is running, but it's a clean install and all the data we want is in 6.3)

--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/ZHn7eYw0me8tLAx1%40IUPUI.Edu.

--

Atenciosamente,

Michel Pinheiro Montenegro

- Bacharel em Sistema de Informação
- [Lato Sensu] Especialista em Engenharia de Sistemas

- [Stricto Sensu] Mestre em Ciência da Computação

- [Stricto Sensu] Doutorando em Ciência da Computação

E-mail/Gtalk: michel.m...@gmail.com

[Tim Donohue]

Hi Michel,

We have a guide for migrating data at https://wiki.lyrasis.org/display/DSDOC7x/Migrating+DSpace+to+a+new+server

This should work even migrating data from an old 6.x instance to a new 7.x one.

Tim

---

From: dspac...@googlegroups.com <dspac...@googlegroups.com> on behalf of Michel Montenegro <michel.m...@gmail.com>
Sent: Friday, June 2, 2023 10:17 AM
To: dspac...@googlegroups.com <dspac...@googlegroups.com>
Subject: Re: [dspace-tech] Migration Help from 6.3 to 7.5

 

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/CADOAx_jWiA6dkU7O0BSU7BNe0RBek33uCwf3znNXidcBiCZ_og%40mail.gmail.com.

[Michel Montenegro (Professor)]

The command "./dspace database migrate ignored" does not work, it gives an error. What do I need to do to migrate ONLY the Data (Database) from 6.3 to 7.5?

We follow these steps (To migrate ONLY the old database to the new one):
* We did the PG_DUMP on the "dspace" ( Server   6.3) that has data and applied the PG_RESTORE from this base on the  Server  7.5 dspace (The 7.5 Dspace was clean). Everything ran without error!
* When applying the command [Server 7.5] "./dspace database migrate ignored", it gives an error.

1º) Error:
SQL State  : 42P07
Error Code : 0
Message    : ERROR: relation "bitstreamformatregistry" already exists
Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V1.1_Initial_DSpace_1.1_database_schema.sql (C:\dspace\file:\C:\dspace\lib\dspace-api-7.5.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V1.1_Initial_DSpace_1.1_database_schema.sql)
Line       : 90

2º) Error:
SQL State  : 42723
Error Code : 0
Message    : ERROR: function "getnextid" already exists with same argument types
Location   : org/dspace/storage/rdbms/sqlmigration/postgres/V1.1_Initial_DSpace_1.1_database_schema.sql (C:\dspace\file:\C:\dspace\lib\dspace-api-7.5.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V1.1_Initial_DSpace_1.1_database_schema.sql)
Line       : 46

[DSpace Technical Support]

Unfortunately, we'd need more information to help out.  It sounds like the "./dspace database migrate ignored" script is attempting to run migrations from DSpace version 1.1 on your database.  That means it somehow believes that you don't have a valid DSpace database at all? Or it somehow thinks you are running DSpace 1.0 (which is really really odd, if you are running 6.3).   Maybe your pg_dump or pg_restore was missing some tables? 

You can try running "./dspace database info" to see what version of DSpace the script believes that your database supports.  It should say your database is running 6.3... or that scripts through version 6.3 have already been run.  But, based on the error you shared, it sounds like it may say something else entirely.

Tim

[Michel Montenegro]

DSPace 7.5 runs without error, if you do the pure installation, letting it create its base, without any previous base.
See the command "dspace database info", everything succeeds and runs normally(Imagem 1 and 2)

Image 1

Image 2

7.5 worked (Empty)!
I want to get the data (Dump) from Dspace 6.3 and add it here so as not to lose this information, how should I proceed?

1. Server 7.5 is up and running (No 6.3 data).
2. I need to take data from 6.3 and bring it to 7.5, how should I proceed in detail based on what was said above?

Test:

I changed the database to 6.3, then sent the command "dspace database info", and it said that ALL versions are pending.(Image 3 and 4).

Image 3

Image 4

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/d10366d0-48db-47e7-96fd-b91e014be905n%40googlegroups.com.

[DSpace Technical Support]

Hi,

Those screenshots from the 6.3 database are definitely wrong.  If all the migrations are "Pending" that essentially means that either DSpace or Flyway (the tool DSpace uses to auto-update your database) cannot tell that your existing database has *any* tables.  It seems to think you are starting from an empty database.

Are you sure you were able to successfully restore all the 6.3 database content into your new database?  
Or, maybe somehow your "schema_version" table in your 6.3 database is *empty*? (That's the table which Flyway uses to manage all the migrations that it knows it's already installed for you)

It sounds to me like your data hasn't moved properly.  DSpace is trying to perform a "fresh install" on your 6.3 database, and that's obviously not correct.  So, something is wrong in the database's tables which is making DSpace think that it needs to do a "fresh install" of all tables.

Tim

[Michel Montenegro]

The problem seems to be at the base of 6.3 (At this moment I don't know how the team's DBAs solved it when generating the dump), but we managed to start the migration, however during the migration it stopped and generated the following error (The 6.3 database this one being migrated had nothing added, it's the 6.3 core ):

-- Update the sequences
SELECT setval('cwf_workflowitem_seq', max(workflowitem_id)) FROM cwf_workflowitem;
SELECT setval('cwf_collectionrole_seq', max(collectionrole_id)) FROM cwf_collectionrole;
SELECT setval('cwf_workflowitemrole_seq', max(workflowitemrole_id)) FROM cwf_workflowitemrole;
SELECT setval('cwf_pooltask_seq', max(pooltask_id)) FROM cwf_pooltask;
SELECT setval('cwf_claimtask_seq', max(claimtask_id)) FROM cwf_claimtask;
SELECT setval('cwf_in_progress_user_seq', max(in_progress_user_id)) FROM cwf_in_progress_user;
]; ERROR: duplicate key value violates unique constraint "resourcepolicy_pkey"
  Detalhe: Key (policy_id)=(1) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "resourcepolicy_pkey"
  Detalhe: Key (policy_id)=(1) already exists.
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:247)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
        at org.dspace.storage.rdbms.DatabaseUtils.executeSql(DatabaseUtils.java:1283)
        ... 30 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "resourcepolicy_pkey"
  Detalhe: Key (policy_id)=(1) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
        at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
        ... 32 more

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/687b0fd8-b1cd-44fc-a8ce-5d65301e544en%40googlegroups.com.

[DSpace Technical Support]

Hi,

In order to provide advice/help, we'd need to understand when that error occurs.  What command were you running when this occurred?

If this was during the "./dspace database migrate ignored", then it may be possible that running "./dspace database update-sequences" will solve the issue.  Sometimes, after moving a database from one place to another, the database sequences get messed up or reset.  This "update-sequences" command will usually fix the problem and then let the "migrate" command succeed.

If that's not the step that threw the error, then we'd need more information on what command you ran that generated this error. 

Tim

[Michel Montenegro]

I accessed the DSpace 7.5 bin directory and ran the following command "C:\dspace\bin>dspace database update-sequences" on the 6.3 database, it returned the following error:

C:\dspace\bin>dspace database update-sequences

 

Using DSpace installation in: C:\dspace
Running org/dspace/storage/rdbms/sqlmigration/postgres/update-sequences.sql
Caught exception:
org.postgresql.util.PSQLException: ERROR: relation "systemwidealert" does not exist
  Posição: 923
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)

        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)

        at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:405)
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
        at java.base/java.lang.reflect.Method.invoke(Method.java:577)
        at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:277)
        at org.dspace.app.launcher.ScriptLauncher.handleScript(ScriptLauncher.java:133)
        at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:98)

You received this message because you are subscribed to a topic in the Google Groups "DSpace Technical Support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dspace-tech/wcWjzQp5xh4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/617cdaee-ccbf-4b29-a6da-e3de34f3fd64n%40googlegroups.com.

[Mohammad S. AlMutairi]

On Friday, June 16, 2023 at 11:44:22 AM UTC+3 Michel Montenegro wrote:

The problem seems to be at the base of 6.3 (At this moment I don't know how the team's DBAs solved it when generating the dump), but we managed to start the migration, however during the migration it stopped and generated the following error (The 6.3 database this one being migrated had nothing added, it's the 6.3 core ):

 Hi All,

It seems all the issues you are facing in your migration is caused of how the the old database is exported and imported in the new server. Just make sure before following the steps you see below the pgcrypto extension was created on the database in the old server and not add into the extensions schema. Once you sucessed on importing the database follow the other steps in the link Tim provided https://wiki.lyrasis.org/display/DSDOC7x/Migrating+DSpace+to+a+new+server

# You need to change the bin folder path in step 2 & 6 to match yours.

1) cd "C:\Program Files\PostgreSQL\9.13\bin"
2) .\pg_dump -U postgres -W -h localhost -Fc -v -C -E UTF8 -d dspace -f C:\dspace-63.sql
3) Move dspace-63.sql file to the new dspace server

On the new dspace server execute:
1) psql --username=postgres dspace
2) DROP EXTENSION pgcrypto;
3) DROP SCHEMA extensions;
4) \q
5) cd "C:\Program Files\PostgreSQL\13\bin"
6) .\pg_restore -U postgres -W -h localhost -v -e -c --if-exists -d dspace C:\dspace-63.sql
7) psql --username=postgres dspace
8) \dn
9) \dx
10) \q

 

Hope it helps.

Mo.

[Mohammad S. AlMutairi]

Correction:  # You need to change the bin folder path in step 1 & 5 to match yours.

[Michel Montenegro]

Mohammad S. AlMutairi I ran the instructions and the dump and restore went flawlessly with no errors.

When running "dspace database info" the result seems correct, however it had two IGNORED instead of success. (See attached image "Dump_Restore_Sucess6x_and_2Ignore.png")

I attached the two logs referring to the commands "dspace database update-sequences" e "dspace database migrate ignored".

--
All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx
---
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/f26b09c5-cdfa-4970-8ea8-1076f21323abn%40googlegroups.com.

[Mohammad S. AlMutairi]

All you need after importing the database properly into the new server is to run  [dspace]/bin//dspace database migrate ignored on the new server and just copy the assetstore folder from the old server to the new one and finally do a discovery index by running [dspace]/bin/dspace index-discovery -b . Check Tim's comments/explanations in the following thread https://groups.google.com/g/dspace-tech/c/_7XI9xmwpI8/m/CH__FIU_AwAJ and this one too https://groups.google.com/g/dspace-tech/c/_7XI9xmwpI8/m/DvYbGKx3AgAJ ... Ignore any other comments in that thread and only apply what matches the issue you have so you don't get it confused.

Mo.

[Michel Montenegro]

Mohammad and support

Executing the command [1]:

C:\dspace\bin>dspace database info

Result:

| Category  | Version             | Description                                                       | Type      | Installed On                | State      |

| Versioned | 7.0.2018.06.07 | DS-3851-permission                                        | SQL       | 2023-06-21 10:25:10 | Success  |

| Versioned | 5.0.2014.11.04 | Enable XMLWorkflow Migration                  | JDBC     |                                    | Ignored  |

| Versioned | 6.0.2015.09.01 | DS 2701 Enable XMLWorkflow Migration   | JDBC     |                                    | Ignored  |
| Versioned | 7.0.2019.05.02 | DS-4239-workflow-xml-migration                     | SQL       |                                    | Pending  |

Note: The command "dspace database skip [Version]" didn't work, I tried using it on versions marked as "ignored" (5.0.2014.11.04 and 6.0.2015.09.01).

Executing the command  [2]:

C:\dspace\bin>dspace database migrate ignored

The commands run normally, and stop right here:

-- Update the sequences

ERROR: duplicate key value violates unique constraint "resourcepolicy_pkey"

Executing the command  [3]:

C:\dspace\bin>dspace database update-sequences

Result:

Caught exception: org.postgresql.util.PSQLException: ERROR: relation "systemwidealert" does not exist

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/46253e26-287c-4f80-8a4c-a81f3b41ac1fn%40googlegroups.com.

[Michel Montenegro]

[Help] I read everything they sent, tested it and nothing.

Apparently everything is fine, it starts the migration, but it gives this error when it arrives at version "6.0.2015.09.01 DS-2701", it tries to insert existing information. I tried to skip this specific version and it didn't work.

Is it a migration bug?

Version(6.0.2015.09.01  DS-2701) where the error actually occurs:

Caused by: java.sql.SQLException: Flyway executeSql() error occurred at org.dspace.storage.rdbms.DatabaseUtils.executeSql(DatabaseUtils.java:1253) at org.dspace.storage.rdbms.xmlworkflow.V6_0_2015_09_01__DS_2701_Enable_XMLWorkflow_Migration.migrate(V6_0_2015_09_01__DS_2701_Enable_XMLWorkflow_Migration.java:75)

During migration in version it generates duplicate key error in table "resourcepolicy":

-- Update the sequences

SELECT setval('cwf_workflowitem_seq', max(workflowitem_id)) FROM cwf_workflowitem;

SELECT setval('cwf_collectionrole_seq', max(collectionrole_id)) FROM cwf_collectionrole;

SELECT setval('cwf_workflowitemrole_seq', max(workflowitemrole_id)) FROM cwf_workflowitemrole;

SELECT setval('cwf_pooltask_seq', max(pooltask_id)) FROM cwf_pooltask;

SELECT setval('cwf_claimtask_seq', max(claimtask_id)) FROM cwf_claimtask;

SELECT setval('cwf_in_progress_user_seq', max(in_progress_user_id)) FROM cwf_in_progress_user;

]; ERROR: duplicate key value violates unique constraint "resourcepolicy_pkey"

  Detalhe: Key (policy_id)=(1) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "resourcepolicy_pkey"

  Detalhe: Key (policy_id)=(1) already exists.

Image 1

Image 2

[DSpace Technical Support]

Hi,

It sounds like your database sequences have become outdated... and DSpace 7 is trying to install resourcepolicies starting with ID=1... this is incorrect behavior as it should start the ID at the next available value.

The way to normally fix this is to run the "update-sequences" command BEFORE you export your database contents out of DSpace 6.3.   Or, you could try downloading the v6.3 version of the "update-sequences.sql" script and running it manually on your database:  https://github.com/DSpace/DSpace/blob/dspace-6.3/dspace/etc/postgres/update-sequences.sql

(In that file you minimally need to run the line that pertains to "resourcepolicy_seq" as that's the table that is causing you issues.)

I've added a note about this to our Migration guide... see bullet 2 under "Step 2: Prepare your data to copy..."

https://wiki.lyrasis.org/display/DSDOC7x/Migrating+DSpace+to+a+new+server

Hopefully that helps, as I suspect that's the issue you're hitting.

Tim