pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: 唯一★ <270246512@qq.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: 回复: BUG #16101: tables in the DB is not available after pg_restore
Date: Wed, 13 Nov 2019 15:02:14 +0800
Message-ID: <tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com> (raw)
In-Reply-To: <20191108111339.xlopjkbwfhxfvwt3@development>
References: <16101-5a569e5aac6aa84e@postgresql.org>
	<20191108111339.xlopjkbwfhxfvwt3@development>

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







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

主题: 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:
>The following bug has been logged on the website:
>
>Bug reference:      16101
>Logged by:          zeng
>Email address:      270246512@qq.com
>PostgreSQL version: 10.10
>Operating system:   suse
>Description:
>
>hi,
>I have one question about pg_restore tool, tables in the DB is not available
>after restore. Even if the user is still the owner of table after restore.
>Steps:
>1.    Create one database and the owner is user1. And create table and pop
>the data with user1
>2.    Use pg_dump tool to backup one database with superuser postgres.
>3.    Use pg_restore tool to restore the database with the parameter �Cclean
>and supper user.
>4.    Connect the db with user1 and found the user does not select table as
>below.
>  cdb=> \dt
>Did not find any relations.
>

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                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

view thread (2+ messages)  latest in thread

Message-ID: <tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com>
Permalink:  ../tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com/
Also on:    postgresql.org/message-id/tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: 270246512@qq.com, tomas.vondra@2ndquadrant.com
  Subject: Re: 回复: BUG #16101: tables in the DB is not available after pg_restore
  In-Reply-To: <tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com>
  References: <16101-5a569e5aac6aa84e@postgresql.org> <20191108111339.xlopjkbwfhxfvwt3@development> <tencent_5865E10D689BCC05DFD0BC291ED869BEAA05@qq.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox