Chapter 17 PostgreSQL Database and MySQL Administration
» Table of Contents |  | | » Glossary | | | » Index | | |
| | |
| ||
| ||
Table of Contents
Internet Express provides the PostgreSQL and MySQL database management systems.
PostgreSQL is an advanced database server that supports most SQL constructs, including subselects, transactions, and user-defined types and functions. Each PostgreSQL server controls access to a number of databases, storage areas used by the server to partition information. A typical installation may have several databases, for example: a production database, used to keep all information about a company; a training database, used for training and testing purposes; and private databases, used by individuals to store personal information.
MySQL is an open source database management system that relies on SQL for processing the data in the database. MySQL is most commonly used for Web applications and for embedded application
This chapter provides information that helps you administer your Internet Express PostgreSQL database
Considerations when installing PostgreSQL (Section : Installing PostgreSQL).
Starting and stopping the PostgreSQL server (Section : Starting and Stopping PostgreSQL Server).
Viewing the PostgreSQL log file (Section : Viewing the PostgreSQL Log File).
Location of PostgreSQL files, commands, reference pages, and accounts for administering your PostgreSQL account (Section : Important Files and Directories).
Running the postmaster startup script that enables you to run the PostgreSQL commands (Section : Running the Postmaster Startup Script).
Administering existing PostgreSQL accounts and databases (Section : Using Existing PostgreSQL Accounts).
Setting up a crontab entry for vacuuming databases (Section : Setting up a Crontab Entry for Vacuuming Databases).
Scaling PostgreSQL (Section : Scaling PostgreSQL).
This chapter also provides the following information about MySQL:
MySQL directories and files(Section : Directories and Files Established by MySQL Installation)
Starting and stopping the MySQL server (Section : Starting and Stopping the MySQL Server Using a Command Line)
MySQL configuration files (Section : MySQL Configuration Files)
MySQL log files (sec-mysql-log-files)
Installation tasks include getting the subset, compiling, initializing, starting the server, and creating a database. See the Internet Express Installation Guide for the installation procedure.
PostgreSQL is installed in /usr/internet/pgsql/. A /usr/local/pgsql symbolic link is created to duplicate the PostgreSQL default installation path.
When you install PostgreSQL, it automatically creates an account called postgres with /usr/internet/pgsql/ as its home directory.
You can start and stop the PostgreSQL server from the Administration utility. Follow these steps:
From the Manage Components menu, choose PostgreSQL Database Management System. The Manage PostgreSQL menu is displayed (Figure 17-1).
From the Manage PostgreSQL menu, choose Start/Stop PostgreSQL. The current state of the PostgreSQL server is displayed:
To start a stopped server, click on the Start button.
If the server is running, click on Stop to stop the server or Restart to stop and restart the server. Figure 17-2 shows the Start/Stop PostgreSQL form when the server is running.
A status message is displayed after each action.
The PostgreSQL server logs information in the /usr/internet/pgsql/data/postmaster.log file. View the contents of this log file from the Administration Utility, as follows:
From the Manage Components menu, choose PostgreSQL Database Management System. The Manage PostgreSQL menu is displayed (Figure 17-1).
On the Manage PostgreSQL menu, choose View PostgreSQL Log. The contents of the log file are displayed, as in Figure 17-3. Use the standard navigation features to advance page by page, go to a specific page, or search for a particular text string.
Before you can administer your PostgreSQL account, ensure that:
PostgreSQL is installed (see Section ).
The PostgreSQL server is running (see Section : Starting and Stopping PostgreSQL Server).
You are a configured PostgreSQL user. From the command line, type su - postgres, which sets the environment variables and adds the PostgreSQL commands in your path.
You have created a database. The Internet Express installation procedure automatically creates a database system, which includes a database named template(1). For information on creating a database, see the reference page createdb in /usr/internet/pgsql/man. If you have an existing PostgreSQL account and database, see Section : Using Existing PostgreSQL Accounts.
To start a session and connect to a database, type psql database name (all lowercase) at the command prompt. This connects you to the PostgreSQL server from which you can issue queries and receive replies from the server.
Table 17-1: PostgreSQL Files and Directories contains information about files, commands, environment variables, and reference pages that helps you administer your PostgreSQL account. For information about performing specific PostgreSQL administrative tasks such as creating users and databases, backup and restore, performance, troubleshooting, customization, system tables, and access configuration (server access, database access, table access), see the administrator's guide on PostgreSQL Web site:
|
http://www.postgresql.org |
|
The PostgreSQL documentation is also installed in /usr/internet/pgsql/doc as part of the IAEPSQL subset.
Table 17-1 PostgreSQL Files and Directories
| Directory | Contents |
|---|---|
| /usr/internet/pgsql/man | Location of PostgreSQL reference pages. |
| /usr/internet/pgsql/doc | Location of the PostgreSQL documentation. |
| /usr/internet/pgsql/bin/ | Location of the PostgreSQL commands. |
| /usr/internet/pgsql/ | Home directory of the PostgreSQL account where all files and directories are installed. |
| /usr/internet/pgsql/.profile | Contains a set of environment variable definitions for running most PostgreSQL commands. |
| /usr/internet/pgsql/bin/pg_wrap | Command for defining the environment and running any PostgreSQL commands. |
| /usr/internet/pgsql/data/pg_hba.conf | File for configuring access control to a PostgreSQL database. |
| /usr/internet/pgsql/jdbc | Contains the pre-compiled jdbc drivers. |
The postmaster daemon manages the communication between frontend and backend processes. It automatically runs as a background process when you install PostgreSQL and when you reboot. (Postmaster does not interact with the user.) Only one postmaster should be running at a time in a given PostgreSQL installation. An installation means a database directory and postmaster port number. You can run more than one postmaster on a machine only if each one has a separate directory and port number.
Internet Express provides a startup script for postmaster in /sbin/init.d/postgres and a symbolic link in /sbin/rc3.d/S70postgres, which ensures that the postmaster daemon always runs.
The Internet Express installation procedure creates a default PostgreSQL account (postgres). If you already have an existing account, possibly in another directory, use the pg_wrap command in /usr/internet/pgsql/bin/pg_wrap as a prefix to any other PostgreSQL command you run. This command resets the environment variables to the existing account.
As part of routine database maintenance and to ensure that PostgreSQL runs efficiently, periodically run a vacuum on your database.
Vacuuming a database allows you to:
Recover disk space occupied by updated or deleted rows in the database.
Because data affected by update or delete procedures are not immediately removed by PostgreSQL, you can remove the data by periodically vacuuming the database. When you run the vacuum on your database, disk space is opened for reuse and disk space requirement are kept low.
Protect against loss of data due to transaction ID wrap-around.
PostgreSQL assigns each transaction a unique ID, which determines the chronology of transactions. The ID has a fixed length of 32 bits. When the server has been running for a long time (more than 4 billion transactions), the transaction ID will eventually wrap to zero. This can cause old transactions to reappear, resulting in catastrophic data loss. Periodically vacuuming the database prevents this type of transaction ID wrap-around.
Using the Administration utility, you can set up a crontab entry that runs a vacuum on your entire database at a specified time of day at daily or weekly intervals. The PostgreSQL server must be running for the vacuum to be performed.
Although the vacuum can run in parallel with normal database operations (that is, select, insert, update, and delete), HP recommends that you schedule your database to be vacuumed during a low-usage period.
To schedule the database vacuum, follow these steps:
From the Manage Components menu, choose PostgreSQL Database Management System.
From the PostgreSQL Database Management System form, choose Setup Vacuum Crontab. The Setup Vacuum Crontab form is displayed, allowing you to specify and submit the database vacuum options (Figure 17-4).
On the Setup Vacuum Crontab form, provide the following settings:
Select the desired frequency for the database vacuum: daily or weekly. If you select weekly, also select the day of the week that you would want to run the vacuum.
Select time of day using 24-hour clock format. This is the time that the vacuum will run.
If your system is part of a cluster, you must also select the cluster member from which to run the vacuum.
If there was no previous crontabentry, click on Create New to add the entry. If there was an existing entry, click on Submit to update the crontab entry with the new values. A status message is displayed when the operation has completed.
To delete a crontab entry so that the database vacuum will no longer run, click on Delete. A status message is displayed when the operation has completed, indicating that the current entry has been removed.
| |
| |
| |
 | Note: The crontab entry that you created using the using the Setup Vacuum Crontab form should only be edited from the Administration utility. Directly editing this entry in the crontab file or adding additional entries which call /usr/internet/pgsql/bin/ix_vacuumdb can produce errors. If you want to create custom crontab entries for vacuuming your database, set the command for your crontab entry to call /usr/internet/pgsql/bin/vacuumdb. See the reference pages for crontab and vacuumdb for more information. |
