Upgrading System Postgres To Version 7.3

This document expalins how to update your system and user PostgreSQL from version 7.2.x to version 7.3. When you are through and the hsphere database has been converted to UNICODE, you are highly recommended to update your PostgreSQL to 7.4.7.

* Check your PostgreSQL version.

Requirements

• H-Sphere 2.4 and higher.
• Current PostgreSQL updated to version 7.2.

Update Process

The update:
1. stops the Control Panel
2. updates Postgres server
3. restarts Control Panel with the new Postgres version.
   Note: If the script runs into an error on a user database server, you are notified of it, the script skips the box and turns to the next one. When you are through with the update, see recover-howto-eng.txt file to lean how to recover the box that hasn't got updated. When you fix the error, you'll need to update this box manually.

Update Procedure:

Step 1. Back Up All Databases

IMPORTANT: You are highly recommended to backup your databases into a directory other than Postgres home directory so you don't lose data if anything goes wrong.

1. Log in as root and stop the control panel.

2. Create and enter backup directory:

   mkdir pg_backup
   cd pg_backup

3. Get the password for wwwuser. You'll need it to query the database:

   cat ~cpanel/shiva/psoft_config/hsphere.properties | grep PASS

4. Dump H-Sphere system database.

   Export schema:

   pg_dump -u -s -f schema.db hsphere
   chmod 600 schema.db
   cp schema.db schema_backup.db

   Export data:

   pg_dump -u -a -f data.db hsphere
   chmod 600 data.db
   cp data.db data_backup.db

   Notes:

   • If your system database is large, the dump can take several hours to complete. You can speed it up by setting

     fsync=off
     in postgresql.conf. When you are done, unset this option back for safety reasons.

   • The dump file is created with 644 permissions by default; you need to set more secure 600 permissions to prevent the data from being read by other users.

5. Dump SiteStudio databases and other databases.

   pg_dump -u -s -f counter_schema.db counter
   pg_dump -u -a -f counter_data.db counter
   pg_dump -u -s -f poll_schema.db poll
   pg_dump -u -a -f poll_data.db poll
   pg_dump -u -s -f guestbook_schema.db guestbook
   pg_dump -u -a -f guestbook_data.db guestbook
   If you have any other databases, back them up, too. There's no need to back up template0 and template1.
6. It is advisable that you validate or check all the dumps to make sure you'll be able to recover data after the upgrade. You could make more than one dump for each database and then compare the two dump files with the diff utility, for example:
   diff schema.db schema1.db
   diff data.db data1.db
7. For additional security, you may disallow access to the backup directory for all other users:
   cd ..
   chmod -R 600 pg_backup
   cd pg_backup

Step 2. Convert exported H-Sphere data to UTF-8 (HS 2.4 upgrade only!)

Starting with version 2.4, H-Sphere uses UTF-8 encoding. On this step, you need to convert hsphere database to UNICODE. Don't change encoding for SiteStudio databases.

1. To find out your current H-Sphere database encoding, type:

   su -l cpanel -c 'psql hsphere'
   hsphere# \encoding

2. If the encoding is not UNICODE (UTF-8), for example ASCII, convert the dump into Unicode with the iconv utility.

   Linux:
   iconv --from-code=<REGIONAL_ENCODING> --to-code=UTF-8 -o utf_data.db data.db
   mv utf_data.db data.db
   FreeBSD:
   iconv -f <REGIONAL_ENCODING> -t UTF-8 data.db > utf_data.db
   mv utf_data.db data.db
   [-] If your dump file exceeds 2GB

   1) Split it into smaller files, 1GB each:

   split -b 1024m data.db data_db

   2) Run iconvfor for each of these files to convert them to UNICODE:

   iconv --from-code=<REGIONAL_ENCODING>--to-code=UTF-8 -o utf_data_db.aa data_db.aa
   iconv --from-code=<REGIONAL_ENCODING>--to-code=UTF-8 -o utf_data_db.ab data_db.ab
   ...

   3) Join them back into data.db:

   cat utf_data_db.aa utf_data_db.ab utf_data_db.ac ... > data.db

   Here, <REGIONAL_ENCODING> is the source encoding. For example, for native US English encoding:

   Linux:
   iconv --from-code=ISO-8859-1 --to-code=UTF-8 -o utf_data.db data.db
   FreeBSD:
   iconv -f ISO-8859-1 -t UTF-8 data.db > utf_data.db

   The resulting data.db file will contain the data converted to Unicode.

