Database software must be installed and configured properly before you can install and use the Accounting and Reporting Console. This section describes how to set up the database software using either a PostgreSQL database, a MySQL database, or an Oracle database.
Detailed information on the PostgreSQL database software can be found in the Postgres documentation.
Once the PostgreSQL software is installed, you can start the database server.
Download, compile, and install the PostgreSQL database software. Create a user account to own the database processes. Usually, this user is postgres. Add the PostgreSQL bin directory and necessary LD_LIBRARY_PATH settings to your environment.
Create a home directory for the postgres user.
In this example, the home directory is /space/postgres/data.
% mkdir -p /space/postgres/data % useradd -d /space/postgres postgres % chown postgres /space/postgres/data % su - postgres |
Continue as described in the PostgreSQL documentation to set up a database.
> initdb -D /space/postgres/data creating directory /space/postgres/data... ok creating directory /space/postgres/data/base... ok creating directory /space/postgres/data/global... ok creating directory /space/postgres/data/pg_xlog... ok creating directory /space/postgres/data/pg_clog... ok creating template1 database in /space/postgres/data/base/1... ok creating configuration files... ok initializing pg_shadow... ok enabling unlimited row size for system tables... ok initializing pg_depend... ok creating system views... ok loading pg_description... ok creating conversions... ok setting privileges on built-in objects... ok vacuuming database template1... ok copying template1 to template0... ok Success. You can now start the database server using: postmaster -D /space/postgres/data or pg_ctl -D /space/postgres/data -l logfile start |
Make the following changes to the pg_hba.conf file.
This change permits unrestricted and password free access to the database superuser postgres but requires md5 encrypted passwords for all other database users. Replace nnn.nnn.nnn with your subnet address without the trailing 0. You also can add access rules on a per-host basis by adding similar lines with host IP addresses.
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all postgres trust local all all md5 # IPv4-style local connections: #host all all nnn.nnn.nnn.0 255.255.255.0 md5
Make the following changes to the postgresql.conf file, to enable TCP/IP access from other hosts.
Ensure that the value of shared_buffers is at least twice the value of max_connections.
tcpip_socket = true max_connections = 40 (increase if necessary)
Start the database.
In this example, -i enables TCP/IP communication, and -S is for silent mode.
> postmaster -S -i |
Verify the installation.
As the postgres user, try the following commands:
% su - postgres > createuser -P test_user Enter password for new user: Enter it again: Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) n CREATE USER > createdb -O test_user -E UNICODE test CREATE DATABASE |
Execute commands as the database super user.
> psql test Welcome to psql 7.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit |
test=# create table test (x int, y text); CREATE TABLE test=# insert into test values (1, 'one'); INSERT 16982 1 test=# insert into test values (2, 'two'); INSERT 16983 1 test=# select * from test; x | y ---+------ 1 | one 2 | two (2 rows) test=# \q > psql -U test_user test Password: Welcome to psql 7.4.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=> |
After you have successfully tested your database, you should proceed to the next task, How to Set Up a PostgreSQL Database.
Log in as the database superuser, for example, postgres.
# su - postgres |
Create the owner of the database.
> createuser -P arco_write Enter password for new user: Enter it again: Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) n CREATE USER |
Create the database for accounting and reporting.
> createdb -O arco_write arco CREATE DATABASE |
Create a database user for reading the database.
If you do not use the arco_read user, you need to modify the privileges script that is used in the next step.
> createuser -P arco_read Enter password for new user: Enter it again: Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER |
Ask your database administrator for an instance of an Oracle database.
You need two database users for this instance, arco_write and arco_read. The arco_write user must be able to create or alter tables, views, and indexes. Privileges for the arco_read user are set later by an SQL script.
Ask your database administrator for the connection string to the database.
The Accounting and Reporting Console uses views, As a result, the console supports MySQL database version 5.0.x and higher. For more information on the MySQL database software, see the MySQL documentation .
In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory. Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. Therefore, database and table names are case sensitive in most varieties of UNIX, and not case sensitive in Windows.
Download the appropriate MySQL software for your system from http://www.mysql.com.
The standard installation directory for UNIX systems is /usr/local/mysql. If you install the software into a different directory, you have to change the settings for the scripts provided in the package.
ARCo is a Java web-based application and needs the JavaTM DataBase connectivity (JDBCTM) driver for converting JDBC calls into the network protocol used by the MySQL database. You can download the JDBC driver from http://www.mysql.com/products/connector.
Create a symbolic link from the installation directory to mysql.
# ln -s $installation_directory/mysql-standard-5.0.26-solaris10-i386 mysql |
The mysql directory contains several files and subdirectories:
The bin subdirectory contains client programs and the server.
The MySQL server and server startup scripts:
mysqld is the MySQL server.
mysqld_safe, mysql.server, and mysqld_multi are server startup scripts.
mysql_install_db initializes the data directory and the initial databases.
mysqlmanager is the MySQL instance manager.
Client programs that access the server:
mysql is a command-line client for executing SQL statements interactively or in batch mode.
mysqladmin is an administrative client.
mysqlcheck performs table maintenance operations.
mysqldump and mysqlhotcopy make database backups.
mysqlimport imports data files.
mysqlshow displays information about databases and tables.
Utility programs that operate independently of the server:
myisamchk performs table maintenance operations.
myisampack produces compressed, read-only tables.
mysqlbinlog is a tool for processing binary log files.
perror displays the meaning of error codes.
The scripts subdirectory contains the mysql_install_db script used to initialize the mysql database containing the grant tables that store the server access permissions.
Add a login user and group for mysqld.
# groupadd mysql # useradd -g mysql mysql |
Change the ownership of program binaries to root and ownership of the data directory to the user that you use to run mysqld.
# chown -R root . # chown -R mysql data # chgrp -R mysql . |
Create the MySQL grant tables.
# scripts/mysql_install_db --user=mysql |
Start the MySQL server.
# bin/mysqld_safe --user=mysql & |
Assign the root password.
# ./bin/mysqladmin -u root password 'new-password' # ./bin/mysqladmin -u root -h ${hostname} password 'new-password' |
To start the MySQL server at boot time, copy support-files/mysql.server to /etc/init.d and link it to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql. If MySQL is not installed in /usr/local/mysql., edit the file to change the basedir and datadir variables.
Add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly.
Log in to the database as a superuser.
# mysql -u root -p<password>
Create user and grant privileges for arco_read and arco_write.
mysql> GRANT ALL on *.* to 'arco_write'@'<hostname>' identified by '<password>' with GRANT OPTION; mysql> GRANT ALL on *.* to 'arco_write'@'localhost' identified by '<password>' with GRANT OPTION; mysql> GRANT ALL on *.* to 'arco_write'@'%' identified by '<password>' with GRANT OPTION; mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'<hostname>' identified by '<password>'; mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'localhost' identified by '<password>'; mysql> GRANT SELECT,SHOW VIEW on <db_name>.* to 'arco_read'@'%' identified by '<password>';
Log in to the database as arco_write user.
# mysql -u arco_write -p<password>
Create the accounting and reporting database.
mysql> CREATE DATABASE ARCO;
Install the Accounting and Reporting Software.