pg_migrator issue with contrib

Brad Nicholson wrote:

I've been kicking the tires on this a bit, and I've found an issue when
dealing with contrib/ (specifically dblink, although I haven't looked
around anymore).

dblink_current_query() is not in the 8.4 version - when I run
pg_migrator on an 8.3 cluster that has dblink installed, I get the
following:

Restoring database schema
psql:/home/postgres/pg_migrator_dump_db.sql:271: ERROR: could not find
function "dblink_current_query" in file
"/opt/dbs/pgsql84-beta2/lib/dblink.so"

There were problems executing "/opt/dbs/pgsql84-beta2/bin/psql" --set
ON_ERROR_STOP=on --port 5432 -f "/home/postgres/pg_migrator_dump_db.sql"
--dbname template1 >> "/dev/null"

Yep, pg_migrator will exit on any restore error. This really relates to
the problem of how we handle /contrib schema migration from one
release to the other. Good thing you posted to hackers because that is
really the group that can address this. pg_migrator is really just
restoring the schema that pg_dump is producing.

pg_migrator exits leaving me with a corrupted 8.3 instance.

When you say "corrupted", I assume you mean you have remove the _old
suffixes to restart your 8.3 instance, right? I hope that is the only
corruption issue --- please confirm.

At the very least, a mention in the documentation of incompatible
contrib module(s) would be nice. Even better would be a sanity check
added to prevent this.

OK, I am looking to the hackers group for recommentations on this. I
wonder if I should recommend uninstalling /contrib modules before the
upgrade, but that is not possible for custom data types that have
columns already defined in the old cluster.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

At the very least, a mention in the documentation of incompatible
contrib module(s) would be nice. Even better would be a sanity check
added to prevent this.

OK, I am looking to the hackers group for recommentations on this. I
wonder if I should recommend uninstalling /contrib modules before the
upgrade, but that is not possible for custom data types that have
columns already defined in the old cluster.

There is no nice answer to this. It goes way beyond data types: you
could be using the module stuff in indexes, functions, views etc. You
can't just drop the stuff. The best I have been able to do in similar
cases is to install the updated module in the database before restoring,
and ignore any restoration errors about "foo already exists" or "foo not
found in .so file". Not sure how well that translates to pg_migrator,
though.

cheers

andrew

On Fri, 2009-06-05 at 15:50 -0400, Bruce Momjian wrote:

Brad Nicholson wrote:

I've been kicking the tires on this a bit, and I've found an issue when
dealing with contrib/ (specifically dblink, although I haven't looked
around anymore).

dblink_current_query() is not in the 8.4 version - when I run
pg_migrator on an 8.3 cluster that has dblink installed, I get the
following:

Restoring database schema
psql:/home/postgres/pg_migrator_dump_db.sql:271: ERROR: could not find
function "dblink_current_query" in file
"/opt/dbs/pgsql84-beta2/lib/dblink.so"

There were problems executing "/opt/dbs/pgsql84-beta2/bin/psql" --set
ON_ERROR_STOP=on --port 5432 -f "/home/postgres/pg_migrator_dump_db.sql"
--dbname template1 >> "/dev/null"

Yep, pg_migrator will exit on any restore error. This really relates to
the problem of how we handle /contrib schema migration from one
release to the other. Good thing you posted to hackers because that is
really the group that can address this. pg_migrator is really just
restoring the schema that pg_dump is producing.

pg_migrator exits leaving me with a corrupted 8.3 instance.

When you say "corrupted", I assume you mean you have remove the _old
suffixes to restart your 8.3 instance, right? I hope that is the only
corruption issue --- please confirm.

Unfortunately no - when I try and start the old version, I get:

pg_ctl -D pgsql83/ start
postgres: could not find the database system
Expected to find it in the directory "/opt/DATA/pgsql83",
but could not open file "/opt/DATA/pgsql83/global/pg_control": No such
file or directory

I am specifying both new and old paths to pg_migrator, as I don't have
my data directories in standard locations.

Test case is pretty straight forward.

Create an 8.3 instance
Add dblink
follow through the pg_migrator upgrade path
try and start your 8.3 instance after the failure

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.

Bruce Momjian <bruce@momjian.us> writes:

