Thread: db not dumping properly, or at least not restoring
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.
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.