Postgresql – pgAdmin IV and PostgreSQL 12.0: ERROR: schema “public” already exists

pgadminpgadmin-4postgresqlpostgresql-12

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.

Best Answer

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.

Related Solutions

PostgreSQL not working, pgAdmin always says server not listening

Since you installed multiple versions of PostgreSQL, there is a good chance your cluster is running on a different port than 5432. Additional clusters automatically use the next free port unless instructed otherwise. Like 5433, 5434 etc.

Check your setting in postgresql.conf to find out and connect to the proper port. postgresql.conf is usually installed in your data directory, which in turn is defined in pg_env.bat, usually located here

C:\Program Files\PostgreSQL\<version>\pg_env.bat

Default location of the data directory:

C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

Where <version> is 9.1 or 9.2 in your case.
Base directory varies according to your version of Windows.

Obviously initdb fails to create a database cluster. Read the chapter in the manual to get a grip on initdb. Maybe a problem with missing privileges?

Here is a closely related question with a solution.

Postgresql – pg_restore: [archiver (db)] could not execute query: ERROR: schema “public” already exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

--clean Clean (drop) database objects before recreating them

and

--create Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

--clean Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

--create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.

Best Answer

Related Solutions

PostgreSQL not working, pgAdmin always says server not listening

Postgresql – pg_restore: [archiver (db)] could not execute query: ERROR: schema “public” already exists

Related Question