backup and restore

"Martin A. Marques" wrote:

Hi,

I would like to know if postgres comes with some kind of backup application?
I mean something that would do database backup and restore, something like
informix's ontape and logical logs.

Take a look at pg_dump and pg_dumpall

-------------
Hannu

On Mon, 02 Oct 2000, Hannu Krosing wrote:

"Martin A. Marques" wrote:

Hi,

I would like to know if postgres comes with some kind of backup
application? I mean something that would do database backup and restore,
something like informix's ontape and logical logs.

Take a look at pg_dump and pg_dumpall

Well, that would only be part og what I'm looking for. The thing I like about
informix is that I can make a Level 0 backup of all the data (equal to the
pg_dumpall), and then leave the logical logs downloading continuosly, so that
if in one moment the system breaks, I restore the Level 0 backup and then
apply the logical logs, which are the small changes that have been done to
the database in each transaction, administration, etc.

Could this be added? I am willing to help with the coding.

Saludos... :-)

--
"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

This will be in 7.1 as WAL (write-ahead log).

On Mon, 02 Oct 2000, Hannu Krosing wrote:

"Martin A. Marques" wrote:

Hi,

I would like to know if postgres comes with some kind of backup
application? I mean something that would do database backup and restore,
something like informix's ontape and logical logs.

Take a look at pg_dump and pg_dumpall

Well, that would only be part og what I'm looking for. The thing I like about
informix is that I can make a Level 0 backup of all the data (equal to the
pg_dumpall), and then leave the logical logs downloading continuosly, so that
if in one moment the system breaks, I restore the Level 0 backup and then
apply the logical logs, which are the small changes that have been done to
the database in each transaction, administration, etc.

Could this be added? I am willing to help with the coding.

Saludos... :-)

--
"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Mart���n Marqu���s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

-- 
  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 Sun, 08 Oct 2000, Bruce Momjian wrote:

This will be in 7.1 as WAL (write-ahead log).

What does this exactly mean? Will this WAL be an equivalent of the logical
log of Informix? Where will they be kept? In the database, as a file, or as
either?
Looks pretty good. All that is needed after this is a good blob
implementation (for text and binary data).

Saludos... ;-)

--
"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

"Martin A. Marques" wrote:

On Sun, 08 Oct 2000, Bruce Momjian wrote:

This will be in 7.1 as WAL (write-ahead log).

What does this exactly mean? Will this WAL be an equivalent of the logical
log of Informix? Where will they be kept? In the database, as a file, or as
either?
Looks pretty good. All that is needed after this is a good blob
implementation (for text and binary data).

TOAST. Coming for 7.1.

And we thought 7.0 had alot of new features relative to 6.5.....
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

"Martin A. Marques" wrote:

On Sun, 08 Oct 2000, Bruce Momjian wrote:

This will be in 7.1 as WAL (write-ahead log).

What does this exactly mean? Will this WAL be an equivalent of the logical
log of Informix? Where will they be kept? In the database, as a file, or as
either?
Looks pretty good. All that is needed after this is a good blob
implementation (for text and binary data).

TOAST. Coming for 7.1.

And we thought 7.0 had alot of new features relative to 6.5.....

I think the true feature-killer release was 6.5.

-- 
  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 Sun, 08 Oct 2000, Bruce Momjian wrote:

This will be in 7.1 as WAL (write-ahead log).

What does this exactly mean? Will this WAL be an equivalent of the logical
log of Informix? Where will they be kept? In the database, as a file, or as
either?
Looks pretty good. All that is needed after this is a good blob
implementation (for text and binary data).

Yes, I think it will be similar to Informix logical logs. Not sure
where they will be kept, but assume you can put it anywhere you want.
Not sure if tape logging will be enabled. It will be written as a file.

-- 
  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

Bruce Momjian wrote:

Lamar Owen Wrote:

And we thought 7.0 had alot of new features relative to 6.5.....

I think the true feature-killer release was 6.5.

