Currently, WAL files will be archived as soon as archive_command is set.
IMHO, this is not desirable if no permanent backup is wanted, but only
scheduled online backup because; it will flood the wal_archive
destination with files that will never be used.

I propose to introduce a GUC "permanent_archiving" or so, to select
whether wal archiving happens permanently or only when a backup is in
progress (i.e. between pg_start_backup and pg_stop_backup).


Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> I propose to introduce a GUC "permanent_archiving" or so, to select
> whether wal archiving happens permanently or only when a backup is in
> progress (i.e. between pg_start_backup and pg_stop_backup).

This is silly. Why not just turn archiving on and off?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>I propose to introduce a GUC "permanent_archiving" or so, to select
>>whether wal archiving happens permanently or only when a backup is in
>>progress (i.e. between pg_start_backup and pg_stop_backup).
>
>
> This is silly. Why not just turn archiving on and off?

Not quite. I want online backup, but no archiving. Currently, I have to
edit postgresql.conf and SIGHUP to "turn on archiving" configuring a
(hopefully) writable directory, do the backup, edit postgresql.conf and
SIGHUP again. Not too convenient...

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> This is silly. Why not just turn archiving on and off?

> Not quite. I want online backup, but no archiving. Currently, I have to
> edit postgresql.conf and SIGHUP to "turn on archiving" configuring a
> (hopefully) writable directory, do the backup, edit postgresql.conf and
> SIGHUP again. Not too convenient...

You don't get to count the edit/SIGHUP steps, because those would be the
same for any other GUC.

AFAICS you could get the effect by setting up an archive_command script
sleep 100
exit 1
so that the archiver will do nothing.

BTW, I don't actually understand why you want this at all. If you're
not going to keep a continuing series of WAL files, you don't have any
PITR capability. What you're proposing seems like a bulky, unportable,
hard-to-use equivalent of pg_dump. Why not use pg_dump?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>Tom Lane wrote:
>>
>>>This is silly. Why not just turn archiving on and off?
>
>
>>Not quite. I want online backup, but no archiving. Currently, I have to
>>edit postgresql.conf and SIGHUP to "turn on archiving" configuring a
>>(hopefully) writable directory, do the backup, edit postgresql.conf and
>>SIGHUP again. Not too convenient...
>
>
> You don't get to count the edit/SIGHUP steps, because those would be the
> same for any other GUC.

That's right, but my proposal would implicitely switch on archiving
while backup is in progress, thus explicitely enabling/disabling
archiving wouldn't be necessary.
>
> AFAICS you could get the effect by setting up an archive_command script
> sleep 100
> exit 1
> so that the archiver will do nothing.

Doesn't WAL expect the WAL files already archived to be recyclable, so
they could get overwritten in the pg_xlog dir while backup is running?
Additionally, the doc recommends omitting pg_xlog from the file level
backup, so a restart would need the archived wal files, no?


>
> BTW, I don't actually understand why you want this at all. If you're
> not going to keep a continuing series of WAL files, you don't have any
> PITR capability. What you're proposing seems like a bulky, unportable,
> hard-to-use equivalent of pg_dump. Why not use pg_dump?

Because pg_dump will take too long and create bloated dump files. All I
need is a physical backup for disaster recovery purposes without
bringing down the server.

In my case, I'd expect a DB that uses 114GB on disk to consume 1.4TB
when pg_dumped, too much for the available backup capacity (esp.
compared to net content, about 290GB). See other post "inefficient bytea
escaping" for details.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On May 25, 2006, at 11:24 AM, Andreas Pflug wrote:
>> BTW, I don't actually understand why you want this at all. If you're
>> not going to keep a continuing series of WAL files, you don't have
>> any
>> PITR capability. What you're proposing seems like a bulky,
>> unportable,
>> hard-to-use equivalent of pg_dump. Why not use pg_dump?
>
> Because pg_dump will take too long and create bloated dump files.
> All I need is a physical backup for disaster recovery purposes
> without bringing down the server.
>
> In my case, I'd expect a DB that uses 114GB on disk to consume
> 1.4TB when pg_dumped, too much for the available backup capacity
> (esp. compared to net content, about 290GB). See other post
> "inefficient bytea escaping" for details.

Another consideration is that you can use rsync to update a
filesystem-level backup, but there's no pg_dump equivalent. On a
large database that can make a sizable difference in the amount of
time required for a backup.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> That's right, but my proposal would implicitely switch on archiving
> while backup is in progress, thus explicitely enabling/disabling
> archiving wouldn't be necessary.

I'm not sure you can expect that to work. The system is not built to
guarantee instantaneous response to mode changes like that.

>> BTW, I don't actually understand why you want this at all. If you're
>> not going to keep a continuing series of WAL files, you don't have any
>> PITR capability. What you're proposing seems like a bulky, unportable,
>> hard-to-use equivalent of pg_dump. Why not use pg_dump?

> Because pg_dump will take too long and create bloated dump files. All I
> need is a physical backup for disaster recovery purposes without
> bringing down the server.