For better security, run the following command:
chmod 600 data.db

Step 3. Upgrade Posgtres

1. Stop Postgres.

2. Uninstall the following PosgreSQL packages (you may have an incomplete list):

   postgresql
   postgresql-libs
   postgresql-contrib
   postgresql-devel
   postgresql-server

   Don't uninstall other packages, such as postgresql-perl.
   

   For Linux:

   1) get the names of the PostgreSQL packages installed:

   rpm -qa | grep -i postgres

   2) uninstall the packages one by one:

   rpm -e --nodeps <PACKAGE_NAME>

   For FreeBSD:

   1) get the names of the packages installed:
   # pkg_info | grep postgresql
   2) remove the old package:
   # pkg_delete <PACKAGE_NAME>

3. Move postgres directory to a backup location:
   For Linux:

   mv ~postgres/data pg_backup
   For FreeBSD:
   mv ~pgsql/data pg_backup

4. Install the new PostgreSQL packages.

   For Linux:

   # rpm -ivh postgresql-7.3.4-xPGDG.i386.rpm
   # rpm -ivh postgresql-libs-7.3.4-xPGDG.i386.rpm
   # rpm -ivh postgresql-server-7.3.4-xPGDG.i386.rpm

   where x is 2 for RedHat 7.2/7.3/AS/ES/WS, 1 for RedHat ES 3.

   For FreeBSD:

   # pkg_add postgresql-7.3.4_1.tgz

Step 4. Create And Configure DB User

1. Make sure that TCP/IP connection is enabled in PostgreSQL. Check in the PostgreSQL init script (/etc/init.d/postgresql for Linux, /usr/local/etc/rc.d/010.pgsql.sh for FreeBSD) that pg_ctl starts with the -i option.
   For RedHat:
   
   su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT} -i' start > /dev/null 2>&1"
   For FreeBSD:
   
   [ -d \${PGDATA} ] && exec ${PREFIX}/bin/pg_ctl start -s -w -l ${logfile}"
   the option -w should be changed to -o '-i':
   
   [ -d \${PGDATA} ] && exec ${PREFIX}/bin/pg_ctl start -s -o '-i' -l ${logfile}

   This option corresponds to setting tcpip_socket=true in postgresql.conf.
   Read man pg_ctl, man postmaster and related PgSQL manual for details.

2. Start PostgreSQL for the first time.
   On RedHat servers:
   

   /etc/rc.d/init.d/postgresql start
   On FreeBSD servers, initiate the PostgreSQL service database manually before you start Postgres:
   
   su - pgsql -c initdb
   /usr/local/etc/rc.d/010.pgsql.sh start
3. Enter as the postgres user:
   For Linux:
   su - postgres
   For FreeBSD:
   su - pgsql
4. Create wwwuser:
   createuser wwwuser
5. Set password for wwwuser.
   Open file ~cpanel/shiva/psoft_config/hsphere.properties and copy the wwwuser password. Then enter it into the database:
   psql template1
   alter user wwwuser with password '<WWWUSER_PASSWORD>';
6. Configure PostgreSQL according to Step 4 of the Postgres Installation manual.

Step 5. Optimize H-Sphere Database Schema

Follow documentation on accelerating CP performance:

1. Convert int8 to int4
2. Change the type of l_server_ips.ip_num back to int8

Step 6. Recreate Databases

1. Create H-Sphere database:

   If you are staying with H-Sphere 2.3.x:
   createdb -U wwwuser hsphere
   If you are upgrading to H-Sphere 2.4:
   createdb -E UNICODE -U wwwuser hsphere
2. Create H-Sphere DB schema:
   psql -q -U wwwuser -f schema.db hsphere
3. Import H-Sphere system data:
   psql -q -U wwwuser -f data.db hsphere
   Note: If you face problems with importing data, please see the Troubleshooting section in CP Acceleration guide.

4. Recreate SiteStudio and other databases from dump files.

createdb -U wwwuser counter
createdb -U wwwuser poll
createdb -U wwwuser guestbook
psql -q -U wwwuser -f counter_schema.db counter
psql -q -U wwwuser -f counter_data.db counter
psql -q -U wwwuser -f poll_schema.db poll
psql -q -U wwwuser -f poll_data.db poll
psql -q -U wwwuser -f guestbook_schema.db guestbook
psql -q -U wwwuser -f guestbook_data.db guestbook

5. Start the Control Panel.