A very puzzling backup/restore problem

From: stan <stanb(at)panix(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: A very puzzling backup/restore problem
Date: 2019-10-24 10:52:27
Message-ID: 20191024105227.GA13265@panix.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)smokey: ~stan(at)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(at)smokey: ~stan(at)smokey:~$ psql
psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
Type "help" for help.

[local] stan(at)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(at)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(at)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(at)stan=> delete from task_instance ;
DELETE 31

Then I try to restore from the dump file.

[local] stan(at)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-24 06:28:44.502699-04 t Drawings..."

After that error, U cannot see any objects in the table

[local] stan(at)stan=> \dt
Did not find any relations.
[local] stan(at)stan=> \q
]0;stan(at)smokey: ~stan(at)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vikas Sharma 2019-10-24 13:10:30 PGPool version 4.0.6-1
Previous Message Rashmi V Bharadwaj 2019-10-24 09:15:32 Failed to increase the restart LSN of PostgreSQL logical replication slot