回复： 回复： 回复： 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

------------------ 原始邮件 ------------------
发件人:&nbsp;"Tomas Vondra"<tomas(dot)vondra(at)2ndquadrant(dot)com&gt;;
发送时间:&nbsp;2019年11月13日(星期三) 晚上11:18
收件人:&nbsp;"唯一★"<270246512(at)qq(dot)com&gt;;
抄送:&nbsp;"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 Wed, Nov 13, 2019 at 10:43:20PM +0800, 唯一★ wrote:
&gt;hi,&amp;nbsp;
&gt;
&gt;
&gt;The restore command is "pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1"
&gt;Here are reproduce steps.
&gt;1. login in the postgres DB with super user postgres and create DB
&gt;&amp;nbsp;CREATE DATABASE cmdb OWNER cm WITH TEMPLATE = template0;
&gt;&amp;nbsp;GRANT ALL PRIVILEGES ON DATABASE cmdb to cm;
&gt;
&gt;
&gt;
&gt;2.pop the data to DB.
&gt;&amp;nbsp;pgbench -i -h 127.0.01 -U cm -s 20 -d cmdb
&gt;
&gt;
&gt;3.use the pg_dump to backup the data.
&gt;&amp;nbsp;pg_dump -h 127.0.0.1 -U postgres -d cmdb -p 5432 -Fc -f bk_1
&gt;4.take the restore via pg_restore.
&gt;&amp;nbsp;pg_restore -h 127.0.0.1 -U postgres -d cmdb bk_1 -c -v
&gt;

Works just fine for me:

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

&nbsp;&nbsp;&nbsp;&nbsp; cmdb2=# \d
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List of relations
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Schema |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type&nbsp; | Owner
&nbsp;&nbsp;&nbsp;&nbsp; --------+------------------+-------+-------
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public | pgbench_accounts | table | user
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public | pgbench_branches | table | user
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public | pgbench_history&nbsp; | table | user
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public | pgbench_tellers&nbsp; | table | user
&nbsp;&nbsp;&nbsp;&nbsp; (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&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