Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group