Apache, PHP

Apache, PHP

This document has two sections: basic and advanced. Both cover installing and configuring PHP for use with Apache. The basic section is exclusively Apache and PHP, while the advanced section covers Apache, PHP, and PostgreSQL.

Choose your path, or continue reading

Apache and PHP (Basic)

Apache, PHP, and PostgreSQL (Advanced)

Prerequisite Knowledge

Basic Unix System Administration.

Familiarity with the Apache Web Server.

Software Background

Apache is web server software. It is responsible for listening on a particular port (usually 80, 8000, 8080, or equivalent) for incoming requests for files (usually by a Web browser such as Netscape or Mosaic). Upon receiving a request for a file, Apache returns its contents to the Web browser which is responsible for displaying the hypertext mark-up language (HTML) document contents (images, media, and so forth).

PHP is a hypertext preprocessor. When integrated with web server software (such as Apache), it becomes a powerful mechanism for adding intelligent scripts inside of HTML documents. Certain pages (whose file name typically ends in .php, .php3, or .phtml) requested from the web browser are passed through the PHP engine before being returned to the web browser. The PHP engine examines the page for PHP script, executes the script, and returns an HTML document to the browser. The power of the scripting language stems from its tight integration with major databases (MySQL, PostgreSQL, and others).

PostgreSQL is database software. As an open-source, fully featured database it is a cost-effective means to store application information. PHP and PostgreSQL can be tightly integrated. This allows Apache, albeit indirectly through PHP, to get database queries for particular web pages.

Apache and PHP

Preparation

Switch to the root user:

su -

Install flex. Using Mandrake Linux:

urpmi flex

Download Apache (httpd-2.0.53.tar.bz2) to /usr/local/src:

https://httpd.apache.org

Download PHP (php-4.3.10.tar.bz2) to /usr/local/src:

https://php.net

Install Apache and PHP

Change to the /usr/local/src directory:

cd /usr/local/src

Extract both Apache and PHP (use tab to auto-complete file names):

tar jxf httpd-2.0.53.tar.bz2

tar jxf php-4.3.10.tar.bz2

Configure, build, and install Apache (about 10 minutes):

cd httpd-2.0.53

./configure --prefix=/usr/local/apache --enable-so --enable-rewrite

make && make install

Configure, build, and install PHP (about 10 minutes):

cd ../php*

./configure --with-apxs2=/usr/local/apache/bin/apxs

make && make install

Configure PHP

Copy the php.ini file:

cd /usr/local/php-4.3.10

cp php.ini-dist /usr/local/lib/php.ini

Configure Apache

These configuration items are likely already set for you. But you may wish to verify them, just in case.

Edit Apache configuration file at /usr/local/apache/conf/httpd.conf.

Add LoadModule statement:

LoadModule php4_module modules/libphp4.so

Add PHP-awareness for specific file types:

AddType application/x-httpd-php .php .phtml

AddType application/x-httpd-php-source .phps

Validate Configuration

Start Apache

/usr/local/apache/bin/apachectl restart

Figure out where Apache puts its HTML files (e.g., .html).

Create a PHP file in the same place (e.g., test.php):

<?php

echo 'Hello, World!'

?>

Browse to the PHP script: https://localhost/test.php

Thank You!

I hope you found this article worth the click.

Sponsored Links.

Wink, wink, nudge, nudge.

Apache, PHP, and PostgreSQL

This section of the article is rather out of date. However, the steps should be sufficient to give you a flavour of how to make these three software packages talk with each other.

Estimated Installation Time: Under 2 hours

Download

If not done already, download the software packages in question. (See the next section for a list of file names.)

PostgreSQL from www.postgresql.org.

Apache from www.apache.org.

PHP from www.php.net.

Software Versions

To facilitate updating this document, when version numbers are referenced, they will appear as x.y.z. Substitute in the appropriate values where:

Apache is version 1.3.20

PHP is version 4.0.5

PostgreSQL is version 7.1.2

RedHat Linux is version 7.0

For example, the file postgresql-x.y.z.tar.gz becomes postgres-7.1.2.tar.gz.

Preparation

Except where noted, the instructions must be carried out by root.

If it does not exist, create /usr/local/src:

mkdir /usr/local/src

Copy all related files to /usr/local/src. There should be:

apache_x.y.z.tar.gz

php-x.y.z.tar.gz

postgresql-base-x.y.z.tar.gz

postgresql-test-x.y.z.tar.gz

postgresql-x.y.z.tar.gz

Extract all files:

for i in *.gz; do tar -zxf $i; done

If this doesn't work, manually extract all the files:

tar -zxf apache_x.y.z.tar.gz

tar -zxf php-x.y.z.tar.gz

tar -zxf postgresql-base-x.y.z.tar.gz

tar -zxf postgresql-test-x.y.z.tar.gz

tar -zxf postgresql-x.y.z.tar.gz

Try using the Tab key to auto-complete file names.

If they don't already exist, create two new users:

www

postgres

PostgreSQL Configuring and Installation

Since integrating PHP with PostgreSQL requires the latter's libraries to be available for compiling PHP, PostgreSQL must be installed first.

Change to the PostgreSQL directory:

cd /usr/local/src/postgres-x.y.z

Configure, make, and install PostgreSQL (go grab a drink):

./configure; make; make install

Append the following line to /etc/ld.so.conf:

/usr/local/pgsql/lib

Run the dynamic linker:

ldconfig -v

Apache Configuring for PHP

Apache must be configured before PHP can be configured.

Change to the Apache directory:

cd /usr/local/src/apache_x.y.z

Configure Apache (to be installed in /usr/local/apache)

./configure --prefix=/usr/local/apache

PHP Configuring and Installation

Now that Apache has been configured, PHP can be configured.

Change to the PHP directory:

cd /usr/local/src/php-x.y.z

Configure PHP with Apache and PostgreSQL in mind:

./configure --with-apache=/usr/local/src/apache_x.y.z

--with-pgsql=shared

--enable-track-vars

--with-xml

Make and install PHP (go grab a drink):

make; make install

Copy PHP's ini file:

cp php.ini-dist /usr/local/lib/php.ini

Apache Configuring and Installation

Now that PHP has been configured and installed, Apache must be reconfigured to take PHP into account, then installed.

Change to Apache's directory:

cd /usr/local/src/apache_x.y.z

Configure Apache with PHP in mind (libphp4.a is correct; libmodphp4.a is incorrect):

./configure --prefix=/usr/local/apache

--activate-module=src/modules/php4/libphp4.a

Make and install Apache (grab another drink):

make; make install

Tell Apache to link PHP pages with PHP. Edit /usr/local/apache/conf/httpd.conf. Add (or uncomment) the lines:

AddType application/x-httpd-php .php .php3 .phtml

AddType application/x-httpd-php-source .phps

Don't exit the editor just yet, as there's another line to change.

Allowing Database Access

Now that PHP, PostgreSQL and Apache have been installed, they must be hooked up together. Apache's process is usually run as nobody by default. Change this from nobody to the www account.

Setup

Apache and PostgreSQL still need additional pieces of information in order to cooperate vicariously through PHP.

Edit /usr/local/apache/conf/httpd.conf. Find the line:

User nobody

Change it to:

User www

Login as postgres.

Edit /home/postgres/.bashrc (presuming bash is the default shell). Append the following lines:

PGDATA=/home/postgres/database

PATH='$PATH:/usr/local/pgsql/bin'

export PGDATA PATH

Issue the following command to ensure the environment variables are set:

source /home/postgres/.bashrc

Initialize the database with:

initdb

If all went well, the console should read:

This database system will be initialized with username 'postgres'.

This user will own all the data files and must also own the server process.

Creating database system directory /home/postgres/database

Creating database system directory /home/postgres/database/base

Creating database XLOG directory /home/postgres/database/pg_xlog

Creating template database in /home/postgres/database/base/template1

Creating global relations in /home/postgres/database/base

Adding template1 database to pg_database

Creating view pg_user.

Creating view pg_rules.

Creating view pg_views.

Creating view pg_tables.

