Creating the PostgreSQL Database - Marin County Office of Education

cuttlefishblueData Management

Dec 16, 2012 (4 years and 7 months ago)

309 views

Quintessential School Systems
Beta Test 206:
Converting SYSCTL
from Image to SQL
© Quintessential School Systems (QSS), 2009
All Rights Reserved
2121 S. El Camino Real, Suite D200 --- San Mateo, CA 94403 --- Voice 650/372-0200 --- Fax 650/372-3386 --- www.qss.com
Published: 2/2/2009

Copyright Notice
Copyright © Quintessential School Systems, 2009
This document contains proprietary information which is protected by copy-
right. All rights are reserved. No part of this publication may be reproduced,
transmitted, transcribed, stored in a retrieval system or translated into any lan-
guage or computer language, in any form or by any means, electronic, mechan-
ical, optical, chemical, manual or otherwise without the prior written approval
of Quintessential School Systems (QSS).
The information contained in this document is subject to change without
notice.
QUINTESSENTIAL SCHOOL SYSTEMS MAKES NO WARRANTY OF
ANY KIND WITH REGARD TO THIS MATERIAL, INCLUDING, BUT
NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
Quintessential School Systems shall not be liable for errors contained
herein or resulting from the use of this material.
QSS/OASIS, STUDENT/3000, and SCHOOL/3000 are registered trademarks
of Quintessential School Systems.
The samples of reports, windows, and dialog boxes in this document are pro-
vided to illustrate the operation of the software at a typical site. All names and
addresses are fictitious. The actual windows, dialog boxes, and reports at your
site may vary from the samples in this documentation.

Beta Test 206: Converting SYSCTL from Image to SQL
1
2/2/2009
Contents
Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Getting Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Documentation Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Installing the Migration Kit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Installing Files on the HP e3000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Creating the PostgreSQL Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Creating the Microsoft SQL Server Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Converting SYSCTL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Unloading and Converting SYSCTL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Loading SYSCTL into PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Loading SYSCTL into Microsoft SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . 15
Creating the Print File (PQUE) Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Creating the PostgreSQL Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Creating the Microsoft SQL Server Database . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Migrating DISTRICT.PUB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
The DISTRICT.PUB File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Launching the Migration Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Setting Up the SQL Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Setting Up the PostgreSQL Database Server . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Setting Up the Microsoft SQL Database Server . . . . . . . . . . . . . . . . . . . . . . . . 30
HOW-TO Build PostgreSQL from Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Instructions for Building from Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Creating Scripts Manually . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Commands for Alternative Installation in "/opt" Directories . . . . . . . . . . . . . . 42
Objectives
Beta Test 206: Converting SYSCTL from Image to SQL
1
2/2/2009
Objectives
This document tells how to convert the following Image database on the HP e3000 to
SQL and load the converted data into a SQL database:
♦ SYSCTL
The procedures in this document have the following goals:
♦ Migration of Image databases on the HP e3000 to a SQL platform. As of Septem-
ber 2007, QSS supports the following platforms:
◊ PostgreSQL running on either Linux or HP-UX
◊ Microsoft SQL Server
♦ Identifying any errors in the migration process such as data errors not found or cor-
rected by the migration process that cause the SQL Migration Report to be exces-
sively large or prevent records from being loaded into SQL.
♦ Providing an opportunity to clean up your data before the actual database
migration.
♦ Create and load databases needed for future application Beta tests.
Complete the instructions in this guide in the following order:
1 If needed, set up the SQL database server.
◊ If this is your site’s first Beta test, set up the SQL database server, as described
in “Setting Up the SQL Database Server” on page 26.
◊ If you site has already done a Beta test, the SQL database server is set up. You
can go directly to installing the migration kit, as described in the next step.
2 Install the migration kit for Beta Test 206, as described in “Installing the Migration
Kit” on page 4.
3 Convert the data in SYSCTL, other system databases (MENCTL, MENU,
QDACTL, QSSPRE), and user security files (@.USERSEC), then load it into the
sysctl database on the SQL database server, as described in “Converting SYSCTL”
on page 12.
If the data does not convert properly the first time, you can repeat these steps as
many times as are needed to get a clean conversion.
Getting Help
Beta Test 206: Converting SYSCTL from Image to SQL
2
2/2/2009
Getting Help
Use the QSS Support Forums to get help. QSS has set up the "Migration - Database"
forum for you to post any questions or comments that come up as you follow these
instructions.
Before seeking help from QSS in reviewing the results of a database migration,
please do the following:
♦ Record all job numbers and save $STDLISTs.
♦ Leave all reports in LSPOOL or Print Manager.
♦ Copy and paste to a saved text file the output from the SYSQLCMD script or any
other scripts that you want reviewed.
To obtain access to QSS Support Forums:
1 Point your Web browser to www.qss.com.
2 Click the "Support" link.
3 Click the "Secure Support Area" link.
4 Enter the user name and password for your site.
5 Click the "All Forums" link.
6 Enter your user name and password for the QSS Support Forums.
Each person who uses the QSS Support Forum has his or her own user name. You
must be registered to post questions and comments. When registering, use your
name and district or county, such as "Mary Jones - Central USD."
7 Click the "All Forums" link.
8 Use the "Migration - Database" forum for questions and comments about the data-
base conversion.
Before posting a new topic, search the forum to see if your question has already
been asked and answered.
For questions about using the QSS Support Forums, contact your account manager.
WARNING. Output from any $STDLIST, report, or script for data conversion may contain
confidential information such as social security numbers, names, and phone numbers.
Therefore, do not post output from the conversion process directly to any QSS support
forum without carefully reviewing and editing this information.
Documentation Conventions
Beta Test 206: Converting SYSCTL from Image to SQL
3
2/2/2009
Documentation Conventions
This document uses the following conventions for reading and typing commands on
the computer.
♦ The font Courier Bold indicates commands you should type.
♦ The font Courier illustrates sample output from the commands you typed.
Some output may not be exactly as shown but should be similar. Not all output
will be shown.
♦ The font Courier Bold Italic is used to indicate a substitution should be
made in the command you type with the appropriate information for your site. For
example, the notation <password> means to substitute the password for your
system.
Installing the Migration Kit Installing Files on the HP e3000
Beta Test 206: Converting SYSCTL from Image to SQL
4
2/2/2009
Installing the Migration Kit
These instructions tell how to install the migration kit for Beta Test 206. This process
involves installing software on the HP e3000 and the SQL database server.
To convert the SYSCTL database:
1 Install the conversion files on the HP e3000, as described in "Installing Files on the
HP e3000" on this page.
2 Create the sysctl database on the SQL database server.
◊ If you are using PostgreSQL, follow the instructions in “Creating the Postgr-
eSQL Database” on page 7.
◊ If you are using Microsoft SQL Server, follow the instructions in “Creating the
Microsoft SQL Server Database” on page 8.
Installing Files on the HP e3000
These instructions tell how to install the files on the HP e3000 that are needed for
converting SYSCTL. They assume the following:
♦ The store to disk file BT206STD.INSTALL.QSSDEV is available on your HP
e3000.
♦ The SQL database server has been set up. For details, see “Setting Up the SQL
Database Server” on page 26.
To install the migration kit on your HP e3000:
1 Log on to the HP e3000 and restore the migration group build script from the store
to disk file.
:HELLO PGMR.QSSDEV
ENTER USER (PGMR) PASSWORD: <password>
:FILE T=BT206STD.INSTALL;DEV=DISC
:FILE SYSLIST=$STDLIST
:RESTORE *T;BLDMIG.INSTALL.QSSDEV;SHOW
FILES RESTORED : 1
2 Build the migration groups.
:BLDMIG.INSTALL
Please enter (CR - carriage return) at prompt
GETVSN H.00.00 compiled 04/15/02 14:12

Installing the Migration Kit Installing Files on the HP e3000
Beta Test 206: Converting SYSCTL from Image to SQL
5
2/2/2009
Group.account to check for volume name. Use spaces
to use PUB.<logon-acct>.
Press the RETURN key to see output as the groups are being built.
cblmig group created...
cfgmig group created...
datmig group created...
jclmig group created...
objmig group created...
prnmig group created...
tarmig group created...
schmig group created...
vffmig group created...
vsfmig group created...
cblxmig group created...
cfgxmig group created...
datxmig group created...
jclxmig group created...
objxmig group created...
prnxmig group created...
tarxmig group created...
schxmig group created...
vffxmig group created...
vsfxmig group created...
If any group was created by a previous release, will not be included in your output.
3 Restore the rest of the files. (assumes the file equations from step 1 are still
present)
:RESTORE *T;@.@.QSSDEV;SHOW
FILES RESTORED : 14
4 Copy files for migrating to SQL into production. This step logs on as
MGR.QSSPROD.
:STREAM BT206TFR.INSTALL.QSSDEV
ENTER USER (MGR) PASSWORD: <password>
5 Create the groups and copy necessary files into the user account.
If your user account is not QSSUSER, replace it with what you are actually using.
You will see some additional output as JCL files are modified to use your user
account.
Installing the Migration Kit Installing Files on the HP e3000
Beta Test 206: Converting SYSCTL from Image to SQL
6
2/2/2009
:HELLO MGR.QSSUSER
ENTER USER (MGR) PASSWORD: <password>
:BT206CPY.INSTALL.QSSDEV
Please enter (CR - carriage return) at prompt
GETVSN H.00.00 compiled 04/15/02 14:12

Group.account to check for volume name. Use spaces
to use PUB.<logon-acct>.
Press the RETURN key to see output as the groups are being built and files are
copied.
migdata group created...
migfix group created...
XSQLSY.DATA copied...
XSQLSYST.JCL copied...
XFTPSYST.JCL copied...
SY10UNLD.JCL copied...
XDIFTPST.JCL copied...
If the migdata and migfix groups were created by a previous release, they will
not be listed on the screen.
6 Review the control file XSQLSY.DATA.QSSUSER and edit if necessary.
The default values that should be appropriate for most customers using
PostgreSQL.
7 Edit the batch job XFTPSYST.JCL control file.
Replace the following with appropriate logon information to the SQL database
server:
◊ <ip/sysname> with either the ip address or system name of the server.
◊ <password> with the password for the qssdba user on the server
◊ <directory> with the directory on the server you decided to use for the
migration files as discussed in the appropriate server setup document.
WARNING: If using a database platform other than PostgreSQL, such as SQL Server, you
MUST make appropriate changes to the "Target Server (SQL) Settings" section.
Installing the Migration Kit Creating the PostgreSQL Database
Beta Test 206: Converting SYSCTL from Image to SQL
7
2/2/2009
8 If you want FTP to transfer files automatically after each data conversion, change
the setjcw doftp,0 to setjcw doftp,1 in the XSQLSYST.JCL
file.
9 Ensure the following command is part of your QSSUSER logon process which is
already setting other variables such as PPUTL, PFUTL:
SETVAR PCUTL,"CFGUTL"
10 Create the database on the SQL database server.
There are different instructions for each database server that QSS supports. Fol-
low the instructions for the database server installed at your site.
◊ If your site is using PostgreSQL, see “Creating the PostgreSQL Database” on
page 7.
◊ If your site is using Microsoft SQL server, see “Creating the Microsoft SQL
Server Database” on page 8.
Creating the PostgreSQL Database
These instructions describe how to create the sysctl database at sites that are using
PostgreSQL. If your site is not using PostgreSQL, see step 10 on page 7 for guidance
in selecting the proper instructions for your site.
To create the PostgreSQL database:
1 Log on to the HP e3000 and transfer the SQL scripts to the database server. Use
the same values setup in the XFTPSYST.JCL file in step 7 on page 6.
:HELLO PGMR.QSSDEV
:FTP.ARPA.SYS
open <ip/sysname>
qssdba
<password>
cd <directory>
binary
put pgtsys10.schmig sqlsys10.tar
quit
All remaining steps are done on the Linux or HP-UX server.
2 Log on to the database server.
Log on as the qssdba user and change to the directory on the server that you
decided to use for the migration files, as described in “Setting Up the PostgreSQL
Database Server” on page 26.
Installing the Migration Kit Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
8
2/2/2009
login as: qssdba
qssdba's password: <password>
cd <directory>
3 Extract the SQL script files from the tar file.
tar -xvf sqlsys10.tar
./sqlsys10.sql
./sqlsys10_ci.sql
./sqlsys10_di.sql
4 Create the Sysctl SQL database.
createdb -E SQL_ASCII sysctl
CREATE DATABASE
psql sysctl
sysctl=# \i sqlsys10.sql
The screen will display many massages like:
ERROR: table "????????" does not exist
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index...
CREATE TABLE
CREATE INDEX
These messages are a normal part of the process.
5 Close the Sysctl SQL database.
sysctl=# \q
6 Log off the database server.
exit
Creating the Microsoft SQL Server Database
These instructions describe how to create the sysctl database at sites that are using
Microsoft SQL Server. If your site is not using SQL Server, see step 10 on page 7 for
guidance in selecting the proper instructions for your site.
To create the database on Microsoft SQL Server:
1 Log on to the HP e3000 and transfer the SQL scripts to the database server. Use
the same values setup in the XFTPSYST.JCL file in step 7 on page 6.
:HELLO PGMR.QSSDEV
:FTP.ARPA.SYS
open <ip/sysname>
Installing the Migration Kit Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
9
2/2/2009
qssdba
<password>
cd <directory>
binary
put mszsys10.schmig sqlsys10.zip
quit

The remaining steps are done on Microsoft SQL Server.
2 Logon to the database server.
3 Use explorer to change to the directory on the server that you decided to use for the
migration files as discussed in “Setting Up the Microsoft SQL Database Server”
on page 30.
A fast way to get there is from the Start menu select Run and type the full path of
the directory. Although you may have changed to beta206 in the prior ftp step, the
actual directory may be inside the ftproot directory, such as C:\Inetpub\ftproot.
4 Extract the SQL script files from the zip file.
Double-click the file "sqlsys10.zip" and extract to the "C:\QSS\SQL scripts"
directory.
5 Connect to the SQL Server..
From the Start menu select Programs >> Microsoft SQL Server >> Query
Analyzer
Complete the Connect to SQL Server dialogue box similar the one below.
Installing the Migration Kit Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
10
2/2/2009
6 Create the Sysctl SQL database.
In the SQL Query Analyzer select
File >> Open
and open the file
C:\QSS\SQL scripts\sysctl.sql
Click the execute button in the toolbar to execute the query.
After the process completes, messages like the following display in the message
window:
Server: Msg 3701, Level 11, State 8, Line 1
Cannot drop the database 'sysctl', because it does not exist in
the system catalog.
The CREATE DATABASE process is allocating 0.63 MB on disk
'sysctl'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'sysctl_log'.
Type added.
Type added.
These messages are expected and are not considered errors for this process


Installing the Migration Kit Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
11
2/2/2009
7 Create the tables in the Sysctl SQL database.
In the SQL Query Analyzer select
File >> Open
and open the file
C:\QSS\SQL scripts\sqlsys10.sql
Click the execute button in the toolbar to execute the query
You will see many messages similar to:
Server: Msg 3701, Level 11, State 5, Line 49
Cannot drop the table '?????????', because it does not exist in
the system catalog.
These messages are expected and are not considered errors for this process.
8 Exit the Sysctl SQL database
In the SQL Query Analyzer, select:
File >> Exit
9 Log off the database server.

Converting SYSCTL Unloading and Converting SYSCTL
Beta Test 206: Converting SYSCTL from Image to SQL
12
2/2/2009
Converting SYSCTL
These instructions tell how to convert the SYSCTL database from Image to SQL. You
can load the converted data into either PostgreSQL or Microsoft SQL server.
To convert the SYSCTL database:
1 Unload the SYSCTL database on the HP e3000, as described in “Unloading and
Converting SYSCTL” on page 12.
2 Load the converted data into the Linux or Unix server at your site.
◊ If you are using PostgreSQL, follow the instructions in “Loading SYSCTL into
PostgreSQL” on page 14.
◊ If you are using Microsoft SQL Server, follow the instructions in “Loading
SYSCTL into Microsoft SQL Server” on page 15.
Unloading and Converting SYSCTL
These instructions tell how to unload the data in the SYSCTL database, convert the
Image data for loading into SQL, and transfer the conversion files to the database
server.
1 Unload the SYSCTL database.
◊ Before unloading the database, make all QSS/OASIS users log off.
This step requires exclusive access to the SYSCTL database. No one can be
using QSS/OASIS while the unload is in progress. All traditional users must log
off. If your site is using QSSControlCenter (QCC), make sure that all QCC
users are logged off and that you have shut down the servers for QCC.
◊ Type the following commands:
:HELLO MGR.QSSUSER
ENTER USER (MGR) PASSWORD: <password>
:STREAM SY10UNLD.JCL
◊ After the unload completes, you can allow QSS/OASIS users to log on again.
If your site is using QCC, restart the QCC servers so that QCC users can have
access to the SYSCTL database on the HP e3000.
Converting SYSCTL Unloading and Converting SYSCTL
Beta Test 206: Converting SYSCTL from Image to SQL
13
2/2/2009
2 Convert the SYSCTL database.
◊ Type the following command to convert the data:
:STREAM XSQLSYST.JCL
◊ After the conversion completes, log on to QCC and check the reports in Print
Manager. The last page summarizes converted datasets and counts for number
of errors and warnings.
You can do the following:
> Correct problems in the original Image database on the HP e3000. Repeat
Step 2 before converting the data again.
> Suppress warnings for data that is being converted correctly. If there are
numerous errors for data that is being converted correctly, you can edit the
XSQLPP.DATA file to turn off the warnings. Assign a "class" to a dataset
with the warning turned off. Repeat this step to get another report with the
error removed.
3 If needed, transfer conversion files to the database server.
This step is needed only if the XSQLSYST.JCL file is not set up so FTP automati-
cally transfers the conversion files to the Unix or Linux server.
:STREAM XFTPSYST.JCL
4 Load the converted data into the database server for SQL.
◊ If you are using PostgreSQL, see “Loading SYSCTL into PostgreSQL” on page
14.
◊ If you are using Microsoft SQL Server, see “Loading SYSCTL into Microsoft
SQL Server” on page 15.
IMPORTANT: If your site is using a platform other than PostgreSQL, review the control file
XSQLSY.DATA.QSSUSER to verify that it is set up properly, such as the needed
modifications to the Target Server (SQL) Settings section.
Converting SYSCTL Loading SYSCTL into PostgreSQL
Beta Test 206: Converting SYSCTL from Image to SQL
14
2/2/2009
Loading SYSCTL into PostgreSQL
These instructions tell how to load the SYSCTL database into PostgreSQL after you
have unloaded as described in “Unloading and Converting SYSCTL” on page 12.
To load a PostgreSQL database:
1 Complete the database conversion process described in “Converting Image Data-
bases” on page 12.
2 Log on to the sever for SQL databases at your site.
Type the following commands:
login as: qssdba
qssdba’s password: <password>
cd <directory>
Change to the directory that you selected for conversion files, when creating the
database servers, as described in “Setting Up the SQL Database Server” on page
26.
3 Remove any trailing blanks and the end-of-record character from the transferred
files.
◊ This step copies the conversion files and formats the data into the proper format
for loading into SQL.
The process creates copies of the uppercase XY files with names in lowercase.
The lowercase files are used for loading data into SQL. The uppercase files are
deleted unless you modify the XSQLSY.DATA control file to change this
default behavior.
◊ Type the following command:
. ./SYSEDCMD
A message like the following one displays for each dataset:
sed -e 's/\ / /g' -e 's/~ *$//' XYMUUS00 > xymuus00
4 Access the SQL database.
Type the following command:
psql sysctl
Converting SYSCTL Loading SYSCTL into Microsoft SQL Server
Beta Test 206: Converting SYSCTL from Image to SQL
15
2/2/2009
5 Remove the indexes.
Type the following command:
sysctl=# \i sqlsys10_di.sql
The terminal should display many DROP INDEX messages as the command exe-
cutes. Removing the indexes makes the bulk load go much faster.
6 Bulk load the data.
Type the following command:
sysctl=# \i SYSQLCMD
The screen should display a message like this for each dataset:
TRUNCATE TABLE
18:02:34 Loading app_user from xymuus00
Report errors as directed in “Getting Help” on page 2.
7 Create the indexes.
sysctl=# \i sqlsys10_ci.sql
8 Exit the sysctl SQL database.
sysctl=# \q
9 Log off the database server.
exit
Loading SYSCTL into Microsoft SQL Server
These instructions tell how to load the converted data for sites that are using
Microsoft SQL Server after you have completed the instructions in “Unloading and
Converting SYSCTL” on page 12.
To load SYSCTL into Microsoft SQL Server:
1 Log on to the database server.
NOTE: These instructions are for SQL Server 2000. SQL Server 2003 may have different
commands and dialog boxes.
Converting SYSCTL Loading SYSCTL into Microsoft SQL Server
Beta Test 206: Converting SYSCTL from Image to SQL
16
2/2/2009
2 Remove any trailing blanks and the end of record character from the transferred
files.
This step creates copies of the all uppercase XY files using the same name but
using all lowercase and the file extension.blk. The lowercase files will be loaded
into SQL. The uppercase files will be removed unless the default setting was
changed in the control file XSQLSY.DATA.
◊ From the Start menu select Run and type cmd.

◊ Type the following commands substituting the directory on the database server
you decided to use for the migration files as discussed in “Creating the
Microsoft SQL Server Database” on page 8.
cd <directory>
rename SYSEDCMD SYSEDCMD.bat
SYSEDCMD


Converting SYSCTL Loading SYSCTL into Microsoft SQL Server
Beta Test 206: Converting SYSCTL from Image to SQL
17
2/2/2009
◊ When the command executes, the window displays lines like the following for
each dataset that it processes:
sed -e "s/\ / /g" -e "s/~ *$//" XYMUUS00
1>\Inetpub\ftproot\beta206\xymuus00.blk
◊ After the command finishes, type the following command to close the window:
exit
3 Access the Sysctl SQL database.
◊ From the Start menu select Programs >> Microsoft SQL Server >> Query
Analyzer
◊ Complete the Connect dialogue box similar to the example below.

◊ Query Analyzer normally connects you to the master database. Use the data-
base drop-down list box in the toolbar to change to the sysctl database.

Converting SYSCTL Loading SYSCTL into Microsoft SQL Server
Beta Test 206: Converting SYSCTL from Image to SQL
18
2/2/2009
4 Remove the indexes to make the bulk load of data go faster.
◊ In the SQL Query Analyzer select File >> Open, then open the following file:
C:\QSS\SQL scripts\sqlsys10_di.sql
◊ Click the execute button in the toolbar to execute the query.
◊ When the command completes, the following message displays in the com-
mand window:
The command(s) completed successfully.
5 Bulk load the data.
◊ In the SQL Query Analyzer, select File >> Open, then open the file
SYSQLCMD.
This file is in the directory on the database server you decided to use for the
migration files as discussed in the document “Creating the Microsoft SQL
Server Database” on page 8.
◊ Click the execute button in the toolbar to execute the query.
Lines like the following should display for each data set as it is processed:
18:12:34 Loading app_user from xymuus00
Report any errors to QSS by cutting the output from this command and pasting
it into a message on the QSS Customer Forum for "Migration - Databases."
Before posting to the forum, remove any confidential data, such as social secu-
rity numbers or phone numbers. For details, see “Getting Help” on page 2.
6 Create the indexes.
In the SQL Query Analyzer select File >> Open and open the file
C:\QSS\SQL scripts\sqlsys10_ci.sql
◊ Click the execute button in the toolbar to execute the query.
NOTE: You may need to change the 'Files of type' to 'All Files' to see SYSQLCMD.



Converting SYSCTL Loading SYSCTL into Microsoft SQL Server
Beta Test 206: Converting SYSCTL from Image to SQL
19
2/2/2009
◊ The message window displays this message when the process is done:
The command(s) completed successfully.
7 Exit the Sysctl SQL database
In the SQL Query Analyzer select File >> Exit.
8 Log off the database server.
Creating the Print File (PQUE) Database Creating the PostgreSQL Database
Beta Test 206: Converting SYSCTL from Image to SQL
20
2/2/2009
Creating the Print File (PQUE) Database
Follow the version of the instructions appropriate for your site.
♦ If you are using PostgreSQL, follow the instructions in “Creating the PostgreSQL
Database” on page 20.
♦ If you are using Microsoft SQL Server, follow the instructions in “Creating the
Microsoft SQL Server Database” on page 21.
Creating the PostgreSQL Database
These instructions describe how to create the sysctl database at sites that are using
PostgreSQL. If your site is not using PostgreSQL, see step 10 on page 7 for guidance
in selecting the proper instructions for your site.
To create the PostgreSQL database:
1 Log on to the HP e3000 and transfer the SQL scripts to the database server. Use
the same values setup in the XFTPSYST.JCL file in step 7 on page 6.
:HELLO PGMR.QSSDEV
:FTP.ARPA.SYS
open <ip/sysname>
qssdba
<password>
cd <directory>
binary
put pgtpqu10.schmig sqlpqu10.tar
quit
All remaining steps are done on the Linux or HP-UX server.
2 Log on to the database server.
Log on as the qssdba user and change to the directory on the server that you
decided to use for the migration files, as described in “Setting Up the PostgreSQL
Database Server” on page 26.
login as: qssdba
qssdba's password: <password>
cd <directory>
Creating the Print File (PQUE) Database Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
21
2/2/2009
3 Extract the SQL script files from the tar file.
tar -xvf sqlpqu10.tar
./sqlpqu10.sql
./sqlpqu10_ci.sql
./sqlpqu10_di.sql
4 Create the Pque SQL database.
createdb -E SQL_ASCII pque
CREATE DATABASE
psql pque
pque=# \i sqlpqu10.sql
The screen will display many massages like:
ERROR: table "????????" does not exist
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index...
CREATE TABLE
CREATE INDEX
These messages are a normal part of the process.
5 Add the print file odometer record.
pque=# INSERT INTO odometer VALUES ( ‘00’, 1);
INSERT 0 1
6 Close the Pque SQL database.
pque=# \q
7 Log off the database server.
exit
Creating the Microsoft SQL Server Database
These instructions describe how to create the sysctl database at sites that are using
Microsoft SQL Server. If your site is not using SQL Server, see step 10 on page 7 for
guidance in selecting the proper instructions for your site.
To create the database on Microsoft SQL Server:
1 Log on to the HP e3000 and transfer the SQL scripts to the database server. Use
the same values setup in the XFTPSYST.JCL file in step 7 on page 6.
:HELLO PGMR.QSSDEV
:FTP.ARPA.SYS
Creating the Print File (PQUE) Database Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
22
2/2/2009
open <ip/sysname>
qssdba
<password>
cd <directory>
binary
put mszpqu10.schmig sqlpqu10.zip
quit

The remaining steps are done on Microsoft SQL Server.
2 Logon to the database server.
3 Use explorer to change to the directory on the server that you decided to use for the
migration files as discussed in “Setting Up the Microsoft SQL Database Server”
on page 30.
A fast way to get there is from the Start menu select Run and type the full path of
the directory. Although you may have changed to beta206 in the prior ftp step, the
actual directory may be inside the ftproot directory, such as C:\Inetpub\ftproot.
4 Extract the SQL script files from the zip file.
Double-click the file "sqlpqu10.zip" and extract to the "C:\QSS\SQL scripts"
directory.
5 Connect to the SQL Server..
From the Start menu select Programs >> Microsoft SQL Server >> Query
Analyzer
Complete the Connect to SQL Server dialogue box similar the one below.
Creating the Print File (PQUE) Database Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
23
2/2/2009
6 Create the Pque SQL database.
In the SQL Query Analyzer select
File >> Open
and open the file
C:\QSS\SQL scripts\pque.sql
Click the execute button in the toolbar to execute the query.
After the process completes, messages like the following display in the message
window:
Server: Msg 3701, Level 11, State 8, Line 1
Cannot drop the database 'pque', because it does not exist in the
system catalog.
The CREATE DATABASE process is allocating 0.63 MB on disk 'pque'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'pque_log'.
Type added.
Type added.
These messages are expected and are not considered errors for this process.


