Re: A very puzzling backup/restore problem
От | Adrian Klaver |
---|---|
Тема | Re: A very puzzling backup/restore problem |
Дата | |
Msg-id | 42316356-9409-3908-5a13-9f20c1f5b04f@aklaver.com обсуждение исходный текст |
Ответ на | Re: A very puzzling backup/restore problem (stan <stanb@panix.com>) |
Ответы |
Re: A very puzzling backup/restore problem
(stan <stanb@panix.com>)
|
Список | pgsql-general |
On 10/24/19 7:32 AM, stan wrote: > On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: >> On 10/24/19 3:52 AM, stan wrote: >>> >>> >>> I have a very confusing isse. I am trying to backup and restre a signle >>> table . >>> >>> first I dump the table. >> >> Actually you are just dumping the table data. >> >> More below. >>> >>> >>> Script started on 2019-10-24 06:29:12-0400 >>> ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump >>> >>> Then I connect to the db, and verify that things are as expected. >>> >>> ]0;stan@smokey: ~stan@smokey:~$ psql >>> psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) >>> Type "help" for help. >>> >>> [1m[local] stan@stan=[0m> \dt >>> List of relations >>> Schema | Name | Type | Owner >>> --------+--------------------------+-------+------- >>> public | biz_constants | table | stan >>> public | bom_item | table | stan >>> public | costing_unit | table | stan >>> public | customer | table | stan >>> public | earthquake | table | stan >>> public | employee | table | stan >>> public | expense_report_instance | table | stan >>> public | gl_code | table | stan >>> public | mfg | table | stan >>> public | mfg_part | table | stan >>> public | mfg_vendor_relationship | table | stan >>> public | permitted_work | table | stan >>> public | phone_number_test | table | stan >>> public | project | table | stan >>> public | project_budget_component | table | stan >>> public | project_cost_category | table | stan >>> public | rate | table | stan >>> public | salary | table | stan >>> public | task_instance | table | stan >>> public | vendor | table | stan >>> public | work_type | table | stan >>> (21 rows) >>> >>> [1m[local] stan@stan=[0m> \d task_instance >>> [?1049h[22;0;0t[?1h= Table "public.task_instance" >>> Column | Type | Collation | Nullable | Default >>> ---------------+--------------------------+-----------+----------+-------------- --------------------------------- >>> task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) >>> project_key | integer | | not null | >>> employee_key | integer | | not null | >>> work_type_key | integer | | not null | >>> hours | numeric(5,2) | | not null | >>> work_start | timestamp with time zone | | not null | >>> work_end | timestamp with time zone | | not null | >>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>> lock | boolean | | | true >>> descrip | character varying | | | >>> Indexes: >>> "task_instance_pkey" PRIMARY KEY, btree (task_instance) >>> "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) >>> Foreign-key constraints: >>> "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work >>> [?1049h[22;0;0t[?1h= Table "public.permitted_work" >>> Column | Type | Collation | Nullable | Default >>> ---------------+--------------------------+-----------+----------+-------------- ----- >>> employee_key | integer | | not null | >>> work_type_key | integer | | not null | >>> permit | boolean | | not null | false >>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>> Indexes: >>> "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) >>> Foreign-key constraints: >>> "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETE RESTRICT >>> "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETERESTRICT >>> >>> Then I delete the rows from the table. >>> >>> [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; >>> DELETE 31 >>> >>> Then I try to restore from the dump file. >>> >>> [1m[local] stan@stan=[0m> \i task_instance.dump >>> SET >>> SET >>> SET >>> SET >>> SET >>> set_config >>> ------------ >>> (1 row) >>> >>> SET >>> SET >>> SET >>> SET >>> psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist >>> LINE 3: permitted_work >>> ^ >> >> In your \d task_instance above I do not see a trigger that calls >> public.check_permission(). Does one exist or was it cut off the output you >> pasted? > > It exists. Perhaps I am using a different /dt format? I am not accustomed > to seeing the triggers when I do it. > >> >> Also look in the dump file. Given that you are using 11.5 I'm going to >> assume it is resetting the search_path and that the unqualified schema name >> of permitted_work below is your issue. > > That makes sense. If I delete all the SET lines things do work. > > Is this a known bug on version 11.5? Or am I doing something incorrectly? > BTW 11.5 is the latest package for Ubuntu, I believe. Not a bug, a security fix: https://www.postgresql.org/about/news/1834/ PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22 released! https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path All versions greater then and equal to those mentioned above have the fix. > > Should I start always specifying the schema? Seems to add confusion to me. Yes. See CVE link for why that is important. > > Thanks for the education here. > >> >>> QUERY: SELECT >>> permit FROM >>> permitted_work >>> WHERE >>> NEW.employee_key = permitted_work.employee_key >>> AND >>> NEW.work_type_key = permitted_work.work_type_key >>> CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement >>> COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." >>> >>> After that error, U cannot see any objects in the table >>> >>> [1m[local] stan@stan=[0m> \dt >>> Did not find any relations. >>> [1m[local] stan@stan=[0m> \q >>> ]0;stan@smokey: ~stan@smokey:~$ exit >>> >>> Script done on 2019-10-24 06:30:48-0400 >>> >>> quiting psql and reconecting shows that the obkects ARE there, with the >>> taks)instance table empty. >>> >>> What am I doing wrong? >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: