[Spacewalk-list] issue upgrading large postgresql database

[john miller]

I am upgrading from 1.7 to 1.8 and the schema upgrade failed with:

 tail
/var/log/spacewalk/schema-upgrade/20130621-134829-to-spacewalk-schema-1.8.log

 spacewalk-schema-1.7-to-spacewalk-schema-1.8/127-rhnPackageCapability-version.sql.postgresql
(1 row)

psql:/var/log/spacewalk/schema-upgrade/20130621-134829-script.sql:2221:
FATAL:  terminating connection due to administrator command
FATAL:  terminating connection due to administrator command
psql:/var/log/spacewalk/schema-upgrade/20130621-134829-script.sql:2221:
connection to server was lost


Trying to reindex or vacuum rhnPackageCapability also failed. After
much weeping and gnashing of teeth I found that rhnPackageCapability is the
largest table in my database in terms of relpages.


relname                 | relpages
-----------------------------------------+----------
rhnpackagecapability                    |    83454
rhn_pkg_cap_name_version_uq             |    70087
rhnchecksum                             |    68787
rhn_snapshot_id_pk                      |    67040
rhnsnapshotservergroup                  |    57522

schema  |             name             |  size   |  index  | ratio |  total
----------+------------------------------+---------+---------+-------+---------

public   | rhnsnapshotpackage           | 17 GB   | 15 GB   | 47%   | 32 GB
public   | rhn_snapshotpkg_sid_nid_uq   | 0 bytes | 15 GB   | 100%  | 15 GB
public   | rhnsnapshotconfigrevision    | 2070 MB | 1977 MB | 49%   | 4047
MB
public   | rhnpackagefile               | 1630 MB | 774 MB  | 33%   | 2404
MB
public   | rhnpackagecapability         | 652 MB  | 716 MB  | 53%   | 1368
MB

My assumption is that some sort of timeout or autovacuum is interfering.
Running in stand-alone mode I was able to re-index the tables.

su postgres -c "postgres --single -D /var/lib/pgsql/data -P -f i
spaceschema"
backend> REINDEX TABLE rhnpackagecapability;
backend> REINDEX DATABASE spaceschema;


I was able to complete the upgrade by manually copying the remaining
commands into the backend console. Do newer spacewalk-schema-upgrades run
from stand-alone? Is there a way to do that? A disadvantage I am missing?

It looks like there is talk of adding full front end /back end protocol
support to the standalone mode in postgresql 9.3 which should allow the
current schema upgrade to run in stand-alone mode.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20130624/414d8662/attachment.htm>