SQL Relay - Getting Started With PostgreSQL

Getting Started With PostgreSQL

Installation

RPM-based Linux
Debian Linux
Slackware Linux
FreeBSD
OpenBSD
NetBSD
SCO OpenServer
Solaris
Compining From Source
Starting the Database at Boot Time
Initial Configuration

Creating a Database
Accessing a Database
Accessing a Database With SQL Relay

Installation

I've sucessfully installed PostgreSQL on Linux, FreeBSD, NetBSD, OpenBSD, Solaris and SCO OpenServer.

FreeBSD, OpenBSD, NetBSD and most Linux distributions come with PostgreSQL and give you the option of installing it when you install the rest of the system. If you want or need to install it afterward, follow the instructions below.

RPM-based Linux

To install PostgreSQL on an RPM-based Linux distribution like RedHat, Mandrake or TurboLinux, acquire the postgresql, postgresql-server, postgresql-devel and postgresql-libs RPMS from RPMFind or possibly from the CD's that came with your distribution and install them using rpm -i.

Debian Linux

If you have configured apt as illustrated in the APT-HOWTO, run apt-get install postgresql and apt-get install postgresql-dev. If your distribution came with PostgreSQL, you may be promted to enter a CD. If not, it will be downloaded from the internet. When the commands complete, PostgreSQL will be installed.

Slackware Linux

If there are PostgreSQL packages for Slackware, I've never been able to find them. I've always had to compile from source.

FreeBSD

If you have an internet connection, run pkg_add -r postgresql. When the command completes, PostgreSQL will be installed. You can also install PostgreSQL from the Ports CD(s) that came with your distribution using /stand/sysinstall.

OpenBSD

The postgresql package is available from ftp.openbsd.org or on CD's that came with your distribution. You can install it using pkg_add. Once the package is installed, you should run the following commands:

useradd -m postgres
passwd postgres (assign the new user a password)
mkdir /usr/local/pgsql /usr/local/pgsql/var
chown -R postgres:users /usr/local/pgsql
su postgres
initdb -D /usr/local/pgsql/var
exit

NetBSD

The postgresql and postgresql-clients packages are available from ftp.netbsd.org or on CD's that came with your distribution . You can install them using pkg_add.

SCO OpenServer

For SCO OpenServer, PostgreSQL packages are available from the Skunkware ftp server. SCO OpenServer packages are often called VOL's because they come as a set of files named VOL.000.000, VOL.000.001, etc. These VOLS can be installed using the Software Manager (custom).

Solaris

If there are PostgreSQL packages for Solaris, I've never been able to find them. I've always had to compile from source.

Compiling From Source

If you want to compile PostgreSQL from source, it should compile cleanly on all of the platforms mentioned above with the exception of SCO OpenServer. I have never been able to get it to compile on SCO. The source code is available from the PostgreSQL site. You should create a user named postgres and assign it a password, then build and install PostgreSQL using that user. By default, PostgreSQL installs itself in /usr/local/pgsql. Note that you should add /usr/local/pgsql/bin to your PATH environment variable and /usr/local/pgsql/lib to your LD_LIBRARY_PATH environment variable.

After compiling and installing PostgreSQL, you should log in as the postgres user and run initdb -D /usr/local/pgsql/var to initialize the database.

Starting the Database at Boot Time

The package distributions of PostgreSQL install a script which starts the database at boot time. If you compiled from source, you'll need to install a script like the following to start the database at boot time.

#!/bin/sh

case "$1" in
        start)
                su -l postgres -s /bin/sh -c "/usr/local/pgsql/bin/pg_ctl  -D /usr/local/pgsql/var -p /usr/local/pgsql/bin/postmaster start  > /dev/null 2>&1" < /dev/null
                ;;
        stop)
                kill `ps -efa | grep postmaster | grep -v grep | awk '{print $2}'`
                ;;
        *)
                echo $"Usage: $0 {start|stop}"
                exit 1
esac

exit 0

Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running.

Initial Configuration

It's necessary to perform administrative tasks as the postgres user. Assigning it a password makes this easier.

By default, PostgreSQL is fairly insecure. Any user on the local machine can connect to the database without supplying a password. To begin securing PostgreSQL, log in as postgres.

By default, PostgreSQL has a database called template1 containing privileges and other housekeeping data and an adminstrative user named postgres. The postgres database user initially has no password. To assign it a password, run a command like the following. This command assigns the postgres user the password newpassword.

For PostgreSQL 7:
psql -c "ALTER USER postgres WITH PASSWORD 'newpassword'" -d template1
For PostgreSQL 6:
psql -c "ALTER USER postgres WITH PASSWORD newpassword" -d template1

Now edit the pg_hba.conf file (usually found in /var/lib/pgsql, /var/lib/pgsql/data or /usr/local/pgsql/var/data) and look for lines like the following:

local all trust host all 127.0.0.1 255.255.255.255 trust

Modify these lines to read:

local all password host all 127.0.0.1 255.255.255.255 password

By default, PostgreSQL listens on a unix socket, but not on an inet socket. Thus, clients running on the local machine can connect to the database, but clients running on remote hosts cannot. To change this behavior, I had to modify the pg_ctl script and add the -i option to the postmaster startup command. There doesn't appear to be an easier way. Additionally, you must add a line to the pg_hba.conf file to allow remote hosts to access local databases. The following line allows all hosts in the 192.168.2.0 class C network to access all databases on the local machine, using password authentication.

host all 192.168.2.0 255.255.255.0 password

Creating a Database

After installation and database initialization, PostgreSQL is ready to use but to do any useful work, you'll have to create a database.

In PostgreSQL terminology, a database is a collection of files managed by a program called postmaster. Technically, any user can create a database and run a postmaster to manage it, but for simplicity's sake, we'll just make our database owned by the postgres user.

To create a database named testdb with an administrator named postgres, log in as postgres and run the following command.

For PostgreSQL 7:
psql -U postgres -c "CREATE DATABASE testdb" -d template1
Password: newpassword
For PostgreSQL 6:
psql -u -c "CREATE DATABASE testdb" -d template1
Username: postgres Password: newpassword

Once the database has been created, you can log into it as follows.

For PostgreSQL 7:
psql -U postgres -d testdb
Password: newpassword
For PostgreSQL 6:
psql -u -d testdb
Username: postgres Password: newpassword

Once logged in, you can create, drop and modify tables and run queries in the database.

PostgreSQL authentication is a complex topic. Lines in the pg_hba.conf file define access rules and restrictions for hosts and databases. For more information, consult the PostgreSQL online documentation.

To create database users, use commands like the following. This command creates a user named testuser with password testpassword.

For PostgreSQL 7:

psql -U postgres -c "CREATE USER testuser WITH PASSWORD 'testpassword'" -d testdb
Password: newpassword

For PostgreSQL 6:

psql -u -c "CREATE USER testuser WITH PASSWORD testpassword -d testdb
Username: postgres
Password: newpassword

You can now log into the database as testuser using the following command.

For PostgreSQL 7:
psql -U testuser -d testdb
Password: testpassword
For PostgreSQL 6:
psql -u -d testdb
Username: testuser Password: testpassword

If you want to drop a user, you can do so. The following command drops a user named testuser.

For PostgreSQL 7:

psql -U postgres -c "DROP USER testuser" -d testdb
Password: newpassword

For PostgreSQL 6:

psql -u -c "DROP USER testuser" -d testdb
Username: postgres
Password: newpassword

If you want to drop the database, you can do so with the following command.

For PostgreSQL 7:

psql -U postgres -c "DROP DATABASE testdb" -d template1
Password: newpassword

For PostgreSQL 6:

psql -u -c "DROP DATABASE testdb" -d template1
Username: postgres
Password: newpassword

This should be enough to get you started. To set up more complex configurations, consult the online documentation.

Accessing a Database

Accessing a PostgreSQL database using the psql client tool is simple. For example, to access a database called testdb on the local machine as the testuser user with password testpassword, use the following command.

For PostgreSQL 7:
psql -U testuser -d testdb
Password: testpassword

For PostgreSQL 6:
psql -u -d testdb
Username: testuser
Password: testpassword

If you want to access a database on a remote machine, say on testhost, use the -h option as follows.

For PostgreSQL 7:
psql -U testuser -d testhost -d testdb
Password: testpassword

For PostgreSQL 6:
psql -u -h testhost -d testdb
Username: testuser
Password: testpassword

Once you're connected to the database, the psql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, end it with a semicolon or type \g on the next line. To exit, type \q.

A sample psql session follows.

[user@localhost user]$ psql -U testuser testdb
Password: 
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

testdb=> create table testtable (
testdb(> col1 char(40),
testdb(> col2 integer
testdb(> );
CREATE
testdb=> select * from pg_tables where tableowner='testuser';
 tablename | tableowner | hasindexes | hasrules | hastriggers 
-----------+------------+------------+----------+-------------
 testtable | testuser   | f          | f        | f
(1 row)

testdb=> \d testtable
          Table "testtable"
 Attribute |     Type      | Modifier 
-----------+---------------+----------
 col1      | character(40) | 
 col2      | integer       | 

testdb=> insert into testtable values ('hello',50);
INSERT 468520 1
testdb=> insert into testtable values ('hi',60);   
INSERT 468521 1
testdb=> insert into testtable values ('bye',70);
INSERT 468522 1
testdb=> select * from testtable;
                   col1                   | col2 
------------------------------------------+------
 hello                                    |   50
 hi                                       |   60
 bye                                      |   70
(3 rows)

testdb=> update testtable set col2=0 where col1='hi';
UPDATE 1
testdb=> select * from testtable;
                   col1                   | col2 
------------------------------------------+------
 hello                                    |   50
 bye                                      |   70
 hi                                       |    0
(3 rows)

testdb=> delete from testtable where col2=50;
DELETE 1
testdb=> select * from testtable;
                   col1                   | col2 
------------------------------------------+------
 bye                                      |   70
 hi                                       |    0
(2 rows)

testdb=> drop table testtable;
DROP
testdb=> \q

Accessing a Database With SQL Relay

Accessing PostgreSQL from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called postgresqltest. This instance connects to the testdb database running on the remote server testhost as the user testuser with password testpassword.

<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

        <instance id="postgresqltest" port="9000" socket="/tmp/postgresqltest.socket" dbase="postgresql" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
                <users>
                        <user user="postgresqltest" password="postgresqltest"/>
                </users>
                <connections>
                        <connection connectionid="postgresqltest" string="host=testhost;db=testdb;user=testuser;password=testpass" metric="1"/>
                </connections>
        </instance>

</instances>

Now you can start up this instance with the following command.

sqlr-start -id postgresqltest

To connect to the instance and run queries, use the following command.

sqlrsh -id postgresqltest

The following command shuts down the SQL Relay instance.

sqlr-stop postgresqltest