Brad Nicholson wrote:

At the very least, a mention in the documentation of incompatible
contrib module(s) would be nice. Even better would be a sanity check
added to prevent this.

OK, I am looking to the hackers group for recommentations on this.

One thing I was going to suggest is that pg_migrator check that all the
.so's in the old installation also exist in the new one. However that
could be overkill since you don't know for sure if the old .so is
referenced anywhere in the database.

As for the specific problem at hand, it might've been a mistake to
replace dblink_current_query() with a SQL function instead of changing
the internal implementation of the C function. We could still fix that.

regards, tom lane

Brad Nicholson wrote:

When you say "corrupted", I assume you mean you have remove the _old
suffixes to restart your 8.3 instance, right? I hope that is the only
corruption issue --- please confirm.

Unfortunately no - when I try and start the old version, I get:

pg_ctl -D pgsql83/ start
postgres: could not find the database system
Expected to find it in the directory "/opt/DATA/pgsql83",
but could not open file "/opt/DATA/pgsql83/global/pg_control": No such
file or directory

I am specifying both new and old paths to pg_migrator, as I don't have
my data directories in standard locations.

Test case is pretty straight forward.

Create an 8.3 instance
Add dblink
follow through the pg_migrator upgrade path
try and start your 8.3 instance after the failure

Uh, I assume you read all of the INSTALL instructions, including the
last item:

10. Reverting to old cluster

...

--> If you ran pg_migrator _without_ --link or did not start the new server,
the old cluster was not modified except that an ".old" suffix was
appended to $PGDATA/global/pg_control and tablespaces directories. To
reuse the old cluster, remove the tablespace directories created by the
new cluster and remove the ".old" suffix from the old cluster tablespace
directory names and $PGDATA/global/pg_control; then you can restart the
old cluster.

I just modified the arrow text to be clearer. The rename of
pg_controldata is done to prevent accidental starting of the old cluster
in case of migration success.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Andrew Dunstan wrote:

Bruce Momjian wrote:

At the very least, a mention in the documentation of incompatible
contrib module(s) would be nice. Even better would be a sanity check
added to prevent this.

OK, I am looking to the hackers group for recommentations on this. I
wonder if I should recommend uninstalling /contrib modules before the
upgrade, but that is not possible for custom data types that have
columns already defined in the old cluster.

There is no nice answer to this. It goes way beyond data types: you
could be using the module stuff in indexes, functions, views etc. You
can't just drop the stuff. The best I have been able to do in similar
cases is to install the updated module in the database before restoring,
and ignore any restoration errors about "foo already exists" or "foo not
found in .so file". Not sure how well that translates to pg_migrator,
though.

I suspected this answer but figured I would get a definative answer
rather than guessing.

Based on the way pg_migrator works I am going to suggest that if
/contrib restore generates an error that they uninstall the /contrib
from the old cluster and retry.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

Brad Nicholson wrote:

At the very least, a mention in the documentation of incompatible
contrib module(s) would be nice. Even better would be a sanity check
added to prevent this.

OK, I am looking to the hackers group for recommentations on this.

One thing I was going to suggest is that pg_migrator check that all the
.so's in the old installation also exist in the new one. However that
could be overkill since you don't know for sure if the old .so is
referenced anywhere in the database.

Or in this case that the new *.so has the same functions.

As for the specific problem at hand, it might've been a mistake to
replace dblink_current_query() with a SQL function instead of changing
the internal implementation of the C function. We could still fix that.

I am afraid /contrib is going to be a mine field for this type of
problem so I am going to recommend uninstaling the /contrib module if
possible and retry the migration. That should work in this case.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

There is no nice answer to this. It goes way beyond data types: you
could be using the module stuff in indexes, functions, views etc. You
can't just drop the stuff. The best I have been able to do in similar
cases is to install the updated module in the database before restoring,
and ignore any restoration errors about "foo already exists" or "foo not
found in .so file". Not sure how well that translates to pg_migrator,
though.

I suspected this answer but figured I would get a definative answer
rather than guessing.

Based on the way pg_migrator works I am going to suggest that if
/contrib restore generates an error that they uninstall the /contrib
from the old cluster and retry.

