回复： BUG #16101: tables in the DB is not available after pg_restore

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=#

------------------ 原始邮件 ------------------
发件人:&nbsp;"Tomas Vondra"<tomas(dot)vondra(at)2ndquadrant(dot)com&gt;;
发送时间:&nbsp;2019年11月8日(星期五) 晚上7:13
收件人:&nbsp;"唯一★"<270246512(at)qq(dot)com&gt;;"pgsql-bugs"<pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;;

主题:&nbsp;Re: BUG #16101: tables in the DB is not available after pg_restore

On Fri, Nov 08, 2019 at 07:37:24AM +0000, PG Bug reporting form wrote:
&gt;The following bug has been logged on the website:
&gt;
&gt;Bug reference:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16101
&gt;Logged by:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; zeng
&gt;Email address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 270246512(at)qq(dot)com
&gt;PostgreSQL version: 10.10
&gt;Operating system:&nbsp;&nbsp; suse
&gt;Description:
&gt;
&gt;hi,
&gt;I have one question about pg_restore tool, tables in the DB is not available
&gt;after restore. Even if the user is still the owner of table after restore.
&gt;Steps:
&gt;1.&nbsp;&nbsp;&nbsp; Create one database and the owner is user1. And create table and pop
&gt;the data with user1
&gt;2.&nbsp;&nbsp;&nbsp; Use pg_dump tool to backup one database with superuser postgres.
&gt;3.&nbsp;&nbsp;&nbsp; Use pg_restore tool to restore the database with the parameter –clean
&gt;and supper user.
&gt;4.&nbsp;&nbsp;&nbsp; Connect the db with user1 and found the user does not select table as
&gt;below.
&gt;&nbsp; cdb=&gt; \dt
&gt;Did not find any relations.
&gt;

A wild guess - the objects are in a schema that is not listed in
search_path.

If that's not the case, you'll have to give us more details, and perhaps
a reproducer so that we can look more closely.

regards

--
Tomas Vondra&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training &amp; Services