pgsql/doc/src/sgml/ref (allfiles.sgml)

Since we may have a workable backup/restore based on WAL available in 7.1,
I am now wondering at the wisdom of creating 'pg_restore', which reads the
new pg_dump archive files. It is probably better to have pg_backup &
pg_restore as the backup/restore utilities.

As a result do people have any objection to changing pg_restore to
pg_undump? Or pg_load?

Any other suggestions would also be welcome.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

What was the matter with the name pg_restore?

Since we may have a workable backup/restore based on WAL available in 7.1,
I am now wondering at the wisdom of creating 'pg_restore', which reads the
new pg_dump archive files. It is probably better to have pg_backup &
pg_restore as the backup/restore utilities.

As a result do people have any objection to changing pg_restore to
pg_undump? Or pg_load?

Any other suggestions would also be welcome.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Mon, 16 Oct 2000, Bruce Momjian wrote:

What was the matter with the name pg_restore?

I didn't wanna be the one to ask, but I was kinda confused on that point
too ...

Since we may have a workable backup/restore based on WAL available in 7.1,
I am now wondering at the wisdom of creating 'pg_restore', which reads the
new pg_dump archive files. It is probably better to have pg_backup &
pg_restore as the backup/restore utilities.

As a result do people have any objection to changing pg_restore to
pg_undump? Or pg_load?

Any other suggestions would also be welcome.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

Don't go changing yet. When Vadim has something, we can decide. I
think we may have unique commands for logging control and stuff, so
let's see how it plays out.

At 00:45 16/10/00 -0400, Bruce Momjian wrote:

What was the matter with the name pg_restore?

The fact that we will have a 'proper' backup/restore with the WAL changes,
and it seems more appropriate that the new utilities should be called
pg_backup & pg_restore. This leaves the 'undump' part of pg_dump without a
name. So I will most likely call it pg_load since dump & load are commonly
associated as verbs.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 00:45 16/10/00 -0400, Bruce Momjian wrote:

What was the matter with the name pg_restore?

The fact that we will have a 'proper' backup/restore with the WAL changes,
and it seems more appropriate that the new utilities should be called
pg_backup & pg_restore. This leaves the 'undump' part of pg_dump without a
name. So I will most likely call it pg_load since dump & load are commonly
associated as verbs.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

As a result do people have any objection to changing pg_restore to
pg_undump? Or pg_load?

Also possible would be a name like Oracle
pg_exp and pg_imp for export and import.
(or pg_export and pg_import)

Load and unload is often more tied to data only (no dml).

I agree that the current name pg_restore for its current functionality
is not good and misleading in the light of WAL backup.

Andreas

I like the pg_{import,export} names myself ... *nod*

On Mon, 16 Oct 2000, Zeugswetter Andreas SB wrote:

As a result do people have any objection to changing pg_restore to
pg_undump? Or pg_load?

Also possible would be a name like Oracle
pg_exp and pg_imp for export and import.
(or pg_export and pg_import)

Load and unload is often more tied to data only (no dml).

I agree that the current name pg_restore for its current functionality
is not good and misleading in the light of WAL backup.

Andreas

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

I like the pg_{import,export} names myself ... *nod*

Sounds fine also; but we have compatibility issues in that we
still need pg_dump. Maybe just a symbolic link to pg_export.

Yes, we still need in pg_dump, because of pg_dump is thing
quite different from WAL based backup/restore. pg_dump
is utility to export data in system independant format
using standard SQL commands (with COPY extension) and WAL
based backup system is to export *physical* data files
(and logs). So, pg_dump should be preserved asis.

Vadim

Philip Warner writes:

I like the pg_{import,export} names myself ... *nod*

Sounds fine also; but we have compatibility issues in that we still need
pg_dump. Maybe just a symbolic link to pg_export.

I'm not so fond of changing a long-established program name for the sake
of ethymological correctness or consistency with other products (yeah,
right). I got plenty of suggestions if you want to start that. I say
stick to pg_dump[all], and name the inverse pg_undump, pg_load, or
pmud_gp.

Btw., it will still be possible to restore, er, reload, with psql, right?

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Philip Warner writes:

I like the pg_{import,export} names myself ... *nod*

Sounds fine also; but we have compatibility issues in that we still need
pg_dump. Maybe just a symbolic link to pg_export.

I'm not so fond of changing a long-established program name for the sake
of ethymological correctness or consistency with other products (yeah,

Agreed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 10:08 16/10/00 -0300, The Hermit Hacker wrote:

I like the pg_{import,export} names myself ... *nod*

Sounds fine also; but we have compatibility issues in that we still need
pg_dump. Maybe just a symbolic link to pg_export.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

On Tue, 17 Oct 2000, Peter Eisentraut wrote:

Philip Warner writes:

I like the pg_{import,export} names myself ... *nod*

Sounds fine also; but we have compatibility issues in that we still need
pg_dump. Maybe just a symbolic link to pg_export.

I'm not so fond of changing a long-established program name for the sake
of ethymological correctness or consistency with other products (yeah,
right). I got plenty of suggestions if you want to start that. I say
stick to pg_dump[all], and name the inverse pg_undump, pg_load, or
pmud_gp.

pmud_gp? *raised eyebrow*

At 00:12 17/10/00 +0200, Peter Eisentraut wrote:

Btw., it will still be possible to restore, er, reload, with psql, right?

Correct.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

At 15:07 16/10/00 -0700, Mikheev, Vadim wrote:

So, pg_dump should be preserved asis.

Just to clarify; I have no intention of doing anything nasty to pg_dump.
All I plan to do is rename the pg_restore to one of
pg_load/pg_import/pg_undump/pmud_gp, to make way for a WAL based restore
utility, although as Bruce suggests, this may be premature.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

So, pg_dump should be preserved asis.

Just to clarify; I have no intention of doing anything nasty to pg_dump.
All I plan to do is rename the pg_restore to one of
pg_load/pg_import/pg_undump/pmud_gp, to make way for a WAL based restore
utility, although as Bruce suggests, this may be premature.

It is not premature. We will need a WAL based restore for 7.1
or we imho don't need to enable WAL for 7.1 at all.

Andreas

Just to clarify; I have no intention of doing anything nasty to pg_dump.

Oh, ok, it wasn't clear, sorry -:)

All I plan to do is rename the pg_restore to one of
pg_load/pg_import/pg_undump/pmud_gp, to make way for a WAL based
restore utility, although as Bruce suggests, this may be premature.

It is not premature. We will need a WAL based restore for 7.1
or we imho don't need to enable WAL for 7.1 at all.

I missed your point here - why ?!
New backup/restore is not only result of WAL.
What about recovery & performance?
Hm, WAL is required for distributed transactions
and we are not going to have them in 7.1 - does it
also mean that we don't need to enable WAL in 7.1?

There is WAL - general mechanism for transaction
recovery & performance, alternative (with regard to
non-overwriting storage manager) approach to transaction
systems. And there are WAL based features. Sooner
we'll get base sooner we'll have features.

Vadim

It is not premature. We will need a WAL based restore for 7.1
or we imho don't need to enable WAL for 7.1 at all.

I missed your point here - why ?!
New backup/restore is not only result of WAL.
What about recovery & performance?

Ok, recovery is only improved for indexes, no ?
Performance must imho be worse in your first round
(at least compared to -F mode).
There is room for improvement that was not there
before WAL (like avoiding write calls, non-overwrite ...)
but those are not implemented yet.
Please correct me if I am wrong here, but imho we accept that
slowdown, because we gain so much.

Hm, WAL is required for distributed transactions
and we are not going to have them in 7.1 - does it
also mean that we don't need to enable WAL in 7.1?

No, but rollforward is currently the main feature, no ?
Does it make sense to ship WAL without using it's currently
main feature ?

There is WAL - general mechanism for transaction
recovery & performance, alternative (with regard to
non-overwriting storage manager) approach to transaction
systems. And there are WAL based features. Sooner
we'll get base sooner we'll have features.

Ok, you have implemented startup rollforward anyway.
I think that the logic for a rollforward that starts with a restored tar
backup (if done correctly) will be exactly or at least nearly the same.

That is:
Walk the log, see if the entry is already done, do it if not, else
go to next entry in log.

It could be the responsibility of the dba to decide with which log to begin
rollforward after restore.

Andreas

It is not premature. We will need a WAL based restore for 7.1
or we imho don't need to enable WAL for 7.1 at all.

I missed your point here - why ?!
New backup/restore is not only result of WAL.
What about recovery & performance?

Ok, recovery is only improved for indexes, no ?
Performance must imho be worse in your first round
(at least compared to -F mode).

^^^^^^^^
only

There is room for improvement that was not there
before WAL (like avoiding write calls, non-overwrite ...)
but those are not implemented yet.

And what? If there will be no WAL with base functionality
now then there will be no additional WAL benefits (eg savepoints)
later. WAL based backup/reatore is one of additional WAL benefit.
*Hopefully* we'll be able to implement it now.

BTW, avoiding writes is base WAL feature, ie - it'll be
implemented in 7.1.

Please correct me if I am wrong here, but imho we accept that
slowdown, because we gain so much.

Hm, WAL is required for distributed transactions
and we are not going to have them in 7.1 - does it
also mean that we don't need to enable WAL in 7.1?

No, but rollforward is currently the main feature, no ?

I'm going to rollback changes on abort in 7.1. Seems I've
mentioned both redo and UNDO (without compensation records)
AM methods many times.

Does it make sense to ship WAL without using it's currently
main feature ?

Sorry, but it's not always possible to have all at once.
But again, hopefully we'll have backup/restore.
Thanks to Philip.

(BTW, replication server prototype announced by Pgsql, Inc
could be used for incremental backup)

Vadim

BTW, avoiding writes is base WAL feature, ie - it'll be
implemented in 7.1.

Wow, great, I thought first step was only to avoid sync :-)

