Carrier-Sensitive Routing User Guide - PostgreSQL Database Operations and Functionalities [Cisco Carrier Sensitive Route Server Software]

PostgreSQL Database Operations and Functionalities
Database Operations

Activating the Database
Exiting the Database
Reactivating the Database
Exporting a Database
Reintializing a Database

PostgreSQL Database Operations and Functionalities

This chapter describes the operations and functionalities of the postgreSQL database and contains the following sections:

Database Operations
Database Functionalities

The postgreSQL database is a relational database management system. A database in this management system stores entities such as carriers, rules, contacts, routes, and the relationships among the entities. The management system can manage many different databases simultaneously. The CSR creates a database in this system and performs call routing based on the data in the database.

Database Operations

This section describes the following tasks:

Activating the Database
Exiting the Database
Reactivating the Database
Exporting a Database
Reintializing a Database

Activating the Database

To activate the postgreSQL database, perform the following steps:

Step 1 Log in to the Solaris system by entering postgres as the user.

A "postgres" password prompt may appear.

Step 2 Enter the following command:

 /etc/init.d/postgres_init start

This command calls postgreSQL command postmaster -S -i -B 40 -N 20 -D /usr/local/pgsql/data. The parameters are described as follows:

-S—Tells the postmaster to run in silent mode. This is not recorded to a log file.
-i—Tells postmaster to allow network connections. If this is not set, the postmaster accepts connections only from the local host.

Note This is needed for the GUI to connect to the database even when it is running on the same machine.

-B and -N—Set up shared memory segments.
-D—Tells the postmaster the location of the database files.

Note For servers with console login, disable Xwindow in the file .profile, so the database can be restarted from a reboot.

Exiting the Database

To exit the postgreSQL database, perform the following steps:

Step 1 Log in to the Solaris system by entering postgres as the user.

A "postgres" password prompt may appear.

Step 2 Enter the following command:

 /etc/init.d/postgres_init stop

This command calls the postgreSQL command pg_ctl stop.

Reactivating the Database

To reactivate the postgreSQL database, perform the following steps.

Step 1 Log in into the Solaris system by entering postgres as the user.

A "postgres" password prompt may appear.

Step 2 Enter the following command:

 /etc/init.d/postgres_init restart

This command calls the postgreSQL command pg_ctl restart. The parameters for this command are described in "Activating the Database" section.

Exporting a Database

To export a database to a text file or to back up the entire database system, perform the following steps:

Exporting to a Text File

Step 1 Export a database into a text file by entering the following command:

 pg_dump -x databasename > outfile

Note "-x" is optional. When it is used, the "dumped" database loses the ownership information.

Step 2 Reload the dataset by entering the following command:

 psql -e database < outfile

Note "-e" is optional. When it is used, every log message "echoes" on the screen.

Backing Up the Database System

You can back up the database system file directory /usr/local/pgsql/data/base into a tar file.

Reintializing a Database

To reinitialize a database, the existing database must be destroyed, and a new one must be created.

To reinitialize a database, performing the following steps:

Step 1 Destroy the database by entering the following command with "csrdb" as the name of the database:

 dropdb csrdb

Note The database to be destroyed must not be connected to the CSR application, CSR GUI, and pgsql.

Step 2 Create a new database by entering the following command:

 createcsrdb csrdb

After the database is re-created, the CSR and CSR GUI can be restarted and connected to the new database. If "csrdb" is not used as the name of the database, note the following:

To have CSR started upon reboot of the Solaris system, modify the following files:
- etc/init.d/csr_init
- /etc/rc2.d/S99csr_init
To start CSR from the command prompt, specify the database name in command csr newdbname &

Database Functionalities

This section describes the following:

Database Tables
PostgreSQL Database Command Summary

Database Tables

This section describes database tables, constraints placed on each table, and relationships between tables.

Note Advanced knowledge of SQL is required to access the database through SQL commands. Use the psql interface to access an SQL prompt. For more detail on psql interface, see the postgreSQL online manual.

Configuration Tables

See "Data Schema," for the database script to create database tables, constraints and relationships.

Note The configuration data must contain one record that is created when the user executes the createcsrdb command.

Table 2-1 Configuration Data Table

