Reference Information › Appliance Catalog Reference Guide › System Catalog › Database Appliances › PGSQL64 - PostgreSQL Database Appliance

PGSQL64 - PostgreSQL Database Appliance

Functional Overview

Important! PGSQL is not available in CA AppLogic 2.8+; Please use PGSQL64 instead.

PGSQL64 is a database appliance based on the PostgreSQLdatabase engine (http://www.postgresql.org). It provides an easy way to add a database to any application. PGSQL64 is an enterprise-style database appliance that supports many features that are currently not supported by the MYSQLx appliances. Some of these features include stored procedures, triggers, views and user-defined data types.

PGSQL64 stores the database on an application-defined volume that can be configured on each PGSQ64L instance. PGSQL64 optionally creates an empty database when it starts on an empty volume. The PGSQL64 database volume cannot be shared among multiple PGSQL instances (one database volume per PGSQL instance).

PGSQL64 clients access the configured database through the in terminal. The database requests are processed and completed back through the same terminal. PGSQL64 allows any valid Postgres user to access the database through the in terminal (the appliance has one preconfigured superuser role: standard 'postgres'). PGSQL64 can be configured with the maximum number of concurrent connections supported through in.

PGSQL64 also has the ability to maintain a database log accessible through the log terminal. The log is useful for tracking database information and error logging. The log path, name, age and contents are configurable. In addition, various database statistics and debugging information may also be enabled to help with profiling database access patterns and diagnosing problems/errors.

PGSQL64 is typically used for either general purpose web database applications (small databases with a large number of users who execute simple queries) or decision-support databases (large databases with a small number of users who execute complex queries).

Resources

Note: Memory should be increased based on two main factors: number of concurrent users and database size. Typically, PGSQL can support about 80 concurrent users per 128 MB of memory. The larger the database, the more memory PGSQL should have for processing. For example, PGSQL should be configured with at least 1G of memory for a 10G database - more than 1G for better performance.

Terminals

The default interface is enabled. It is intended for diagnostics and troubleshooting (over SSH). Future versions of this appliance may disable SSH access.

User Volumes

Important! The data volume must exclusively dedicated to the PGSQL instance (cannot be shared with other appliances).

Properties

Note: None of the PGSQL properties are case sensitive excluding file names and paths.

Logging Properties

Important! The PGSQL appliance will fail to start if logging is enabled and the log terminal is not connected.

Custom Counters

The PGSQL appliance reports the following custom counters through the mon terminal, provided the log_cmd_stats property is set to yes. These counters belong to the PostgreSQL counter group:

Error Messages

In case of appliance startup failure, the following errors may be logged to the system log:

Dashboard Messages

In cases where critical errors are encountered that may require user attention, PGSQL logs messages to the grid's dashboard as follows:

These messages are logged only one time once the free disk space amount drops below the threshold.

Performance

The PGSQL performance is greatly affected by the sync_on_write, log_filename and log_cmd_stats property values. The best performance results can be achieved when the fsync, logging and statistics are all disabled (sync_on_write=no, log_filename="", log_cmd_stats=no). The table below contains sample pgbench test results (specified in tps units - transactions per second) performed when these features are enabled and disabled. pgbench is a utility that is shipped with PostgreSQL that is used to measure database performance.

Simple Two-tier Application (Web Database Application)

The following diagram shows a typical usage of the PGSQL appliance in a two-tier web application geared toward a lot of users executing simple queries:

Appliances in use:

• usr - input gateway
• srv - web server with active content (for example, CGI scripts)
• dbase - PostgreSQL database appliance

Client requests arrive on the usr gateway. The gateway forwards the requests to the web server, which serves the request. When script (for example, Perl or PHP) on srv need to access persistent data, it uses the dbase appliance through the db terminal.

In this example, the database used with dbase is not read-only and many users may access it through srv executing simple queries. As such, dbase is configured to use 256MB of memory. Also, the database does not keep any database error log files (the log terminal is not connected).

Example property configuration:

Note: The data volume must also be configured on dbase and the content volume must be configured on web. To create application volumes that can be used here, see the Grid Users Guide.

Scalable Two-tier Application (Web Database Application)

The following diagram shows a typical usage of the PGSQL appliance in a two-tier web application in which the database is used to share state and data between multiple, load-balanced web servers (geared toward a lot of users executing simple queries). In addition, this example has a separate input for maintenance, through which an administrator can log in and access the database for maintenance and an input through which an administrator can log in and view the PostgreSQL error log.

Appliances in use:

• usr - input gateway for user requests
• webs - web load-balancer for user requests
• srv1, srv2 - web servers with active content (for example, CGI scripts)
• config - web server content storage
• dbase - PostgreSQL database appliance
• logs - storage for database and web error log files
• maint - input gateway for maintenance access
• admin - a generic Linux server used by the administrator
• gway - a network gateway for administrator's access to the Internet
• log - input gateway for access to the database error log

Client requests arrive on the usr gateway. The gateway forwards the requests to the webs load balancer, which directs the request to one of the web servers ( srv1 or srv2). When scripts (for example, Perl or PHP) on the web servers need to access persistent data, they use the dbase appliance through their db terminals.

The dbase database writes its error log to the logs appliance through the log terminal. In addition, an administrator can log in through the log gateway to the logs appliance and view the database error log files (as well as the web server log files).

Additionally, an administrator can log in over SSH through the maint gateway to the admin server. From the admin server, the administrator can access the dbase database for statistics or changing the database schema. The admin server can access the Internet through the gway gateway, for example, to download a newer version of libraries or the database schema.

In this example, the database used with dbase is not read-only and many users may access it through the web servers executing simple queries. As such, dbase is configured to use 256MB of memory. Also, the database is configured to keep one week of log files, one per day (after each week the log files are overwritten).

Example property configuration:

Notes:

• The data volume must also be configured on the dbase appliance and the data volume must also be configured on the logs appliance.
• The maint, admin, gway, and log appliances are not required for the operation of the two-tier application. If present, the admin server may have cron jobs for database scrubbing, sending e-mail, and so on.

Simple Two-tier Application (Decision Support System)

The following diagram shows a typical usage of the PGSQL appliance in a two-tier web application implementing a decision-support system for a few users executing complex queries over a large database that is several GBs in size.

Appliances in use:

• usr - input gateway
• srv - web server with active content (for example, CGI scripts)
• config - web server content storage
• dbase - PostgreSQL database appliance
• logs - storage for database and web error log files
• admin - input gateway for viewing log files

Client requests arrive on the usr gateway. The gateway forwards the requests to the srv server, which serves the request. When scripts (for example, Perl or PHP) on srv need to access persistent data, it uses the dbase appliance through the db terminal. The dbase appliance is configured to store its log files within the root directory of the share exposed by logs.

Using a browser, administrators connect to the admin gateway to view the PostgreSQL log files. The admin gateway forwards the requests to the logs NAS appliance.

In this example, the database used with dbase is not read-only and users may access it through srv executing complex queries (large queries accessing millions of rows). As such, dbase is configured to use 1536MB of memory. Also, the database is configured to keep one week of log files, one per day (after each week the log files are overwritten).

Example property configuration:

Note: The data volume must also be configured on the dbase appliance and the data volume must also be configured on the logs appliance.

Simple Two-tier Application (Web Read-only Database Application)

A less common usage of the PGSQL appliance is a two-tier web application using a read-only database. This is the same as the previous simple application examples except the database is read-only (no modifications are allowed). It is important to parameterize PGSQL such that it uses a read-only database as it will turn off various PostgreSQL features that are not needed for read-only databases which results in better performance (database auto-vacuuming, database modification statistics, and so on).

Example property configuration:

Debugging Database Applications

PGSQL exposes properties to enable the debugging of a database by using the parameterized log file ( log_filename). For example, this is useful when you need to track SQL statements, see how long it takes to execute SQL statements, see why an SQL statement is failing, and so on. The debug information includes:

• standard debug information exposed by PostgreSQL
• SQL statement processing and execution statistics (useful for queries)
• PostgreSQL statistics on SQL commands, database row-level access, and so on.
• user connection/disconnection

Typically to debug a database, the following properties are configured:

The debug information is either stored in the error log accessed through the log terminal or is available through one of the PostgreSQL statistic views: http://www.postgresql.org/docs/8.3/static/monitoring-stats.html.

Notes

Be aware of the following:

• PGSQL is designed for relatively straightforward database uses (no master-slave, replication, and so on). If you need a database with more advanced features, let us know.
• PGSQL allows any valid Postgres user to access the database (roles postgres and root are preconfigured as superuser; new roles could be created); user passwords are ignored when accessing through in terminal. The database can only be accessed either through the in terminal or locally on the PGSQL appliance itself.
• Volume requirements
  • Help ensure the data volume is created with the ext3 filesystem; otherwise PostgreSQL will fail to start.
  • You can leave the volume empty (just created) if auto_create is set to yes. PGSQL will create an empty database on the volume when it is started.
  • The volume contains only the database data. The data can be moved by copying all files to a new (for example, larger) volume.
  • PostgreSQL configuration files on the data volume are copied from /appliance/conf directory.
  • For existing databases, PGSQL expects the PostgreSQL PG_VERSION file to be present in the root directory of the volume. If this file is missing, PGSQL assumes the database is invalid and will overwrite it if auto_create is yes.
• For read/write databases ( read_only is no), PGSQL performs auto-vacuums the database. This verifies that the database does not waste disk space on the data volume. This is a built-in feature of PostgreSQL. Without auto-vacuuming, the data volume can become full. It is mandatory to set the log_cmd_stats setting to yes to enable auto-vacuuming.
• The appliance connected to PGSQL is responsible for creating its own database and tables if they do not exist. Alternatively, you can manually copy the initial database to the data volume.
• The appliance or server connected to PGSQL='s =in terminal should have the PostgreSQL client library to access the PostgreSQL server and submit requests. This library may be downloaded here.
• References:
  • http://www.postgresql.org -- PostgreSQL's website
  • http://www.postgresql.org/docs/8.3/interactive/index.html -- PostgreSQL 8.3 documentation

Open Source and Third Party Software Used on this Appliance

The following open source 3rd party software is used in addition to that software found on the appliance base class (LUX64 is the base class of PGSQL64).