No, but rollforward is currently the main feature, no ?

I'm going to rollback changes on abort in 7.1. Seems I've
mentioned both redo and UNDO (without compensation records)
AM methods many times.

I don't think that I misunderstood anything here. If the commit
record is in the tx log this tx will have to be rolled forward, and
not aborted. Of course open tx's on abort will be rolled back.
But this roll forward for committed tx could be a starting point, no?

Does it make sense to ship WAL without using it's currently
main feature ?

Sorry, but it's not always possible to have all at once.

Sorry, my main point was not to argument against WAL in 7.1,
but to state, that backup/restore would be very important.

(BTW, replication server prototype announced by Pgsql, Inc
could be used for incremental backup)

Yes, that could be a good starting point for rollforward if it is
based on WAL.

We should not call this tx log business "Incremental backup"
an incremental backup scans all pages, and backs
them up if they changed in respect to the last higher level backup.
(full backup, level 1 backup, level 2 backup ....)
Oracle only uses this chargon, since they don't have such a
backup, and want to fool their customer's managers.
All other DB companies make correct use of the wording
"incremental backup" in the above sense.

Andreas

We should not call this tx log business "Incremental backup"
an incremental backup scans all pages, and backs
them up if they changed in respect to the last higher level backup.
(full backup, level 1 backup, level 2 backup ....)

You may be tying implementation too closely to function; so long as
succesive incremental backups are (a) incremental since the last higher
level backup, and (b) sufficient to restore the database when cobined with
other incremental backups, then ISTM that the method of deriving the backup
(from logs, or reading data pages) is irrelevant.

Yes, definitely, but they actually refer to their redo logs directly,
not something extracted from the logs like you describe.
They call tx log backup incremental backup which we should avoid.

I too am used to incremental backups that actually read data pages, but if
the WAL has enough information to determine the pages, the why not use
it...but maybe I'm missing something.

Something to think about later, but imho reading the data pages is The Way.

Do we have the info when a page was last modified (in respect to a WAL position,
not wallclock time) on each page ? This is probably an info we will need.

Andreas

At 10:38 18/10/00 +0200, Zeugswetter Andreas SB wrote:

We should not call this tx log business "Incremental backup"
an incremental backup scans all pages, and backs
them up if they changed in respect to the last higher level backup.
(full backup, level 1 backup, level 2 backup ....)

You may be tying implementation too closely to function; so long as
succesive incremental backups are (a) incremental since the last higher
level backup, and (b) sufficient to restore the database when cobined with
other incremental backups, then ISTM that the method of deriving the backup
(from logs, or reading data pages) is irrelevant.

I too am used to incremental backups that actually read data pages, but if
the WAL has enough information to determine the pages, the why not use
it...but maybe I'm missing something.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

BTW, avoiding writes is base WAL feature, ie - it'll be
implemented in 7.1.

Wow, great, I thought first step was only to avoid sync :-)

? If syncs are not required then why to do write call?

No, but rollforward is currently the main feature, no ?

I'm going to rollback changes on abort in 7.1. Seems I've
mentioned both redo and UNDO (without compensation records)
AM methods many times.

I don't think that I misunderstood anything here. If the commit
record is in the tx log this tx will have to be rolled forward, and
not aborted. Of course open tx's on abort will be rolled back.
But this roll forward for committed tx could be a starting point, no?

Currently records inserted by aborted transactions remain in db
untill vacuum. I try to rollback changes - ie *delete* inserted
tuples on abort (though could do not do this), - isn't there
some difference now?

