db not dumping properly, or at least not restoring

2009/10/16 Kirk Wythers <kwythers@umn.edu>:

I am trying to move databases to another macine (and update from 8.2 to 8.4
along the way). I first tried pg_dumpall, but I found that one of the data
bases did not restore and data, just an empty db with no tables. Since then
I have tried pg_dump with the following:

bash-3.2$ /usr/local/pgsql/bin/pg_dump -o mn_timber >
/Volumes/disk3/backup_db/mn_timber20091016.out

then restore on the new machine with:

kwythers$ pg_restore -C -d postgres mn_timber20091016.out

But I am getting the error:

pg_restore: [archiver] input file does not appear to be a valid archive
onceler:~ kwythers$

Looking at the db on the original machine, all looks good.

mn_timber=# \d
                  List of relations
 Schema |          Name          |   Type   |  Owner
--------+------------------------+----------+----------
 public | all_timber_data        | view     | kwythers
 public | county                 | table    | kwythers
 public | forties                | table    | kwythers
 public | geometry_columns       | table    | kwythers
 public | grid_cell              | view     | kwythers
 public | mn_pls_grid            | table    | kwythers
 public | mn_pls_grid_gid_seq    | sequence | kwythers
 public | rdir                   | table    | kwythers
 public | session                | table    | kwythers
 public | session_session_id_seq | sequence | kwythers
 public | spatial_ref_sys        | table    | kwythers
 public | timber_type            | table    | kwythers
 public | timber_volume          | table    | kwythers
 public | timber_volume_seq      | sequence | kwythers
(14 rows)

mn_timber=# SELECT * FROM timber_volume;
 grid_id | tt_id | year | cords | mbm | poles | posts | tv_id
---------+-------+------+-------+-----+-------+-------+-------
 263515 |    17 | 1920 |    11 |   2 |       |       | 10176
 266999 |     6 | 1920 |     7 |  19 |       |       | 10869
 1141653 |     5 | 1920 |    10 |   1 |       |       | 10238
 1143744 |     5 | 1920 |     2 |   1 |       |       | 10293
 263560 |     9 | 1920 |       |   5 |       |       | 10346
 264027 |     3 | 1920 |    49 |   1 |       |       | 10391
 264180 |     9 | 1920 |    70 |   5 |       |       | 10430
 263728 |     4 | 1920 |       |     |   919 |  1495 | 10468
 263667 |    17 | 1920 |     1 |     |       |       | 10501
 263658 |    17 | 1920 |    15 |     |       |       | 10528
 263984 |     3 | 1920 |    98 |     |       |       | 10554
 264289 |    17 | 1920 |     1 |     |       |       | 10579
 263973 |     4 | 1920 |       |     |    40 |    40 | 10601
.
.
.
(38437 rows)

Any ideas what the problem could be here?

Thanks in advance.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I believe pg_dump, by default, outputs in plain format. pg_restore
only accepts tar and custom, so you should be able to just pass the
file to psql, or back it up again with a different format.

As the documentation states: "pg_restore is a utility for restoring a
PostgreSQL database from an archive created by pg_dump in one of the
non-plain-text formats."

Thom

On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers <kwythers@umn.edu> wrote:

Any ideas what the problem could be here?

Use the pg_dump from the target (i.e. newer) pgsql. I.e. if going
from 8.3.8 to 8.4.1, use the pg_dump that comes with 8.4.1 to dump the
8.3.8 database.

I usually just do it like so:

(First migrate accounts:)
pg_dumpall --globals -h oldserver | psql -h newserver postgres
(then each database:)
createdb -h newserver dbname
pg_dump -h oldserver dbname | psql -h newserver dbname
(repeat as needed, save output for error messages)

On Oct 16, 2009, at 4:51 PM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers <kwythers@umn.edu>
wrote:

Any ideas what the problem could be here?

Use the pg_dump from the target (i.e. newer) pgsql. I.e. if going
from 8.3.8 to 8.4.1, use the pg_dump that comes with 8.4.1 to dump the
8.3.8 database.

Can I assume that this is even more critical if gong from 8.2 to 8.4?

I usually just do it like so:

(First migrate accounts:)
pg_dumpall --globals -h oldserver | psql -h newserver postgres

I'm a little confused here. Are you saying to used the network
connections between thetwo servers and to pipe the dumpall directly to
the psql load?

(then each database:)
createdb -h newserver dbname

Then create new databases on the the new server to match the. The
names from the old server?

pg_dump -h oldserver dbname | psql -h newserver dbname
(repeat as needed, save output for error messages)

Then dump each database individually and pipe the dump to the psql load?

These two procedures seem to duplicate the goal? Or am I mosaic
something?

On Fri, Oct 16, 2009 at 6:11 PM, Kirk Wythers <kwythers@umn.edu> wrote:

On Oct 16, 2009, at 4:51 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers <kwythers@umn.edu> wrote:

Any ideas what the problem could be here?

Use the pg_dump from the target (i.e. newer) pgsql.  I.e. if going
from 8.3.8 to 8.4.1, use the pg_dump that comes with 8.4.1 to dump the
8.3.8 database.

Can I assume that this is even more critical if gong from 8.2 to 8.4?

About the same really. Always use the newer / target pg version pg_dump

I usually just do it like so:

(First migrate accounts:)
pg_dumpall --globals -h oldserver | psql -h newserver postgres

I'm a little confused here. Are you saying to used the network connections
between thetwo servers and to pipe the dumpall directly to the psql load?

Yes. Note that I'm using pg_dumpall to get JUST the accounts (i.e. --globals)

(then each database:)
createdb -h newserver dbname

Then create new databases on the the new server to match the. The names from
the old server?

Yep.

pg_dump -h oldserver dbname | psql -h newserver dbname
(repeat as needed, save output for error messages)

Then dump each database individually and pipe the dump to the psql load?

Yep.

These two procedures seem to duplicate the goal? Or am I mosaic something?

No, dumpall --globals is just to migrate the accounts over first.