Обсуждение: Problem restoring Database

I have just upgraded froom Fedora test1 to test2 which has upgraded
postgres version from 7.4.2 to 7.4.5 (frrom rpm -q)

I have used pg_dumpall to dump my database, the restore appeared to go
OK, except there is no data in two of my tables (only these two)

output from \d \d tb_invoice_header
                                          Table
"public.tb_invoice_header"
       Column       |         Type
|                               Modifiers
--------------------+-----------------------+-----------------------------------------------------------------------
 inv_id             | integer               | not null default
nextval('public.tb_invoice_header_inv_id_seq'::text)
 client_id          | integer               |
 payable_recievable | character varying(15) |
 amount             | money                 |
 amount_paid        | money                 |
 date_paid          | date                  |
 cheque             | character varying(20) |
Indexes:
    "ih_pk" primary key, btree (inv_id)

 \d tb_invoice_detail
                                     Table "public.tb_invoice_detail"
   Column    |          Type          |
Modifiers
-------------+------------------------+-------------------------------------------------------------------
 id          | integer                | not null default
nextval('public.tb_invoice_detail_id_seq'::text)
 invoice_id  | integer                |
 code        | character varying(15)  |
 description | character varying(100) |
 amount      | money                  |
 vat_code    | smallint               |
 line_total  | money                  |
Indexes:
    "tb_id_pk" primary key, btree (id)
Foreign-key constraints:
    "$1" FOREIGN KEY (invoice_id) REFERENCES tb_invoice_header(inv_id)

The dump file contains all the data

Anyone have any idea what is happening, and more important how to rescue

thanks

On Thursday 01 April 2004 16:56, mike wrote:
> I have just upgraded froom Fedora test1 to test2 which has upgraded
> postgres version from 7.4.2 to 7.4.5 (frrom rpm -q)

Clever, since 7.4.5 hasn't been released yet. If that's actually what it says,
consider getting in touch with the packager, it should probably be 7.4.2-5.
Oh, between minor versions (e.g. 7.4.1 => 7.4.99) you don't need to
dump/restore, you can leave the files in-place. It's only when you go from
7.4 to 7.5 you need to dump/restore.

> I have used pg_dumpall to dump my database, the restore appeared to go
> OK, except there is no data in two of my tables (only these two)

You don't have to restore into a database, you can just redirect stdout to a
file:

pg_restore --table=tb_invoice_header mydumpfile > dump-invoice-headers.txt

You can manually import this using psql then. I'm a little puzzled as to how
this can happen without any error messages though - might you have missed
one?

--
  Richard Huxton
  Archonet Ltd

mike wrote:
> I have just upgraded froom Fedora test1 to test2 which has upgraded
> postgres version from 7.4.2 to 7.4.5 (frrom rpm -q)

Uhh unless I am missing something what you have done is impossible.
There is no 7.4.5....

Are you sure it didn't upgrade from 7.3.2 to 7.3.5? Or WORSE:

7.3.2 to 7.4.2?

If you have your dump still, you should be in fine shape. What flags
did you pass to pg_dumpall?

Sincerely,

Joshua D. Drake



>
> I have used pg_dumpall to dump my database, the restore appeared to go
> OK, except there is no data in two of my tables (only these two)
>
> output from \d \d tb_invoice_header
>                                           Table
> "public.tb_invoice_header"
>        Column       |         Type
> |                               Modifiers
> --------------------+-----------------------+-----------------------------------------------------------------------
>  inv_id             | integer               | not null default
> nextval('public.tb_invoice_header_inv_id_seq'::text)
>  client_id          | integer               |
>  payable_recievable | character varying(15) |
>  amount             | money                 |
>  amount_paid        | money                 |
>  date_paid          | date                  |
>  cheque             | character varying(20) |
> Indexes:
>     "ih_pk" primary key, btree (inv_id)
>
>  \d tb_invoice_detail
>                                      Table "public.tb_invoice_detail"
>    Column    |          Type          |
> Modifiers
> -------------+------------------------+-------------------------------------------------------------------
>  id          | integer                | not null default
> nextval('public.tb_invoice_detail_id_seq'::text)
>  invoice_id  | integer                |
>  code        | character varying(15)  |
>  description | character varying(100) |
>  amount      | money                  |
>  vat_code    | smallint               |
>  line_total  | money                  |
> Indexes:
>     "tb_id_pk" primary key, btree (id)
> Foreign-key constraints:
>     "$1" FOREIGN KEY (invoice_id) REFERENCES tb_invoice_header(inv_id)
>
> The dump file contains all the data
>
> Anyone have any idea what is happening, and more important how to rescue
>
> thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

On Thu, 2004-04-01 at 20:08, Joshua D. Drake wrote:
> mike wrote:
> > I have just upgraded froom Fedora test1 to test2 which has upgraded
> > postgres version from 7.4.2 to 7.4.5 (frrom rpm -q)
>
> Uhh unless I am missing something what you have done is impossible.
> There is no 7.4.5....
>
> Are you sure it didn't upgrade from 7.3.2 to 7.3.5? Or WORSE:
>
> 7.3.2 to 7.4.2?
>
> If you have your dump still, you should be in fine shape. What flags
> did you pass to pg_dumpall?
>
> Sincerely,
>
> Joshua D. Drake
>
>
>

even more curious is that I have just done a new dump/restore using
pg_dump (ie: not dumpall) and eveything worked fine

> >
> > I have used pg_dumpall to dump my database, the restore appeared to go
> > OK, except there is no data in two of my tables (only these two)
> >
> > output from \d \d tb_invoice_header
> >                                           Table
> > "public.tb_invoice_header"
> >        Column       |         Type
> > |                               Modifiers
> >
--------------------+-----------------------+-----------------------------------------------------------------------
> >  inv_id             | integer               | not null default
> > nextval('public.tb_invoice_header_inv_id_seq'::text)
> >  client_id          | integer               |
> >  payable_recievable | character varying(15) |
> >  amount             | money                 |
> >  amount_paid        | money                 |
> >  date_paid          | date                  |
> >  cheque             | character varying(20) |
> > Indexes:
> >     "ih_pk" primary key, btree (inv_id)
> >
> >  \d tb_invoice_detail
> >                                      Table "public.tb_invoice_detail"
> >    Column    |          Type          |
> > Modifiers
> > -------------+------------------------+-------------------------------------------------------------------
> >  id          | integer                | not null default
> > nextval('public.tb_invoice_detail_id_seq'::text)
> >  invoice_id  | integer                |
> >  code        | character varying(15)  |
> >  description | character varying(100) |
> >  amount      | money                  |
> >  vat_code    | smallint               |
> >  line_total  | money                  |
> > Indexes:
> >     "tb_id_pk" primary key, btree (id)
> > Foreign-key constraints:
> >     "$1" FOREIGN KEY (invoice_id) REFERENCES tb_invoice_header(inv_id)
> >
> > The dump file contains all the data
> >
> > Anyone have any idea what is happening, and more important how to rescue
> >
> > thanks
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>