pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore
3+ messages / 2 participants
[nested] [flat]

* 回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore
@ 2019-11-14 02:05  唯一★ <270246512@qq.com>
  0 siblings, 1 reply; 3+ messages in thread

From: 唯一★ @ 2019-11-14 02:05 UTC (permalink / raw)
  To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi,
 
 
 
Thanks for your support.

I saw you take the restore on new DB "cmdb2" and not original DB "cmdb" when taking the backup via pg_dump. My reproduce is use the same DB.
 
Furthermore, your commands does not specified the username, it will use the same username with environment. it does not reproduce it if use the same users.  
 
My reproduce is use supper user to take backup and restore. And connect to database with DB owner “cmto” check the tables.
 
I am sure that it takes the pg_dump and pg_restore on the same system and DB.
 
Could you reproduce it with the same steps?
 
 
 
Thanks
 
Zeng



------------------ 原始邮件 ------------------
发件人: "Tomas Vondra"<tomas.vondra@2ndquadrant.com>;
发送时间: 2019年11月13日(星期三) 晚上11:18
收件人: "唯一★"<270246512@qq.com>;
抄送: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
主题: Re:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore



On Wed, Nov 13, 2019 at 10:43:20PM +0800, 唯一★ wrote:
>hi,&nbsp;
>
>
>The restore command is "pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1"
>Here are reproduce steps.
>1. login in the postgres DB with super user postgres and create DB
>&nbsp;CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
>&nbsp;GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
>
>
>
>2.pop the data to DB.
>&nbsp;pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
>
>
>3.use the pg_dump to backup the data.
>&nbsp;pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1
>4.take the restore via pg_restore.
>&nbsp;pg_restore -h 127.0.0.1 -U postgres -d cmdb bk_1 -c -v
>

Works just fine for me:

     [user@development ~]$ pg_ctl -D tmp/data init
     ...
     [user@development ~]$ pg_ctl -D tmp/data -l pg.log start
     waiting for server to start.... done
     server started
     [user@development ~]$ createdb cmdb
     [user@development ~]$ pgbench -i -s 1 cmdb
     dropping old tables...
     NOTICE:  table "pgbench_accounts" does not exist, skipping
     NOTICE:  table "pgbench_branches" does not exist, skipping
     NOTICE:  table "pgbench_history" does not exist, skipping
     NOTICE:  table "pgbench_tellers" does not exist, skipping
     creating tables...
     generating data...
     100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s)
     vacuuming...
     creating primary keys...
     done.
     [user@development ~]$ pg_dump -d cmdb -p 5432 -Fc -f bk_1
     [user@development ~]$ pg_restore -d cmdb2 bk_1 -c -v
     pg_restore: connecting to database for restore
     ...
     pg_restore: warning: errors ignored on restore: 7
     [user@development ~]$ psql cmdb2
     psql (12.1)
     Type "help" for help.

     cmdb2=# \d
                  List of relations
      Schema |       Name       | Type  | Owner
     --------+------------------+-------+-------
      public | pgbench_accounts | table | user
      public | pgbench_branches | table | user
      public | pgbench_history  | table | user
      public | pgbench_tellers  | table | user
     (4 rows)

Are you sure you're running the pg_restore on the right machine and not
on the same system where you did the pg_dump? Don't you have some
per-user search_path setting applied using ALTER USER?

regards

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* 回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore
@ 2019-11-17 14:40  唯一★ <270246512@qq.com>
  parent: 唯一★ <270246512@qq.com>
  0 siblings, 1 reply; 3+ messages in thread

From: 唯一★ @ 2019-11-17 14:40 UTC (permalink / raw)
  To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi, 


Do you have update for this issue?


Thanks
Zeng




------------------ 原始邮件 ------------------
发件人: "唯一★"<270246512@qq.com>;
发送时间: 2019年11月14日(星期四) 上午10:05
收件人: "Tomas Vondra"<tomas.vondra@2ndquadrant.com>;
抄送: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
主题: 回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore




Hi,
 
 
 
Thanks for your support.

I saw you take the restore on new DB "cmdb2" and not original DB "cmdb" when taking the backup via pg_dump. My reproduce is use the same DB.
 
Furthermore, your commands does not specified the username, it will use the same username with environment. it does not reproduce it if use the same users.  
 
My reproduce is use supper user to take backup and restore. And connect to database with DB owner “cmto” check the tables.
 
I am sure that it takes the pg_dump and pg_restore on the same system and DB.
 