I have added the following paragraph to the pg_migrator INSTALL docs:

If an error occurs while restoring the database schema, pg_migrator will
exit and you will have to revert to the old cluster as outlined in step
#10 below. To try pg_migrator again, you will need to modify the old
cluster so the pg_migrator schema restore succeeds. If the problem is a
/contrib module, you might need to uninstall the /contrib module from
the old cluster and install it in the new cluster after migration.

The only other idea I have would be to add a switch that allows
schema restore errors to be ignored, but I would like to see if the
above text is enough first.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Hi,

Ah, we need module/extension/package/plugin so badly...

Le 5 juin 09 à 22:19, Bruce Momjian a écrit :

I am afraid /contrib is going to be a mine field for this type of
problem so I am going to recommend uninstaling the /contrib module if
possible and retry the migration. That should work in this case.

You can't seriously recommend that, I'm afraid.
As Andrew (Dunstan) was saying up-thread, the faulty module (from
contrib or pgfoundry) could hold some indexes (btree, gist, gin) and/
or data types in the cluster relations.

So if you uninstall the module (drop type cascade, drop operator
class, ...) you lose data.

Some example modules that I can think of and are wildspread in the
field, as far as I know, are ip4r (data type and indexes), orafce
(functions, views, tables), and some less spread are prefix (data type
and indexes) or temporal (period data type, indexes).

Please do not recommend people to lose their precious data to be able
to upgrade. You could tell them pg_migrator isn't an option in their
case, though. At least we're left with a faster (multi-threaded)
pg_restore :)

Regards,
--
dim

Bruce,

Assuming a contrib module *hasn't* changed its API, does pg_migrator
link against the 8.4 version of the module's .so, or the old 8.3 version?

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Fri, Jun 5, 2009 at 6:11 PM, Dimitri Fontaine <dfontaine@hi-media.com> wrote:

Some example modules that I can think of and are wildspread in the field, as
far as I know, are ip4r (data type and indexes), orafce (functions, views,
tables), and some less spread are prefix (data type and indexes) or temporal
(period data type, indexes).

And hstore...

David Blewett

Josh Berkus wrote:

Bruce,

Assuming a contrib module *hasn't* changed its API, does pg_migrator
link against the 8.4 version of the module's .so, or the old 8.3 version?

8.4 version, or whatever is in the 8.4 lib, which should be 8.4.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Dimitri Fontaine wrote:

Hi,

Ah, we need module/extension/package/plugin so badly...

Le 5 juin 09 ? 22:19, Bruce Momjian a ?crit :

I am afraid /contrib is going to be a mine field for this type of
problem so I am going to recommend uninstaling the /contrib module if
possible and retry the migration. That should work in this case.

You can't seriously recommend that, I'm afraid.
As Andrew (Dunstan) was saying up-thread, the faulty module (from
contrib or pgfoundry) could hold some indexes (btree, gist, gin) and/
or data types in the cluster relations.

So if you uninstall the module (drop type cascade, drop operator
class, ...) you lose data.

Some example modules that I can think of and are wildspread in the
field, as far as I know, are ip4r (data type and indexes), orafce
(functions, views, tables), and some less spread are prefix (data type
and indexes) or temporal (period data type, indexes).

Please do not recommend people to lose their precious data to be able
to upgrade. You could tell them pg_migrator isn't an option in their
case, though. At least we're left with a faster (multi-threaded)
pg_restore :)

Very good point, and something I had not considered. I tried
uninstalling hstore and it gladly dropped any hstore columns!

I have updated the INSTALL instructions:

If an error occurs while restoring the database schema, pg_migrator will
exit and you will have to revert to the old cluster as outlined in step
#10 below. To try pg_migrator again, you will need to modify the old
cluster so the pg_migrator schema restore succeeds. If the problem is a
/contrib module, you might need to uninstall the /contrib module from
the old cluster and install it in the new cluster after the migration,
assuming the module is not being used to store user data.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On 6/5/09 6:27 PM, Bruce Momjian wrote:

Josh Berkus wrote:

Bruce,

Assuming a contrib module *hasn't* changed its API, does pg_migrator
link against the 8.4 version of the module's .so, or the old 8.3 version?