Name	Type	Allow Null	Default	Constraints	Comments
ActiveDataset	text	NO	`_1'	Valid values: _1 _2	There are two datasets in the CSR (active and inactive). There are two of each table for entities that are created (for example, Carriers_1, Carriers_2). The active dataset marks which of the above is active.
LoggingLevel	text	NO	`Error'	Valid values: error info debug	When the application, is active, logs are generated. This marks the level of logging that is being done. During realtime, this runs only at error level to avoid performance deterioration.
CSR_ID	varchar(64)	NO			ID used to register with the gatekeeper which application is connecting. (Also known as the application ID).
CSR_Version	text				Version of CSR. This is entered when the GUI requests it from the CSR.
GKTMP_Version	integer				Version of GKTMP. This is entered when the GUI requests it from the CSR.
Priority	integer	NO	1	>=1	Priority to register this application to the gatekeeper.
ListenPort	integer			>0 and < 32767	TCP port to listen for incoming connection. A gatekeeper or simulator may connect to the CSR, and this is the port to connect to.
ThreadCount	integer	NO	10		Number of threads in the working pool that handle routes in the rules engine. This number must be adjusted for different systems.
MaxSizeOfQueue	integer	NO	5000		Maximum size of the queue to handle calls. Typically, the queue is either empty or has an entry. During busy hours, the queue may become larger and cause long turn-around times for the calls to be routed.
TestRoutesAccountingEnabled	boolean	NO	FALSE		When issuing test commands through the GUI, the call units are updated if this is set to True.
RejectCarrierInactive	boolean	NO	FALSE		If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectNullCarrier	boolean	NO	FALSE		If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectCarrierUnknown	boolean	NO	FALSE		If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectDestinationUnknown	boolean	NO	FALSE		If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectPercentage	boolean	NO	FALSE		If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectUnits	boolean	NO	FALSE		If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectNoDstCarrier	boolean	NO	FALSE		If this rejection rule applies, the CSR returns a rejection instead of a null body.

Table 2-2 Gatekeeper Table

Name	Type	Allow Null	Default	Constraints	Comments
GKKey	integer	NO			Primary key. Key of the gatekeeper.
GKID	varchar(64)	NO			ID of the gatekeeper. Must be unique.
GK_IP	inet	NO			IP address of the gatekeeper.
GK_Port	integer	NO		>0 and < 32767	Port that the gatekeeper is listening on.
ListenARQ	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenLRQ	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenIRR	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenDRQ	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenRRQ	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenURQ	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenLCF	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenLRJ	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenRAI	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ListenBRQ	boolean	NO	FALSE		Set to TRUE if registering for this type of message.
ConnectionState	text	NO		Valid values: connect disconnect	If set to connect, the CSR attempts to connect to this gatekeeper.

Table 2-3 TestRoutes Table

Name	Type	Allow Null	Default	Constraints	Comments
TestKey	integer	NO			Primary key. Key of this test route.
TestTime	time				Time of the test.
TestDate	date				Date of the test.
DatasetID	text		`_1'	Valid values: _1 _2	This marks which dataset to issue the test on.
ANI	varchar(64)				ANI message.
DNIS	varchar(64)				DNIS message.
CarrierID	varchar(64)				ID of source carrier.
DstCarrierID	varchar(64)				ID of destination carrier.
RunMe	Boolean	NO	FALSE		Set to TRUE when this test is to be run by the CSR.
HomeCarrierID	varchar(64)				ID of home carrier.
ResultCode	text				Result code for this test.

Table 2-4 TestOutputs Table

Name	Type	Allow Null	Default	Comments
TestKey	integer	NO		Foreign key. Key of this test (see TestRoutes table).
SelectOrder	integer	NO		For a given TestKey, this shows the order in which the carriers will be returned. The database does not guarantee the original ordering.
DstCarrierID	varchar(64)	NO		ID of Destination Carrier.
ListKey	integer	NO		Specifies key for the contact list associated with the particular carrier.
IsSelected	boolean	NO	FALSE	Specifies whether this is a selected carrier.

Table 2-5 LogEntries Table

Name	Type	Allow Null	Comments
TestKey	integer	NO	Foreign key. Test key that this log file was generated for.
EntryOrder	integer	NO	For a given Test key, this field is used to show the order in which the log events were generated. The database does not guarantee the original ordering.
LogString	varchar(1024)	NO	The log entries associated with the test run.

Caution The following table should not be modified or deleted. It consists of error messages that are logged. The CSR application does not work without this table.

Table 2-6 StringResources Table

Name	Type	Allow Null	Default	Constraints	Comments
ResourceKey	integer	NO			Primary key. Key to the resource.
ResourceString	text	NO			String (error value).

Table 2-7 Filters Table

Name	Type	Allow Null	Constraints	Comments
FilterKey	integer	NO		Primary key. Key of the filter.
GKID	varchar(64)	NO		Foreign key. ID of the gatekeeper associated with this filter.
MsgType	text	NO	Valid values: ARQ LRQ RRQ URQ LCF LRJ DRQ BRQ IRR RAI	The message type for this filter.
FilterType	text	NO	Valid values: endpoint supportedprefix destinationinformation remoteextesionaddress' redirectreason answercall notificationonly	Type of filter.
Value	text	NO		Value of this message. There are certain values for each filter type enforced by the GUI.

Dataset Tables

The dataset tables use functionality that is specific to postgreSQL, that is, table inheritance. This allows a table to be created and other tables to be inherited from it. For example, you can create a Carriers Table and have tables Carriers_1 and Carriers_2 inherit from it. This allows two datasets to be created.

Table 2-8 Carriers Table

Name	Type	Allow Null	Default	Constraints	Comments
CarrierKey	integer	NO		<>0	Primary key. Key of the carrier.
CarrierName	varchar(64)	NO			Name of carrier.
CarrierType	text	NO		Valid values: home itsp tdm	Type of carrier.
CarrierID	varchar(64)	NO			ID of carrier. Must be unique.
MaxIngressUnits	integer			>=0	Maximum ingress units.
MaxEgressUnits	integer			>=0	Maximum egress units.
RuleKey	integer				Foreign key. Rule associated with this specific carrier.
ListKey	integer	NO			Foreign key. Key of the list.
isActive	boolean	NO	TRUE		Indicates whether carrier is active.

Note Tables Carriers_1 and Carriers_2 can inherit from the Carriers table.

Table 2-9 MatchPatterns Table

Name	Type	Allow Null	Constraints	Comments
PatternKey	integer	NO	<> 0	Primary key. Key of the pattern.
Pattern	varchar(64)	NO		Unique. Valid values are 0 to 9, a to f, A to F, *, and "."
RouteKey	integer			Foreign key. Route that this pattern is associated with.

Note Tables MatchPatterns_1 and MatchPatterns_2 can inherit from this table.

Table 2-10 Routes Table

Name	Type	Allow Null	Constraints	Comments
RouteKey	integer	NO	<> 0	Primary key. Key of the route.
RouteName	text			Name of the route.
RuleKey	integer			Foreign key. Rule associated with this route.

Note Tables Routes_1 and Routes_2 can inherit from the Routes table.

Table 2-11 Rules Table

Name	Type	Allow Null	Default	Constraints	Comments
RuleKey	integer	NO		<> 0	Primary key. Key of the rule.
RuleName	text				Optional name to describe this rule.
IsDefaultRule	boolean	NO	FALSE		Indicates if this is the default rule. One default rule must exist.
MinQoS	integer			>= 1 and <=9	Minimum quality of service. This is used with RejectInsufficientQoS rule.
PreferredCarrierKey	integer				Foreign key. Key to a carrier. Used only when SelectPreferredCarrier rule is used.
RejectMaxOriginationPercentage	boolean	NO	FALSE		Indicates whether to reject this rule.
RejectMaxIngressUnits	boolean	NO	FALSE		Indicates whether to reject this rule.
RejectMaxIngressUnitsPerRoute	boolean	NO	FALSE		Indicates whether to reject this rule.
RejectEgressCostMoreThanIngress	boolean	NO	FALSE		Indicates whether to reject this rule.
RejectInsufficientQoS	boolean	NO	FALSE		Indicates whether to reject this rule.
RejectMaxEgressUnits	boolean	NO	FALSE		Indicates whether to reject this rule.
RejectMaxEgressUnitsPerRoute	boolean	NO	FALSE		Indicates whether to reject this rule.
PreventITSPtoITSP	boolean	NO	FALSE		Indicates whether to reject this rule.
SelectMinCostPerEgressRoute	integer	NO	0	>= 0 and <= 5	Specifies the order of selection rule from which the CSR runs.
SelectBestQoS	integer	NO	0	>= 0 and <= 5	Qos level.
SelectSameCarrier	integer	NO	0	>= 0 and <= 5	Specifies carrier.
SelectPreferredCarrier	integer	NO	0	>= 0 and <= 5	Specifies preferred carrier.
SelectPercentageEgress	integer	NO	0	>= 0 and <= 5	Specifies egress percentage.

Note Tables Rules_1 and Rules_2 can inherit from the Rules table.

Table 2-12 Contacts Table

Name	Type	Allow Null	Constraints	Comments
ContactKey	integer	NO	<> 0	Primary key. Key of the contact.
RASAddress	varchar(64)	NO		RAS address.
AddressType	text	NO	Valid values: ipv4 gkid dnsname	Type of address (IP address, gatekeeper ID, or DNS name). The CSR converts the DNS name to IP address. When you use the DNS name, a DNS server must be available. Otherwise, this value is not used.
Cost	integer		>= 0	Specifies desired cost.
Priority	integer		>= 0	Specifies desired priority.

Note Tables Contacts_1 and Contacts_2 can inherit from the Contacts table.

Lists Table

Table 2-13 Lists Table

Name	Type	Allow Null	Default	Constraints	Comments
ListKey	integer	NO		<> 0	Primary key. Key of the list.
ListName	text				Name of the list.

Note Tables Lists_1 and Lists_2 can inherit from the Lists table.

Table 2-14 ContactLists Table

Name	Type	Allow Null	Default	Constraints	Comments
ListKey	NO				Foreign key. Key of the list.
ContactKey	NO				Foreign key. Key of the contact.

Note Tables ContactLists_1 and ContactLists_2 can inherit from this table.

Table 2-15 RouteAttributes Table

Name	Type	Allow Null	Constraints	Comments
AttributeKey	integer	NO	<>0	Primary key. Key of the route attribute.
AttributeName	text			Name of attribute associated with the route.
CarrierKey	integer	NO		Foreign key. Key of the carrier associated with this route attribute.
DaypartType	text	NO	Valid values: absolute weekly daily always	The CSR searches for the correct value in the following order: absolute = specific date/time range. Start date, end date, start time, and end time must be entered. weekly=a specific day of the week during a specific time. Start time, end time, and weekday must be entered. daily=each day for a specific time. Start time and end time must be entered. always=all the time.
WeekDay	integer		>= 0 and <= 6. As 0=Sunday, 6=Saturday.	This is for the weekly setting.
StartTime	time		Valid format: hour(0-23):min (0-60):sec (0-60)	This is for the absolute, weekly, and daily settings.
EndTime	time		Valid format: hour(0-23):min (0-60):sec (0-60)	This is for absolute, weekly, and daily settings.
Cost	integer		>= 1	Cost associated with this attribute.
MaxUnits	integer		>= 0	Maximum units.
MaxPercentage	integer		>= 1 and <=100	Maximum percentage.

Note Tables IngressRouteAttributes_1 and IngressRouteAttributes _2 can inherit from the RouteAttributes table.

Table 2-16 EgressRouteAttributes Table

Name	Type	Allow Null	Default	Constraints	Comments
QoS	integer			>= 0 and <= 9	Quality of service.
ListKey	integer				Foreign key. Key of list associated with this egress route attribute.

Note EgressRouteAttributes_1 and EgressRouteAttributes_2 can inherit from the EgressRouteAttributes and RouteAttriubtes tables.

Table 2-17 QoS Table

Name	Type	Allow Null	Constraints	Comments
IngressCarrierKey	integer	NO		Foreign key. Key of the ingress carrier.
EgressCarrierKey	integer	NO		Foreign key. Key of the egress carrier
RouteKey	integer	NO		Foreign key. Key of the route associated with the QoS.
RelativeQoS	integer	NO	>= 1 and <= 9	Relative quality of service.

Note Tables QoS_1 and QoS_2 can inherit from this QoS table.

Table 2-18 QoS Table

Name	Type	Allow Null	Default	Constraints	Comments
RouteKey	integer	NO			Foreign key. Key of the route associated with this QoS.
AttributeKey	integer	NO			Foreign key. Key of the attribute associated with this QoS.

Note Tables IngressRoutes_1, IngressRoutes_2, EgressRoutes_1, and EgressRoutes_2 can inherit from this QoS table.

PostgreSQL Database Command Summary

Following is a summary of frequently used postgreSQL commands. Refer to the online manual pages and help section for more information.

createdb—Creates a new database in postgreSQL. A database name is required when using this command.
createuser—Creates a postgreSQL user. A user name is required when using this command.
dropdb—Removes a database from postgreSQL. A database name is required when using this command.
dropuser—Removes a postgreSQL user. A user name is required when using this command.
initdb—Creates a new postgreSQL database installation. This command is used only once when the database is installed. It creates the base directory. This command cannot be used again.
psql—SQL interface to the postgreSQL databases. A database name is required when using this command.
vacuumdb—Cleans and analyzes a postgreSQL database.

Table of Contents

PostgreSQL Database Operations and Functionalities

Database Operations

Activating the Database

Exiting the Database

Reactivating the Database

Exporting a Database

Exporting to a Text File

Backing Up the Database System

Reintializing a Database

Database Functionalities

Database Tables

Configuration Tables

Dataset Tables

Lists Table

PostgreSQL Database Command Summary