Creating the Print File (PQUE) Database Creating the Microsoft SQL Server Database
Beta Test 206: Converting SYSCTL from Image to SQL
24
2/2/2009
7 Create the tables in the Pque SQL database.
In the SQL Query Analyzer select
File >> Open
and open the file
C:\QSS\SQL scripts\sqlsys10.sql
Click the execute button in the toolbar to execute the query
You will see many messages similar to:
Server: Msg 3701, Level 11, State 5, Line 49
Cannot drop the table '?????????', because it does not exist in
the system catalog.
These messages are expected and are not considered errors for this process.
8 Add the print file odometer record.
Press the New Query button and type the following in the new query window
that opened.
USE PQUE;
INSERT INTO odometer
VALUES ( '00', 1 );
Click the execute button in the toolbar to execute the query
9 Exit the Pque SQL database
In the SQL Query Analyzer, select:
File >> Exit
10 Log off the database server.


Migrating DISTRICT.PUB The DISTRICT.PUB File
Beta Test 206: Converting SYSCTL from Image to SQL
25
2/2/2009
Migrating DISTRICT.PUB
The DISTRICT.PUB File
The DISTRICT.PUB file defines the names of each district. Many reports use this file
as the source for the district name that prints in the page headings. The first line in the
file corresponds to district 1, the second to district 2, and so on.
Launching the Migration Job
The JCL file XDIFTPST.JCL coverts the DISTRICT.PUB file on the HP e3000 to the
proper format for Linux, then uses FTP to copy to the converted file to the Linux
server.
To migrate the DISTRICT.PUB file:
1 Log on to the HP e3000 as MGR.QSSUSER.
2 Edit the XDIFTPST.JCL file.
Replace the following with appropriate logon information to the SQL database
server:
◊ <ip/sysname> with either the ip address or system name of the server.
◊ <password> with the password for the qssdba user on the server.
3 Stream XDIFPTST.JCL.
Setting Up the SQL Database Server Setting Up the PostgreSQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
26
2/2/2009
Setting Up the SQL Database Server
These instructions tell how to set up the database server for the database platform that
your site has chosen.
♦ Do this step only once before your first database migration. Once you have set up
the database server, you do not need to so again before doing additional data
migrations.
♦ This document includes the instructions for all platforms that QSS supports. How-
ever, each site sets up only one database server. Follow the instructions for your
chosen platform.
◊ If your site is using PostgreSQL, follow the instructions in "Setting Up the Post-
greSQL Database Server" on this page.
◊ If your site is using Microsoft SQL Server, follow the instructions in “Setting
Up the Microsoft SQL Database Server” on page 30.
Setting Up the PostgreSQL Database Server
These instructions are for sites that are using the PostgreSQL database. They tell how
to set up the server so that you can manage QSS databases.
To set up the PostgreSQL database server:
1 Verify that FTP is working on your server.
Before you can install the database server, an FTP server must be installed and
working.
◊ For Linux, you can use any of the FTP servers available in your Linux
distribution.
◊ For HP-UX, FTP should already be installed.
Type the following command to verify that an FTP server is installed and running:
ftp localhost
◊ If you get output similar to the following, your FTP server is working properly.
Press control-C to exit the FTP server.
Connected to localhost.
220 FTP server
Name (localhost:root):
Setting Up the SQL Database Server Setting Up the PostgreSQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
27
2/2/2009
◊ If you get output similar to the following, then either the FTP server is not
enabled, or the ftp server is not installed. Type quit to exit FTP.
ftp: connect: Connection refused
ftp> quit
To correct this problem install an FTP server, if one is not already installed, and
enable it. Hint: most FTP servers run as a network service under inetd.
2 Log on to the server as root and create the qssdba user. This user is the database
administrator for all QSS databases.
Type the following commands or use a GUI tool to create the new user.
useradd -c 'QSS DB Admin' -d /home/qssdba qssdba
passwd qssdba
New password: <password>
Retype new password: <password>
3 Choose the directory you want to use to contain all the migration files used in load-
ing the SQL database.
◊ Here are some hints for identifying a file system with enough disk space.
An average size district or small COE will typically require 2-4 Gb, large dis-
tricts and medium county offices will typically require 5-10 Gb, and a large
COE will typically require in excess of 10 Gb.
Depending on how much space is available in the file system used by the home
directory, you could create a directory for this in the home directory for the user
qssdba which is /home/qssdba. Some distributions and/or system administra-
tors who installed Linux or HP-UX create a separate file system for /home.
Others just allow it to be included in the / directory.
You can use the command df to determine what file systems are in use and how
much space is available (HP-UX uses bdf). If you see a separate entry for
/home then it has its own file system. If you don't see a separate entry then it is
included under the / directory.
◊ If /home has sufficient room and you want to create a directory in qssdba's
home use the following commands while logged on as qssdba. Note: you can
use any name you want, but this example uses beta206.
cd /home/qssdba
mkdir beta206
Setting Up the SQL Database Server Setting Up the PostgreSQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
28
2/2/2009
◊ If either you don't want to use /home/qssdba or you can't because of a lack or
available space, then you can create another file system that is large enough and
temporarily mount it under the /mnt directory. Since there are many ways to
allocate disk space those commands are not included in this example, but once
you have allocated the appropriate amount of physical disk space you can use
the following commands while logged on as root. Note: you can use any name
you want, but this example uses beta206.
cd /mnt
mkdir beta206
chown qssdba:users beta206
Then you can use the mount command to mount the file system. Type man
mount for help on this command.
◊ Remember this directory, either /home/qssdba/beta206 or /mnt/beta206, as
you will need to refer to it several times during the installation. You are wel-
come to use any directory you like on the Linux server the only exceptions are
the user qssdba must have write access to the directory and it must be large
enough to contain all the work files used by the migration process.
Here are a couple of books that may be helpful for managing file systems:
◊ Linux in a Nutshell, O'Reilly & Associates
◊ Running Linux, O'Reilly & Associates
4 PostgreSQL must be installed and the postmaster background process must be
running.
◊ PostgreSQL version 8.2 or later is required.
You may use an earlier version for the Beta test, but you will need to upgrade
before the actual migration. On most Linux distributions, a version of
PostgreSQL is available or can be built from source. On HP-UX, you must
build from source. For specific instructions, see “HOW-TO Build PostgreSQL
from Source” on page 35.
◊ When you install PostgreSQL, make sure the configured data directory has suf-
ficient available disk space.
By default PostgreSQL uses the directory /var/lib/pgsql/data/ to store the data-
bases. You should verify with your system administrator which directory is
actually being used. Use the df command (hp-ux used bdf) as described in step
1 to determine the available space. Depending on your system configuration
the PostgreSQL data directory (/var/lib/pgsql/data) may be a separate entry with
it's own file system, be included in a separate file system used by the /var direc-
Setting Up the SQL Database Server Setting Up the PostgreSQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
29
2/2/2009
tory, or be included under the / directory. You should allow at least 1 and a half
times the amount of space that the migration work files use as discussed in step
1.
If you don't have enough space under the appropriate file system then you may
want to consider creating a separate file system just for the PostgreSQL data
directory that is large enough.
◊ To verify the background process is running type the following command:
ps -ef | grep postmaster
The screen should display a line like this:
postgres 1234 1 0 Sep 20 ? 0:04 /usr/local/pgsql/bin/postmaster
◊ To verify the version of PostgreSQL, type the following command:
psql --version
The output looks like this:
psql (PostgreSQL) 8.2.4
contains support for command-line editing
Here are some references that may assist you in installing and managing
PostgreSQL.
◊ www.postgresql.org
◊ Stinson, Barry, PostgreSQL: Essential Reference, New Riders Publishing
◊ Douglas, Korry & Susan, PostgreSQL, Sams Publishing
◊ Practical PostgreSQL, O'Reilly & Associates
5 Change to the PostgreSQL user.
The PostgreSQL user is the default PostgreSQL user created when PostgreSQL
was installed. This user is the administrator and can create other PostgreSQL
users.
su - postgres
Setting Up the SQL Database Server Setting Up the Microsoft SQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
30
2/2/2009
6 Grant qssdba the appropriate authority to manage PostgreSQL databases.
createuser qssdba
Shall the new role be a superuser? (y/n) y
CREATE ROLE
7 Upon successful completion of the above steps, you can create the sysctl database,
as described in “Installing the Migration Kit” on page 4.
Setting Up the Microsoft SQL Database Server
These instructions are for sites that are using Microsoft SQL Server. They tell how to
set up the server so you can manage QSS databases.
To set up Microsoft SQL Server:
1 FTP service must be installed and enabled to permit transferring files from the
HPe3000 to the server running SQL Server. You will need the user and password
of a user setup to FTP files later.
◊ To verify the FTP service is installed and running do the following while logged
on to your server.
From the Start menu select Run and open cmd.

In the command line window type the command:
ftp localhost
◊ If you get output similar to the following, then your FTP service is working
properly. Press control-C to exit the FTP server and type exit to close the com-
mand window.
NOTE: These instructions are for SQL Server 2000. SQL Server 2005 may have different
commands and dialog boxes.

Setting Up the SQL Database Server Setting Up the Microsoft SQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
31
2/2/2009
Connected to localhost.
220 Microsoft FTP Service (Version 5.0)
User (localhost:(none)):
◊ If you get output similar to the following then either the ftp service is not
enabled or the ftp service is not installed. Type quit to exit ftp.
ftp: connect: Connection refused
ftp> quit
FTP can be installed from the Control Panel. Double-click Add/Remove Pro-
grams, then click Add/Remove Windows Components. It is available under
details for Internet Information Services (IIS).
FTP can be configured from the Control Panel. Double-click Administrative
Tools, double-click Internet Information Services, expand your server, and
right-click Default FTP Site and select properties.
2 Install sed for Windows.
Sed is a utility program for editing files from a command line. You can find it at
http://gnuwin32.sourceforge.net/packages/sed.htm
◊ After installing this program, make sure the sed.exe program can be found in
your path.
From the Start menu select Run and open cmd.
In the command line window type the command:
sed
◊ If it is installed and in your path, you should see output which begins similar to
the following
Usage: sed [OPTION]... {script-only-if-no-other-script} [input-
file]...
◊ If you get output similar to the following then either sed is not installed or can-
not be found because the program directory is not in your path.
'sed' is not recognized as an internal or external command,
operable program or batch file.
If the GNU win32 version is installed it should be installed as:
Setting Up the SQL Database Server Setting Up the Microsoft SQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
32
2/2/2009
C:\ Program Files\GnuWin32\bin\sed.exe
It can be temporarily added to your path by the command:
path %path%;C:\Program Files\GnuWin32\bin\
This command can be permanently added to the path environment variable
from the Control Panel. Double-click System, click the Advanced tab, click
the Environment Variables button, and edit the path variable. Add the follow-
ing to the end, being careful not to change or delete anything else.
;C:\Program Files\GnuWin32\bin\
3 Choose the directory to contain all the migration files used in loading the SQL
database.
An average size district or small COE will typically require 2-4 Gb, large districts
and medium County Offices will typically require 5-10 Gb, and a large COE will
typically require in excess of 10 Gb.
Most likely your ftp configuration will put the directories available for writing into
an 'ftp jail' such as C:\Inetpub\ftproot. If this is the case then no user connected to
the ftp service can change to a directory outside of the 'ftp jail'. In this case you
may want to create the directory C:\Inetpub\ftproot\beta206 to receive these files.
To use this directory while logged on to the server requires the full path C:\Inet-
pub\ftproot. To use this directory while connecting via ftp only requires \beta206.
If your ftp service does not 'jail' the user then you are free to use any directory you
want on the server such as C:\beta206 or C:\QSS\beta206 for example.
Remember the directory used as you will need to refer to it several times during
the installation. The disk drive must have enough free space to contain all the
work files used by the migration process.
4 SQL Server must be installed and the service running.
You can verify this by running the SQL Server Enterprise Manager. From the Start
menu select Programs >> Microsoft SQL Server >> Enterprise Manager.
Setting Up the SQL Database Server Setting Up the Microsoft SQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
33
2/2/2009
The SQL Server Enterprise Manager should open and you can now administer
your SQL server.
5 You must have a SQL user called qssdba with the appropriate authority to manage
SQL databases.
To create this user expand Microsoft SQL Servers, SQL Server Group, (local),
and Security to find Logins. Right click on Logins and select New Login.
On the General tab, type the name qssdba, check the SQL Server Authentica-
tion box and enter a password.
Change to the Server Roles tab and check Database Creators and Bulk Insert
Administrators (these are the minimum security privileges required, but you
could check System Administrators instead to give qssdba all security privi-
leges).
IMPORTANT: SQL Server must be capable of running a case sensitive database. The
default is case insensitive. For SQL Server 2000 and later this can be specified at the
database level when the database is created using the appropriate collation. This is the
approach used by the installation scripts for building the database. For older versions the
collation is specified during installation and is set at the system level. It can be changed
which requires rebuilding the master database but this is a difficult and complicated process
and instructions to do this are not be provided in this document.

Setting Up the SQL Database Server Setting Up the Microsoft SQL Database Server
Beta Test 206: Converting SYSCTL from Image to SQL
34
2/2/2009
6 You must have a utility program installed that can read and extract files from zip
archives.
Upon successful completion of the above steps, you can begin performing the steps in
“Installing the Migration Kit” on page 4.


