To install PostgreSQL on your Virtual Private Server, connect to your server via Telnet or SSH and run the following command:

% vinstall pgsql

The PostgreSQL installation includes all the necessary PostgreSQL files, initializes a database with the same name as your user name, and activates the postmaster daemon. Several wrapper scripts are available for all PostgreSQL commands. The wrapper scripts are automatically created when PostgreSQL is installed.

PostgreSQL is installed in a version-specific directory (~/usr/local/pgsql-7.X.X) and a symlink named pgsql is created that points to that directory.

Configuration

To configure PostgreSQL (which is optional), add the following lines to your shell startup file, according to which shell your Virtual Private Server is running.

NOTE: To find out which shell your server is running, type:

% echo $SHELL
  • /bin/csh
    If you are using /bin/csh or one of its variants, then add the following lines to the ~/.cshrc file on your Virtual Private Server.

    
    setenv PGDATA /usr/local/pgsql/data
    setenv PGLIB /usr/local/pgsql/lib
    set path = (/usr/local/pgsql/bin $path)
          
  • /bin/sh & /bin/bash
    If you are using the Bourne shell (/bin/sh or /bin/bash) then add the following lines to the ~/.profile file on your server:

    
    PATH=$PATH:/usr/local/pgsql/bin
    PGDATA=/usr/local/pgsql/data
    PGLIB=/usr/local/pgsql/lib
    export PGDATA PGLIB
          

The psql Client

The main tool for using PostgreSQL is the psql client. To start psql use this command:

% psql

The psql client will start up and you will be able to type in SQL code and ask for help.

NOTE: You may see the following error:


Connection to database '(null)' failed.
FATAL: PQsetdb: Unable to determine a Postgres username!
        

To resolve this, simply use the following command:

% vpwd_mkdb ~/etc/passwd

This program will read your password file at ~/etc/passwd and create a Berkeley DB format file. PostgreSQL uses this new file to look up user names and account information.

Multi-Language Abilities in PostgreSQL

PostgreSQL allows for a number of languages by enabling specific character-sets in the databases. When you create a database in PostgreSQL, you can use the -E flag to enable support for a specific character set.

% initdb -E SET

The following is a list of the available character sets and the SET name to use to enable support for it.

SET

 

Description

SQL_ASCII

 

ASCII

EUC_JP

 

Japanese EUC

EUC_CN

 

Chinese EUC

EUC_KR

 

Korean EUC

EUC_TW

 

Taiwanese EUC

UNICODE

 

Unicode(UTF-8)

MULE_INTERNAL

 

Mule Internal Code

LATIN1 ISO 8859-1

 

English and some Europe Lang

LATIN2 ISO 8859-2

 

English and some Europe Lang

LATIN3 ISO 8859-3

 

English and some Europe Lang

LATIN4 ISO 8859-4

 

English and some Europe Lang

LATIN5 ISO 8859-5

 

English and some Europe Lang

KOI8

 

KOI8-R(U)

WIN

 

Windows CP1251

ALT

 

Windows CP866

 

Removing PostgreSQL

To remove PostgreSQL from your server, connect to it via Telnet or SSH and do the following:

Remove the PostgreSQL software by running the commands that match your Virtual Private Server O/S.

% vuninstall pgsql

Edit your ~/etc/rc file, removing the line that contains postmaster.

Run the ps command, like this:

% ps -x

Determine the process ID of the PostgreSQL daemon and use kill to stop the PostgreSQL daemon:

% kill PROCESS-ID

Please note: the information on this page applies to ITS web hosting plans. It may or may not apply to other environments. If you are looking for a feature described here, or better support from your hosting provider, please consider hosting your site with ITS!

1555 N Naperville/Wheaton Road, Suite 107
Naperville, IL 60563
phone 630.420.2550
fax 630.420.2771