Creating view pg_indexes.

Loading pg_description.

Vacuuming database.

Success. You can now start the database server using:

/usr/local/pgsql/bin/postmaster -D /home/postgres/database

or

/usr/local/pgsql/bin/pg_ctl -D /home/postgres/database start

For PHP to call upon the database from within HTML files served to web browsers by Apache, PostgreSQL must be told to use the -i option. The PostgreSQL database process would then be started using:

/usr/local/pgsql/bin/postmaster -i -D /home/postgres/database

Create a Database and Table (Relation)

For demonstration purposes, the database will be named AddressBook. It will have one table in it named Addresses. While logged in as postgres, apply the steps that follow.

Start the database process:

postmaster -i -D $PGDATA

If all went well, the console should read similar to:

DEBUG: Data Base System is starting up at [Date/Time]

DEBUG: Data Base System is in production state at [Date/Time]

Create the database called AddressBook:

createdb AddressBook

The console should show a successful reply:

CREATE DATABASE

Create a table inside of AddressBook using psql's interactive mode (the second line will be typed in at the prompt AddressBook=*):

psql AddressBook

CREATE TABLE Addresses ('Name' text, 'Phone' text, 'Email' text);

The console should show a successful reply:

CREATE

Granting Table Access

In order for the Apache process, in coordination with PHP, to modify the Addresses table, PostgreSQL must be told which user (in this example www) has what rights on which table(s). While logged in as postgres, apply the steps that follow.

Create a user inside of AddressBook using psql's interactive mode (same as before):

psql AddressBook

CREATE USER www NOCREATEDB NOCREATEUSER;

While still at the AddressBook prompt, grant permissions:

GRANT all ON Addresses TO www;

The console should show a successful reply:

CHANGE

Restart Apache

Stop then start the Apache web server (do not restart) using:

/usr/local/apache/bin/apachectl stop

/usr/local/apache/bin/apachectl start

PHP and PostgreSQL

Login as www. Copy and paste the following HTML code into a file named /home/www/public_html/add.html, ensuring all directories (and files) are world-readable:

<HTML>

<BODY>

<FORM ACTION='add-entry.php' METHOD='GET'>

<TABLE BORDER=1>

<TR>

<TD>

<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=2>

<TR>

<TD>Name</TD>

<TD><INPUT TYPE='TEXT' NAME='Name' VALUE=''></TD>

</TR>

<TR>

<TD>Phone</TD>

<TD><INPUT TYPE='TEXT' NAME='Phone' VALUE=''></TD>

</TR>

<TR>

<TD>E-mail</TD>

<TD><INPUT TYPE='TEXT' NAME='Email' VALUE=''></TD>

</TR>

<TR>

<TD COLSPAN=2 ALIGN=CENTER>

<INPUT TYPE='SUBMIT' VALUE='Add Entry'>

</TD>

</TR>

</TABLE>

</TD>

</TR>

</TABLE>

</FORM>

</BODY>

</HTML>

Launch a web browser, and visit: https://localhost/~www/add.html. The browser should display a form similar to:

Name

Phone

E-mail

Create another file called add-entry.php, copy and paste the following:

<HTML>

<BODY>

<?PHP

// Connect to PostgreSQL.

$db = pg_Connect( 'host=localhost dbname=AddressBook' );

if( !$db )

// Convert the variables from HTTP request parameters. This is a security

// feature added to the PHP language.

$Name=$_GET['Name'];

$Email=$_GET['Email'];

$Phone=$_GET['Phone'];

// Create an SQL statement to insert the information into the table.

$query = 'INSERT INTO Addresses VALUES( '$Name', '$Phone', '$Email' );';

// Connect to the AddressBook database; run the SQL statement.

$result = pg_Exec( $db, $query );

if( !$result )

// Get the results of the SQL statement.

$rows = pg_NumRows( $result );

if( $rows = 0 )

// Get the record (i.e., row) that was just added.

$query = 'SELECT * FROM Addresses WHERE 'Name' = '$Name';';

$result = pg_Exec( $db, $query );

$row = pg_Fetch_Row( $result, 0 );

