Problem restoring Database

[mike]

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'::tex t)
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

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Joshua D. Drake]

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'::tex t)
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.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[mike]

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'::tex t)
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html