8.4 version, or whatever is in the 8.4 lib, which should be 8.4.

So, here's what we need for 8.3 --> 8.4 for contrib modules:

1) make a list of contrib modules which do not convert cleanly (testers?)
2) document these.
3) give pg_migrator some hackish way to install 8.4 contrib modules from
source before copying over the database.
4) set pg_migrator to ignore duplicate object warnings if it does the above.

Note that we expect NOT to have this issue for 8.4-->8.5, since we'll
have a full module infrastructure by then. Really!

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Josh Berkus <josh@agliodbs.com> writes:

So, here's what we need for 8.3 --> 8.4 for contrib modules:

1) make a list of contrib modules which do not convert cleanly (testers?)
2) document these.
3) give pg_migrator some hackish way to install 8.4 contrib modules from
source before copying over the database.
4) set pg_migrator to ignore duplicate object warnings if it does the above.

1A) consider whether we can reduce/eliminate the conversion problems.
We have five days.

I don't think we need testing, per se. The first step should be to diff
the 8.3 and 8.4 versions of the various contrib .sql.in files and
determine what changed. Any module whose .sql.in file hasn't changed
is definitely safe.

regards, tom lane

On 6 Jun 2009, at 19:50, Tom Lane wrote:

We have five days.

I don't think we need testing, per se. The first step should be to
diff
the 8.3 and 8.4 versions of the various contrib .sql.in files and
determine what changed. Any module whose .sql.in file hasn't changed
is definitely safe.

I can tell you already that dblink has changed, and I had to drop it
before migration, otherwise everything went fine. Migration of 57GB
data in place took about 1 minute.

Le 6 juin 09 à 20:45, Josh Berkus a écrit :

So, here's what we need for 8.3 --> 8.4 for contrib modules:

That does nothing for external modules whose code isn't in PostgreSQL
control. I'm thinking of those examples I cited up-thread --- and some
more. (ip4r, temporal, prefix, hstore-new, oracfe, etc).

Could pg_migrator detect usage of "objects" oids (data types in
relation, index opclass, ...) that are unknown to be in the standard -
core + contrib distribution, and quit trying to upgrade the cluster in
this case, telling the user his database is not supported?

Note that we expect NOT to have this issue for 8.4-->8.5, since
we'll have a full module infrastructure by then. Really!

Note: added in-place upgrade to requirements of the first version of
the feature.
--
dim

Dimitri Fontaine wrote:

Le 6 juin 09 ? 20:45, Josh Berkus a ?crit :

So, here's what we need for 8.3 --> 8.4 for contrib modules:

That does nothing for external modules whose code isn't in PostgreSQL
control. I'm thinking of those examples I cited up-thread --- and some
more. (ip4r, temporal, prefix, hstore-new, oracfe, etc).

Agreed, that's why the new INSTALL text addresses this clearly. You
might want to read my blog entry about why the INSTALL file is so
important for pg_migrator:

http://momjian.us/main/blogs/pgblog.html#June_6_2009

Could pg_migrator detect usage of "objects" oids (data types in
relation, index opclass, ...) that are unknown to be in the standard -
core + contrib distribution, and quit trying to upgrade the cluster in
this case, telling the user his database is not supported?

Well, they will get an error and see the INSTALL file --- I don't see
having pg_migrator go around looking for things as a fruitful effort.

Note that we expect NOT to have this issue for 8.4-->8.5, since
we'll have a full module infrastructure by then. Really!

Note: added in-place upgrade to requirements of the first version of
the feature.

Yes, this will certainly spur development of a better /contrib install
system.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Grzegorz Jaskiewicz wrote:

On 6 Jun 2009, at 19:50, Tom Lane wrote:

We have five days.

I don't think we need testing, per se. The first step should be to
diff
the 8.3 and 8.4 versions of the various contrib .sql.in files and
determine what changed. Any module whose .sql.in file hasn't changed
is definitely safe.

I can tell you already that dblink has changed, and I had to drop it
before migration, otherwise everything went fine. Migration of 57GB
data in place took about 1 minute.

The good news is that the INSTALL instructions where clear enough for
the tester to understand that uninstalling dblink from the old cluster
and reinstalling it in the new cluster would work.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +