Re: db not dumping properly, or at least not restoring - Mailing list pgsql-general

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