Error while migrating nextcloud database from MySQL to PostgreSQL

ℹ️ Support
,
1

Nextcloud version: 18.0.4
Operating system and version: Debian Buster
Apache or nginx version: nginx 1.17.10
PHP version: 7.4

I’m trying to migrate the nextcloud database from MySQL to PostgreSQL. While this went well with my testing instance (which does not have much apps or data), I have a problem migrating my production setup.

I’m using this command for migration:
php occ db:convert-type --clear-schema --all-apps --password “password” pgsql nextcloud 10.20.30.56 nextcloud -v

and this is the error I get:

Clearing schema in new database
Creating schema in new database

In AbstractPostgreSQLDriver.php line 79:
                                                                               
  [Doctrine\DBAL\Exception\DriverException]                                    
  An exception occurred while executing 'DROP INDEX "primary"':                
                                                                               
  SQLSTATE[42704]: Undefined object: 7 ERROR:  index "primary" does not exist  
                                                                               

Exception trace:
  at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php:79
 Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException() at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:169
 Doctrine\DBAL\DBALException::wrapException() at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:145
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1012
 Doctrine\DBAL\Connection->query() at /var/www/cloud.mydomain.de/lib/private/DB/Migrator.php:262
 OC\DB\Migrator->applySchema() at /var/www/cloud.mydomain.de/lib/private/DB/Migrator.php:85
 OC\DB\Migrator->migrate() at /var/www/cloud.mydomain.de/lib/private/DB/Connection.php:457
 OC\DB\Connection->migrateToSchema() at /var/www/cloud.mydomain.de/lib/private/DB/MigrationService.php:486
 OC\DB\MigrationService->executeStep() at /var/www/cloud.mydomain.de/lib/private/DB/MigrationService.php:414
 OC\DB\MigrationService->migrate() at /var/www/cloud.mydomain.de/core/Command/Db/ConvertType.php:251
 OC\Core\Command\Db\ConvertType->createSchema() at /var/www/cloud.mydomain.de/core/Command/Db/ConvertType.php:200
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:915
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /var/www/cloud.mydomain.de/lib/private/Console/Application.php:214
 OC\Console\Application->run() at /var/www/cloud.mydomain.de/console.php:99
 require_once() at /var/www/cloud.mydomain.de/occ:11

In PDOConnection.php line 90:
                                                                               
  [Doctrine\DBAL\Driver\PDOException (42704)]                                  
  SQLSTATE[42704]: Undefined object: 7 ERROR:  index "primary" does not exist  
                                                                               

Exception trace:
  at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:90
 Doctrine\DBAL\Driver\PDOConnection->query() at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1010
 Doctrine\DBAL\Connection->query() at /var/www/cloud.mydomain.de/lib/private/DB/Migrator.php:262
 OC\DB\Migrator->applySchema() at /var/www/cloud.mydomain.de/lib/private/DB/Migrator.php:85
 OC\DB\Migrator->migrate() at /var/www/cloud.mydomain.de/lib/private/DB/Connection.php:457
 OC\DB\Connection->migrateToSchema() at /var/www/cloud.mydomain.de/lib/private/DB/MigrationService.php:486
 OC\DB\MigrationService->executeStep() at /var/www/cloud.mydomain.de/lib/private/DB/MigrationService.php:414
 OC\DB\MigrationService->migrate() at /var/www/cloud.mydomain.de/core/Command/Db/ConvertType.php:251
 OC\Core\Command\Db\ConvertType->createSchema() at /var/www/cloud.mydomain.de/core/Command/Db/ConvertType.php:200
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:915
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /var/www/cloud.mydomain.de/lib/private/Console/Application.php:214
 OC\Console\Application->run() at /var/www/cloud.mydomain.de/console.php:99
 require_once() at /var/www/cloud.mydomain.de/occ:11

In PDOConnection.php line 88:
                                                                               
  [PDOException (42704)]                                                       
  SQLSTATE[42704]: Undefined object: 7 ERROR:  index "primary" does not exist  
                                                                               

Exception trace:
  at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:88
 PDO->query() at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:88
 Doctrine\DBAL\Driver\PDOConnection->query() at /var/www/cloud.mydomain.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1010
 Doctrine\DBAL\Connection->query() at /var/www/cloud.mydomain.de/lib/private/DB/Migrator.php:262
 OC\DB\Migrator->applySchema() at /var/www/cloud.mydomain.de/lib/private/DB/Migrator.php:85
 OC\DB\Migrator->migrate() at /var/www/cloud.mydomain.de/lib/private/DB/Connection.php:457
 OC\DB\Connection->migrateToSchema() at /var/www/cloud.mydomain.de/lib/private/DB/MigrationService.php:486
 OC\DB\MigrationService->executeStep() at /var/www/cloud.mydomain.de/lib/private/DB/MigrationService.php:414
 OC\DB\MigrationService->migrate() at /var/www/cloud.mydomain.de/core/Command/Db/ConvertType.php:251
 OC\Core\Command\Db\ConvertType->createSchema() at /var/www/cloud.mydomain.de/core/Command/Db/ConvertType.php:200
 OC\Core\Command\Db\ConvertType->execute() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:915
 Symfony\Component\Console\Application->doRunCommand() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /var/www/cloud.mydomain.de/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /var/www/cloud.mydomain.de/lib/private/Console/Application.php:214
 OC\Console\Application->run() at /var/www/cloud.mydomain.de/console.php:99
 require_once() at /var/www/cloud.mydomain.de/occ:11

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

The PostgreSQL database also says:

2020-04-24 11:30:38.650 CEST [20183] nextcloud@nextcloud ERROR:  index "primary" does not exist
2020-04-24 11:30:38.650 CEST [20183] nextcloud@nextcloud STATEMENT:  DROP INDEX "primary"

I’m unsure where this error comes from. I’ve seen a similar error with the two-factor app some year ago, but I already tried disabling this app. The error stays the same.

The output of your Nextcloud log in Admin > Logging:

  • no output for the migration

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

  • classic mysql configuration

The output of your Apache/nginx/system log in /var/log/____:

  • no errors in the webserver or system logs

Maybe someone has an idea? Thanks in advance!

Best regards
Oliver

2

Hi magenbrot,

i think db:convert-type only knows one direction: from sqlite into mysql or postgresql etc. I’m not sure you can cross from mysql into postgresql.
Take a look here: https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/db_conversion.html

An other idea is to look into your new postgresql database, with psql or something. Are new tables created? It seems there script does something. As I understand create schema means a table for migration is created in the new database.

You say, in the test system it works without errors. So maybe you will try it in the production system without apps? Switch: “–all-apps”

Last, take a look here: https://github.com/owncloud/documentation/issues/485
It’s describes not your probem directly, but some one else work with this tool in your way. And you can find more information about.

good luck!

bye.

3

Hi @LloydS,

thanks for your response. In fact it worked without migrating the apps. But I wanted to give it another try and find out where it breaks.
So I went back to MySQL and enabled statement logging in PostgreSQL and started migrating with apps again.
Once it broke I could see that it was trying to drop the index of the oc_twofactor_admin_codes table. I disabled the TwoFactor Admin Support App, dropped the table and tried again.

Now it is happily migrating from MySQL to PostgreSQL.

During research I’ve found this page: Two Factor admin support on NC 14.0.4 broken
Maybe this problem still exists.

Thank you.