I got a error when I restored


#1

Hello everyone,
I’m getting some error when I run the restore command…

SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
ALTER TYPE
ERROR:  type "enum_Notes_permission" already exists
SET
SET
ERROR:  relation "Authors" already exists
ALTER TABLE
ERROR:  relation "Authors_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "Notes" already exists
ALTER TABLE
ERROR:  relation "Revisions" already exists
ALTER TABLE
ERROR:  relation "SequelizeMeta" already exists
ALTER TABLE
ERROR:  relation "Sessions" already exists
ALTER TABLE
ERROR:  relation "Temp" already exists
ALTER TABLE
ERROR:  relation "Temps" already exists
ALTER TABLE
ERROR:  relation "Users" already exists
ALTER TABLE
ALTER TABLE
ERROR:  duplicate key value violates unique constraint "Authors_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY Authors, line 1
 setval 
--------
      2
(1 row)

ERROR:  duplicate key value violates unique constraint "Notes_pkey"
DETAIL:  Key (id)=(139cc586-bbe4-4e08-87b5-c77dab9c2d67) already exists.
CONTEXT:  COPY Notes, line 1
ERROR:  duplicate key value violates unique constraint "Revisions_pkey"
DETAIL:  Key (id)=(d3a30eb1-24b1-4bd2-a08d-0b79a17b0d7e) already exists.
CONTEXT:  COPY Revisions, line 1
ERROR:  duplicate key value violates unique constraint "SequelizeMeta_pkey"
DETAIL:  Key (name)=(20150504155329-create-users.js) already exists.
CONTEXT:  COPY SequelizeMeta, line 1
ERROR:  duplicate key value violates unique constraint "Sessions_pkey"
DETAIL:  Key (sid)=(YU3T4MbAOIL0zoKwI1SWUphyroxHm0PS) already exists.
CONTEXT:  COPY Sessions, line 1
COPY 0
COPY 0
ERROR:  duplicate key value violates unique constraint "Users_pkey"
DETAIL:  Key (id)=(8758f908-b87f-4376-aaff-507a16ba73ff) already exists.
CONTEXT:  COPY Users, line 1
ERROR:  multiple primary keys for table "Authors" are not allowed
ERROR:  multiple primary keys for table "Notes" are not allowed
ERROR:  multiple primary keys for table "Revisions" are not allowed
ERROR:  multiple primary keys for table "SequelizeMeta" are not allowed
ERROR:  multiple primary keys for table "Sessions" are not allowed
ERROR:  multiple primary keys for table "Temp" are not allowed
ERROR:  multiple primary keys for table "Temps" are not allowed
ERROR:  multiple primary keys for table "Users" are not allowed
ERROR:  relation "Users_profileid_key" already exists
ERROR:  relation "authors_note_id_user_id" already exists
ERROR:  relation "notes_alias" already exists
ERROR:  relation "notes_shortid" already exists

Any thoughts on how I can fix it ? Thx

Btw, is this restore command right?
$ cat backup.sql | docker exec -i $(docker-compose ps -q database) psql -U hackmd

I kind of care about what"but last command is" in the document means…Did I get something wrong?


#2

Hey there :wave:

It seems like you are restoring the database while codimd is already running. That only works when you don’t do a full database restore.

If you are running codimd on a new instance right now and there is nothing meaningful in the database yet, you can run docker-compose down -v which should delete the volume of the database as well as the running containers.

Then start the database only then using docker-compose up -d database and then you can restore the data using cat backup.sql | docker-compose exec database psql -U hackmd. Afterwards you docker-compose up -d should start codimd and everything should work as before.

When you already have meaningful data in your database please reach out there again, in order to get further help, because in this case we probably want to restore into a second database and only restore the data.


#3

In my case, my codimd already has meaningful data. I want to backup them before I update the codimd.

Although the upgrade should not cause any problems, just prepare for all possibilities.

Due to the restore command isnot work for me in the morning. I created a new volume , and copied the _data folder from codimd-container_mydatabase. It also seems like a way to backup. But I am not sure if this will miss something…


#4

When you copy the database volume, please make sure the database is stopped, otherwise you are likely to get inconsistent data which the database will refuse to use.

For the backup itself you can do a full backup using docker-compose exec database pg_dump hackmd -U hackmd > backup.sql. the backup.sql will contain a full database backup that you can restore to a new database using the commands from my last comment.

This is a full backup and should serve you well. (Depending on your image upload type, you might also need to backup your uploaded images separately.)

If you only care about the data, since CodiMD will recreate the schema anyway, you can also go for: docker-compose exec database pg_dump hackmd -U hackmd --data-only > backup.sql but keep in mind that this might be incompatible when restoring to newer versions than use backuped from.

Also you don’t need to run the restore command you used above before upgrading. At least not on an existing database. That’s something you would always do to a new database.


#5

Thanks for your really fast support. This is really helpful. :heart: