Re: A very puzzling backup/restore problem

On Thu, Oct 24, 2019 at 07:57:14AM -0500, Ron wrote:
> Why did you do "\i task_instance.dump" instead of "pg_restore"?

Ah, that may be the root of my problem. I had in the back of my mind that
the result  of a pg_dump was a free standing SQL script, but that my be
exactly what is going on. I will try to use pg_restore, and make certain
that resolves my issue.

In the meantime, I found that if I deleted all the SET lines from the dump
I could restore with psql. But that is probably not the right way to do
this.

>
> On 10/24/19 5: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.
> >
> >
> > 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
DELETERESTRICT 
> >
> > 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?
>
> --
> Angular momentum makes the world go 'round.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin