Re: database files - Mailing list pgsql-general

On 22/10/2003 20:41 Gail Zacharias wrote:
> 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 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.
>
> 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?

AFAIK, each database has its own directory and each table or index has its
own file but that won't help you much as they're given numeric names names
on disk (I thinks they use the objects OID) and I doubt that anyone
outside of the core developers would have the knowledge to find out which
files to copy, copy them and then manually edit the system catalogs on the
target machine so that the data can be read. Plus of course, on a *nix
machine, the data and directories are accessible only to the postgres user!

The correct way to do this is using pg_dump which can dump either a whole
database or a single file and then restore onto the other machine using
either psql or pg_restore (which one you use depends on the options you
supply to pg_dump). pg_dump runs inside a transaction which ensures a
consistent view of the dumped data.
>
> 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?

The only issue there _might_ be is the newline character if you dump to
ascii files but you can simply run unix2dos on the dump file in that case.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+