Jsp, Tomcat, J2Ee, Linux Web Hosting, Hibernate Programming Blog

CHAPTER 26 POSTGRESQL ADMINISTRATION to 3MB of memory to be used. This might not sound like much, but on a server with a small amount of RAM (say 256MB) that has to handle a large number of connections (say 100), you can see how this could easily exhaust all of the available RAM on the system. This setting can be set per connection, though, so one trick you can use is to set the value higher for specific connections that might need to run more intensive queries, like a reporting interface. To change this setting on an individual connection, you would use the SET command: SET work_mem = 2028; maintenance_work_mem This setting, also known as vacuum_mem prior to version 8, is similar to the work_mem setting but is used for system tasks, including vacuuming and creating new indexes. Its value is a number equivalent to 1KB; the default value is 16,384KB (16MB) as of 8.0, and 8,192KB (8MB) in prior versions. Since these operations are not generally run in concurrent fashion, it is often safe to set this value even higher if you work with larger tables. This value can also be set per session. max_prepared_transactions This setting, new in PostgreSQL 8.1, controls the number of transactions that can be simultaneously prepared for two-phase commit. The default value is five, but if you are not using twophase commit, then you can effectively set this to 0. While this won t result in large performance increases, since use of two-phase commit within PHP is almost nonexistent, it doesn t hurt to turn it off. max_fsm_relations This setting sets the maximum number of relations (tables and indexes) that will be tracked within the freespace map. Its value is a number equal to one relation, with a default setting of 1,000. For most people, this amount is enough, but setting this too low can cause serious performance issues, so it is best to occasionally verify you have set it appropriately. You can determine the number of relations that are needed by using the following query: SELECT count(*) FROM pg_class WHERE relkind IN (’r',’t'); Since this setting is set cluster-wide, you need to run it once on each database within the cluster and add the results together to get the proper level needed. This value requires a full restart of PostgreSQL for any changes to take effect. max_fsm_pages The max_fsm_pages setting controls the maximum number of disk pages that will be tracked within the free space map. It takes a value equal to one page, with a minimum value equal to 16 max_fsm_relations, and a default setting of 20,000. This value is critical in helping to manage the underlying disk pages used, and should be set high enough to handle all pages that are part of an update or deletion between vacuums. The easiest way to determine an appropriate level is to periodically run VACUUM VERBOSE on the database running under a production-level load, which will produce a summary of the number of disk pages modified, and a note regarding what this setting should be set to if it is too low. Also be aware that this setting is set cluster-wide, so if you have multiple databases in
You want to have a cheap webhost for your apache application, then check apache web hosting services.

596 CHAPTER 26 POSTGRESQL ADMINISTRATION Table 26-3. OS-Specific Methods for Starting PostgreSQL Operating System Alternative Start/Stop Method Debian Provides a pg_ctlcluster script to control different PostgreSQL options FreeBSD Provides a script in /etc/rc.d called 010.pgsql.sh Red Hat Provides a standard init script called postgresql, available in /etc/init.d/ Windows Provides shortcuts in the Start menu, and can also be controlled from the Services menu Tuning Your PostgreSQL Installation Once you have your PostgreSQL server up and running, you will want to look into tuning your installation for maximum performance. This is accomplished by changing parameters within the postgresql.conf configuration file, which is normally found within the PGDATA directory of your PostgreSQL installation. As of PostgreSQL 8.0, there were more than 100 different options for configuring your PostgreSQL server; however, only a small set of those is needed for tuning. In this section, we walk through the most important options for configuring PostgreSQL. Managing Resources The first group of settings we look at focuses on managing the amount of resources your PostgreSQL server will use. Because PostgreSQL is designed to run on minimal hardware, the default settings can often be considerably low for running on modern hardware. For this reason, these settings are generally the first things you will want to adjust on your system. shared_buffers The shared_buffers setting controls the amount of shared memory used by PostgreSQL. Its value is a number where 1 unit represents 8,192 bytes of memory. The minimum is 16, or twice the number of maximum allowed connections, whichever is greater; the default is typically 1,000. For tuning, many people suggest setting this parameter to a value equal to 20 percent of the RAM that will be dedicated to PostgreSQL and then adjusting down to find the best performance for your workload. General usage has demonstrated that increasing this value over 10,000 is usually not helpful, so on systems with large amounts of RAM, you might want to start at this level. This value requires a full restart of PostgreSQL for any changes to take effect. work_mem This setting, also known as sort_mem prior to version 8, controls the amount of memory that can be used for internal sort operations and hash tables before these operations switch to using temporary disk files. Its value is a number equivalent to 1KB; the default value is 1024KB (1MB). While it is optimal to avoid using disk files where possible, it is important to remember that this setting operates per sort, not per query, so setting the value too high can cause your system to dedicate too much memory to a given query. Consider an example in which you have a query that involves joining two tables with a hash-join, returning a distinct result set, which in turn is ordered by an arbitrary column in the result. This single query would involve at least three sort operators, and so would allow up
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

CHAPTER 26 POSTGRESQL ADMINISTRATION 595 Table 26-2. pg_ctl Options Option Explanation U On Windows, specifies the username for the user to start the service. P On Windows, specifies the password for the user to start the service. Since there are a number of command modes and options, there are bound to be some cases that do not apply to your situation. However, let s take a closer look at some common examples to better show how the command syntax comes together. The following command starts PostgreSQL, waiting for the server to complete startup before returning, and logging any output to the file called my_log: pg_ctl -w -l my_log start The following is the most aggressive of the three ways to stop PostgreSQL using pg_ctl: pg_ctl stop -m immediate The different stop modes work as follows: Smart mode: The default stop mode; it waits for all clients to disconnect and then shuts down the server. Fast mode: Causes all active transactions to be rolled back, forcibly disconnects any client connections, and then shuts down the server. Immediate mode: Simply aborts all active server processes before shutting down. Since this is not a clean shutdown, the server goes through a recovery run upon restart. In general, it is best to attempt to stop PostgreSQL with the least forcible stop mode, and then increase its aggressiveness if needed. The following command would restart the PostgreSQL server, making it run on port 5480 after restart: pg_ctl -o “port=5480″ restart The following command causes the database initialized in /var/lib/pgsql/data to reread its configuration files. This is a commonly executed command when changing configuration settings in the postgresql.conf or pg_hba.conf files. pg_ctl -D /var/lib/pgsql/data reload Operating System Commands Many operating systems (OSs) have their own ways to start and stop a PostgreSQL server. Usually, these options are created by packages for that specific operating system. Since those options are platform-dependent, we won t go into the details of each of these methods. However, you should be aware that there may be another method for starting and stopping your server. Table 26-3 lists several operating systems and their alternative methods for starting the PostgreSQL server. For more details on these methods, please consult the documentation provided with either your OS or the package that installs PostgreSQL on your system.
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

594 CHAPTER 26 POSTGRESQL ADMINISTRATION buffer processing. While you can call this postmaster executable process directly, the recommended way is to use the pg_ctl program. Using pg_ctl This program can be used for both starting and stopping PostgreSQL, and provides a number of options for doing so. First, take a look at Table 26-1, which lists and describes the different command types that can be involved with pg_ctl. Table 26-1. pg_ctl Command Modes Command Explanation start Starts a new postmaster process stop Stops a running postmaster process restart Stops a running postmaster and then starts it again reload Sends a signal to the postmaster to reload its configuration files status Determines if a postmasteris currently running kill Sends a specific signal to a specified process (new in PostgreSQL 8.0) register Allows you to register a system service on Windows platforms unregister Allows you to unregister a system service that was previously registered As you can see, the pg_ctl command is quite versatile for controlling your PostgreSQL database server. In addition to these command modes, pg_ctlalso takes a number of different options, which are listed and described in Table 26-2. Table 26-2. pg_ctl Options Option Explanation D datadir Specifies the location of the PostgreSQL database files. Defaults to whatever PGDATA is set to. l filename Logs server output to the file specified. Creates the file if it does not exist. m mode Specifies the shutdown mode (smart, fast, or immediate). o options Allows specific options to be passed directly to the postmaster process. p path Specifies the location of the postmaster executable. Defaults to the same directory as pg_ctl. s Specifies that no informational messages will be output, only errors. w Waits (up to 60 seconds) for the start or shutdown to complete. Defaults to shutdown. W Do not wait for start or shutdown to complete. Defaults to start and restart. N On Windows, specifies the name of the system service to register.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

CHAPTER 26 PostgreSQL Administration There is an old saying that with great power, comes great responsibility. Though not originally uttered in reference to database software, it certainly seems a fitting way to describe the current systems that exist in the market. If you want a powerful, enterprise class system, you likely have to deal with a system that requires an onerous amount of tuning with an exorbitant number of knobs and switches, many of which have nothing to do with the feature you really intend to use. On the other end are database systems that may be quick and easy to install, but provide only the most basic database functionality. With PostgreSQL, the developers have tried to strike a balance between a high level of features and tuning parameters, but implemented in a way that is almost hidden from those who do not need to use those options. This makes PostgreSQL one of the easiest databases to administer, even when working within an enterprisecritical environment, without having to sacrifice functionality. In this chapter, we take a look at some of these knobs and switches that you can use to help tune your PostgreSQL system for maximum performance, and go over some of the basic system maintenance tasks that you should be aware of if you are going to administer a PostgreSQL system. By the end of this chapter you should be familiar with: Starting and stopping a PostgreSQL server The configuration variables most important for system tuning and smooth operation Creating and working with tablespaces System maintenance tasks, including VACUUM and ANALYZE How to upgrade between versions of PostgreSQL Backup and recovery of a PostgreSQL system While working with these options is not hard, it is an important topic. Even if you don t need to maintain a production environment, many of the tasks described here will be helpful for keeping your own development systems up and running. Starting and Stopping the Server The first task you need to be familiar with is starting and stopping your database server. When you start PostgreSQL, what you are doing is launching the postmaster executable, which fires up a process for itself, as well as two subprocesses, one for statistics processing and one for
Check Tomcat Web Hosting services for best quality webspace to host your web application.

