Re: database files

Gail Zacharias <gz(at)clozure(dot)com> writes:

> I am investigating the possibility of using pgsql as the database in
> an application. I have some unusual requirements that I'd like to
> ask you all about. I apologize in advance if my terminology is a
> little "off", I'm not familiar with pgsql (yet).

I think your terminology is fine.

> 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.

The way to do this with PostgreSQL is to make a backup of the database
and then load it into the other machine. For example on the master
database you would do:

pg_dumpall --clean --verbose > backup.sql

You would then put that backup.sql file on your zip disk or whatever
and carry it to your new machine where you would do something like:

psql -U postgres template1 -f backup.sql

> Is this sort of thing possible? Is a database stored in a single
> file or multiple files? Can the location of the file(s) be
> controlled? Are the files accessible and consistent while pgsql is
> running? I assume not all the time, but is there a reliable way to
> make them accessible (i.e. copyable) and consistent short of
> shutting down pgsql?

Databases are stored in multiple files in a directory plus the log
files and whatnot are stored in another part of the directory
structure. It is theoretically possible to shutdown your postmaster
and then copy the files to another location, but I wouldn't recommend
it. pg_dumpall works well, and it is far more fullproof.

> Is the file format of the pgsql database files compatible between
> OS's? E.g. could I take some database files from Linux and use them
> on Windows?

I don't know the answer to that, but I would be interested in finding
out. My theory is that file formats and other arcana are far better
left to Tom Lane and the rest of the PostgreSQL hackers. This is
especially true considering the fact that on disk formats change
between versions.

Did I mention that pg_dumpall will solve your problem handily?

> Thanks in advance for any advice,

Jason