Well, this is true. I skipped the whole 6.4 series for a reason :-). I
just _knew_ 6.5 was going to be _it_... (in actuality, it's because
RPM's for 6.4 weren't available until 6.5 was nearly ready to
release...).

It was feature-shock to go from 6.3.2 to 6.5.... But the performance
difference was the real kicker, for multiuser concurrent processing.

But that changelog entry for 6.5.3->7.0 was _huge_. And there were
significant changes to many files, not just a few -- the RPMset
differences were substantial from the building/packaging side, the side
I deal with.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Well, that would only be part og what I'm looking for. The thing I like about
informix is that I can make a Level 0 backup of all the data (equal to the
pg_dumpall), and then leave the logical logs downloading continuosly, so that
if in one moment the system breaks, I restore the Level 0 backup and then
apply the logical logs, which are the small changes that have been done to
the database in each transaction, administration, etc.

Could this be added? I am willing to help with the coding.

This is what Version 7.1 WAL is all about.
There might be some help wanted in one of the possible backup methods:
1. a pg_dumpall restore, and a subsequent restore of logs
2. a restore of a "physical backup of db files" + subsequent restore of logs

I think Vadim has the 1st way in his works.
The 2nd way would need some work and testing, and probably some utility to
backup the files in the correct order
(Could be something that calls tar with appropriate arguments).
I am still pretty sure that a physical backup without synchronization with the
postmaster is possible with a little extra work. E.g. checking index validity
after restore and rebuild if bogus. The better way would probably be to not backup
index files at all and rebuild them after restore.

A distinct suffix for different file types would definitely help in this area (.dat, .idx, .tmp ...).
I think this would be a good idea overall.

Andreas

At 16:21 12/10/00 +0200, Zeugswetter Andreas SB wrote:

Could this be added? I am willing to help with the coding.

This is what Version 7.1 WAL is all about.
There might be some help wanted in one of the possible backup methods:
1. a pg_dumpall restore, and a subsequent restore of logs
2. a restore of a "physical backup of db files" + subsequent restore of logs

I think Vadim has the 1st way in his works.

My guess is that there are some issues to resolve: I presume the WAL uses
OID to identify rows, which will mean that BLOB restoration won't work
because it changes the OID of the BLOB. Also, pg_dump will restore row OIDs
for table data, but not the OID for such things as sequences. Guessing
again, I'd say that sequence updates in the WAL will also be OID based.

It's definitely true that the WAL is an essential first step to proper
backup, but there's probably the need to write a backup utility as well.
Unless of course Vadim has done that as well...

----------------------------------------------------------------
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 |/

Could this be added? I am willing to help with the coding.

This is what Version 7.1 WAL is all about.
There might be some help wanted in one of the possible backup methods:
1. a pg_dumpall restore, and a subsequent restore of logs
2. a restore of a "physical backup of db files" +
subsequent restore of logs

I think Vadim has the 1st way in his works.

No, 2nd. WAL reflects layout of tuples in data files, so I don't see
how pg_dump output could be used.
I'll really appreciate if someone will help with this issue ... after
alpha testing will start next week.

Vadim

On Thu, 12 Oct 2000, Mikheev, Vadim wrote:

Could this be added? I am willing to help with the coding.

This is what Version 7.1 WAL is all about.
There might be some help wanted in one of the possible backup methods:
1. a pg_dumpall restore, and a subsequent restore of logs
2. a restore of a "physical backup of db files" +
subsequent restore of logs

I think Vadim has the 1st way in his works.

No, 2nd. WAL reflects layout of tuples in data files, so I don't see
how pg_dump output could be used.
I'll really appreciate if someone will help with this issue ... after
alpha testing will start next week.

Where is this code? How can we get it?

"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

No, 2nd. WAL reflects layout of tuples in data files, so I don't see
how pg_dump output could be used.
I'll really appreciate if someone will help with this issue ... after
alpha testing will start next week.

Where is this code? How can we get it?

Code is in CVS.
Philip Warner already contacted me that he want to do WAL based
backup/restore and we'll proceed in a few days. If someone want to
help in other areas, WAL todo follows in separate message.

Vadim