pg_upgrade

Bruce Momjian wrote:

Tom asked about pg_upgrade as part of our initdb for timezone.

I have made some improvements to pg_upgrade in CVS and have successfully
migrated a regression database from a 7.2 to another 7.2 database using
it. (At least the tables show some data; very light testing.)

Here is a patch I need to /contrib/pg_resetxlog to support a new "-x
XID" option to set the XID in pg_control. Patch attached. This is the
last feature I needed for a functioning pg_upgrade for 7.1->7.2 and
7.2->7.2 databases.

Many commercial distributions like this script, and with our
newly-needed initdb to fix our timezonetz problem, it seemed like a good
time. :-) It certainly reduces upgrade time.

(BTW, where are we on that timezonetz patch anyway? Tom posted it two
days ago and I haven't seen any comments.)

pg_upgrade is still disabled.

-- 
  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 <pgman@candle.pha.pa.us> writes:

Here is a patch I need to /contrib/pg_resetxlog to support a new "-x
XID" option to set the XID in pg_control.

I don't like this patch. It seems weird to add -x as an independent
function rather than just have pg_resetxlog do its normal thing and
allow -x to override the xid value. -x defined that way makes sense
in the context of pg_resetxlog's original mission (in particular, one
should be able to use it in the situation where the old pg_control is
unrecoverable). Also, there's no good reason for pg_upgrade not to
reset the xlog --- certainly we would not want the records therein to
be replayed against the pg_upgraded database!

There is a more serious problem, also. Pages transferred over from the
old database will contain LSN values pointing into the old xlog. If
these are past the end of the new database's xlog (very probable) then
you have a strong risk of "XLogFlush: request past end of xlog" errors,
which per Vadim's insistence we treat as a system-wide fatal condition.

Probably the cleanest way to deal with that is to tweak pg_resetxlog
further to have an optional switch with a minimum xlog position.
It already knows how to set up its cleared xlog with a position >=
end of the removed log, so you could have an additional option switch
that forces the new position to be >= switch value. To issue the
switch, pg_upgrade would have to look at the old xlog files to determine
the endpoint of the old xlog. Seems messy but not impossible.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Here is a patch I need to /contrib/pg_resetxlog to support a new "-x
XID" option to set the XID in pg_control.

I don't like this patch. It seems weird to add -x as an independent
function rather than just have pg_resetxlog do its normal thing and
allow -x to override the xid value. -x defined that way makes sense
in the context of pg_resetxlog's original mission (in particular, one
should be able to use it in the situation where the old pg_control is
unrecoverable). Also, there's no good reason for pg_upgrade not to
reset the xlog --- certainly we would not want the records therein to
be replayed against the pg_upgraded database!

OK, if we want to reset WAL at the same time, which does make sense as
you say, here is the patch. This is even easier for me. It just
optionally sets the XID as part of the normal operation. (I am going to
commit this patch because it is better for you and smaller than the one
I just committed from last night.)

There is a more serious problem, also. Pages transferred over from the
old database will contain LSN values pointing into the old xlog. If
these are past the end of the new database's xlog (very probable) then
you have a strong risk of "XLogFlush: request past end of xlog" errors,
which per Vadim's insistence we treat as a system-wide fatal condition.

Probably the cleanest way to deal with that is to tweak pg_resetxlog
further to have an optional switch with a minimum xlog position.
It already knows how to set up its cleared xlog with a position >=
end of the removed log, so you could have an additional option switch
that forces the new position to be >= switch value. To issue the
switch, pg_upgrade would have to look at the old xlog files to determine
the endpoint of the old xlog. Seems messy but not impossible.

Wow, that sounds hard. Can you give me some hints which pg_control
field that is in?

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

Probably the cleanest way to deal with that is to tweak pg_resetxlog
further to have an optional switch with a minimum xlog position.
It already knows how to set up its cleared xlog with a position >=
end of the removed log, so you could have an additional option switch
that forces the new position to be >= switch value. To issue the
switch, pg_upgrade would have to look at the old xlog files to determine
the endpoint of the old xlog. Seems messy but not impossible.

Also, how do I find the current xlog segment position. Is it one of
these fields shown by pg_controldata?

pg_controldata
pg_control version number: 71
Catalog version number: 200110251
Database state: SHUTDOWNED
pg_control last modified: 01/10/02 13:00:04
Current log file id: 0
Next log file segment: 7
Latest checkpoint location: 0/6000010
Prior checkpoint location: 0/0
Latest checkpoint's REDO location: 0/6000010
Latest checkpoint's UNDO location: 0/6000010
Latest checkpoint's StartUpID: 8
Latest checkpoint's NextXID: 105
Latest checkpoint's NextOID: 16557
Time of latest checkpoint: 01/10/02 13:00:04
Database block size: 8192
Blocks per segment of large relation: 131072
LC_COLLATE: C
LC_CTYPE: C

-- 
  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 <pgman@candle.pha.pa.us> writes:

Also, how do I find the current xlog segment position. Is it one of
these fields shown by pg_controldata?

"latest checkpoint location" should do.

BTW, if your script is relying on pg_resetxlog to be available, best to
ensure that it's there before you do anything irreversible ...

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Also, how do I find the current xlog segment position. Is it one of
these fields shown by pg_controldata?

"latest checkpoint location" should do.

OK. I will add a -l flag to specify that location.

BTW, if your script is relying on pg_resetxlog to be available, best to
ensure that it's there before you do anything irreversible ...

Oh yes, I will make sure it is available _and_ has the flags from the
new version.

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

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Also, how do I find the current xlog segment position. Is it one of
these fields shown by pg_controldata?

"latest checkpoint location" should do.

BTW, if your script is relying on pg_resetxlog to be available, best to
ensure that it's there before you do anything irreversible ...

Do we want to remove the 7.1beta WAL format code from
/contrib/pg_resetxlog? Remember, this utility was originally written to
allow for a WAL format change during 7.1beta testing. Seems like dead
code now.

-- 
  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 <pgman@candle.pha.pa.us> writes:

Do we want to remove the 7.1beta WAL format code from
/contrib/pg_resetxlog?

Sure, I don't think there's any strong need for it anymore. Anyone who
did need that version could get it out of the 7.1 release, anyway.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Do we want to remove the 7.1beta WAL format code from
/contrib/pg_resetxlog?

Sure, I don't think there's any strong need for it anymore. Anyone who
did need that version could get it out of the 7.1 release, anyway.

OK, I will do that as a separate patch later.

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

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Also, how do I find the current xlog segment position. Is it one of
these fields shown by pg_controldata?

"latest checkpoint location" should do.

BTW, if your script is relying on pg_resetxlog to be available, best to
ensure that it's there before you do anything irreversible ...

OK, here is code to set the checkpoint log id and offset using a new -l
flag. It also now displays the checkpoint location with -n.

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

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Do we want to remove the 7.1beta WAL format code from
/contrib/pg_resetxlog?

Sure, I don't think there's any strong need for it anymore. Anyone who
did need that version could get it out of the 7.1 release, anyway.

The following patch removes the V0 WAL handling that was there only for
7.1beta.

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

Tom asked about pg_upgrade as part of our initdb for timezone.

I have made some improvements to pg_upgrade in CVS and have successfully
migrated a regression database from a 7.2 to another 7.2 database using
it. (At least the tables show some data; very light testing.)

pg_upgrade is still disabled in CVS, it doesn't install, and there is no
manual page so it is still an unused command. I have made the commit so
people can review where I have gone and make comments.

To test it, you have to find the line that says 7.2 and remove the '#'
comment. This is for testing purposes only, so far.

Status report: I have completed all the steps necessary for pg_upgrade
to work for 7.1->7.2 and for 7.2->7.2 databases. I will run tests
tomorrow, and once I am sure it works, I will ask others to test.

I will not enable it until everyone agrees. Are there people interested
in this tool being in 7.2, or who are against this tool being in 7.2?

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

Status report: I have completed all the steps necessary for pg_upgrade
to work for 7.1->7.2 and for 7.2->7.2 databases. I will run tests
tomorrow, and once I am sure it works, I will ask others to test.

