[Dbconfig-common-devel] Questions about upgrading

[Ross Boylan]

On Mon, Jul 10, 2006 at 02:55:07AM -0400, sean finney wrote:
...
> as for postgres 7.4 vs 8.x, there isn't anything setup to detect
> which version to use, which could be a problem for some mixed
> environments, but it wouldn't be too hard to work out some detection
> code and set a couple extra environment variables to psql.
> 
The postgres wrappers seem to be able to do some auto-detection, since
the psql that started when I connected was 7.4.  A 7.4 database is on
the standard port, but I thought psql would get me the latest version
of the psql client.

I don't know how extensive such facilities are. pg_wrapper in
postgresql-client-common does the magic.

> > That's really useful to know.  There is so much stuff going on in
> > dbconfig that I wasn't sure it was that simple.
> 
> it isn't always, and in this case even i think there's a catch:
> it is possible that there are some substitutions that take place
> in the sql code, so you might want to look through for anything
> that looks like a template variable like _DBC_FOO_ or something.

I finally did execute the upgrade code as user postgres.  This turned
out not be so clever: bacula was unable to access some items in the
new database, and couldn't function.  After some false starts I
managed to set appropriate authorizations (I needed to do so on
sequences as well as tables).  I thought since bacula owned the db it
wouldn't have trouble with getting to anything in it.  Wrong.

All of which shows the perils of not taking your advice literally (you
suggested doing the operation as bacula), and also the value of some
hints with the package.

> 
> > One of the reasons this approach concerns me is that if someone wants
> > to subequently upgrade a database, either because they have multiple
> > instances or because they are in a situation like mine, there is no
> > obvious way to do it.  I gather from above that running the scripts
> > manually is about right if you get the user and choice of database
> > right.  But I gather dbconfig can do more than execute sql scripts on
> > upgrade, and, again, there's no obvious way to access this
> > functionality outside of package install/upgrade.
> 
> yeah.  ideally, the maintainer should still provide information on
> how to perform installs/upgrades manually, even if it's "just run
> x command < sqlfile".  i would consider opening a wishlist bug
> against the package for this.

I might do that.

Another post suggested that database upgrades would upgrade the
corresponding databases, but at least for postgres it doesn't always
do so.  postgres, as packaged for Debian, is explicitly intended to
allow multiple versions of the DB (e.g., 7.4 and 8.1) to run
simultaneously.

I agree that it seems unlikely the dbconfig can cope with all possible
scenarios; it would be nice if it could interact well with most
scenarios, given proper behavior by the package that uses dbconfig.

Anyway, thanks again for your help.  I am relieved to have backups
again.

Ross