Unable to Start After DB Import (error: relation X already exists)

Jessinra · November 9, 2020, 5:10pm

Unable to Start After DB Import (error: relation X already exists)

Hi, I have the same strapi app deployed on multiple environments (local, Heroku, etc) and I’m trying to move the data from one to the other.

I followed this docs [1] (Importing and Exporting Heroku Postgres Databases | Heroku Dev Center) and exported the Postgres database I use for strapi in the Heroku environment, I successfully exported the DB.
After I tried to import the data into the local Postgres database (also referring docs [1]), my local strapi won’t start. (check logs below)

it always shows error: relation X already exists. X = one of the relations defined

Things I tried & the result

(failed) I tried to drop the database then recreate a new one, follow docs [1] to restore, and run strapi local => can’t start, same logs.
(failed)
I tried to use heroku pg:pull method (Push and Pull Databases To and From Heroku | Heroku), to pull data from Heroku to the local DB, the pull process succeed, but the local strapi won’t start (same logs).
(somewhat successful)
I tried to drop the database => recreate a new one => run npm run develop (start strapi on development env) => strapi initialize the database with tables => then I follow the docs [1] => result: the data partially imported (some type has no data, or partially imported)

Logs

Server wasn't able to start properly.
[2020-11-08T10:32:43.724Z] error error: relation "core_store" already exists
    at Parser.parseErrorMessage (/Users/jessinra/Code/Project/skadi/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/Users/jessinra/Code/Project/skadi/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/jessinra/Code/Project/skadi/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/jessinra/Code/Project/skadi/node_modules/pg-protocol/dist/index.js:8:42)
    at Socket.emit (events.js:314:20)
    at addChunk (_stream_readable.js:307:12)
    at readableAddChunk (_stream_readable.js:282:9)
    at Socket.Readable.push (_stream_readable.js:221:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
From previous event:
    at Client_PG._query (/Users/jessinra/Code/Project/skadi/node_modules/knex/lib/dialects/postgres/index.js:234:12)
    at Client_PG.query (/Users/jessinra/Code/Project/skadi/node_modules/knex/lib/client.js:158:17)
    at Runner.query (/Users/jessinra/Code/Project/skadi/node_modules/knex/lib/runner.js:135:36)
    at Runner.<anonymous> (/Users/jessinra/Code/Project/skadi/node_modules/knex/lib/runner.js:228:25)
    at processImmediate (internal/timers.js:461:21)
From previous event:
    at Runner.queryArray (/Users/jessinra/Code/Project/skadi/node_modules/knex/lib/runner.js:227:12)
    at /Users/jessinra/Code/Project/skadi/node_modules/knex/lib/runner.js:37:25
From previous event:
    at Runner.run (/Users/jessinra/Code/Project/skadi/node_modules/knex/lib/runner.js:25:16)
    at SchemaBuilder.Target.then (/Users/jessinra/Code/Project/skadi/node_modules/knex/lib/interface.js:14:43)

Describe the bug

Can’t run strapi after database import

Steps to reproduce the behavior

export (dump) strapi database from heroku
import dump into strapi database
run npm run develop / start

Expected behavior

Strapi runs successfully, all data loaded successfully

Screenshots

System

Node.js version: v14.11.0
NPM version: 6.14.8
Strapi version: 3.2.5
Database: postgres
Operating system: ubuntu 18.04
Other dependencies (package.json)

    "knex": "<0.20.0",
    "node-fetch": "^2.6.1",
    "patch-package": "^6.2.2",
    "pg": "^8.3.3",
    "sqlite3": "latest",
    "strapi": "3.2.5",
    "strapi-admin": "3.2.5",
    "strapi-connector-bookshelf": "3.2.5",
    "strapi-plugin-content-manager": "3.2.5",
    "strapi-plugin-content-type-builder": "3.2.5",
    "strapi-plugin-documentation": "3.2.5",
    "strapi-plugin-email": "3.2.5",
    "strapi-plugin-graphql": "3.2.5",
    "strapi-plugin-upload": "3.2.5",
    "strapi-plugin-users-permissions": "3.2.5",
    "strapi-provider-upload-cloudinary": "3.2.5",
    "strapi-utils": "3.2.5"

really appreciate if someone could reach out soon
thank you very much in advance!

DMehaffy · November 9, 2020, 9:53pm

If possible can you generate a sample new app, and take a dump of your database so I can do some testing? As mentioned in the GitHub issue, I don’t think it’s directly related to a Strapi code problem but something odd in the export. But i’d like to see how it’s being done from your side just so I can try to reproduce and dig into the issue.

Jessinra · November 10, 2020, 2:24pm

sure, https://github.com/Jessinra/kemendikbud-strapi this is my app,

I found a workaround yesterday and managed to import successfully, and the step-by-step is written in the README.md

Some things I found out:
Might be the Postgres’s version incompatibility issue:
Heroku use v12.8*, my local Linux use v12.8*, my prod server use dokku (only support v11.6)

reference of solution:
https://github.com/dokku/dokku-postgres/issues/18

dlinc · December 22, 2020, 3:08am

I’m having the exact same error, @Jessinra, after attempting a db import – is there somewhere I can take a look at your README.md and your solution? The link you provided appears to have been taken down. Seems like I’m using the exact same setup.

Basically, I’m trying to import a version of my database from the Staging server to a Development server, but am then unable to run the “yarn develop” command – and seeing this error:

error: relation "core_store" already exists

Thanks for any advice!

Ivo_Tanev · September 15, 2021, 11:36am

The same error here.

I was working with 2 databases: Dev_DB and Prod_DB

I switched my local database config to connect to the production database (because we decided to use only the production DB) And when i start the local server i got:

relation "core_store" already exists.

But when I start the strapi in the server terminal with strapi start or even ‘strapi develop’ command - it works fine. Any idea?

Ivo_Tanev · September 15, 2021, 2:06pm

Now I realize that, the databases are identical. When I change the settings in config/database.js file (for example I just change the host and user and password to point another external database this error is occured. The databases are identical I just do export → import

Mhm0ud · October 7, 2021, 7:25am

I have the same issue I’m trying to clone the DB from the development environment to another environment by changing the username and password for the new deployment.
That what I’m doing:
" I’m using Postgres DB name “test”, I clone the DB to a new one name: “test_new” and I configure the application to connect to the newly cloned DB “test_new”, the issue is, when the application start is trying to create tables which already exist in the DB, so it failed to start.
Why does Strapi try to create the tables? while it’s already created. "

Gabbe · March 31, 2022, 2:57pm

Any progress? I’m stuck with a similar problem.

saschakycia · April 30, 2022, 12:14am

I had exact the same error message after migrating the SQL DB from my staging environment. But i solved it by loggin into the postgres user, then make sure the new created user for strapi have proper privileges with the following lines:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;

In my instance i only had one schema called “public” and my user was “strapi_develop”. After the user had proper access, the error message was gone and the server started successfully.

Hope it helps someone!

andrew-braun · May 14, 2022, 12:03pm

Oh my god thank you–this is the only thing that worked for me!

My process was this:

heroku pg:backups:capture --a <my-app>
heroku pg:backups:download--a <my-app>
Use pgAdmin restore with these settings: include CREATE DATABASE statement, Clean before restore. Not sure how necessary, but seemed to minimize my errors. On running this, I got a bunch of errors saying:
pg_restore: error: could not execute query: ERROR: role "xxxxxxxxxxxxx" does not exist
Command was: ALTER TABLE public.strapi_permission OWNER TO xxxxxxxxxxxxx;

So it makes sense that we need to alter something about permissions!

Then I ran your SQL like so:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO strapi; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO strapi;

And it worked like a charm! Seriously a lifesaver–I now have absolutely identical environments and data across my local machine, staging, and prod, which will really help with testing and troubleshooting

kote · July 18, 2022, 7:28am

hi there, had the same issue, granted PRIVILEGES with no luck(

andrew-braun · July 18, 2022, 7:45am

I’ve had it not work sometimes as well–in one case I’d made a new database and left the owner as postgres instead of strapi. Worth checking that!