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

CHAPTER 27 THE MANY POSTGRESQL CLIENTS Learning More About Supported SQL Commands Once you re logged into the server, execute h to view all available commands. At the time of this writing, there were 109 commands. To view all of them, execute the following: corporate=> h This produces the following output: Available help: ABORT CREATE LANGUAGE DROP VIEW ALTER AGGREGATE CREATE OPERATOR CLASS END ALTER CONVERSION CREATE OPERATOR EXECUTE ALTER DATABASE CREATE ROLE EXPLAIN ALTER DOMAIN CREATE RULE FETCH ALTER FUNCTION CREATE SCHEMA GRANT ALTER GROUP CREATE SEQUENCE INSERT ALTER INDEX CREATE TABLE LISTEN ALTER LANGUAGE CREATE TABLE AS LOAD ALTER OPERATOR CLASS CREATE TABLESPACE LOCK ALTER OPERATOR CREATE TRIGGER MOVE ALTER ROLE CREATE TYPE NOTIFY ALTER SCHEMA CREATE USER PREPARE ALTER SEQUENCE CREATE VIEW PREPARE TRANSACTION ALTER TABLE DEALLOCATE REINDEX ALTER TABLESPACE DECLARE RELEASE SAVEPOINT ALTER TRIGGER DELETE RESET ALTER TYPE DROP AGGREGATE REVOKE ALTER USER DROP CAST ROLLBACK ANALYZE DROP CONVERSION ROLLBACK PREPARED BEGIN DROP DATABASE ROLLBACK TO SAVEPOINT CHECKPOINT DROP DOMAIN SAVEPOINT CLOSE DROP FUNCTION SELECT CLUSTER DROP GROUP SELECT INTO COMMENT DROP INDEX SET COMMIT DROP LANGUAGE SET CONSTRAINTS COMMIT PREPARED DROP OPERATOR CLASS SET ROLE COPY DROP OPERATOR SET SESSION AUTHORIZATION CREATE AGGREGATE DROP ROLE SET TRANSACTION CREATE CAST DROP RULE SHOW CREATE CONSTRAINT TRIGGER DROP SCHEMA START TRANSACTION CREATE CONVERSION DROP SEQUENCE TRUNCATE CREATE DATABASE DROP TABLE UNLISTEN CREATE DOMAIN DROP TABLESPACE UPDATE CREATE FUNCTION DROP TRIGGER VACUUM CREATE GROUP DROP TYPE CREATE INDEX DROP USER
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

616 CHAPTER 27 THE MANY POSTGRESQL CLIENTS VERSION = ‘PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc(GCC) 3.3.5 (Debian 1:3.3.5-8ubuntu2)’ AUTOCOMMIT = ‘on’ VERBOSITY = ‘default’ PROMPT1 = ‘%/%R%# ‘ PROMPT2 = ‘%/%R%# ‘ PROMPT3 = ‘>> ‘ DBNAME = ‘corporate’ USER = ‘websiteuser’ PORT = ‘5432′ ENCODING = ‘SQL_ASCII’ HISTFILE = ‘~/.psql_history’ HISTSIZE = ‘500′ Storing Configuration Information in a Startup File PostgreSQL users have two startup files at their disposal, both of which can be used to affect psql s behavior on the system-wide and user-specific levels, respectively. The system-wide psqlrcfile is located within PostgreSQL s etc/ directory on Linux and within %APPDATApostgresql on Windows, whereas the user-specific file is stored within the user s home directory and prefixed with a period (.), as is standard for configuration files of this sort. Note On Windows, the system-wide psqlrc file should use .conf as the extension. Also, to determine the location of %APPDATA%, open a command prompt and execute echo %APPDATA%. Further, on both Linux and Windows, you can create version-specific startup files by appending a dash and specific version number to psqlrc. For example, a system-wide startup file named psqlrc-8.1.0 will be read only when connecting to a PostgreSQL server running version 8.1.0. Both files support the same syntax, and anything stored in the system-wide file can also be stored in the user-specific version. However, keep in mind that if both files contain the same setting, anything found in the user-specific version will override the value declared in the system-wide version, because the user-specific version is read last. So what might one of these files look like? The following presents an example of what you might expect to find within a user s .psqlrc file: # Set the prompt set PROMPT1 ‘%n@%m::%`date +%H:%M:%S`> ‘ # Set the location of the history file set HISTFILE ~/pgsql/.psql_history
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

CHAPTER 27 THE MANY POSTGRESQL CLIENTS Storing psql Variables and Options Of course, heavy-duty command-line users know that repeatedly entering commonly used commands can quickly become tedious. To eliminate such repetition, you should take advantage of aliases, configuration files, and environment variables at every possibility. To set an environment variable from within psql, just execute the set command followed by the variable name and a corresponding value. For example, suppose your database consists of a table named apressproduct. You re constantly working with this table and, accordingly, are growing sick of typing in its name. You can forego the additional typing by assigning an environment variable, like so: corporate=> set ap ‘apressproduct’ Now it s possible to execute queries using the abbreviated name: corporate=> SELECT name, price FROM :ap; Note that a colon must prefix the variable name in order for it to be interpolated. psql also supports a number of predefined variables. A list of the most commonly used psql variables are presented in Table 27-2. Table 27-2. Commonly Used psql Variables Variable Description PAGER Determines which paging utility is used to page output that requires more space than a single screen. PGDATABASE The presently selected database. PGHOST The name of the server hosting the PostgreSQL database. PGHOSTADDR The IP address of the server hosting the PostgreSQL database. PGPORT The post on which the PostgreSQL server is listening for connections. PGPASSWORD Can be used to store a connecting password. However, this variable is deprecated, so you should use the .pgpass file instead for password storage. PGUSER The name of the connected user. PSQL_EDITOR The editor used for editing a command prior to execution. This feature is particularly useful for editing and executing long commands that you may wish to store in a separate file. After looking to PSQL_EDITOR, psql will then examine the contents of the EDITORand VISUAL variables, if they exist. If examination of all three variables proves inconclusive, notepad.exe is executed on Windows, and vi on all other operating systems. To view a list of all presently set variables, execute set without passing it any parameters, like so: corporate=> set For instance, executing this command on our Ubuntu server produces:
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

614 CHAPTER 27 THE MANY POSTGRESQL CLIENTS does a great job of succinctly defining each command, this section highlights just a few of the commands that you might find particularly useful. Further, many of the commands pertinent to the review of existing databases, schemas, tables, and users are introduced in the coming chapters. Note psql s tab-completion feature can save you a great deal of typing when executing commands. As you work through the following examples, tap the Tab key on occasion to review its behavior. Connecting to a New Database Over the course of a given session, you ll often need to work with more than one database. To change to a database named vendor, execute the following command: corporate=> connect vendor You can save a few keystrokes by using the abbreviated version of this command, c. Executing Commands Located Within a Specific File Repeatedly entering a predetermined set of commands can quickly become tedious, not to mention error-prone. Save yourself from such repetition by storing the commands within a separate file and then executing those commands by invoking the i command and passing along the name of the file, like so: corporate=> i audit.sql Editing a File Without Leaving psql If you are relying on commands found in a separate file, the task of repeatedly executing the command and then exiting psql to make adjustments to those commands from within an editor can become quite tedious. To save yourself from the tedium, you can edit these files without ever leaving psql by executing e. For example, to edit the audit.sql file used in the previous example, execute the following command: corporate=> e audit.sql This will open the file within whatever editor has been assigned via the PSQL_EDITOR variable (see Table 27-2 for more information about this variable). Once you ve completed editing the file, save the file using the editor s specific save command and exit the editor (:wq in vim, for instance). You will be returned directly back to the psql interface, and can again execute the file using i if you wish. Sending Query Output to an External File Sometimes you may wish to redirect query output to an external file for later examination or additional processing. To do so, execute the o command, passing it the name of the desired output file. For instance, to redirect all output to a file named output.sql, execute the o command, like so: corporate=> o output.sql
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

CHAPTER 27 THE MANY POSTGRESQL CLIENTS Commonplace psql Tasks psql offers administrators, particularly those who prefer or are particularly adept at working with the command line, a particularly efficient means for interacting with all aspects of a PostgreSQL server. Of course, unlike the point-and-click administration solutions introduced later in this chapter, you need to know the command syntax to make the most of psql. This section shows you how to execute the most commonplace tasks using this powerful utility. Logging Onto and Off the Server Before you can do anything with psql, you need to pass along the appropriate credentials. The most explicit means for passing these credentials is to preface each parameter with the appropriate option flag, like so: %>psql -h 192.168.3.45 -d corporate -U websiteuser Upon execution, you are prompted for user websiteuser s password. If the username and corresponding password are validated, you are granted access to the server. If the database happens to reside locally, you can forego specifying the hostname, like so: %>psql corporate websiteuser In either case, once you ve successfully logged in, you see output similar to the following: Welcome to psql 8.1.2, the PostgreSQL interactive terminal. Type: copyright for distribution terms h for help with SQL commands ? for help with psql commands g or terminate with semicolon to execute query q to quit corporate=> Note that the prompt specifies the name of the chosen database, which can be useful particularly if you re simultaneously logged in to numerous servers. If you re logged in as a superuser, the prompt will appear a bit differently, like so: corporate=# Once you ve completed interacting with the PostgreSQL server, you can exit the connection using q, like so: corporate=> q Doing so returns you to the operating system s command prompt. psql Commands Once you ve entered the psql utility, execute ?to review a list of psql-specific commands. This produces a list of more than 50 commands divided into six categories. Because this summary
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