> In my case, I'd expect a DB that uses 114GB on disk to consume 1.4TB
> when pg_dumped, too much for the available backup capacity (esp.
> compared to net content, about 290GB). See other post "inefficient bytea
> escaping" for details.

The conventional wisdom is that pg_dump files are substantially smaller
than the on-disk footprint ... and that's even without compressing them.
I think you are taking a corner case, ie bytea data, and presenting it
as something that ought to be the design center.

Something that might be worth considering is an option to allow pg_dump
to use binary COPY. I don't think this'd work nicely for text dumps,
but seems like custom- or tar-format dumps could be made to use it.
This would probably be a win for many datatypes not only bytea, and it'd
still be far more portable than a filesystem dump.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Jim Nasby wrote:
> On May 25, 2006, at 11:24 AM, Andreas Pflug wrote:
>>> BTW, I don't actually understand why you want this at all. If you're
>>> not going to keep a continuing series of WAL files, you don't have any
>>> PITR capability. What you're proposing seems like a bulky, unportable,
>>> hard-to-use equivalent of pg_dump. Why not use pg_dump?
>>
>> Because pg_dump will take too long and create bloated dump files. All
>> I need is a physical backup for disaster recovery purposes without
>> bringing down the server.
>>
>> In my case, I'd expect a DB that uses 114GB on disk to consume 1.4TB
>> when pg_dumped, too much for the available backup capacity (esp.
>> compared to net content, about 290GB). See other post "inefficient
>> bytea escaping" for details.
>
> Another consideration is that you can use rsync to update a
> filesystem-level backup, but there's no pg_dump equivalent. On a large
> database that can make a sizable difference in the amount of time
> required for a backup.
That's fine to cut the backup execution time, but to guarantee
consistency while the cluster is running pg_start_backup/pg_stop_backup
and WAL archiving will still be necessary.

Regards,
Andreas


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, 2006-05-25 at 17:25 +0200, Andreas Pflug wrote:
> Tom Lane wrote:
> > Andreas Pflug <pgadmin@pse-consulting.de> writes:
> >
> >>I propose to introduce a GUC "permanent_archiving" or so, to select
> >>whether wal archiving happens permanently or only when a backup is in
> >>progress (i.e. between pg_start_backup and pg_stop_backup).
> >
> >
> > This is silly. Why not just turn archiving on and off?
>
> Not quite. I want online backup, but no archiving.

I can see what you want and why you want it. It's good to have the
option of a physical online backup as opposed to the logical online
backup that pg_dump offers.

> Currently, I have to
> edit postgresql.conf and SIGHUP to "turn on archiving" configuring a
> (hopefully) writable directory, do the backup, edit postgresql.conf and
> SIGHUP again. Not too convenient...

You're doing this for pgAdmin right?

My understanding was that we had the tools now to edit the
postgresql.conf programmatically?

Seems like its not too convenient to change the way the server operates
to do this, as long as we solve the SIGHUP/postgresql.conf problem. I'm
also not that happy about curtailing people's options on backup either:
if people decided they wanted to have a mixture of isolated on-line
backup (as you suggest), plus active archiving at other times they would
still have the problems you suggest.

Not sure what the edit commands are offhand, but we would need the
following program:

- edit postgresql.conf
- pg_reload_conf()
- wait 30
- pg_start_backup('blah')
- backup
- pg_stop_backup()
- unedit postgresql.conf
- pg_reload_conf()

Which could then be wrapped even more simply as

- pg_start_backup_online('blah')
- backup
- pg_stop_backup_online()

Which overall seems lots easier than changing the server and adding
another parameter.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>That's right, but my proposal would implicitely switch on archiving
>>while backup is in progress, thus explicitely enabling/disabling
>>archiving wouldn't be necessary.
>
>
> I'm not sure you can expect that to work. The system is not built to
> guarantee instantaneous response to mode changes like that.

Um, as long as xlog writing stops immediate recycling when
pg_start_backup is executed everything should be fine, since archived
logs are not expected to be present until pg_stop_backup is done.

>
> The conventional wisdom is that pg_dump files are substantially smaller
> than the on-disk footprint ... and that's even without compressing them.
> I think you are taking a corner case, ie bytea data, and presenting it
> as something that ought to be the design center.

I certainly have an extreme cornercase, since data is highly
compressible. I won't suggest to replace pg_dump by physical backup
methods, but disaster recovery may take considerably longer from a dump
than from filesystem level backup.

>
> Something that might be worth considering is an option to allow pg_dump
> to use binary COPY. I don't think this'd work nicely for text dumps,
> but seems like custom- or tar-format dumps could be made to use it.
> This would probably be a win for many datatypes not only bytea, and it'd
> still be far more portable than a filesystem dump.

I'd really love a copy format that works for binary and text data as
well, optimally compressed. Initial replication to a new slony cluster
node uses COPY, and network bandwidth may become the restricting factor.
Line protocol compression would be desirable for that too, but that's
another story.


Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster