Data Management

Avoid command-line management of PostgreSQL databases with phpPgAdmin

Open source database administrators will rejoice over the help offered by phpPgAdmin. This tool will make working with your PostgreSQL database a snap. Scott Lowe shows you how to install and use it.


Downloading and installing an open source database such as PostgreSQL or MySQL to run a dynamically generated Web site is a great help for those who administer these sites. Managing and maintaining one of these databases can be a bit of a chore, though, because they’re both managed with command-line utilities. In this Daily Feature, I’ll walk you through an installation and configuration of a tool used to manage the PostgreSQL open source database platform, phpPgAdmin.

The missing link
WebPg was to be the next release of this management tool. However, when I went to the sourceforge.net project page to see if there were any new releases beyond the initial alpha release, I found that the download for WebPg was gone. In its place was a note from the development team members indicating that they had found major problems with the design and had scrapped it. They’re still planning a complete rewrite of phpPgAdmin, but it won’t be WebPg. I am quite disappointed with this news because I’ve used both phpPgAdmin and WebPg and I much preferred WebPg’s interface and could see the great potential that it had.

The phpPgAdmin tool is a server-side Web application. Once it’s downloaded and installed, it’s accessed via a Web browser on a client machine. The system requirements for phpPgAdmin are as follows:
  • PHP 3.x or above, but this PHP 4 or higher is highly recommended
  • PostgreSQL database 6.5 or higher, but 7 or higher is recommended
  • A Web server with PHP capability
  • A version 4.0 or better browser (Netscape 6 is not recommended.)

The current released and stable version of phpPgAdmin is version 2.3.1, released on Oct. 30, 2001. In addition, version 2.4.beta 1 is available; it’s stable, in my experience, and was released on Nov. 25. Both of the downloads can be reached via links at the project’s home page. Here, I’ll use the current beta software, version 2.4.beta 1. For my test installation, I’ll download the distribution into my home directory on my Linux server. I’m also using Apache and PostgreSQL installed on the same server.

Install and configure phpPgAdmin
First, I’ll install and configure an installation of phpPgAdmin in its own subdirectory under the Apache htdocs folder, which is the root for documents on my Web server. Before I begin, I’ll log in as the root user. Next, I’ll switch to the htdocs folder by typing cd /usr/local/apache/htdocs, which is the root of my Web server.

To expand the distribution that was downloaded, I’ll type gunzip -dc /home/slowe/phpPgAdmin_2-4-beta-1.tar.gz | tar xvf – at the command prompt. This creates a directory named phpPgAdmin under the htdocs directory. Next, I must switch to the phpPgAdmin directory that was just created with the expansion of the program distribution with the cd /usr/local/apache/htdocs/phpPgAdmin command.

The installation of phpPgAdmin is very simple and since it depends on PHP and PostgreSQL, there’s nothing to compile. All that is installed is a set of PHP scripts that serve various purposes.

The second step to getting this product working is setting up a few configuration parameters that are required for it to be able to find and log in to the PostgreSQL installation.

It’s all in the location
It’s important to note that phpPgAdmin doesn’t have to be installed on the same server as PostgreSQL.

All of the primary configuration options are stored in a PHP file which phpPgAdmin uses as a global configuration file. The distribution includes a file named config.inc.php-dist, which must be copied to config.inc.php. It’s best to copy rather than rename this file just in case the configuration file gets damaged due to user error. To do this, I’ll run the command cp config.inc.php-dist config.inc.php from within the directory housing the file.

I’ll edit this file and modify a few parameters to match my PostgreSQL configuration in my favorite text editor (Pico, a part of the Pine e-mail application) with the command pico config.inc.php. Figure A shows the various configuration parameters.

Figure A


In many of these parameters, a [1] follows the name of the parameter. The phpPgAdmin tool can administer a number of servers using the same configuration file and the number in the brackets is the index for the array, starting at the number 1. The parameter cfgServerDefault tells phpPgAdmin which server it should connect to initially. The number after the equal sign (=) in this parameter corresponds to a configured server array value. If a zero (0) is used for the cfgServerDefault, then a list of available servers will be presented to the user to choose from instead of an automatic connection to a server.

The looks

Figure B
The phpPgAdmin login screen


Once these parameters are set up properly, it’s time to browse to the phpPgAdmin site to see what it looks like and what it can do. When I browse to http://SERVER_ADDRESS/phpPgAdmin/index.php, where SERVER_ADDRESS is the address of the server phpPgAdmin is installed on, I’m presented with the login screen shown in Figure B.

Figure C
Along the left-hand side of the window is a list of the databases that are installed on my lab server.


Once I have typed in the appropriate username and password, I click the Login button to continue on to the phpPgAdmin main administration screen, which looks like the one in Figure C. Links below the list of installed databases allow the administration of users, groups, and reporting features. Clicking on the plus sign (+) next to a particular database opens a list of the tables that are contained within. Choosing a table from the list brings up complete details about that table, as shown in Figure D.

Figure D
In the top portion is an overview of the fields included in the chosen table.


Each field has various options: Change, Drop, Index, and Unique. The first two allow the user to either change the field or drop it from the database, while the last two allow the user to index the field or create a unique index.

Below the table definition are a number of other options to manage the table. From this screen, the data can be viewed, the SQL structure can be viewed, and the table can be renamed or copied to a new table. Additionally, new fields can be added to the table and privileges can be assigned to specific users.

Conclusion
The phpPgAdmin tool does an excellent job of replacing most of the command-line functions via this easy-to-use Web interface. It also allows the user to easily copy data between tables, create a flat file out of data in the contents of a database, and make modifications to the structures without having to use a single line of SQL.

What does the future hold for this program? It was to have been WebPg, but that idea has been officially scrapped in favor of a different path. The developers now plan to create a base framework for a new administration package that will support PostgreSQL administration but that will also be extensible via modules to support other database types. It will be interesting to see how this team can migrate this excellent package to other platforms.

Want more?
Would you like to see more database and open source content on TechProGuild? Send Jack a note.

 

Editor's Picks