回复： BUG #16101: tables in the DB is not available after pg_restore - Mailing list pgsql-bugs

Hi,

After restore the database via pg_restore,  the search_path look well and is the same as before restore.

Could you help check the issue?

Thanks

Zeng

Restore command and log:

pg_restore -h 127.0.0.1 -U postgres -d cmdb bk_1 -c -v

pg_restore: connecting to database for restore

pg_restore: dropping TRIGGER employees last_name_changes

pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey

pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey

pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 3398; 2606 47961 CONSTRAINT pgbench_accounts pgbench_accounts_pkey cm

pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.pgbench_accounts" does not exist

    Command was: ALTER TABLE ONLY public.pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey;

pg_restore: dropping CONSTRAINT employee_audits employee_audits_pkey

pg_restore: dropping SEQUENCE SET employee_audits_id_seq

pg_restore: dropping TABLE DATA pgbench_tellers

pg_restore: dropping TABLE DATA pgbench_history

pg_restore: dropping TABLE DATA pgbench_branches

pg_restore: dropping TABLE DATA pgbench_accounts

pg_restore: dropping TABLE DATA employees

pg_restore: dropping TABLE DATA employee_audits

pg_restore: dropping DEFAULT employee_audits id

pg_restore: dropping TABLE pgbench_tellers

pg_restore: dropping TABLE pgbench_history

pg_restore: dropping TABLE pgbench_branches

pg_restore: dropping TABLE pgbench_accounts

pg_restore: [archiver (db)] Error from TOC entry 199; 1259 47945 TABLE pgbench_accounts cm

pg_restore: [archiver (db)] could not execute query: ERROR:  table "pgbench_accounts" does not exist

    Command was: DROP TABLE public.pgbench_accounts;

pg_restore: dropping TABLE employees

pg_restore: dropping SEQUENCE OWNED BY employee_audits_id_seq

pg_restore: dropping SEQUENCE employee_audits_id_seq

pg_restore: dropping TABLE employee_audits

pg_restore: dropping FUNCTION log_last_name_changes()

pg_restore: dropping COMMENT EXTENSION plpgsql

pg_restore: dropping EXTENSION plpgsql

pg_restore: dropping COMMENT SCHEMA public

pg_restore: dropping SCHEMA public

pg_restore: creating SCHEMA "public"

pg_restore: creating COMMENT "SCHEMA public"

pg_restore: creating EXTENSION "plpgsql"

pg_restore: creating COMMENT "EXTENSION plpgsql"

pg_restore: creating FUNCTION "public.log_last_name_changes()"

pg_restore: creating TABLE "public.employee_audits"

pg_restore: creating SEQUENCE "public.employee_audits_id_seq"

pg_restore: creating SEQUENCE OWNED BY "public.employee_audits_id_seq"

pg_restore: creating TABLE "public.employees"

pg_restore: creating TABLE "public.pgbench_accounts"

pg_restore: creating TABLE "public.pgbench_branches"

pg_restore: creating TABLE "public.pgbench_history"

pg_restore: creating TABLE "public.pgbench_tellers"

pg_restore: creating DEFAULT "public.employee_audits id"

pg_restore: processing data for table "public.employee_audits"

pg_restore: processing data for table "public.employees"

pg_restore: processing data for table "public.pgbench_accounts"

pg_restore: processing data for table "public.pgbench_branches"

pg_restore: processing data for table "public.pgbench_history"

pg_restore: processing data for table "public.pgbench_tellers"

pg_restore: executing SEQUENCE SET employee_audits_id_seq

pg_restore: creating CONSTRAINT "public.employee_audits employee_audits_pkey"

pg_restore: creating CONSTRAINT "public.pgbench_accounts pgbench_accounts_pkey"

pg_restore: creating CONSTRAINT "public.pgbench_branches pgbench_branches_pkey"

pg_restore: creating CONSTRAINT "public.pgbench_tellers pgbench_tellers_pkey"

pg_restore: creating TRIGGER "public.employees last_name_changes"

WARNING: errors ignored on restore: 2

Check log:

sun:/backup1 # psql -h 127.0.0.1 -U cm -d cmdb             psql (10.9)

Type "help" for help.

cmdb=>

cmdb=>

cmdb=> show search_path;

   search_path

-----------------

 "$user", public

(1 row)

cmdb=>

cmdb=> \dt

Did not find any relations.

cmdb=>

cmdb=> \dt

Did not find any relations.

cmdb=>

cmdb=>\q

sun:/backup1 #

sun:/backup1 #

sun:/backup1 # psql -h 127.0.0.1 -U postgres -d cmdb

psql (10.9)

Type "help" for help.

cmdb=# \dt

             List of relations

 Schema |       Name       | Type  | Owner

--------+------------------+-------+-------

 public | employee_audits  | table | cm

 public | employees        | table | cm

 public | pgbench_accounts | table | cm

 public | pgbench_branches | table | cm

 public | pgbench_history  | table | cm

 public | pgbench_tellers  | table | cm

(6 rows)

cmdb=# show search_path;

   search_path

-----------------

 "$user", public

(1 row)

cmdb=#