Re: database files

On Wed, 22 Oct 2003, Gail Zacharias wrote:

> I need to be able to move the database files, as normal user-visible
> files, between machines. I.e. given a database on machine A, I want to
> be able to copy either a single file (ideally) or a single directory
> (less ideal but still ok) to, say, a zip drive, bring it over to another
> machine (with pgsql also installed), start up my application and have it
> access the copied database through pgsql.

While you think this is the preferred method, for postgresql is most
certainly is not. what you need to do is read up a bit on pg_dump and how
to use it to accomplish your goals. For instance, suppose I have two
machines, A and B, and I want to copy the table accounts from the test
database on A to B. Assuming that the test database exists, but the table
accounts doesn't, I can do this (Note these are all command line
programs, not psql):

pg_dump -h A test -t accounts |psql -h B test

Or, if I want to move a whole single database over:

createdb -h B dbname
pg_dump -h A dbname |psql -h B dbname

(This assumes the database dbname didn't exist.)

or, the biggie, assuming B is a freshly initdb'd database, and I want to
move ALL the databases from A to B:

pg_dumpall -h A|psql -h B

Moving individual database files around is a certifiably Bad idea.