PGSQL: PostgreSQL Database Appliance

	At a Glance
	Catalog	System
	Category	Database Appliances
	User volumes	yes
	Min. memory	128 MB
	OS	Linux
	Constraints	no
	Questions/Comments	Ask Forum

Functional Overview

PGSQL is a database appliance based on the PostgreSQL 8.2.9 database engine (http://www.postgresql.org). It provides an easy way to add a database to any application. PGSQL 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.

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

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

PGSQL 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.

PGSQL 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).

Boundary

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

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

Properties:

Logging Properties:

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:

Possible startup errors:

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.

Typical Usage

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 towards a lot of users executing simple queries:

Appliances in use:

• usr - input gateway
• srv - web server with active content (e.g., 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 (e.g., 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 that 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 User Volumes help topic.

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 towards 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 as well as 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 (e.g., 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 (e.g., 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:

Note that 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, etc.

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 (e.g., 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 (e.g., 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 in order 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 that 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, etc).

Example property configuration:

Debugging a database application

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, etc. 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, etc.
• user connection/disconnection

Typically in order 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.2/static/monitoring-stats.html

Notes and Links

• PGSQL is designed for relatively straightforward database uses (no master-slave, replication, etc.). 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
  • Make sure 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 (e.g., 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 ensures that the database does not waste diskspace 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 in order 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 in order to access the PostgreSQL server and submit requests. This library may be downloaded here.
• References:
  • http://www.postgresql.org -- PostgreSQL's web site
  • http://www.postgresql.org/docs/8.2/interactive/index.html -- PostgreSQL 8.2 documentation

Related Documents

• Implementation Design
• Test Plan

Comment Section

Comments on this appliance can be found below and edited here.

-- EricT - 02 Jul 2008

Note: if you use the "Add Comment" button to add a new comment, your browser will take you to the comments page for this topic. Use the link provided there to return back to this page.

-- EricT - 09 Mar 2006