Re: database files

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

Yes, multiple, yes up to a point, no, no.

A possible mechanism would be:
- suppose you want to copy data from server A to server B
- server A is running
- server B is stopped
- checkpoint server A
- rsync the files from server A to server B
- stop the postmaster at A
- rsync again (should not take much time)
- start both postmasters

Note that between both rsyncs the data in server B is not usable (i.e.
it is corrupt). You _have_ to do the last rsync with A's postmaster
stopped to make sure the files are right.

Note that you have to copy the whole PGDATA, including pg_clog and
pg_xlog. This means server B cannot have anything beyond what is on
server A.

You should probably discard the pg_dump route and erServer before trying
to do this ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Major Fambrough: You wish to see the frontier?
John Dunbar: Yes sir, before it's gone.