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.
Ошибка безвредна, но, чтобы избавиться от нее, я думаю, что вам нужно разбить это восстановление на две команды, например:
dropdb -U postgres mydb &&
pg_restore --create --dbname=postgres --username=postgres pg_backup.dump--cleanВариант в pg_restore не выглядит как много , но на самом деле вызывает нетривиальные проблемы.
Для версий до 9.1
Комбинация --createи --cleanв параметрах pg_restore была ошибкой в старых версиях PG (до 9.1). Между ними есть некоторое противоречие (цитируем 9.1 manpage):
—clean Очистить (удалить) объекты базы данных перед их воссозданием
и
—create Создать базу данных перед ее восстановлением.
Потому что какой смысл очищать в новой базе данных?
Начиная с версии 9.2
Комбинация теперь принята, и доктор говорит это (цитируя 9.3 manpage):
—clean Очистить (удалить) объекты базы данных перед их воссозданием. (Это может генерировать некоторые безобидные сообщения об ошибках, если какие-либо объекты не присутствуют в целевой базе данных.)
—create Создать базу данных перед ее восстановлением. Если также указан параметр —clean, удалите и заново создайте целевую базу данных перед подключением к ней.
Теперь, когда оба вместе приводят к такой последовательности во время восстановления:
DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...Нет DROPдля каждого отдельного объекта, только DROP DATABASEв начале. Если не использовать --createэто было бы наоборот.
В любом случае, эта последовательность вызывает ошибку publicуже существующей схемы, потому что создание mydbиз template0нее уже импортировало (что является нормой, это точка базы данных шаблонов).
Я не уверен, почему этот случай не обрабатывается автоматически pg_restore. Возможно, это приведет к нежелательным побочным эффектам, когда администратор решит настроить template0и / или изменить назначение public, даже если мы не должны этого делать.
i am using pgAdmin 4, and i need to restore database and get this error :
I let the number of job is empty and role name is ‘postgres’
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: creating SCHEMA "tiger"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8; 2615 18151 SCHEMA tiger postgres
pg_restore: [archiver (db)] could not execute query: ERROR: schema "tiger" already exists
Command was: CREATE SCHEMA tiger;
pg_restore: creating EXTENSION "fuzzystrmatch"
pg_restore: creating COMMENT "EXTENSION fuzzystrmatch"
pg_restore: creating EXTENSION "postgis"
pg_restore: creating COMMENT "EXTENSION postgis"
pg_restore: creating EXTENSION "postgis_tiger_geocoder"
pg_restore: creating COMMENT "EXTENSION postgis_tiger_geocoder"
pg_restore: creating SCHEMA "topology"
pg_restore: [archiver (db)] Error from TOC entry 7; 2615 17881 SCHEMA topology postgres
pg_restore: [archiver (db)] could not execute query: ERROR: schema "topology" already exists
Command was: CREATE SCHEMA topology;
Here,I got no error, but in the last line, the error is appear
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: creating EXTENSION "address_standardizer"
pg_restore: creating COMMENT "EXTENSION address_standardizer"
pg_restore: creating EXTENSION "ogr_fdw"
pg_restore: creating COMMENT "EXTENSION ogr_fdw"
pg_restore: creating EXTENSION "pgrouting"
pg_restore: creating COMMENT "EXTENSION pgrouting"
pg_restore: creating EXTENSION "pointcloud"
pg_restore: creating COMMENT "EXTENSION pointcloud"
pg_restore: creating EXTENSION "pointcloud_postgis"
pg_restore: creating COMMENT "EXTENSION pointcloud_postgis"
pg_restore: creating EXTENSION "postgis_sfcgal"
pg_restore: creating COMMENT "EXTENSION postgis_sfcgal"
pg_restore: creating EXTENSION "postgis_topology"
pg_restore: creating COMMENT "EXTENSION postgis_topology"
pg_restore: creating TABLE "public.angkot"
pg_restore: creating TABLE "public.det_fasilitas"
pg_restore: creating TABLE "public.det_kegiatan"
pg_restore: creating TABLE "public.det_khatib"
pg_restore: creating TABLE "public.detail_angkot"
pg_restore: creating TABLE "public.fasilitas"
pg_restore: creating TABLE "public.hotel_region"
pg_restore: creating SEQUENCE "public.hotel_region_gid_seq"
pg_restore: creating SEQUENCE OWNED BY "public.hotel_region_gid_seq"
pg_restore: creating SEQUENCE "public.id_keg_seq"
pg_restore: creating TABLE "public.industri_kecil"
pg_restore: creating TABLE "public.jenis_kegiatan"
pg_restore: creating TABLE "public.kategori"
pg_restore: creating TABLE "public.kategori_rm"
pg_restore: creating TABLE "public.kecamatan"
pg_restore: creating TABLE "public.kegiatan"
pg_restore: creating TABLE "public.kondisi_fasilitas"
pg_restore: creating TABLE "public.rm"
pg_restore: creating SEQUENCE "public.kuliner_region_gid_seq"
pg_restore: creating SEQUENCE OWNED BY "public.kuliner_region_gid_seq"
pg_restore: creating TABLE "public.login"
pg_restore: creating SEQUENCE "public.masjid_sq"
pg_restore: creating TABLE "public.mesjid"
pg_restore: creating TABLE "public.objek_wisata"
pg_restore: creating TABLE "public.ustad"
pg_restore: creating DEFAULT "public.gid"
pg_restore: creating DEFAULT "public.gid"
pg_restore: processing data for table "public.angkot"
pg_restore: processing data for table "public.det_fasilitas"
pg_restore: processing data for table "public.det_kegiatan"
pg_restore: processing data for table "public.det_khatib"
pg_restore: processing data for table "public.detail_angkot"
pg_restore: processing data for table "public.fasilitas"
pg_restore: processing data for table "public.hotel_region"
pg_restore: executing SEQUENCE SET hotel_region_gid_seq
pg_restore: executing SEQUENCE SET id_keg_seq
Well, still running wel
pg_restore: processing data for table "public.industri_kecil"
pg_restore: processing data for table "public.jenis_kegiatan"
pg_restore: processing data for table "public.kategori"
pg_restore: processing data for table "public.kategori_rm"
pg_restore: processing data for table "public.kecamatan"
pg_restore: processing data for table "public.kegiatan"
pg_restore: processing data for table "public.kondisi_fasilitas"
pg_restore: executing SEQUENCE SET kuliner_region_gid_seq
pg_restore: processing data for table "public.login"
pg_restore: executing SEQUENCE SET masjid_sq
pg_restore: processing data for table "public.mesjid"
pg_restore: processing data for table "public.objek_wisata"
pg_restore: processing data for table "public.pointcloud_formats"
pg_restore: processing data for table "public.rm"
pg_restore: processing data for table "public.spatial_ref_sys"
pg_restore: processing data for table "public.ustad"
pg_restore: processing data for table "tiger.geocode_settings"
pg_restore: processing data for table "tiger.pagc_gaz"
pg_restore: processing data for table "tiger.pagc_lex"
pg_restore: processing data for table "tiger.pagc_rules"
pg_restore: processing data for table "topology.topology"
pg_restore: processing data for table "topology.layer"
pg_restore: creating CONSTRAINT "public.det_kegiatan_pkey"
pg_restore: creating CONSTRAINT "public.hotel_region_pkey"
pg_restore: creating CONSTRAINT "public.id"
pg_restore: creating CONSTRAINT "public.id_angkot"
pg_restore: creating CONSTRAINT "public.id_detail_angkot"
pg_restore: creating CONSTRAINT "public.id_fasilitas"
pg_restore: creating CONSTRAINT "public.id_jenis_keg"
pg_restore: creating CONSTRAINT "public.id_kategori"
pg_restore: creating CONSTRAINT "public.id_keg"
pg_restore: creating CONSTRAINT "public.id_khatib"
pg_restore: creating CONSTRAINT "public.id_kondisi"
pg_restore: creating CONSTRAINT "public.id_masjid"
pg_restore: creating CONSTRAINT "public.industri_kecil_region_pkey"
pg_restore: creating CONSTRAINT "public.kecamatan_pkey"
pg_restore: creating CONSTRAINT "public.kuliner_region_pkey"
pg_restore: creating CONSTRAINT "public.objek_wisata_pk"
pg_restore: creating CONSTRAINT "public.tgl_khatib"
pg_restore: creating CONSTRAINT "public.username"
pg_restore: creating INDEX "public.hotel_region_geom_idx"
pg_restore: creating INDEX "public.industri_kecil_region_geom_idx"
pg_restore: creating INDEX "public.kecamatan_region_geom_gist"
pg_restore: creating INDEX "public.kuliner_region_geom_idx"
pg_restore: creating INDEX "public.mesjid_region_geom_idx"
pg_restore: creating FK CONSTRAINT "public.angkot_id"
pg_restore: creating FK CONSTRAINT "public.id"
pg_restore: creating FK CONSTRAINT "public.id_fasilitas"
pg_restore: creating FK CONSTRAINT "public.id_jenis_kegiatan"
pg_restore: creating FK CONSTRAINT "public.id_kat"
pg_restore: creating FK CONSTRAINT "public.id_kategori"
pg_restore: creating FK CONSTRAINT "public.id_keg"
pg_restore: creating FK CONSTRAINT "public.id_khatib"
pg_restore: creating FK CONSTRAINT "public.id_kondisi"
pg_restore: creating FK CONSTRAINT "public.id_masjid"
pg_restore: creating FK CONSTRAINT "public.id_masjid"
pg_restore: creating FK CONSTRAINT "public.id_masjid"
pg_restore: creating FK CONSTRAINT "public.id_ustad"
pg_restore: creating FK CONSTRAINT "public.username"
pg_restore: creating ACL "public"
Here is the error
WARNING: errors ignored on restore: 2
The status is Failed with exit code is 1
How to solve that ?
Thanks
This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with:
revoke usage on schema public from public;
This is required when it’s not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).
If this REVOKE hasn’t been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE).
When a user has USAGE privilege, but lacks CREATE privilege on the schema, it’s a different error when trying to create an object: permission denied for schema public.
To check the privileges inside psql, use dn+ public.
By default (shown with extended display x for readability):
# dn+ public
List of schemas
-[ RECORD 1 ]-----+-----------------------
Name | public
Owner | postgres
Access privileges | postgres=UC/postgres
| =UC/postgres
Description | standard public schema
lack of a rolename before = means it’s for all roles (=public)
Without public USAGE privilege
Name | public
Owner | postgres
Access privileges | postgres=UC/postgres
| =C/postgres
Description | standard public schema
Without public USAGE or CREATE privileges
Name | public Owner | postgres Access privileges | postgres=UC/postgres Description | standard public schema
This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with:
revoke usage on schema public from public;
This is required when it’s not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).
If this REVOKE hasn’t been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE).
When a user has USAGE privilege, but lacks CREATE privilege on the schema, it’s a different error when trying to create an object: permission denied for schema public.
To check the privileges inside psql, use dn+ public.
By default (shown with extended display x for readability):
# dn+ public
List of schemas
-[ RECORD 1 ]-----+-----------------------
Name | public
Owner | postgres
Access privileges | postgres=UC/postgres
| =UC/postgres
Description | standard public schema
lack of a rolename before = means it’s for all roles (=public)
Without public USAGE privilege
Name | public
Owner | postgres
Access privileges | postgres=UC/postgres
| =C/postgres
Description | standard public schema
Without public USAGE or CREATE privileges
Name | public Owner | postgres Access privileges | postgres=UC/postgres Description | standard public schema