# Creating the PostgreSQL Database - Marin County Office of Education

Data Management

Dec 16, 2012 (5 years and 5 months ago)

345 views

Quintessential School Systems
Beta Test 206:
Converting SYSCTL
from Image to SQL
© Quintessential School Systems (QSS), 2009
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

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,
♦ 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
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
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
: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
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.
Type the following command:
sysctl=# \i SYSQLCMD
The screen should display a message like this for each dataset:
TRUNCATE TABLE
7 Create the indexes.
sysctl=# \i sqlsys10_ci.sql
8 Exit the sysctl SQL database.
sysctl=# \q
9 Log off the database server.
exit
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.
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

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