612 CHAPTER 27 THE MANY POSTGRESQL CLIENTS general queries such as table selections and insertions, and do much more. In this section, you ll learn about the many features at your disposal when using this terse yet powerful client. psql Options The psql utility is executed from the command line by executing the psql command generally alongside one or several options. Its prototype looks like this: psql [option…][dbname [username]] At a minimum, you need to pass along the dbname and username parameters if these values aren t stored within the .psqlrc configuration file or specified within certain global variables (see the later section Storing psql Variables and Options ). Therefore, to connect a user website to the database corporate found on the PostgreSQL server located on IP address 192.168.3.45, you d execute the following command: %>psql -h 192.168.3.45 corporate website To see the other syntax variations for this task, see the section Logging Onto and Off the Server, later in this chapter. In most cases, these three parameters are all that you will require for typical operations (unless you re connecting locally, meaning the host address won t be required), but you may occasionally wish to pass along various options that will affect psql s behavior. The most commonly used options are presented in Table 27-1. Table 27-1. Common psql Client Options Option Description -c COMMAND Executes a single command and then exits. -d NAME Declares the destination database. The default is your current username. -f FILENAME Executes commands located within the file specified by FILENAME, and then exits. -h HOSTNAME Declares the destination host. –help Shows the help menu and then exits. -l Lists the available databases and then exits. -L FILENAME Sends a session log to the file specified by FILENAME. -p PORT Declares the database port used for the connection. The default is 5432. -U NAME Declares the connecting database username. The default is the current username. -X Does not read the system-wide or user-specific startup file (psqlrc or ~/.psqlrc, respectively). Although manually passing these options along is fine if you need to do so only once or a few times, it can quickly become tedious and error-prone if you have to do so repeatedly. To eliminate these issues, consider storing this information in a configuration file, as discussed in the later section Storing psql Variables and Options.
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

CHAPTER 27 The Many PostgreSQL Clients PostgreSQL is bundled with quite a few utilities, or clients, each of which provides interfaces for carrying out various tasks pertinent to server administration. This chapter offers an in-depth introduction to the most prominent of the bunch, namely psql. Because the psql manual already does a great job at providing a general overview of each client, we ll instead focus on those features that you re most likely to use regularly in your daily administration activities. We ll show you how to log on and off a PostgreSQL server, explain how to set key environment variables both manually and through configuration files, and offer general tips intended to help you maximize your interaction with psql. Also, because many readers prefer to use a graphical user interface (GUI) to manage PostgreSQL, the chapter concludes with a brief survey of three GUIbased administration applications. As is the goal with all chapters in this book, the following topics are presented in an order and format that are conducive to helping a novice learn about psql s key features while simultaneously acting as an efficient reference guide for all readers. Therefore, if you re new to psql, begin with the first section and work through the material and examples. If you re a returning reader, feel free to jump around as you see fit. Specifically, the following topics are presented in this chapter: An introduction to psql: This chapter introduces the psql client along with many of the options that you ll want to keep in mind to maximize its usage. Commonplace psql tasks: You ll see how to execute many of psql s commonplace commands, including how to log on and off a PostgreSQL server, use configuration files to set environment variables and tweak psql s behavior, read in and edit commands found within external files, and more. GUI-based clients: Because not all users prefer or even have access to the command line, considerable effort has been put into commercial- and community-driven GUIbased PostgreSQL administration solutions, several of the more popular of which are introduced in this chapter. What Is psql? For those of you who prefer the command-line interface over GUI-based alternatives, psql offers a powerful means for managing every aspect of the PostgreSQL server. Bundled with the PostgreSQL distribution, psql is akin to MySQL s mysql client and Oracle s SQL*Plus tool. With it, you can create and delete databases, tablespaces, and tables, execute transactions, execute
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

