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

win32: how to backup (dump does not work)

From
"fkater@googlemail.com"
Date:
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



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

From
"Gevik Babakhani"
Date:
> 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


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

From
Magnus Hagander
Date:
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

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

From
"Rodrigo E. De León Plicet"
Date:
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?

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

From
"Joris Dobbelsteen"
Date:
>-----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


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

From
Magnus Hagander
Date:
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

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

From
Magnus Hagander
Date:
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

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

From
Magnus Hagander
Date:
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