Обсуждение: change location of postmaster.pid file?

Hi.  We are looking into the possibility of running a Postgres
server on an underutilized machine.  This machine has very
little local disk space, so we would have to create the data
directory on a shared file system.

The underutilized machine was set up so that it can *only
read* from the shared file system, i.e., cannot write to the
shared file system.

Is it possible to create a database cluster on a machine that
has write access to the shared file system, shut down the
Postgres server on that machine, and then start up the
Postgres server on the machine that cannot write to the
shared file system, and thereafter, *only query* the database.

Since Postgres writes the postmaster.pid file to the data
directory (which would be on the shared file system), the
answer would appear to be no, since the 'underutilized'
machine cannot write any files to the shared file system.

Would it be possible to write the postmaster.pid file to the
local file system on the 'underutilized' machine even though
the data directory is on the shared file system?

I realize that this seems like a bad idea - given that the
purpose of the postmaster.pid file as I understand it is to
prevent more than one postmaster running in a data
directory - but I wanted to ask whether this is a possibility.

Even if it were possible to write the postmaster.pid to the
local file system on the 'underutilized' machine, does Postgres
write other temporary files even if only SELECT statements
are being executed against the database?  And where does it
write those files - in subdirectories of the data directory?

Thank you,
Janet

Janet Jacobsen <jsjacobsen@lbl.gov> writes:
> Is it possible to create a database cluster on a machine that
> has write access to the shared file system, shut down the
> Postgres server on that machine, and then start up the
> Postgres server on the machine that cannot write to the
> shared file system, and thereafter, *only query* the database.

No.  The pid file is only the first and smallest problem you'd run into
with a read-only database filesystem.

            regards, tom lane

On Fri, Jul 10, 2009 at 11:17 PM, Janet Jacobsen<jsjacobsen@lbl.gov> wrote:
> Is it possible to create a database cluster on a machine that
> has write access to the shared file system, shut down the
> Postgres server on that machine, and then start up the
> Postgres server on the machine that cannot write to the
> shared file system, and thereafter, *only query* the database.

Postgres isn't really designed to work this way. It expects to have
write access and will occasionally still write stuff to disk even for
read-only queries.

It won't work even a little bit before 8.3. For 8.3 or later you could
maybe make it work using vacuum freeze but there's no facility to
verify that it's really frozen everything and you'll still be taken by
surprise by queries which try to use temporary space for large sorts
or commands which start transactions that you didn't realize were
necessary.

--
greg
http://mit.edu/~gsstark/resume.pdf

Greg Stark wrote:
> It won't work even a little bit before 8.3. For 8.3 or later you could
> maybe make it work using vacuum freeze but there's no facility to
> verify that it's really frozen everything and you'll still be taken by
> surprise by queries which try to use temporary space for large sorts
> or commands which start transactions that you didn't realize were
> necessary.
>

or pg_stats or ......

Hi.  Thanks for the quick and definitive answers to my questions.
The information you provided will save me from wasting time and
energy trying to see how far I could get otherwise.  Thanks very much.

Janet


Tom Lane wrote:
> Janet Jacobsen <jsjacobsen@lbl.gov> writes:
>
>> Is it possible to create a database cluster on a machine that
>> has write access to the shared file system, shut down the
>> Postgres server on that machine, and then start up the
>> Postgres server on the machine that cannot write to the
>> shared file system, and thereafter, *only query* the database.
>>
>
> No.  The pid file is only the first and smallest problem you'd run into
> with a read-only database filesystem.
>
>             regards, tom lane
>
>
> On Fri, Jul 10, 2009 at 11:17 PM, Janet Jacobsen<jsjacobsen@lbl.gov> wrote:
>
>> > Is it possible to create a database cluster on a machine that
>> > has write access to the shared file system, shut down the
>> > Postgres server on that machine, and then start up the
>> > Postgres server on the machine that cannot write to the
>> > shared file system, and thereafter, *only query* the database.
>>
>
> Postgres isn't really designed to work this way. It expects to have
> write access and will occasionally still write stuff to disk even for
> read-only queries.
>
> It won't work even a little bit before 8.3. For 8.3 or later you could
> maybe make it work using vacuum freeze but there's no facility to
> verify that it's really frozen everything and you'll still be taken by
> surprise by queries which try to use temporary space for large sorts
> or commands which start transactions that you didn't realize were
> necessary.
>
> -- greg http://mit.edu/~gsstark/resume.pdf