610 CHAPTER 26 POSTGRESQL ADMINISTRATION review the release notes of any releases you intend to upgrade through, but most of the time these revision releases tend to be pretty painless. When upgrading between major and minor releases, say between 7.4.2 and 8.0.2, the process is a little more involved. The differences between a major and minor release are fuzzy, and really are no different for practical purposes when discussing migrating between releases. In either case, it is generally the case that the on-disk format for the database will change between these releases. What this means for you is that, when upgrading between major and minor releases, you need to do so using the pg_dump and pg_restore utilities. If you are performing this on a single machine, it is recommended that you install both versions of PostgreSQL in parallel, so that you may use the newer version of pg_dump against the older version of the database. If for some reason you cannot do this, it is still imperative that you run the old pg_dump against your old database before upgrading, so that you will have a copy of the database to load once the newer version is installed. Once the old database has been backed up, you can install and start the new database, and then restore the data into the new version of the database. When upgrading in this manner, it is wise to run an ANALYZE on the upgraded database to ensure that performance information will be set appropriately. Tip Some replication solutions allow replication between versions and, as such, can be used to migrate between two different releases without having to go through a dump and restore. If you have access to a replication solution and need to avoid the downtime involved in the normal upgrade method, this can be a real lifesaver. Summary This chapter presented numerous different administration options and features that are available to PostgreSQL DBAs. We first looked at the basics of starting and stopping your PostgreSQL server. We then walked through a number of different configuration options that are available to help tune your system. We took a look at tablespaces and discussed how using them could help you manage your disk activity. Finally, we examined a number of different database tasks that are common to PostgreSQL, including running VACUUM and ANALYZE, as well as how to go about upgrading between versions. Armed with this information, you are now fully capable of maintaining your own PostgreSQL installation. The next few chapters enable you to expand upon this knowledge by showing you some of the tools available to help you interact with your PostgreSQL server, and by diving deeper into the features of PostgreSQL.
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

CHAPTER 26 POSTGRESQL ADMINISTRATION Table 26-5. pg_restore Options Option Explanation s, –schema-only Restores only the database schema, not any of the data. S, –superuser=username Specifies a superuser to use when disabling triggers. t, –table=table Restores only the specified table. T, –trigger=trigger Restores only the specified trigger. v, –verbose Produces verbose output when restoring. x, –no-privileges, –no-acl Does not emit GRANT/REVOKE commands during restore. –disable-triggers Emits commands to disable triggers during a data-only restore. As you can see, most of the options for pg_restore are similar to those for pg_dump. For clarity, let s take a look at some common pg_restore combinations. The first command restores the archive mydb.tar into the database qa on host dev as user postgres: pg_restore -h dev -U postgres -d qa mydb.tar The next command restores the schema (only) found in the custom-formatted archive file mydb.pgr into a database named test: pg_restore -s -d test -Fc mydb.pgr The final command restores the data (only), disabling triggers as it loads, into the database called test, from the custom-formatted archive file called mydb.pgr: pg_restore -a –disable-triggers -d test -Fc mydb.pgr Upgrading Between Versions PostgreSQL development seems to be moving faster than ever these days. At the time of this writing, PostgreSQL 8.1 was being finalized in an effort to begin testing viable beta releases. This is significant because it s a mere six months after the release of 8.0, which makes 8.1 one of the shortest development cycles yet, for a release that certainly will contain a number of highly anticipated features. Because of this pace of development, you need to be aware of how PostgreSQL releases are designed and, more importantly, what steps you need to take when upgrading between versions. Each PostgreSQL release number contains three sections, corresponding to the major (first section), minor (second section), and revision (third section) releases. Revision releases (for example, upgrading from 8.0.2 to 8.0.3) are the easiest to handle, because the on-disk format for database files is usually guaranteed to remain the same, meaning that upgrading is as simple as stopping your server, installing the binaries from the newer version of PostgreSQL right over top the older version, and then restarting your server. On occasion, there may be some additional steps you need to take (running a SQL statement perhaps), so it is best to
You want to have a cheap webhost for your apache application, then check apache web hosting services.

608 CHAPTER 26 POSTGRESQL ADMINISTRATION pg_restore The pg_restore program is used to restore database dumps that have been created using either pg_dumps tar or custom archive formats. The basic syntax of pg_restoreis certainly straightforward: pg_restore [option] [file name] If the file name is omitted from the command, pg_restore takes its input from standard input. The options for pg_restore are listed in Table 26-5. Table 26-5. pg_restore Options Option Explanation Connection Options h, –host=host Specifies the host to connect to; defaults to PGHOST or local machine. p, –port=port Specifies the port to connect on; defaults to PGPORT or compiled port. U username Specifies the user to connect as; defaults to current system user. W Forces password prompt even if the connecting server does not require it. Backup Options a, –data-only Restores only the data contained in the archive. c, –clean Drops objects before creating them. C, –create Creates the database in the archive and restores into it. d, –dbname Connects to the named database and restores within that database. e, –exit-on-error Exits if an error is encountered; default is to continue and report errors. f, –file=file Specifies an output file for the generated script rather than standard out. F, –format=c|t Specifies the format of the archive; custom or tar-archive. i, –ignore-version Ignores version mismatch between the database and pg_restore. I, –index=index Restores only the named index. l, –list Lists the contents of the archive. L, –use-list=list-file Restores objects in list file in the order listed in the file. n, –schema Restores only the objects or data in the given namespace (i.e. schema). New in 8.1. O, –no-owner Does not execute command to set object ownership. P, –function=function(args) Restores only the specified function name and arguments.
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.