Hi Jake, I don't think it's necessary to upgrade the old dspace PostgreSQL version because of this issue ( You already have version 13 installed on the new server ). This is a common issue that can be caused by several causes. It's not related specifically to dspace. Just try the steps you see below and keep us updated here in the list.
On the old dspace Server execute (Double check the pg_dump path. 9.6?): 1) cd "C:\Program Files\PostgreSQL\9.6\bin" 2) .\pg_dump -U postgres -W -h localhost -Fc -v -C -c --if-exists -E UTF8 -d dspace -f 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 dspace-63.sql 7) psql --username=postgres dspace 8) \dn 9) \dx 10) \q ** The -e (--exit-on-error) you see in step 6 should give you a clue why the old or the new restoring was/is failing so watch for any error and post what it shows here to know exactly what's happening in your server (Hopefully not). Regards, On Friday, February 3, 2023 at 1:54:34 AM UTC+3 jacob.cameron wrote: Hi Mohammad, I’ve actually done that prior to this and still get a large number of errors no matter whether I import using psql or pg_restore for an sql or plain text dump. I’m wondering if I need to upgrade from 9.6 to 13 in Postgres before it’ll fix it. I’m sure I had to do that when I went from 1.8.3 to 6 originally. I could be wrong. That was along time ago. But I have something about that in old notes from that upgrade. Jake -- Jake Cameron, BCS(UNB) Systems Support Specialist III Information Systems and Technical Services University of Lethbridge Library Phone:(403)329-2756 This e-mail, including any and all attachments, is only for the use of the intended recipient(s) and may contain information that is confidential or privileged. If you are not the intended recipient, you are advised that any dissemination, copying or other use of this e-mail is prohibited. Please notify the sender of the error in communication by return e-mail and destroy all copies of this e-mail. Thank you. *From:* [email protected] <[email protected]> *On Behalf Of *Mohammad S. AlMutairi *Sent:* January 27, 2023 5:21 PM *To:* DSpace Technical Support <[email protected]> *Subject:* Re: [dspace-tech] Issue with database migration 6.3 to 7.4 Caution: This email was sent from someone *outside of the University of Lethbridge*. Do not click on links or open attachments unless you know they are safe. Suspicious emails should be forwarded to [email protected]. Hi all, @Tim, I think Jacob issue is caused by the way he dumped his old database and the way he tried to restore it. Those " thousands of invalid command /N" should've not shown up if the database was dumped in a custom binary format and the new server database was cleaned (emptied) or even recreated before restoring the old database int it using pg_restore (It's a must to use pg_restore for any binary format dumps). There is a good chance for him to able to migrate unless he is affected by the known issue you mentioned. https://www.pgadmin.org/docs/pgadmin4/6.18/backup_dialog.html Regards On Friday, January 27, 2023 at 11:04:41 PM UTC+3 Tim Donohue wrote: Hi Sean, This issue is a known issue which some sites encounter during the upgrade to DSpace 7 *if they have previously ignored migrations which never ran during a past 5.x ->6.x upgrade*. Essentially, to summarize, there are two "5.7" migrations which can fail during the upgrade to 7.x... these migrations are *obsolete* and can be skipped over. There's a thread discussing this on dspace-tech here, along with "workarounds" to skip the two problematic 5.7 migrations: https://groups.google.com/g/dspace-tech/c/PlOA1WMvd4M/m/eBVE5RfhBgAJ There's also a fix for this coming in the 7.5 release. In 7.5 (due Feb 20) we have a new "./dspace database skip" command which can be used to skip problematic migrations. See the PR at https://github.com/DSpace/DSpace/pull/8611 So, you are not the first person to hit this migration bug. An easier fix is coming in 7.5... but in the meantime, you can use the dspace-tech link above to workaround the issue if you want to upgrade immediately to 7.4. Tim *From:* [email protected] <[email protected]> on behalf of Cameron, Jacob <[email protected]> *Sent:* Friday, January 27, 2023 12:46 PM *To:* Sean Carte <[email protected]> *Cc:* DSpace Technical Support <[email protected]> *Subject:* RE: [dspace-tech] Issue with database migration 6.3 to 7.4 I’ve tried importing with the postgres account, using an elevated command prompt, in pgadmin and they are all giving the same errors. It’s still failing. I’m at a loss now. There’s a ton of tables missing when it finishes importing. -- Jake Cameron, BCS(UNB) Systems Support Specialist III Information Systems and Technical Services University of Lethbridge Library Phone:(403)329-2756 This e-mail, including any and all attachments, is only for the use of the intended recipient(s) and may contain information that is confidential or privileged. If you are not the intended recipient, you are advised that any dissemination, copying or other use of this e-mail is prohibited. Please notify the sender of the error in communication by return e-mail and destroy all copies of this e-mail. Thank you. *From:* Sean Carte <[email protected]> *Sent:* January 27, 2023 12:31 AM *To:* Cameron, Jacob <[email protected]> *Cc:* DSpace Technical Support <[email protected]> *Subject:* Re: [dspace-tech] Issue with database migration 6.3 to 7.4 Caution: This email was sent from someone *outside of the University of Lethbridge*. Do not click on links or open attachments unless you know they are safe. Suspicious emails should be forwarded to [email protected]. Hi Jacob I had a similar problem with a database I was trying to import: many /N invalid commands. I gave my user superuser privileges and was able to import the dump without those errors. Sean On Thu, 26 Jan 2023 at 23:06, Cameron, Jacob <[email protected]> wrote: Hi Everyone, I exported my DSpace 6.3 database, imported it on the new server running 7.4. There were thousands of invalid command /N that showed up during the import and lots of other ERROR: relation "xxx" does not exist. I did try running dspace database migrate ignored afterwards and was given some errors. I checked the DSpace log and there was some messaged about being unable to find the bean by name or database but that may have been when I deleted my old database before recreating the new one to import to. Any thoughts or suggestions would be welcome. I've seen a few suggestions out there and I've tried them, but I haven't had any luck. C:\DSpace\bin>dspace database migrate ignored Using DSpace installation in: C:\DSpace Database URL: jdbc:postgresql://localhost:5432/dspace Migrating database to latest version AND running previously "Ignored" migrations... (Check logs for details) Migration exception: java.sql.SQLException: Flyway migration error occurred at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:785) at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:672) at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:195) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) 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) Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql failed ----------------------------------------------------------------------------------------- SQL State : 42703 Error Code : 0 Message : ERROR: column "resource_type_id" does not exist Location : org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql (C:\DSpace\file:\C:\DSpace\lib\dspace-api-7.4.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql) Line : 16 Statement : CREATE INDEX metadatavalue_resource_type_id_idx ON metadatavalue (resource_type_id) at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:385) at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:275) at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:55) at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:274) at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:247) at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:141) at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:69) at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:99) at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:139) at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:141) at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:98) at org.flywaydb.core.Flyway$1.execute(Flyway.java:173) at org.flywaydb.core.Flyway$1.execute(Flyway.java:124) at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:214) at org.flywaydb.core.Flyway.migrate(Flyway.java:124) at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:772) ... 9 more Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql failed ----------------------------------------------------------------------------------------- SQL State : 42703 Error Code : 0 Message : ERROR: column "resource_type_id" does not exist Location : org/dspace/storage/rdbms/sqlmigration/postgres/V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql (C:\DSpace\file:\C:\DSpace\lib\dspace-api-7.4.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V5.7_2017.04.11__DS-3563_Index_metadatavalue_resource_type_id_column.sql) Line : 16 Statement : CREATE INDEX metadatavalue_resource_type_id_idx ON metadatavalue (resource_type_id) at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:275) at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:222) at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:126) at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:69) at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:58) at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27) at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:57) at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:377) ... 24 more Caused by: org.postgresql.util.PSQLException: ERROR: column "resource_type_id" does not exist 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:490) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194) at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:201) at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:95) at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:210) ... 30 more C:\DSpace\bin>dspace database info Using DSpace installation in: C:\DSpace Database Type: postgres Database URL: jdbc:postgresql://localhost:5432/dspace Database Schema: public Database Username: dspace Database Software: PostgreSQL version 13.5 Database Driver: PostgreSQL JDBC Driver version 42.4.1 PostgreSQL 'pgcrypto' extension installed/up-to-date? true (version=1.3) FlywayDB Version: 8.4.4 +-----------+----------------+------------------------------------------------------+----------+---------------------+----------------+ | Category | Version | Description | Type | Installed On | State | +-----------+----------------+------------------------------------------------------+----------+---------------------+----------------+ | Versioned | 1.1 | Initial DSpace 1.1 database schema | SQL | | Below Baseline | | Versioned | 1.2 | Upgrade to DSpace 1.2 schema | SQL | | Below Baseline | | Versioned | 1.3 | Upgrade to DSpace 1.3 schema | SQL | | Below Baseline | | Versioned | 1.3.9 | Drop constraint for DSpace 1 4 schema | JDBC | | Below Baseline | | Versioned | 1.4 | Upgrade to DSpace 1.4 schema | SQL | | Below Baseline | | Versioned | 1.5 | Upgrade to DSpace 1.5 schema | SQL | | Below Baseline | | Versioned | 1.5.9 | Drop constraint for DSpace 1 6 schema | JDBC | | Below Baseline | | Versioned | 1.6 | Upgrade to DSpace 1.6 schema | SQL | | Below Baseline | | Versioned | 1.7 | Upgrade to DSpace 1.7 schema | SQL | | Below Baseline | | | 1.8 | Initializing from DSpace 1.8 database schema | BASELINE | 2016-12-01 08:35:37 | Baseline | | Versioned | 3.0 | Upgrade to DSpace 3.x schema | SQL | 2016-12-01 08:35:37 | Success | | Versioned | 4.0 | Upgrade to DSpace 4.x schema | SQL | 2016-12-01 08:35:38 | Success | | Versioned | 4.9.2015.10.26 | DS-2818 registry update | SQL | 2016-12-01 08:35:38 | Success | | Versioned | 5.0.2014.08.08 | DS-1945 Helpdesk Request a Copy | SQL | 2016-12-01 08:35:38 | Success | | Versioned | 5.0.2014.09.25 | DS 1582 Metadata For All Objects drop constraint | JDBC | 2016-12-01 08:35:38 | Success | | Versioned | 5.0.2014.09.26 | DS-1582 Metadata For All Objects | SQL | 2016-12-01 08:35:38 | Success | | Versioned | 5.6.2016.08.23 | DS-3097 | SQL | 2016-12-01 08:35:44 | Success | | Versioned | 6.0.2015.03.06 | DS 2701 Dso Uuid Migration | JDBC | 2016-12-01 08:35:44 | Success | | Versioned | 6.0.2015.03.07 | DS-2701 Hibernate migration | SQL | 2016-12-01 08:35:44 | Success | | Versioned | 6.0.2015.08.31 | DS 2701 Hibernate Workflow Migration | JDBC | 2016-12-01 08:36:14 | Success | | Versioned | 6.0.2016.01.03 | DS-3024 | SQL | 2016-12-01 08:36:14 | Success | | Versioned | 6.0.2016.01.26 | DS 2188 Remove DBMS Browse Tables | JDBC | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.02.25 | DS-3004-slow-searching-as-admin | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.04.01 | DS-1955 Increase embargo reason | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.04.04 | DS-3086-OAI-Performance-fix | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.04.14 | DS-3125-fix-bundle-bitstream-delete-rights | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.05.10 | DS-3168-fix-requestitem item id column | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.07.21 | DS-2775 | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.07.26 | DS-3277 fix handle assignment | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.0.2016.08.23 | DS-3097 | SQL | 2016-12-01 08:36:15 | Success | | Versioned | 6.1.2017.01.03 | DS 3431 Add Policies for BasicWorkflow | JDBC | 2018-11-13 08:28:14 | Success | | Versioned | 5.0.2014.11.04 | Enable XMLWorkflow Migration | JDBC | 2023-01-26 13:40:24 | Out of Order | | Versioned | 5.7.2017.04.11 | DS-3563 Index metadatavalue resource type id column | SQL | | Ignored | | Versioned | 5.7.2017.05.05 | DS 3431 Add Policies for BasicWorkflow | JDBC | | Ignored | | Versioned | 6.0.2015.09.01 | DS 2701 Enable XMLWorkflow Migration | JDBC | | Ignored | | Versioned | 7.0.2017.10.12 | DS-3542-stateless-sessions | SQL | | Pending | | Versioned | 7.0.2018.04.03 | Upgrade Workflow Policy | JDBC | | Pending | | Versioned | 7.0.2018.04.16 | dspace-entities | SQL | | Pending | | Versioned | 7.0.2018.06.07 | DS-3851-permission | SQL | | Pending | | Versioned | 7.0.2019.05.02 | DS-4239-workflow-xml-migration | SQL | | Pending | | Versioned | 7.0.2019.06.14 | scripts-and-process | SQL | | Pending | | Versioned | 7.0.2019.07.31 | Retrieval of name variant | SQL | | Pending | | Versioned | 7.0.2019.11.13 | relationship type copy left right | SQL | | Pending | | Versioned | 7.0.2020.01.08 | DS-626-statistics-tracker | SQL | | Pending | | Versioned | 7.0.2020.10.31 | CollectionCommunity Metadata Handle | JDBC | | Pending | | Versioned | 7.0.2021.01.22 | Remove basic workflow | SQL | | Pending | | Versioned | 7.0.2021.02.08 | tilted rels | SQL | | Pending | | Versioned | 7.0.2021.03.18 | Move entity type to dspace schema | SQL | | Pending | | Versioned | 7.0.2021.09.24 | Move entity type from item template to collection | SQL | | Pending | | Versioned | 7.0.2021.10.04 | alter collection table drop workflow stem columns | SQL | | Pending | | Versioned | 7.1.2021.10.18 | Fix MDV place after migrating from DSpace 5 | SQL | | Pending | | Versioned | 7.3.2022.04.29 | orcid queue and history | SQL | | Pending | | Versioned | 7.3.2022.05.16 | Orcid token table | SQL | | Pending | | Versioned | 7.3.2022.06.16 | process to group | SQL | | Pending | | Versioned | 7.3.2022.06.20 | add last version status column to relationship table | SQL | | Pending | +-----------+----------------+------------------------------------------------------+----------+---------------------+----------------+ -- Jake Cameron, BCS(UNB) Systems Support Specialist III Information Systems and Technical Services University of Lethbridge Library Phone:(403)329-2756 This e-mail, including any and all attachments, is only for the use of the intended recipient(s) and may contain information that is confidential or privileged. If you are not the intended recipient, you are advised that any dissemination, copying or other use of this e-mail is prohibited. Please notify the sender of the error in communication by return e-mail and destroy all copies of this e-mail. Thank you. -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/YQBPR0101MB5031DCDC70DE53B1B52453E084CF9%40YQBPR0101MB5031.CANPRD01.PROD.OUTLOOK.COM . -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/YQBPR0101MB50311AB2D6E52FB2092A5B8084CC9%40YQBPR0101MB5031.CANPRD01.PROD.OUTLOOK.COM <https://groups.google.com/d/msgid/dspace-tech/YQBPR0101MB50311AB2D6E52FB2092A5B8084CC9%40YQBPR0101MB5031.CANPRD01.PROD.OUTLOOK.COM?utm_medium=email&utm_source=footer> . -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/d2b921ab-ae35-45d3-abed-e022db6e9b7bn%40googlegroups.com <https://groups.google.com/d/msgid/dspace-tech/d2b921ab-ae35-45d3-abed-e022db6e9b7bn%40googlegroups.com?utm_medium=email&utm_source=footer> . -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/8d9db410-9a66-456e-a540-ef113138c92cn%40googlegroups.com.
