Re: database files

From: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
To: Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)uk>
Cc: Gail Zacharias <gz(at)clozure(dot)com>, "pgsql-general (at) postgresql (dot) org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: database files
Date: 2003-10-23 13:34:50
Message-ID: 20031023133450.GE24085@dcc.uchile.cl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-10-23 13:44:28 Re: Trigger
Previous Message Tom Lane 2003-10-23 13:30:19 Re: Trigger