CHAPTER 25 INSTALLING POSTGRESQL This starts PostgreSQL, using the database cluster residing in /usr/local/pgsql/data, and logging messages to a file named logfile found in /usr/local/pgsql/logs. The logs directory doesn t exist, by default, so you need to create it if you want to follow this example verbatim. Of course, it s recommended that you consider logging PostgreSQL information to a centralized logging location along with other services, although the process for doing so is beyond the scope of this book. Again, if you re running Windows, you need to change the paths accordingly. Of course, if you chose to install PostgreSQL as a service, then you can both start and stop it within the Services control panel. Finally, you can verify PostgreSQL is running by logging into it using the psql client: postgres$ psql template1 Should you receive a welcome message from psql, everything is working as intended! Note that psql is introduced in significant detail in Chapter 27. Summary This chapter set the stage for beginning your experimentation with the PostgreSQL server. You learned not only how to download and install PostgreSQL, but also how to configure it to best fit your administrative and application needs. Other configuration issues are revisited throughout the remainder of this book as necessary. In the next chapter you ll learn how to effectively manage your newly installed PostgreSQL server, beginning with a detailed introduction to pg_ctl. You ll also learn more about advanced configuration options, how to create and manage tablespaces, how to perform system maintenance tasks using Vacuum and Analyze, and how to both back up and recover your data.
We recommend high quality webhost to host and run your jsp application: christian web host services.

590 CHAPTER 25 INSTALLING POSTGRESQL The chapters that follow go into significant detail regarding some of the concepts discussed in this section, so this is intended only as a brief overview to get you started. Note If you intend to operate PostgreSQL on any publicly accessible server, you are strongly urged to browse ahead to the opening sections of Chapter 29, which outline numerous steps that you should take to secure a PostgreSQL server. On both Linux and Windows, you can determine whether the daemon is presently running by using the pg_ctl command. On Linux the command should be executed like this: postgres$ pg_ctl status -D /usr/local/pgsql/data On Windows, you need to change the path accordingly, but the command is the same: %>pg_ctl status -D C:pgsqldata Tip You can forego specifying the data directory by setting the PGDATA environment variable, one of numerous variables that can affect your interaction with PostgreSQL. On Linux this is accomplished by modifying either /etc/profile, which makes the variable available to all users, or, for a specific user, the user s shellspecific configuration file (for example, .bash_profile). Then, either log out and log back in, or execute the source command if your shell supports it. On Windows this is accomplished by navigating to the Windows Control Panel directory and choosing the System panel. Click Advanced, and then Environment Variables. Click the New button, and assign PGDATA to the Variable name field, and assign the corresponding data path to the Variable value field. You ll need to log on and back off for these changes to take effect. If the daemon isn t running, you ll see the following message: pg_ctl: neither postmaster nor postgres running If the daemon is running, you ll see something to the effect of: pg_ctl: postmaster is running (PID: 4348) /usr/local/pgsql/bin/postmaster “-D” “/usr/local/pgsql/data” Because this is your first encounter with PostgreSQL, it s presumed that the server is offline. To start it, execute the following command: postgres$ pg_ctl start -D /usr/local/pgsql/data -l /usr/local/pgsql/logs/logfile &
You want to have a cheap webhost for your apache application, then check apache web hosting services.

CHAPTER 25 INSTALLING POSTGRESQL Figure 25-2. Enabling contributed modules 8. Ready to Install: PostgreSQL is ready to be installed. Go ahead and click Next to install the software. Once completed, you ll be notified of a successful installation and invited to join the pgsql-announce mailing list, which offers information regarding new releases and bug fixes. If you d like to subscribe, click the button, which takes you to a corresponding Web page. In any case, click Finish to exit the installer. Congratulations, PostgreSQL is now installed! Installing PostgreSQL on Windows 95, 98, and ME The PostgreSQL 8 installer does not function on Windows versions 95, 98, and Me. However, an alternative solution is available for those of you unfortunate enough to still be using these platforms. It involves installing the Cygwin Linux environment for Windows (http:// www.cygwin.com/). This issue likely affects a very small population of readers, so we ve opted to forego a detailed summary of the process and instead direct interested parties to the following Web page, which comprehensively covers the necessary steps: http://www.postgresql.org/docs/faqs.FAQ_CYGWIN.html Caution At the time of this writing, the latest available Cygwin PostgreSQL version is 7.4.5-1. Therefore, readers using this version will be unable to take advantage of the version 8 specific features discussed throughout this book. Starting PostgreSQL for the First Time We imagine that you re eager to test your newly installed server, so this concluding section guides you through a few steps that will help you to verify whether everything is running properly.
Check Tomcat Web Hosting services for best quality webspace to host your web application.

588 CHAPTER 25 INSTALLING POSTGRESQL Figure 25-1. Initializing the database cluster 6. Enable Procedural Languages: If you chose to install PostgreSQL as a service, you also have the option to enable one or several procedural languages, which can be used to write user-defined functions (introduced in Chapter 32). At present, seven choices are available for Windows: PL/pgSQL (enabled by default), PL/Perl, PL/Perl (untrusted), PL/Python (untrusted), PL/Tcl, PL/Tcl (untrusted), and PL/Java (trusted and untrusted). PL/pgSQL is a procedural language that allows you to integrate a series of SQL statements and procedural programming logic together to accomplish a more complex task than what is accomplished with SQL statements alone. PL/Perl offers the same capabilities as PL/pgSQL, but the code is written using the Perl language. The difference between the trusted and untrusted versions of PL/Perl is that in the trusted version, some of Perl s features are disabled (file handle operations and use of USE and REQUIRE, in particular) to ensure a higher level of security, whereas in the untrusted version, you have complete autonomy to use these features. PL/Python allows for the creation of userdefined functions using the Python language, PL/Tcl with the Tcl language, and PL/Java with the Java language. Choose which languages you think might be of use to you and then click Next to continue. Some of these options may be disabled depending upon whether the necessary software is installed on your system. For instance, to activate Perl and Tcl support, you need to install ActiveState (http://www.activestate.com/), ActivePerl, and ActiveTcl packages, respectively. 7. Enable Contrib Modules: This step, shown in Figure 25-2, allows you to enable additional, nonstandard functionality that may be useful depending upon your particular environment. Introducing each of the modules listed in Figure 25-2 is beyond the scope of this chapter, because most will be of no use to the typical reader. However, should you require the use of, for instance, a data structure or some special behavior not otherwise available in the default distribution, be sure to consult these modules before endeavoring to implement the feature. For instance, enabling the ISBN and ISSN module results in the addition of a datatype capable of representing ISBN and ISSN numbers used to identify books and serial publications, respectively.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

CHAPTER 25 INSTALLING POSTGRESQL 5. Initialize the Database Cluster: If you chose to install PostgreSQL as a service, you also have the option to further tweak the server s default configuration. Pictured in Figure 25-1, this screen s title may be a tad misleading to some readers, as cluster tends to be defined as a database or other server type consisting of more than one node. However, the PostgreSQL community defines this term as a collection of databases accessed through a particular daemon instance. With that in mind, you can see that there are several options presented on this screen: 1. Initialize Database Cluster: This option determines whether the database cluster should be initialized (created). Of course, you want to leave this enabled. 2. Port Number: By default, the PostgreSQL server accepts connections on port 5432. Unless you have specific reasons for changing this value, using port 5432 is fine. 3. Addresses: Left unchecked, this setting disallows any PostgreSQL connections other than those originating from the local server. If you plan to run PostgreSQL and the application on the same server (for instance, run a Web site in which both the Web and database server reside on the same machine), leave this unchecked. Otherwise, enable this feature. If you require the ability to connect from remote locations, keep in mind that this isn t the only required step. You also need to modify the pg_hba.conf file, introduced in Chapter 29. 4. Locale: This setting determines PostgreSQL s default locale, which defines settings specific to a particular culture, such as character, number, and monetary formatting and character ordering. Over 150 locales are presently supported, including locales targeting areas as far flung as Greece, Finland, Ecuador, and Thailand. If you re in the United States, set this value to English, United States. 5. Encoding: Because of the disparity of characters, accent marks, and character ordering among languages, you want to make sure that PostgreSQL properly handles encoding according to your specific needs. You do so by choosing the proper encoding with this option. By default this is set to SQL_ASCII. 6. PostgreSQL Administrator Account: Next up are the administrator (Superuser) name and corresponding Password fields. Note that this is different from the previously created account used to operate the service daemon it s the PostgreSQL account that you ll use to administer the internal server workings such as user and database creation. For security reasons, the chosen password should in no circumstances be the same as that used for the service daemon user, assuming that you specified one rather than allowing PostgreSQL to choose one for you. By default, this username is set to postgres, although you re free to choose any name you please.
You want to have a cheap webhost for your apache application, then check apache web hosting services.