Does it make sense to ship WAL without using it's currently
main feature ?

Sorry, but it's not always possible to have all at once.

Sorry, my main point was not to argument against WAL in 7.1,
but to state, that backup/restore would be very important.

Yes but I'm not able to do this work. And note that with WAL in
7.1 we could add backup/restore in any version > 7.1 (eg 7.1.1).

(BTW, replication server prototype announced by Pgsql, Inc
could be used for incremental backup)

Yes, that could be a good starting point for rollforward if it is
based on WAL.

It's not.

We should not call this tx log business "Incremental backup"
an incremental backup scans all pages, and backs
them up if they changed in respect to the last higher level backup.
(full backup, level 1 backup, level 2 backup ....)
Oracle only uses this chargon, since they don't have such a
backup, and want to fool their customer's managers.
All other DB companies make correct use of the wording
"incremental backup" in the above sense.

Scanning *all* pages?! Not the best approach imho.
Or did you meant scanning log to get last *committed"
changes to all pages - ie some kind of log compression?

Vadim

Do we have the info when a page was last modified (in respect
to a WAL position, not wallclock time) on each page ? This is
probably an info we will need.

How else one could know was a change applied to page or not?

Vadim

BTW, avoiding writes is base WAL feature, ie - it'll be
implemented in 7.1.

Wow, great, I thought first step was only to avoid sync :-)

? If syncs are not required then why to do write call?

Yes, writes are only necessary when "too many dirty pages"
are in the buffer pool. Those writes can be done by a page flusher
on demand or during checkpoint (don't know if we need checkpoint,
but you referred to doing checkpoints).

Currently records inserted by aborted transactions remain in db
untill vacuum. I try to rollback changes - ie *delete* inserted
tuples on abort (though could do not do this), - isn't there
some difference now?

this has two sides: less space wastage, but slower rollback.
What you state is a separate issue, and is not part of the
startup rollforward for committed tx'ns. It is part of the rollback
of aborted tx'ns.

We should not call this tx log business "Incremental backup"
an incremental backup scans all pages, and backs
them up if they changed in respect to the last higher level backup.
(full backup, level 1 backup, level 2 backup ....)
Oracle only uses this chargon, since they don't have such a
backup, and want to fool their customer's managers.
All other DB companies make correct use of the wording
"incremental backup" in the above sense.

Scanning *all* pages?! Not the best approach imho.
Or did you meant scanning log to get last *committed"
changes to all pages - ie some kind of log compression?

You could call an incremental backup some kind of log compression, yes,
but it is usually done by reading base data pages (an optimization would be to
skip page ranges that are known to not have changed, but how do you know)

In the backup I have in mind there are 3 things:

1. full backup
2. tx log backup
3. incremental backup with multiple levels

Point 3 is something to keep in mind, but do later.
On restore you do

restore full backup (level 0)
restore incremental backup level 1 (pages that changed after level 0)
restore incremental backup level 2 (pages that changed after level 1)
restore tx logs that were written after level 2

The incremental backups are especially useful in an overwrite smgr,
where you can get a lot of tx log that only changes a few pages.

Andreas

Yes, writes are only necessary when "too many dirty pages"
are in the buffer pool. Those writes can be done by a page flusher
on demand or during checkpoint (don't know if we need checkpoint,
but you referred to doing checkpoints).

How else to know from where in log to start redo and how far go back
for undo ?

Currently records inserted by aborted transactions remain in db
untill vacuum. I try to rollback changes - ie *delete* inserted
tuples on abort (though could do not do this), - isn't there
some difference now?

this has two sides: less space wastage, but slower rollback.
What you state is a separate issue, and is not part of the
startup rollforward for committed tx'ns. It is part of the rollback
of aborted tx'ns.

And I've stated this as separate feature.

Vadim

Yes, writes are only necessary when "too many dirty pages"
are in the buffer pool. Those writes can be done by a page flusher
on demand or during checkpoint (don't know if we need checkpoint,
but you referred to doing checkpoints).

How else to know from where in log to start redo and how far go back
for undo ?

I don't know, but if your checkpoint algorithm does not need to block
other activity, that would be great.
The usual way would involve:
writing all dirty pages to disk during checkpoint
block all modifying activity

One other thing I would like to ask, is O_SYNC not available on all platforms ?
Then you could avoid the (or some) fsync calls in xlog.c ?

And is there a possibility to add -F mode without fsyncs to xlog.c ?

Andreas