$nameResult = $row[0];

$phoneResult = $row[1];

$emailResult = $row[2];

echo 'Name = $nameResult<BR>';

echo 'Phone = $phoneResult<BR>';

echo 'E-mail = $emailResult<BR>';

pg_Close( $db );

?>

<H3>Add Okay!</H3>

</BODY>

</HTML>

Enter information at https://localhost/~www/add.html, select the Add Entry button. The browser should display something similar to:

Name = John Doe

Phone = 1-250-555-1212

E-mail = test@somewhere.org

Add Okay!

At this point, the database AddressBook has a table named Addresses. Inside the table (also called a relation) is an entry with the information that was entered above. This simple example shows just how easy it is to add and retrieve information from a database, using PHP and PostgreSQL. This example leaves much to be desired, such as ensuring that the information is valid, and a search form. Both of those are beyond the scope of this document.

Command Line

From the command line, php does not know about PostgreSQL (although with Apache, it does). This section guides the reader on how to configure PHP so that it can find the PostgreSQL function calls. These steps should be performed by root. If running PHP from the command line is not a requirement, then these steps are optional.

Create /usr/local/php/extensions:

mkdir /usr/local/php/extensions

Move pgsql.so into the newly created directory:

mv /usr/local/src/php-4.0.2/modules/pgsql.so /usr/local/php/extensions

Edit the file /usr/local/lib/php.ini. Change extension_dir to point to /usr/local/php/extensions. Then add a new Dynamic Extension as follows:

extension=pgsql.so

Now PHP should be runnable from a shell prompt, either by calling php directly, or by making executable shell scripts.

Troubleshooting

Francisco, of Quito, Ecuador, tells me the following for PHP5:

./configure --prefix=/usr/local/apache2/php5

--with-apxs2=/usr/local/apache2/bin/apxs --disable-cgi

--enable-force-cgi-redirect --disable-libxml

--libexecdir=/usr/local/apache2/modules

--exec-prefix=/usr/local/apache2/php5

--with-config-file-path=/usr/local/apache2/php5

--with-pgsql

make

su -

make install

You still have to verify that the module is loaded in /usr/local/apache2/conf/http.conf; simply edit the file and uncomment the following lines:

LoadModule php5_module modules/libphp5.so

AddType application/x-httpd-php .php

AddType application/x-httpd-php-sourc .phps

Conclusion

At this point the system should have a complete and stable installation of PHP, Apache, and PostgreSQL.

Copyright 2001-2005 by Dave Jarvis

Using PostgreSQL With PHP

Navigate: PHP Tutorials > PHP > Databases > PostgreSQL

Author: Ghetto024

Date: 03/15/2003

Version 1.0

Experience Level: Unknown

Introduction To PostgreSQL

Recently I've been having a craving for a new database, not because I didn't like MySQL, simply because I wanted to try something else. After doing some research, I decided to learn PostgreSQL. I was amazed at the lack of documentation on using PostgreSQL with PHP. Learning PostgreSQL was a very good experience for me, and after a couple of hours of working with it, I fell in love. This tutorial will go over merely the basic PostgreSQL constructs, and isn't going to go into the more advanced (and very lovely) features of PostgreSQL. This tutorial is intended for people who have never worked with PostgreSQL before, and it will help if you already know SQL. If not, You might have to put a little more effort into understanding this tutorial.

Prerequisites

To follow along with this tutorial, you will need the following:

The PostgreSQL Server already installed

PHP Compiled with PostgreSQL Support

An already made user and a database made for that user

Thats about it, If you need any help installing and setting up PostgreSQL, hit the documentation at PostgreSQL.com.

Connecting to PostgreSQL From PHP

To start off using PostgreSQL from PHP, You'll first need to connect to it. This is accomplished with the pg_connect() function. This function is pretty straightforward and only expects one argument, the connection string. The connection string contains all of the information needed to connect to the database. The arguments available for connection_string includes host, port, tty, options, dbname, user, and password. The way you would usually connect to your database is as follows:

PHP Example: (!)

/* dbname is the name of the database you're connecting to

* user is the PostgreSQL user you're going to connect as

* password is the password for the user you're connecting as

pg_connect('dbname=databasename user=username password=password') or die('Couldn't Connect: '.pg_last_error());

// what pg_last_error() does is return the last error that occured, so you should always die with that to know what happened

Using pg_query() To create a table

Lets create our first table. We will make a script to do this, demonstrating the use of pg_query(). Lets make a table named 'Contacts' with the fields 'name','surname', and 'email'. To do this, use the following query:

PHP Example: (!)

/* We're using the query

CREATE TABLE contacts

(

name varchar(50),

surname varchar(50),

email varchar(50)

)

pg_connect('dbname=dbname user=user password=password') or die('Couldn't Connect '.pg_last_error()); // Connect to the Database

/* Use the Query */

$query = 'CREATE TABLE contacts

name varchar(50),

surname varchar(50),

email varchar(50)

$query = pg_query($query); // Execute the Query

if($query)

echo 'Table Created'; // Check to see if The Query Worked.

else

If there was any error In using that script, then check to see if you supplied the correct username and password, and also check to see that PostgreSQL is running. Also be very careful to only execute that script once and then delete it. Otherwise you will get errors saying that there is already a table named 'contacts'.

Note: You can also Use an administrative tool to execute that query above. A few of my favorites are PHPPgAdmin and WebMin.

Inserting Data Into your database

Now that you've set up your table, its time to insert some records, your database should look something like this:

Database name: Contacts

name | surname | email

Now its time to use the pg_query function again. We will this time insert some information into our database. We will use the SQL Command 'INSERT' to do this, its syntax is as follows:

INSERT INTO table_name (column1, column2,) VALUES (value1, value2,.)

table_name is the name of the table, in the parenthesis you can specify which columns you want to insert into. For values, you will put what you want to insert into the database. so INSERT INTO contacts VALUES('John','Smith','johnsmith@domain.com') would insert John as the first name, Smith as the last name, and johnsmith@domain.com as the e-mail address. Lets try writing a script now to do this.

PHP Example: (!)

pg_connect('dbname=databasename user=username password=username') or die('Couldn't Connect'.pg_last_error());

$query = 'INSERT INTO contacts VALUES('John','Smith','johnsmith@domain.com')';

$query = pg_query($query);

if($query)

echo 'inserted successfully!';

else

That should've done what we wanted to, otherwise you should backtrack and try remaking the table. If that was successful, which it should have been, lets go on to insert a few more records. In fact, Why don't we make a form based inserter? We shall start off with a basic form

PHP Example: (!)

<form method='POST'>

Name: <input type='text' name='name'><br />

Surname: <input type='text' name='surname'><br />

Email Address: <input type='text' name='email'><br />

<input type='submit'>

</form>

Now we shall add the actual inserts to our form. We will first check to see if all fields were filled out, and then insert into the database, so the following should do the trick.

PHP Example: (!)

<?php

if($_REQUEST['name'] && $_REQUEST['surname'] && $_REQUEST['email']) // Check to see if All of the Fields were Filled Out

else

?>

And Voila! We have a form to Insert into our database! By reading the comments, you should fully understand what each part of this script does

The action of this form is echo $_SERVER['PHP_SELF'], which will print the name of the current file. So That this will work no matter what your filename is, as long as the extension is .php

Viewing The Entries in the Database

Excellent, Now that we can add to our database, the only thing that we really need to do now is to VIEW all of the database entries. We can do that using the SELECT Command in SQL. Keep in mind we're still using the pg_query() function. If we wanted to show all of the database fields, we could make a script that'll select all(* in SQL) of the entries.

PHP Example: (!)

pg_connect('dbname=databasename user=user password=password') or die('Couldn't Connect'); // Connect to the Database

$query = 'SELECT * FROM contacts';

$query = pg_query($query);

while($row = pg_fetch_array($query,NULL,PGSQL_ASSOC))

Now don't be afraid if you don't understand every part of this. I just used the function pg_fetch_array() to fetch the results from the query Into an associative array. The keys of the array are named after the column names of the table. so $row['name'] will contain whatever was in the 'name' column of our table. simple, isn't it? The $query Variable is the Query that you want to fetch. NULL is the row number, when you specify NULL, then the function will just skip that parameter. In the last parameter, the one that says PGSQL_ASSOC, that chooses what type of array It will return. PGSQL_ASSOC will have arrays with the column names as keys. PGSQL_NUM will return a numerated array, and PGSQL_BOTH will return both. As a final note, you can replace all of those echo's with a print_r() to see the entire array.

Updating

The only thing left for you to know the basics, is the update command, which is pretty straightforward. It's syntax is

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

So 'UPDATE contacts SET email = 'HIDDEN' WHERE surname = 'smith'' would ddit and set the email to 'HIDDEN' to anybody with the last name 'Smith'. simple, huh?

Conclusion

Ok, if you've followed me this far, you're probably thinking that PostgreSQL Isn't that much different that MySQL, and yes, so far, this is very true. Like I said at the beggining of this tutorial, this is a very BASIC Tutorial. If you've made it this far without any problem, I strongly urge you to check out the PostgreSQL docs and check out some of its MANY features. There IS a world beyond MySQL.

PostgreSQL - installation and configuration

5 September 2000

Need more help on this topic? Click here

This article has 18 comments

Show me similar articles

Here's another plug for FreshPorts. I've been playing with ideas and I've come up a few good improvements. But they will require a database with more features than mySQL. Specifically, I'm going to need stored procedures and functions.

I actually installed PostgreSQL back in late July, but never did anything with it. This article will help you along the way.

PostgreSQL caught my attention because it's been recommended by others. It also has the stored procedures and triggers. These facilities will form the heart and soul of the new database.

NOTE: Since this article was written, the path for the PostgreSQL binaries has changed. When I wrote this article, the pathnames were /usr/local/pgsql/bin/. I have since updated the article to refer to the new location /usr/local/bin/.

Resources I've used

Here are the PostgreSQL resources I've used:

https://www.postgresql.org/docs/aw_pgsql_book/

/usr/local/share/doc/pgsql/postgres/ for earlier versions (e.g. 7.0.3) or /usr/local/share/doc/postgresql/html for later versions (e.g. 7.1.3)

Installation - from ports

If you want to use php first, well, I've already installed that. I'm not sure what you'd do if you want PostgreSQL and php. Perhaps install mod_php later. Does anyone know? If so, add your comments.

As always, I'm installing this from ports. If you haven't already installed your ports tree, you should. Because this is how easy it is to install a port:

# cd /usr/ports/databases/postgresql7/

# make install

There. Done.

Actually, I'm sure there might have been more to it than that. Such as specifying php options. But I can't recall.

You also need to initialize the database with the following command [note that the user pgsql is not used on all systems, on some systems it might be postgres):

# su -l pgsql -c initdb

This database system will be initialized with username 'pgsql'.

This user will own all the data files and must also own the server process.

Creating directory /usr/local/pgsql/data

Creating directory /usr/local/pgsql/data/base

Creating directory /usr/local/pgsql/data/global

Creating directory /usr/local/pgsql/data/pg_xlog

Creating template1 database in /usr/local/pgsql/data/base/1

[snip]

Success. You can now start the database server using:

/usr/local/bin/postmaster -D /usr/local/pgsql/data

or

/usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Now I'm ready to start the database server:

/usr/local/etc/rc.d/010.pgsql.sh start

That should be the name of the file (i.e. 010.pgsql.sh) but if you can't find it, just hunt around in that directory for a simlarly named file.

Allowing users to use psql

This section documents the steps required to allow a user to access a database. You may also want to read the instructions for adding a database user. In this section, the term user refers to a login.

I like the way PostgreSQL works. It creates a special user for you, pgsql. This user does all the work. The database runs as this user, and all work (database creation, adding users, etc) is done as this user.

NOTE: pgsql is not used on all systems, on some systems it might be postgres.

The first step is to add myself as a user, so I don't have to do all my work as pgql. Here's how I added myself as a user. I typed the bits in bold. See also the NOTE below regarding the path.

$ su -l

Password:

[root@set:~] # su pgsql

$ /usr/local/bin/createuser dan

Shall the new user be allowed to create databases? (y/n) y

Shall the new user be allowed to create more new users? (y/n) y

CREATE USER

Done. Now that I've added myself as a user who can create databases, I can use my normal login.

NOTE: In more recent versions of PostgreSQL, the binary is /usr/local/bin/createuser.

Adding a database

Now I dropped back to my usual login and created a database.

$ logout

[root@set:~] # logout

[dan@set:/usr/home/dan] $ /usr/local/bin/createdb mydb

CREATE DATABASE

Done.

Creating a user for this database

Now I dropped back to my usual login and created a database.

$ /usr/local/bin/psql mydb

Welcome to psql, the PostgreSQL interactive terminal.

Type: copyright for distribution terms

h for help with SQL commands

? for help on internal slash commands

g or terminate with semicolon to execute query

q to quit

mydb=#

Now I'll create a user, tester, for this database.

mydb=# create user tester with password 'mypassword';

CREATE USER

You can also specify more constraints:

mydb=# create user tester with password 'mypassword';

CREATE USER

To remove a user:

mydb=# drop user tester;

DROP USER

Creating groups

You can also create groups and place the users in those groups. You can grant permissions collectively to the group instead of individually to the user.

mydb=# CREATE GROUP testers WITH USER dan;

CREATE GROUP

Then you can grant SELECT permission on table thedata permissions to group testers:

mydb=# GRANT SELECT ON thedata TO GROUP testers;

CHANGE

Creating a table

I created a rather simple table for my testing.

mydb=# create table test (id serial, name varchar(10));

NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq'

for SERIAL column 'test.id'

NOTICE: CREATE TABLE/UNIQUE will create implicit

index 'test_id_key' for table 'test'

CREATE

Then I inserted data:

mydb=# insert into test (name) values ('test');

INSERT 18879 1

mydb=# insert into test (name) values ('test2');

INSERT 18880 1

Then I read that data back out:

freshports2=# select * from test;

id | name

1 | test

2 | test2

(2 rows)

Getting php going

I create a simple php test in an existing website. For help on creating websites, look at Apache - virtual hosts.

I added this to testpsql.php3 in my website. Note the amended while loop at the end of this section.

<head>

<title>PostgreSQL test</title>

<body>

<?php

$database=pg_connect('dbname=mydb user=test password=mypassword');

if ($database)

echo '</table>n';

} else

pg_exec ($database, 'end');

} else