| |
| |
| |
After installing the PostgreSQL subset, a postmaster process runs. The postmaster manages communication between front and backend processes. By default, the postmaster is started with settings that limits to 32 the number of backend server processes that can be started by the postmaster. This value may be increased to as high as 1024.
For each backend process the postmaster needs to allocate two shared-memory buffers. All the buffers are allocated when postmaster is started.
To increase the number of concurrent backend server processes, you edit the postgresql.conf file located in /usr/internet/pgsql/data.
Change the max_connections value to indicate the number of processes you want to allow, up to a maximum of 1024. Follow the syntax specified in the postgresql.conf file.
Change the shared_buffers value to be at least twice the number of connections specified in Step 1. This value represents the number of shared-memory buffers that the postmaster will allocate and manage for the backend server processes it starts.
Stop the currently running postmaster process:
#/sbin/init.d/postgres stop
Restart the postmaster process with the new values:
#/sbin/init.d/postgres start.
Look for the postmaster process:
#ps -ef | grep postmaster
If the postmaster process failed to start, increase your kernel's shared memory size limits, as follows:
Review the /usr/internet/pgsql/data/postmaster.log file for an error message produced when trying to startup the postmaster. Most likely, you will see an error message similar to the following message:
IpcMemoryCreate: shmget(key=5432001, size=28901376, 03600) failed: Invalid argument 1. The maximum size for shared memory segments on your system was exceeded. You need to raise the SHMMAX parameter in your kernel to be at least the size number reported in the error, in this case, 28901376 bytes. 2. The requested shared memory segment was too small for your system. You need to lower the SHMMIN parameter in your kernel. 3. The requested shared memory segment already exists but is of the wrong size. This is most likely the case if an old version of PostgreSQL crashed and didn't clean up. The `ipcclean' utility can be used to remedy this. The PostgreSQL Administrator's Guide contains more information about shared memory configuration.The actual numbers reported in the error message will be different. You will need to modify the parameters in your kernel, and in the shm_max and sem_mni values, as follows:
Determine the appropriate shm_max value. This should be the number of bytes reported in the error message (in this example, 28901376).
Determine the appropriate sem_mni value. It should be greater than max_connections / 16. (For example, if max_connections is set to 512, then sem_mni will need to be greater than 32. Set it to 33 or higher.)
Change the following values:
Create a small file named, for example, ipc.stanza and use the appropriate values. For example:
ipc: shm_max=28901376 sem_mni=33Execute:
#/sbin/sysconfigdb -m -f ipc.stanza
Assure that the ipc parameters were added:
# tail /etc/sysconfigtab
Reboot the system.
After rebooting, you can check that the changes have taken effect by this command:
#/sbin/sysconfig -q ipc
PostgreSQL will also start. Confirm that PostgreSQL is running by this command:
#ps -ef | grep postmaster.
If the postmaster process still did not start, review the /usr/internet/pgsql/data/postmaster.log file for an error message.
For information on what may be causing the error, review the PostgreSQL documentation at http://www.postgresql.org or from the installed documentation in the /usr/internet/pgsql/docs directory.
This section describes the files and processes initiated by the MySQL installation, related scripts, and MySQL configuration files and log files.
You can use the Administration utility to performs the following tasks with My SQL:
The MySQL installation procedure includes compiling, initializing, starting the server, and creating a database. MySQL is installed in /usr/internet/mysql/. A /usr/local/mysql symbolic link is created to duplicate the MySQL default installation path.
The MySQL installation creates an account called mysql and the daemon is started by the mysql user.
By default, the installation step installs files under /usr/local/mysql, in the following subdirectories:
Table 17-2 MySQL Directories
| Directory | Contents |
|---|---|
| bin | Client programs and scripts |
| include/mysql | Include (header) files |
| info | Documentation in Info format |
| lib/mysql | Libraries |
| libexec | The “mysqld” server |
| share/mysql | Error message files |
| sql-bench | Benchmarks and “crash-me” test |
| var | Databases and log files |
The installation procedure also runs the mysql_install_db script to set up the initial MySQL grant tables containing the privileges that determine how users are allowed to connect to the server The mysql_install_db script creates the server's data directory. Under the data directory, it creates directories for the MySQL database that holds all database privileges and the test database that you can use to test MySQL. The script also creates privilege table entries for root and anonymous user accounts.
You can start or stop MySQL as follows:
From the Manage Components menu, choose MySQL Database Management System. The Manage MYSQL menu is displayed.
From the Manage MYSQL menu, choose Start/Stop MYSQL. The current state of the MYSQL server is displayed:
To start a stopped server, click on the Start button.
If the server is running, click on Stop to stop the server or Restart to stop and restart the server.
A status message is displayed after each action.
Internet Express provides a startup script for MySQL in /sbin/init.d/mysql.
Use the following command to start MySQL on the command line:
Use the following command to stop MySQL on the command line:
The file /etc/my.cnf stores default startup options for both the server and for clients. To ensure the proper configuration of this file, the MySQL developers have included four sample my.cnf files within the distribution:
my-huge.cnf.sh
my-large.cnf.sh
my-medium.cnf.sh
my-small.cnf.sh
Each of these files denotes recommended configuration settings in accordance with system resource availability.
These files are available under /usr/local/mysql/share/mysql.
The MySQL log files are created under /usr/local/mysql/logs.
The error log file indicates when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
The general log is a general record of actions taken by mysqld. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general log can be useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
View the contents of the MySQL error log from the Administration Utility, as follows:
From the Manage Components menu, choose MYSQL Database Management System. The Manage MYSQL menu is displayed .
On the Manage MYSQL menu, choose View MYSQL Error Log. The contents of the log file are displayed. Use the standard navigation features to advance page by page, go to a specific page, or search for a particular text string.
View the contents of the MySQL error log from the Administration Utility, as follows:
From the Manage Components menu, choose MYSQL Database Management System. The Manage MYSQL menu is displayed .
On the Manage MYSQL menu, choose View MYSQL General Log. The contents of the log file are displayed. Use the standard navigation features to advance page by page, go to a specific page, or search for a particular text string.