I will not enable it until everyone agrees. Are there people interested
in this tool being in 7.2, or who are against this tool being in 7.2?

I have a good sized (~11 GB) database on 7.2b3. I'd like to try
pg_upgrade to move it to CVS tip and/or 7.2RC1, so I guess I can be one
of your testers.

-- Joe

Bruce Momjian writes:

I will not enable it until everyone agrees. Are there people interested
in this tool being in 7.2, or who are against this tool being in 7.2?

You're not going to like my opinion, but I'm going to put it forth anyway.
We've been working on this release for half a year, and there have been
far too many last-minute bright ideas that should have been postponed.
The fact that someone is going to want to upgrade their installation from
a previous release didn't just occur to us yesterday, so while this
development effort is commendable, this is just not the time. I'm not
even going to list any technical reasons here, you can make up your own
list because you're looking at the code. I'm just looking at the emails
and it gives me the creeps already.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Bruce Momjian writes:

I will not enable it until everyone agrees. Are there people interested
in this tool being in 7.2, or who are against this tool being in 7.2?

You're not going to like my opinion, but I'm going to put it forth anyway.
We've been working on this release for half a year, and there have been
far too many last-minute bright ideas that should have been postponed.
The fact that someone is going to want to upgrade their installation from
a previous release didn't just occur to us yesterday, so while this
development effort is commendable, this is just not the time. I'm not
even going to list any technical reasons here, you can make up your own
list because you're looking at the code. I'm just looking at the emails
and it gives me the creeps already.

Gives me the creeps too. :-)

I am working on it only because there isn't other stuff to do and it
isn't delaying anything because it is disabled anyway; we can keep it
for 7.3 if we wish. There was also the problem of a system catalog
change, and that got the fire moving. Also, certain commerical
distributors bug me about this from time to time so even if we don't
officially use it my guess is that some of them may enable it anyway for
their distributions.

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

I will not enable it until everyone agrees. Are there people interested
in this tool being in 7.2, or who are against this tool being in 7.2?

I have never trusted pg_upgrade on production databases. This is where it is
actually targeted to (to minimize downtime). I have always done full
pg_dumpall and then full recreation of the database system, which
unfortunately in my cases take few hours. With so many little changes in the
structures pg_upgrade it is probably not very safe method anyway?

Just my opinion. :-)

Daniel

Daniel Kalchev wrote:

Bruce Momjian said:

I will not enable it until everyone agrees. Are there people interested
in this tool being in 7.2, or who are against this tool being in 7.2?

I have never trusted pg_upgrade on production databases. This is where it is
actually targeted to (to minimize downtime). I have always done full
pg_dumpall and then full recreation of the database system, which
unfortunately in my cases take few hours. With so many little changes in the
structures pg_upgrade it is probably not very safe method anyway?

Just my opinion. :-)

I agree. There are just too many people who complain to me about a lack
of pg_upgrade that I have to do my best on it and let people decide if
they want to use it.

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

Peter Eisentraut <peter_e@gmx.net> writes:

... I'm just looking at the emails
and it gives me the creeps already.

FWIW, I would *never* trust a production database to pg_upgrade in its
current state; it's untested and can't possibly get enough testing
before release to be trustable. But if Bruce wants to work on it,
where's the harm? The discussions I've had with him over the past
couple days are more than valuable enough for development of a future
bulletproof pg_upgrade, whether or not the current script ever helps
anyone.

The only mistake we could make here is to advertise pg_upgrade as
reliable. Which we will not do.

regards, tom lane

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

... I'm just looking at the emails
and it gives me the creeps already.

FWIW, I would *never* trust a production database to pg_upgrade in its
current state; it's untested and can't possibly get enough testing
before release to be trustable. But if Bruce wants to work on it,
where's the harm? The discussions I've had with him over the past
couple days are more than valuable enough for development of a future
bulletproof pg_upgrade, whether or not the current script ever helps
anyone.

The only mistake we could make here is to advertise pg_upgrade as
reliable. Which we will not do.

Some people have large, non-critical databases they want to upgrade to
7.2. I can imagine some people using pg_upgrade for those cases.

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