A very puzzling backup/restore problem

Поиск
Список
Период
Сортировка
От stan
Тема A very puzzling backup/restore problem
Дата
Msg-id 20191024105227.GA13265@panix.com
обсуждение исходный текст
Ответы Re: A very puzzling backup/restore problem  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general

I have a very confusing isse. I am trying to backup and restre a signle
table .

first I dump the table.


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.

[local] stan@stan=> \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)

[local] stan@stan=> \d task_instance
[?1049h[?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
:[?1l>[?1049l[local]stan@stan=> \d permitted_work 
[?1049h[?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 DELETE
RESTRICT

Then I delete the rows from the table.

(END)[?1l>[?1049l[local] stan@stan=> delete from task_instance ;
DELETE 31

Then I try to restore from the dump file.

[local] stan@stan=> \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
                        ^
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

[local] stan@stan=> \dt
Did not find any relations.
[local] stan@stan=> \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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin



В списке pgsql-general по дате отправления:

Предыдущее
От: Laiszner Tamás
Дата:
Сообщение: Re: Composite type storage overhead
Следующее
От: Vikas Sharma
Дата:
Сообщение: PGPool version 4.0.6-1