2

I have a database named mydbname that I created manually. I also created a user myuser for which I granted all privileges on mydbname.

I dumped my database using pg_dump this way:

$> pg_dump --host=myhost --port=myport--username=super --format=c --file=myfile.dump mydbname

The dump was successful. But then, I tried to test a full restoration (schema and data) using pg_restore like this:

$> pg_restore --host=myhost --port=myport--username=super -d mydbname myfile.dump

This failed (no restoration done) and I got errors like this:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 173; 1259 25566 TABLE auth_group myuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "auth_group" already exists
pg_restore: [archiver (db)] Error from TOC entry 189; 1259 25613 TABLE django_migrations myuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "django_migrations" already exists
    Command was: CREATE TABLE django_migrations (
    id integer NOT NULL,
    app character varying(255) NOT NULL,
    name character varyin...
pg_restore: [archiver (db)] Error from TOC entry 190; 1259 25619 SEQUENCE django_migrations_id_seq myuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "django_migrations_id_seq" already exists
    Command was: CREATE SEQUENCE django_migrations_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
Command was: ALTER TABLE ONLY participer_commande
ADD CONSTRAINT participer_commande_produit_id_fbecbd48_fk_porter_produit_id FOREIGN...
pg_restore: [archiver (db)] Error from TOC entry 2981; 2606 25829 FK CONSTRAINT participer_fileinfor_campagne_id_715122f1_fk_porter_ca jbpuser1
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "participer_fileinfor_campagne_id_715122f1_fk_porter_ca" for relation "participer_fileinformations" already exists
WARNING: errors ignored on restore: 118

Could you please help me how to restore the whole database (recreate the whole schema, relations and data)? What is the best way pg_restore should be used?

  • 1
    Did you try the restore on an empty database? – dezso Apr 17 '18 at 20:48
  • Yes, actually what I did is that I first dropped mydbname, create it again, regrant the priviliges to myuser and then called the pg_restore as I described above and it worked fine without any error message. But I would like to avoid doing the drop and creation... Is it possible to do it in one command with the right options of pg_restore? – Ayoub Massoudi Apr 18 '18 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.