HOW-TO Build PostgreSQL from Source Prerequisites
Beta Test 206: Converting SYSCTL from Image to SQL
35
2/2/2009
HOW-TO Build PostgreSQL from Source
This document tells how to build version 8.2 of the PostgreSQL database server for
both the Linux and HP-UX platforms.
♦ Most Linux distributions include PostgreSQL. Install the version that comes with
your distribution and see what version it is. If it is version 8.2, you do not need to
follow these instructions. If it is an earlier version, you need to build version 8.2
from source to upgrade to the version required by QSS.
♦ For HP-UX, you need to build the PostgreSQL database from source.
Most of the instructions apply to both Linux and HP-UX. However, there are some
instructions that apply only to HP-UX. They are noted with "HP-UX Only" in the left
margin.
Many of the tools required for HP-UX are available on the internet from the HP-UX
Porting and Archive Centre (http://hpux.connect.org.uk). Find the tools you need and
download the appropriate "gzipped binary depot" for your version of HP-UX, unzip
them on your HP-UX server using gunzip, and install them using the following com-
mand: "swinstall -s <full path and name of depot file>". To
see if a package is already installed use the command "swlist". All of those refer-
enced below will appear at the bottom of the list after "Product(s) not contained in a
Bundle".
Prerequisites
GNU Make - Other versions of "make" will not work.
♦ For most versions of Linux, the "make" command is actually GNU make. It
should be available on your distribution and is most likely already installed.
♦ For HP-UX the "make" command is HP make so you need to download and install
GNU make. PostgreSQL recommends using version 3.76.1 or higher of GNU
make.
ISO/ANSI C compiler
♦ For Linux, GCC should already be installed. It should be available on your distri-
bution and is most likely already installed.
♦ For HP-UX the C compiler provided is HP C and doesn't work so you need to
download and install GCC. HP currently bundles both the GCC compiler and
binutils into a single package. They were previously available separately and both
were required.
HP-UX
Only
HOW-TO Build PostgreSQL from Source Notes
Beta Test 206: Converting SYSCTL from Image to SQL
36
2/2/2009
GNU Readline library - This provides command line editing and command history in
the PostgreSQL interactive terminal (psql). This is recommended but if you choose
not to use this then you will need to add the option "--without-readline" to your "con-
figure" command.
♦ For Linux you will need both the "readline" and "readline_devel" packages from
your distribution.
♦ For HP-UX you will need to download and install the "readline" package.
Zlib library - This provides compression to the "pg_dump" command. If the library is
not installed then you will need to add the option "--without-zib" to your "configure"
command.
♦ For Linux, you will need both the "zlib" and "zlib_devel" packages from your
distribution.
♦ For HP-UX you will have to download and install this if you want this feature.
Notes
Keep the following in mind as you follow these instructions.
♦ Please read all steps carefully before beginning.
♦ You can modify any of the example commands to fit your particular practices and
standards for installing software as required.
♦ For this document we will be using PostgreSQL 8.2.4 as an example and will be
installing into the default PostgreSQL directories which begin with
"/usr/local/pgsql". At the end will be a list of the same commands installing into
"/opt/postgresql" which you could use instead of the defaults.
Instructions for Building from Source
These instructions go through each step in building PostgreSQL from source. Read
through all of the instructions before you start to follow them.
To build from source:
1 Download and Unpack PostgreSQL
Download and unpack the PostgreSQL source from their website
(http://www.postgresql.org). You can download directly from your web browser
and ftp to your SQL database server, or connect from your SQL database server
directly to any of their ftp servers. Use ".tar.gz" files. Choose and appropriate
location on your server for the source. If you intend on deleting the source files
after installing then you may want to use the "/tmp". If you want to keep the files
HOW-TO Build PostgreSQL from Source Instructions for Building from Source
Beta Test 206: Converting SYSCTL from Image to SQL
37
2/2/2009
around in case you need to recompile with different options later then use a loca-
tion such as "/opt/postgresql/source" as a generic location or you can use your
home group.
Below is an example of downloading the source directly to the SQL database
server from their ftp server into the directory "/opt/postgresql/source" which is
assumed to not already exist. If the ftp server is busy there are other mirror servers
beginning with ftp2, ftp3, ftp4 and so on. Note the directory structure may change
over time but by using the "dir" and "cd" commands you can navigate your way
thru the file system to find what you want. This example requires the root user
because it creates a new directory in "/opt". Root remains the owner of these files.
# mkdir /opt/postgresql
# mkdir /opt/postgresql/source
# cd /opt/postgresql/source
# ftp ftp.us.postgresql.org
Connected to ftp.us.postgresql.org.
Name (ftp.us.postgresql.org:root): anonymous
230 Anonymous user logged in
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> cd /pub/mirrors/postgresql/source/v8.2.4
250 OK. Current directory is
/pub/mirrors/postgresql/source/v8.2.4
ftp> get postgresql-8.2.4.tar.gz
ftp> quit
Unzip and extract the file you have just downloaded:
◊ For Linux, type these commands.
# tar -xzf postgresql-8.2.4.tar.gz
# cd postgresql-8.2.4
◊ For HP-UX, type these commands.
# gzcat postgresql-8.2.4.tar.gz | tar -xf -
# cd postgresql-8.2.4
Note that once you have verified the files unpacked correctly you can delete the
"/opt/postgresql/source/postgresql-8.2.4.tar.gz" file.
2 Configuration
The first step is to use the configure script to set the desired configuration options.
The default location for the install is "/usr/local/pgsql". You should add
"/usr/local/pgsql/bin" to the PATH and "/usr/local/pgsql/man" to the MANPATH of
HOW-TO Build PostgreSQL from Source Instructions for Building from Source
Beta Test 206: Converting SYSCTL from Image to SQL
38
2/2/2009
any user who will be using PostgreSQL including postgres and qssdba which you
will create later. If desired this location can be changed using the "--pre-
fix=<directory>".configure option.
# ./configure
This may take one or two minutes to complete.
If the configure script ends with "configure: creating ./config.status" and several
"config.status:" lines it has completed successfully and you can continue. If it
doesn't you need to correct the error and execute the script again.
If the configure script ends with "configure: error: readline library not found" then
you should either install the "readline" package or use the "--without-readline"
option, for example
"./configure --without-readline".
If the configure script ends with "configure: error: zlib library not found" then you
should either install the "zlib" package or use the "--without-zlib" option, for
example
"./configure --without-zlib".
3 Build the PostgreSQL binaries.
Type the following command for Linux:
# make
Type the following command for HP-UX:
# gmake
If you get the error shown below then GNU make is not installed or could not be
found in your path.
You must use GNU make to build PostgreSQL.
*** Error exit code 1
The GNU make-3.80 depot installs make as "/usr/local/bin/gmake". The directory
"/usr/local/bin" must be in your PATH in order to find GNU make.
NOTE: The next few steps require the root user so switch to it if necessary. (Previous steps
could have been done as any user in that user's home group if desired).
HP-UX
Only
HOW-TO Build PostgreSQL from Source Instructions for Building from Source
Beta Test 206: Converting SYSCTL from Image to SQL
39
2/2/2009
This step may take 5-15 minutes to complete.
4 Install the Files
Type the following command for Linux:
# make install
Type the following command for HP-UX:
# gmake install
This may take 15 seconds to 1 minute to complete.
5 Make the PostgreSQL Data Director
If you are using LVM you should consider creating a new logical volume called
'postgres' to hold the data in a separate file system so the root (/) directory doesn't
fill up. The PostgreSQL documentation suggests using "/usr/local/pgsql/data".
Some Linux distributions use "/var/lib/pgsql/data". Remember the directory you
choose you need because you need to reference it later.
If you use PostgreSQL's suggestion then the /usr/local/pgsql directory already
exits.
# mkdir /usr/local/pgsql/data
Or, if you use an alternative directory then you'll probably have to create the direc-
tory preceding data too. For example:
# mkdir /var/lib/pgsql
# mkdir /var/lib/pgsql/data
If you are using LVM and want to create a new logical volume for this file system
now is the time to do this. For HP-UX use SAM to aid in this process. For HP-UX
the VxFS file system creates a directory called "lost+found" that will be in your
/usr/local/pgsql/data directory once you have mounted the logical volume. This
creates a conflict with the "initdb" command used later so you should temporarily
remove the "lost+found" directory
# rmdir /usr/local/pgsql/data/lost+found
6 Add postgres group and postgres user.
This user is primarily used by the background daemon process. For Linux add the
"-r" option to both commands before "postgres" to create these using "system" ids.
HP-UX
Only
HOW-TO Build PostgreSQL from Source Instructions for Building from Source
Beta Test 206: Converting SYSCTL from Image to SQL
40
2/2/2009
# groupadd postgres
# useradd -c"PostgreSQL database" -g postgres postgres
Note that no password has been set and without a password this user cannot logon.
The only access to this user is by root switching to it. You can add a password if
desired but it is not necessary.
7 Make postgres the owner of the data directory.
# chown postgres:postgres /usr/local/pgsql/data
8 Switch to the postgres user and initialize the database.
Use the directory from step 5 on page 39 after the -D switch.
# su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -E SQL_ASCII
The data directory must be readable only by postgres (permission=700) and
shouldn't be changed with chmod to be less restrictive or else the postmaster pro-
cess will terminate with an error. The initdb command will set the security of the
data directory for you initially.
9 Start the postmaster background daemon.
Note the use of "\" to continue the command on the next line. The command could
have been entered on a single line if you don't mind the terminal wrap around.
Feel free to use any directory and name you want for the log file so long as you can
find it later to check it from time to time for problems.
# /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data \
-l /usr/local/pgsql/postmaster.log -w start
10 Test the background process is running by using the PostgreSQL interactive termi-
nal to get a list of the available databases.
# /usr/local/pgsql/bin/psql -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(2 rows)
11 To shutdown the postmaster background daemon you can use the command.
# /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -s stop
HOW-TO Build PostgreSQL from Source Instructions for Building from Source
Beta Test 206: Converting SYSCTL from Image to SQL
41
2/2/2009
12 If using LVM on HP-UX with a VxFS file system, put back the "lost+found" direc-
tory removed earlier.
# su -
# mklost+found /usr/local/pgsql/data
13 To automatically have the postmaster background daemon start and stop when you
boot and shutdown your server, you need to create an init.d script. You then need
to add symbolic links in the appropriate rc#.d directories so it starts for the appro-
priate run levels.
◊ For Linux they are in "/etc". The build from source process creates these files
for you.
◊ For HP-UX these directories exist in "/sbin".
A sample script and the symbolic links have been provided for HP-UX users
which can be found on the HPe3000 at PGHPUX.TARMIG. FTP this as a
binary file to your SQL database server and untar the file.
On the HP e3000
hello pgmr.qssdev
ftp.arpa.sys
open <server> and enter an appropriate user and password.
binary
cd /tmp
put PGHPUX.TARMIG pghpux.tar
quit
On the HP-UX server
su - root
tar -xvf /tmp/pghpux.tar
rm /tmp/pghpux.tar
You may need to edit the variables that tell the script what your PostgreSQL prefix
is and full name of the log file.
If you want to create these scripts manually, see “Creating Scripts Manually” on
page 42.
HP-UX
Only
HP-UX
Only
HOW-TO Build PostgreSQL from Source Creating Scripts Manually
Beta Test 206: Converting SYSCTL from Image to SQL
42
2/2/2009
Creating Scripts Manually
You can create your own script based on the "template" or "skeleton" file provided
with your distribution found in the init.d directory.
Once the script is finish and tested create the symbolic links.
For Linux, use the following commands (Note that SuSE Linux use a standard such
that the sum of the K and S numbers must equal 22)
# cd /etc/init.d/rc3.d
# ln -s K12postgres -> ../postgres
# ln -s S10postgres -> ../postgres
# cd /etc/init.d/rc3.d
# ln -s K12postgres -> ../postgres
# ln -s S10postgres -> ../postgres

For HP-UX use the following commands (Note HP-UX uses a standard such that the
sum of the K and S numbers must equal 1000)
# ln -s /sbin/init.d/postgres /sbin/rc1.d/K100postgres
# ln -s /sbin/init.d/postgres /sbin/rc2.d/S900postgres
Commands for Alternative Installation in "/opt" Directories
$ su - root
# mkdir /opt/postgresql
# mkdir /opt/postgresql/source
# cd /opt/postgresql/source
FTP the tar.gz source file into /opt/postgresql/source as previously shown
# gzcat postgresql-8.2.4.tar.gz | tar -xf -
# rm postgresql-8.2.4.tar.gz
# cd postgresql-8.2.4
# ./configure --prefix=/opt/postgresql
# gmake
# gmake install
# mkdir /var/opt/postgresql
If using LVM create a logical volume called postgres and mount it at
/var/opt/postgresql.
For Linux add the "-r" option to the groupadd and useradd commands that follow.
# groupadd postgres
# useradd -d /var/opt/postgresql -c"PostgreSQL database" \
-g postgres postgres
HOW-TO Build PostgreSQL from Source Commands for Alternative Installation in "/opt" Directories
Beta Test 206: Converting SYSCTL from Image to SQL
43
2/2/2009
# chown postgres:postgres /var/opt/postgresql
# su - postgres
$ mkdir /var/opt/postgresql/log
$ touch /var/opt/postgresql/log/postmaster.log
$ chmod 640 /var/opt/postgresql/log/postmaster.log
$ mkdir /var/opt/postgresql/data
$ /opt/postgresql/bin/initdb -D /var/opt/postgresql/data \
-E SQL_ASCII
$ /opt/postgresql/bin/pg_ctl -D /var/opt/postgresql/data \
-l /var/opt/postgresql/log/postmaster.log -s -w start
You should add /opt/postgresql/bin to the PATH variable and add /opt/postgresql/man
to the MANPATH variable for the postgres user and any other users of PostgreSQL.
This setup doesn't have the same lost+found problem the default data directory has
because we mount the logical volume one level earlier at /var/opt/postgresql instead
of using the data directory.
This setup also places the log file in a different directory than the data so it can be
read by anyone else who belongs to the postgres group.