PostgreSQL – installation and configuration

PostgreSQL – installation and configuration

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

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


Success. You can now start the database server using:

    /usr/local/bin/postmaster -D /usr/local/pgsql/data
    /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:


Now I’m ready to start the database server:

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

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
[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

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


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


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

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

To remove a user:

mydb=# drop user tester;

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

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;

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

mydb=# GRANT SELECT ON thedata TO GROUP testers;

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 ''
NOTICE: CREATE TABLE/UNIQUE will create implicit
index 'test_id_key' for table 'test'

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.

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

<title>PostgreSQL test</title>

$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)) {
         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";


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
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";

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.

sure the next PostgreSQL article will have more information.

backups (added on 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 (added on 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
/usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Then I started the database server:

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

Improving performance (added on 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;

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;

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

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);
freshports=# explain analyse select * from element where parent_id = 77340;

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

The pgsql user (added on 24 August 2002)

This article makes several references to the user pgsql. Under a FreeBSD system,
the postmaster runs as this user. On some systems, the default superuser is postgres.

18 thoughts on “PostgreSQL – installation and configuration”

  1. (posted by Dan Langille)

    The ZEOS interface is quite handy and I use it quite a lot when sitting
    behind NT. You do have to modify pg_hba.conf to get it (or any network
    access to the database, like ODBC) to work.


    You want to edit /usr/local/pgsql/data/pg_hba.conf (or wherever PG_DATA
    points to)

    add the line:

    host all <your subnet> <your netmask> password passwd

    Then use the pg_passwd utility to create the passwd file like so:

    in /usr/local/pgsql/data execute: pg_passwd passwd
    enter the username and password for the account. This should be the same as
    the database account you want to access the database with. You can also use
    other authentication methods such as Kerberos.

    more info:

    There are also some other tools, pgtksh, pgaccess and pgadmin that I have
    not used but might be worth looking into.

    Happy daemonizing,

  2. (posted by Dan Langille)

    Take a look at pg_access, it is included with pgsql (I run linux mostly, so I don’t know where is gets put on bsd).

    It is a simple, still slightly buggy, Tcl/Tk interface that works a lot like access.

    Also, on source fource, there is a project to make a K desktop app to interface with postgres that looks promising.

    1. you can edit the file: postgresql.conf set tcpip_sock = true

      I had a question,can you help me?
      look the case:

      I create a db named [mydb].And create a user "mydb" for the database [mydb], I use the parameter "-i" startup the postgresql
      (the file pg_hba.conf added this line:

      host mydb mydb trust).

      at client , I use pgAdmin(for win) to connected the server,but it ask me:
      "No pg_hba.conf entry for host,user mydb"


  3. Serge Olivier Canizares

    (posted by Dan Langille)

    I read your article on installing postgreSQL from the ports collection, and noted that you indicated you were looking for a windows frontend to the db because you couldn’t get the ipgsql.exe client from Zeos to connect with the db.

    There are 2 probable reasons for this:

    1. You didn’t start postmaster with the "-i" flag, which allows for socket connexions. Here’s what I put in /usr/local/etc/rc.d/


    [-x /usr/local/pgsql/bin/postmaster ] && {
    su -l postgres -c ‘exec /usr/local/pgsql/bin/postmaster -i \
    -D/home/postgres/data \
    -S -o -F > /home/postgres/errlog’ &
    echo -n ‘ postgres’

    This starts the postmaster looking at the default port. Note: I use "postgres" as the db superuser, probably because I compile from the tarball rather than the port. Yes, I am using FreeBSD, but I’m used to different unices.

    2. You may not have set up the "pg_hba.conf" file properly in the db data directory–the directory that you specify to the postmaster process with the -D flag.

    Suppose you have two machines: the db server on
    and an NT box on

    Put this in the pg_hba.conf (make sure you add write permission first):

    host all password

    This allows ALL machines on the 192.168.4 subnet to access the server, and specifies that clients send their user name and password to verify the user identity to the machine. If you read the docs, you can get fancy with authentication–use encryption, kerberos, etc. But if you have a small network like I do (3
    machines at home protected by a firewall using ipfilter) it may be more trouble than it is worth. So I use the simple command above.

    By the way, this lets me use perl DBI from my apache with mod_perl server, and lets my wife, who still uses 98 on her notebook, use ipsql.exe to work with the db. You can even get M$ Access to interface to postgres this way, if you compile in ODBC support.

  4. (posted by Dan Langille)

    Pgaccess ( is a GUI for the PostgreSQL
    database. It has the ability to create tables, queries, views, reports,
    forms, schema and many other functions. However it does take a bit of configuring to get it working properly on FreeBSD… one of the joys of using FreeBSD as opposed to our more popular cousin ;).

    The main problem is configuring Postgres to use the proper versions of wish,tcl,and tk (as FreeBSD uses multiple versions) and editing a few files.

    The ports didn’t work for me, so I downloaded the Postgres tarball from their website and untarred it in /usr/local. Changing to the
    Postgresql-*.*.*/src/ directory, I configured it as follows:

    ./configure –with-odbc –with-tcl=/usr/local/include/tcl8.3

    You can add the Perl interface and others as you prefer.

    Then do a gmake install, as Postgres won’t compile with regular make.

    I then cd’d to /interfaces/libpgtcl to build libpgtcl and added this to
    the Makefile:

    CFLAGS += -I/usr/local/include/tcl8.3
    CFLAGS += -I/usr/local/include/tk8.3

    Then a gmake install to build it.

    I then went into /usr/local/bin and edited the following lines in
    pgaccess to read:


    I then edited my home .profile to add the directory to my path:

    /usr/local/pgsql/bin; export PATH

    I kept getting libpgtcl errors when I tried to run pgaccess, so I added
    the following to /etc/rc.conf as FreeBSD doesn’t use for

    ldconfig_paths="/usr/lib/compat /usr/X11R6/lib /usr/local/lib /usr/

    You can also do this manually using the ldconfig command but I think
    it’s easier to put it in /etc/rc.conf so it’s there permanently. I then

    After doing your initdb and postmaster commands for Postgres to specify the directory for the databases, you can then call up Postgres with the following:

    postmaster -i -D /path/to/your/databases

    Then a command of:


    should bring up the gui and get you started. Just add your database name after the pgaccess command if you want to start working in an existing database.

    This is how I got it working, but any comments, suggestions or other
    tips are welcome.

  5. Gerard Samuel

    Some php code with respect to postgresql has been changed lately, so here is an updated example….
    I currently using php 4.2.1


    $conn = pg_connect("dbname=test user=myuser");

    if ($conn)
    $result = pg_exec ($conn, ‘select * from test’);
    if ($result)
    echo pg_numrows($result) . ‘ rows to fetch’ . "\n";
    echo ‘<table border="1" width="50%">’ . "\n";

    while ($myrow = pg_fetch_array ($result))
    echo ‘<tr><td>’ . $myrow[‘id’] . ‘</td><td>’ . $myrow[‘name’] . ‘</td></tr>’ . "\n";

    echo ‘</table>’ . "\n";
    echo ‘read from test failed’;
    echo ‘no connection’;


    1. Gerard Samuel

      That didn’t come out too pretty. Trying once more….



      $conn = pg_connect("dbname=test user=www");

      if ($conn)
      $result = pg_exec ($conn, "select * from test");
      if ($result)
      echo pg_numrows($result) . " rows to fetch\n";
      echo "&lt;br&gt;&lt;br&gt;\n";

      while ($myrow = pg_fetch_array ($result))
      echo " $myrow[id] $myrow[name] \n";
      echo "read from test failed";&lt;br&gt; \n";
      echo "no connection";



  6. I just installed PostgreSQL on a FreeBSD box, with many thanks to your article for the great information 🙂

    When i needed to get PHP re-installed to work with my fresh new Postgres install, the following command (when executed in the php-4.2.2 dir) worked quite well. (PHP v4.0 and apache were already installed and functional on the machine).

    (My setup, with graphics functionality and mysql)
    ./configure –with-apxs=/usr/local/apache/bin/apxs –with-mysql=/usr/local/ –with-gd=/usr/local/ –with-freetype –with-imap –with-jpeg-dir=/usr/local/ –with-png-dir=/usr/local/ –with-zlib –with-pgsql=/usr/local/pgsql/

Leave a Comment

Scroll to Top