?>

</body></html>

As you can see, I had to manually break the loop. I have no idea why. I thought pg_fetch_array would return false at the end of the result set, as mentioned in the documentation. But it didn't. So far, it appears I'll have to use a for loop for that and not a while. Any ideas on why should be added as comments. pg_fetch_array was behaving like that? It seems to be standard behaviour.

A search at https://google.com found this example, which I used to create this amended while loop:

for ($i = 0; $i < $NumRows; $i++)

What's next?

I would like a Windows GUI inteface to PostgreSQL. Any suggestions should be added to the comments. I found ZEOS, but couldn't get it to connect. I suspect someone wrong with my access rights, but I was looking at /usr/local/pgsql/lib/pg_hba.conf.

I'm sure the next PostgreSQL article will have more information.

backups

24 December 2000

It's time I added backups to this article. This information is taken from the Admin documentation at /usr/local/share/doc/pgsql/admin/.

A backup is done with this:

% pg_dump dbname > dbname.pgdump

A restore is done with this:

cat dbname.pgdump | psql dbname

Depending upon your path settings, you may have to specify the full path to these binaries. Under FreeBSD, this would be /usr/local/bin/pg_dump.

For a backup script, please read the section on mySQL backups in the article I wrote for mySQL. Just substitute pg_dump for mysqldump.

Various notes

29 November 2001

Tonight I was upgrading, accidentally mind you, from 7.0.3 to 7.1.3. I did this without first deinstalling the old version. Bad idea. As a precaution, you should always dump your old databases before upgrading. I didn't. When I tried to run psql, I was getting these errors:

