Re: [GENERAL] db not dumping properly, or at least not restoring
Hi Kirk, How's it going? You can use pg_dump on the local host to access a db on a remote host, as the output is just SQL, pipe this directly intp a psql command, thus replicating/migrating a database. One note, if you are doing this with a PostGIS db, I find it works better to create an empty target db with PostGIS installed first, then let the constraints on PostGIS objects prevent the old PostGIS being installed in the new db. Or you can copy over the old PostGIS use the PostGIS upgrade SQL. Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Kirk Wythers kwyth...@umn.edu 10/17/09 4:15 PM On Oct 16, 2009, at 4:51 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers kwyth...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db not dumping properly, or at least not restoring
On Fri, Oct 16, 2009 at 6:11 PM, Kirk Wythers kwyth...@umn.edu wrote: On Oct 16, 2009, at 4:51 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers kwyth...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db not dumping properly, or at least not restoring
2009/10/16 Kirk Wythers kwyth...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db not dumping properly, or at least not restoring
On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers kwyth...@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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db not dumping properly, or at least not restoring
On Oct 16, 2009, at 4:51 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers kwyth...@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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
