0

I have a database backup from pgAdmin III with PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg16.04+1) on Ubuntu 16.04 LTS.

I would like to restore this database on my pgAdmin IV with PostgreSQL 12.0 on Docker containers (i.e. pgAdmin IV and PostgreSQL (PostGIS)). The images are: kartoza/postgis:12.0 and dpage/pgadmin4:4.14.

The problem is when I restore the database, pgAdmin IV output shows the following error message:

pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
[...]
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
[...]
pg_restore: creating FK CONSTRAINT "public.places_pilot_area places_pilot_area_fk_street_id"
pg_restore: warning: errors ignored on restore: 1
pg_restore: creating FK CONSTRAINT "public.layer_reference fk_reference_layer_reference1"
[...]
pg_restore: creating FK CONSTRAINT "public.places_pilot_area places_pilot_area_fk_street_id"
pg_restore: warning: errors ignored on restore: 1

I would not like to show these error messages, because they seem to be warnings and not errors (PostgreSQL is trying to create a public schema where it already exists). These errors are not shown on pgAdmin III when I restore using the same database.

There is this similar question. According to that answer, the error is harmless, but I would like to remove it from pgAdmin IV output.

I've already tried to restore with: (1) default options; (2) create option turned on; (3) clean option turned on; and (4) create and clean options turned on; however no success, all previous options result on an error message.

I'm afraid of ignoring these warnings and in the future true errors appear and I do not see them.

Thank you in advance.

Improve this question
3
  • The errors with the foreign key constraints seem more serious; where do they come from? Normally you get such errors when restoring with a non-superuser. Do you also get them with a superuser?
    – Laurenz Albe
    Commented Oct 29, 2019 at 21:56
  • Yes, I do. I use a Docker container with a pgAdmin IV image and inside it a superuser is used. I believe FK errors were just warnings like schema public ones, because when I used just pgAdmin IV to back my database up and to restore it again, it worked fine, no errors appeared. I tried to explain on an answer. Thank you Laurenz for your feedback.
    – rmmariano
    Commented Oct 30, 2019 at 18:13
  • Does this answer your question? pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
    – Leandro
    Commented Dec 7, 2023 at 14:11

1 Answer 1

Reset to default
0

Well, I was able to solve my problem by another path.

I suspected the problem was related to pgAdmin III and not PostgreSQL, then I opened my remote server through pgAdmin IV and I backed my remote database up.

After that, on pgAdmin IV, I restored the database and it worked fine, with no error.

Then, I believe the problem is related to creating a backup on pgAdmin III and restore it on pgAdmin IV.

If someone gets the same error in the future, please, use just pgAdmin IV to avoid problems.

Improve this answer
1
  • Then it is probably different versions of pg_dump and pg_restore at work.
    – Laurenz Albe
    Commented Oct 30, 2019 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.