# psql FreshPort2Test

psql: FATAL 1: SetUserId: user 'root' is not in 'pg_shadow'

The mistake was that I was doing this as root. DOH! I had created all my databases as dan. So asking on IRC, I was told to do this:

# su - pgsql

# psql FreshPort2Test

That worked. I then dumped all my databases as shown in a previous section. Then I saved them all to CD.

Then I did the right thing:

# pkg_delete -f postgresql-7.0.3

# pkg_delete -f postgresql-7.1.3 # cd /usr/ports/databases/postgresql7

# make deinstall

# make install

Then I had to do the initdb manually (and I'm not sure if this is usually done automatically):

# su -l pgsql

$ initdb

This database system will be initialized with username 'pgsql'.

This user will own all the data files and must also own the server process.

Creating directory /usr/local/pgsql/data

Creating directory /usr/local/pgsql/data/base

Creating directory /usr/local/pgsql/data/global

Creating directory /usr/local/pgsql/data/pg_xlog

Creating template1 database in /usr/local/pgsql/data/base/1

DEBUG: database system was shut down at 2001-11-29 17:59:29 EST

DEBUG: CheckPoint record at (0, 8)

DEBUG: Redo record at (0, 8); Undo record at (0, 8); Shutdown TRUE

DEBUG: NextTransactionId: 514; NextOid: 16384

DEBUG: database system is in production state

Creating global relations in /usr/local/pgsql/data/global

DEBUG: database system was shut down at 2001-11-29 17:59:34 EST

DEBUG: CheckPoint record at (0, 108)

DEBUG: Redo record at (0, 108); Undo record at (0, 0); Shutdown TRUE

DEBUG: NextTransactionId: 514; NextOid: 17199

DEBUG: database system is in production state

Initializing pg_shadow.

Enabling unlimited row width for system tables.

Creating system views.

Loading pg_description.

Setting lastsysoid.

Vacuuming database.

Copying template1 to template0.

Success. You can now start the database server using:

/usr/local/bin/postmaster -D /usr/local/pgsql/data

or

/usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Then I started the database server:

# /usr/local/etc/rc.d/010.pgsql.sh start

Note that this script had been sitting around from my previous install. It may not be the same name on your system, but it will be in the same directory.

Improving performance

27 June 2002

If you find that a query isn't running fast enough, look at the situation and act accordingly. Here is an example I encountered when working on FreshSource.

I was looking for all the children of a particular element:

freshports=# select * from element where parent_id = 77340;

id | name | parent_id | directory_file_flag | status

77341 | files | 77340 | D | A

77449 | Makefile | 77340 | F | A

77450 | distinfo | 77340 | F | A

(3 rows)

freshports=# explain analyse select * from element where parent_id = 77340;

NOTICE: QUERY PLAN:

Seq Scan on element (cost=0.00..2165.41 rows=11 width=30) (actual time=548.68..655.47 rows=3 loops=1)

Total runtime: 655.59 msec

As you can see, this query is accomplished by doing a sequential scan on the element table and it takes 0.6s. Let's refresh the statistics on this table, and then run the query again.

freshports=# vacuum analyze element;

VACUUM

freshports=# explain analyse select * from element where parent_id = 77340;

NOTICE: QUERY PLAN:

Seq Scan on element (cost=0.00..2201.85 rows=12 width=30) (actual time=178.50..236.41 rows=3 loops=1)

Total runtime: 236.53 msec

That gets us down to 0.2s, but we are still doing a sequential scan. Let's try an index.

freshports=# create index element_parent_id on element(parent_id);

CREATE

freshports=# explain analyse select * from element where parent_id = 77340;

NOTICE: QUERY PLAN:

Index Scan using element_parent_id on element (cost=0.00..25.89 rows=12 width=30) (actual time=0.38..0.49 rows=3 loops=1)

Total runtime: 0.62 msec

OK, now that is impressive. We've gone from 600ms to 0.6ms. That's 1000 times faster overall. For more information, read the documentation regarding vacuum.