Re: win32: how to backup (dump does not work)

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: "fkater(at)googlemail(dot)com" <fkater(at)googlemail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: win32: how to backup (dump does not work)
Date: 2008-02-27 15:13:49
Message-ID: 20080227151349.GJ10209@svr2.hagander.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, Feb 27, 2008 at 03:59:24PM +0100, fkater(at)googlemail(dot)com wrote:
> On 22:37 Tue 26 Feb , Magnus Hagander wrote:
> > fkater(at)googlemail(dot)com wrote:
>
> >> AFAIK stopping the server, zipping data dir, and restarting the server
> >> creates a zip file which is not easily portable to other computers due
> >> to some ntfs file system permission problems.
> >
> > What exactly would those problems be? If you can shut your server down like
> > this, that's absolutely the easiest way to get it done. It should be
> > portable across all win32 machines at least - and if that's not enough,
>
> FYI (worked around):
>
> issue (1) may have a little relation to the way pg-installer sets the
> permissions, issue (2) is just a pitfall:
>
> (1)
> I've simply tried to stop the server, copied the 'data' directory,
> (renamed old one data.bak), started server on a standard stand-alone
> WinXP (SP2) machine: compared to the working 'data.bak' directory,
> the copied 'data' folder for some reason gets an additional
> permissions entry for the postgres user (see security options of
> directory 'data') which is inherited from the pg's parent directory
> ('8.2' version directory in my case): this entry removes some
> permissions so that the effective permissions for user postgres
> are not enough anymore. Also, when viewing the directories permissions
> of the copied dir, it warns that the order of the permissions are not
> correct and therefore "probably not usable".
>
> I'm not an expert in win32, but the workaround was to disable
> inheritance for the new data dir, remove the extra entry, and set
> permissions for the postgres user to somewhat full (including
> subfolders).

That is the proper fix. We apply a deny permission on user "postgres" so it
cannot change it's own binaries, in case of an exploit. Then we explicitly
remove this deny permission on the data directory - but if you delete it
and recreate it (or rename and recreate), it will inherit the deny
permission again.

> (2)
> The pg service is not started if some empty folders are missing. Some
> zip progs, however, do not zip/unzip these folders by default. So, one
> has to make sure that these folders are included. The pg server adds a
> hint into windows event log.

Ah, that's interesting. That means you need to take care what ZIP program
you use :-)

//Magnus

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-02-27 15:40:09 Re: Regarding interval conversion functions and a seeming lack of usefulness
Previous Message Magnus Hagander 2008-02-27 15:12:13 Re: PostgreSQL-installation-problem on Windows XP Home edition