Could you reproduce it with the same steps?
 
 
 
Thanks
 
Zeng



------------------ 原始邮件 ------------------
发件人: "Tomas Vondra"<tomas.vondra@2ndquadrant.com>;
发送时间: 2019年11月13日(星期三) 晚上11:18
收件人: "唯一★"<270246512@qq.com>;
抄送: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
主题: Re:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore



On Wed, Nov 13, 2019 at 10:43:20PM +0800, 唯一★ wrote:
>hi,&nbsp;
>
>
>The restore command is "pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1"
>Here are reproduce steps.
>1. login in the postgres DB with super user postgres and create DB
>&nbsp;CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
>&nbsp;GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
>
>
>
>2.pop the data to DB.
>&nbsp;pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
>
>
>3.use the pg_dump to backup the data.
>&nbsp;pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1
>4.take the restore via pg_restore.
>&nbsp;pg_restore -h 127.0.0.1 -U postgres -d cmdb bk_1 -c -v
>

Works just fine for me:

     [user@development ~]$ pg_ctl -D tmp/data init
     ...
     [user@development ~]$ pg_ctl -D tmp/data -l pg.log start
     waiting for server to start.... done
     server started
     [user@development ~]$ createdb cmdb
     [user@development ~]$ pgbench -i -s 1 cmdb
     dropping old tables...
     NOTICE:  table "pgbench_accounts" does not exist, skipping
     NOTICE:  table "pgbench_branches" does not exist, skipping
     NOTICE:  table "pgbench_history" does not exist, skipping
     NOTICE:  table "pgbench_tellers" does not exist, skipping
     creating tables...
     generating data...
     100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s)
     vacuuming...
     creating primary keys...
     done.
     [user@development ~]$ pg_dump -d cmdb -p 5432 -Fc -f bk_1
     [user@development ~]$ pg_restore -d cmdb2 bk_1 -c -v
     pg_restore: connecting to database for restore
     ...
     pg_restore: warning: errors ignored on restore: 7
     [user@development ~]$ psql cmdb2
     psql (12.1)
     Type "help" for help.

     cmdb2=# \d
                  List of relations
      Schema |       Name       | Type  | Owner
     --------+------------------+-------+-------
      public | pgbench_accounts | table | user
      public | pgbench_branches | table | user
      public | pgbench_history  | table | user
      public | pgbench_tellers  | table | user
     (4 rows)

Are you sure you're running the pg_restore on the right machine and not
on the same system where you did the pg_dump? Don't you have some
per-user search_path setting applied using ALTER USER?

regards

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: 回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore
@ 2019-11-17 17:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 唯一★ <270246512@qq.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2019-11-17 17:00 UTC (permalink / raw)
  To: 唯一★ <270246512@qq.com>; +Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

"=?gb18030?B?zqjSu6Hv?=" <270246512@qq.com> writes:
> Do you have update for this issue?

You've done nothing to convince anyone that this isn't local
misconfiguration or process error on your part.

In particular, I still like the theory I offered in

https://www.postgresql.org/message-id/5802.1573657223%40sss.pgh.pa.us

that the permissions on the public schema don't allow your
non-superuser role to access anything in that schema.

Looking closer at the "pg_restore -v" trace you posted in

https://www.postgresql.org/message-id/tencent_5865E10D689BCC05DFD0BC291ED869BEAA05%40qq.com

bolsters this theory, because I see

pg_restore: dropping COMMENT SCHEMA public
pg_restore: dropping SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"

but there's never any later

pg_restore: creating ACL "SCHEMA public"

which there ought to be, and there is when I try to reproduce this.
That means the public schema is ending up with default permissions,
which grant no access to anyone but the owner.

Perhaps this happened because you did the dump or the restore
with -x (--no-privileges).  Or possibly that schema's privileges
were manually modified at some earlier point.

In any case, it's fairly hard to believe that you're giving us
a completely accurate statement of facts, because the restore
trace also includes errors like

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;

It seems very unlikely that you could have gotten that if you
were restoring a dump you'd just created from the same database.
So there are additional moving parts here that you have not
mentioned.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2019-11-17 17:00 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-14 02:05 回复:  回复:  回复: BUG #16101: tables in the DB is not available after pg_restore 唯一★ <270246512@qq.com>
2019-11-17 14:40 ` 唯一★ <270246512@qq.com>
2019-11-17 17:00   ` Tom Lane <tgl@sss.pgh.pa.us>

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