The FreeBSD Diary

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/.

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


NOTE: Recent versions of FreeBSD will require this entry in /etc/rc.conf in order for PostgreSQL to start:

postgresql_enable="YES"

Now I'm ready to start the database server:

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

NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:

/usr/local/etc/rc.d/postgresql 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.

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.

$ logout
[root@set:~] # logout
[dan@set:/usr/home/dan] $ /usr/local/bin/createdb mydb
CREATE DATABASE

Done.

$ /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

To remove a user:

mydb=# drop user tester;
DROP USER

NOTE: In recent versions of PostgreSQL, CREATE USER has been deprecated by CREATE ROLE.

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

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)

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) {
   $result = pg_exec ($database, "select * from test");
   if ($result) {
      echo pg_numrows($result) . " rows to fetch\n";
      echo "<table>\n";
      $i = 0;
      while ($myrow = pg_fetch_array ($result, $i)) {
         $i++;
         echo "   <tr><td>" . $myrow["id"] . "</td><td>" . 
                              $myrow["name"] . "</td></tr>\n";
         if ($i > 10) break;
      }
      echo "</table>\n";
   } else {
      echo "read from test failed";
   }

   pg_exec ($database, "end");
} else {
   echo "no connection";
}
?>

</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 http://google.com found this example, which I used to create this amended while loop:

for ($i = 0; $i < $NumRows; $i++) {             
   $myrow = pg_fetch_array ($result, $i);       
   echo "   <tr><td>" . $myrow["id"] . "</td><td>" . 
                 $myrow["name"] . "</td></tr>\n";
}

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

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.

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'

# 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


# 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
$


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

NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:

# /usr/local/etc/rc.d/postgresql 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.

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.