Thread: win32: how to backup (dump does not work)
Hi, I urgently need a way to simply backup the database's data on win32 (e.g. the 'data' directory). As discussed earlier I can't use dump/dumpall since my data needs persistent tableoids which, however, are not the same after a restore. 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. Does anyone have another idea how to backup (besides disk images)? Thank You Felix
> 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. > IIRC you have to re-assign owner and change permissions after you copy your data dir to the new server. Regards, Gevik
fkater@googlemail.com wrote: > Hi, > > I urgently need a way to simply backup the database's data on win32 > (e.g. the 'data' directory). > > As discussed earlier I can't use dump/dumpall since my data needs > persistent tableoids which, however, are not the same after a restore. > > 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, pg_dump really is your only option. //Magnus
On Tue, Feb 26, 2008 at 3:51 PM, fkater@googlemail.com <fkater@googlemail.com> wrote: > As discussed earlier I can't use dump/dumpall since my data needs > persistent tableoids which, however, are not the same after a restore. What about the workarounds / refactoring tips proposed in previous discussions?
>-----Original Message----- >From: pgsql-general-owner@postgresql.org >[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gevik >Babakhani >Sent: Tuesday, 26 February 2008 22:30 >To: fkater@googlemail.com; pgsql-general@postgresql.org >Subject: Re: [GENERAL] win32: how to backup (dump does not work) > >> 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. >> >IIRC you have to re-assign owner and change permissions after >you copy your data dir to the new server. I believe something like NTBackup (or any decent windows backup tool) can properly restore permissions. However, this will only work with centralized authentication (with NT domain) and NO local computer accounts used, when moving between servers (for obvious reasons). I think even WinRAR contains an option to do so, but cannot remember for sure and I never tried it anyways. In cause you mean the permissions prevent you from making a backup, this should be a smaller problem if you are infact a backup operator and use a windows backup tool (they get more rights when requested). - Joris
On Tue, Feb 26, 2008 at 10:30:02PM +0100, Gevik Babakhani 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. > > > IIRC you have to re-assign owner and change permissions after you copy your > data dir to the new server. You don't need to reassign owner normally - we don't care about that. And if you extract into an existing directory, it should inherit it fine :-) But true, if you extract into a nwe directory, you may need to change the permissions. //Magnus
On Tue, Feb 26, 2008 at 11:51:34PM -0000, Joris Dobbelsteen 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. > >> > >IIRC you have to re-assign owner and change permissions after > >you copy your data dir to the new server. > > I believe something like NTBackup (or any decent windows backup tool) > can properly restore permissions. However, this will only work with > centralized authentication (with NT domain) and NO local computer > accounts used, when moving between servers (for obvious reasons). I > think even WinRAR contains an option to do so, but cannot remember for > sure and I never tried it anyways. Actually, it will work for local oens as well, as long as you only use built-in principals. However, the pg install by default uses a custmo account which, as you say, will not be portable to a different machine. > In cause you mean the permissions prevent you from making a backup, this > should be a smaller problem if you are infact a backup operator and use > a windows backup tool (they get more rights when requested). If you are a backup operator, it shouldn't be a problem *at all*. If it is, something is wrong. //Magnus
On Wed, Feb 27, 2008 at 03:59:24PM +0100, fkater@googlemail.com wrote:
> On 22:37 Tue 26 Feb , Magnus Hagander wrote:
> > fkater@googlemail.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