[Spacewalk-list] issue upgrading large postgresql database
john miller
johnmille1 at gmail.com
Mon Jun 24 21:49:25 UTC 2013
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>
More information about the Spacewalk-list
mailing list