PostgreSQL on Ubuntu

Installation

• To install PostgreSQL, run the following command in the command prompt:
• sudo apt-get install postgresql
• Once the installation is complete, you should configure the PostgreSQL server

based on your needs, although the default configuration is viable.

• Configuration
• By default, connection via TCP/IP is disabled. PostgreSQL supports multiple

client authentication methods. By default, IDENT authentication method is

used for postgres and local users.

Please refer the PostgreSQL Administrator’s Guide.

• The following discussion assumes that you wish to enable TCP/IP connections

and use the MD5 method for client authentication. PostgreSQL configuration

files are stored in the /etc/postgresql//main directory.

For example, if you install PostgreSQL 8.3, the configuration files are stored

in the /etc/postgresql/8.3/main directory.

• To configure ident authentication,

add entries to the /etc/postgresql/8.3/main/pg_ident.conf file.

• To enable TCP/IP connections,

edit the file /etc/postgresql/8.3/main/postgresql.conf

• Locate the line #listen_addresses = ‘localhost’ and change it to:
• listen_addresses = ‘localhost’
• To allow other computers to connect to your PostgreSQL server replace

‘localhost’ with the IP Address of your server.

• You may also edit all other parameters, if you know what you are doing!

For details, refer to the configuration file or to the PostgreSQL documentation.

• Now that we can connect to our PostgreSQL server, the next step is to set a

password for the postgres user. Run the following command at a terminal

prompt to connect to the default PostgreSQL template database:

• sudo -u postgres psql template1
• The above command connects to PostgreSQL database template1

as user postgres. Once you connect to the PostgreSQL server, you

will be at a SQL prompt. You can run the following SQL command

at the psql prompt to configure the password for the user postgres.

• ALTER USER postgres with encrypted password ‘your_password’;
• After configuring the password, edit the

file /etc/postgresql/8.3/main/pg_hba.conf to use MD5 authentication

with the postgres user:

• local all postgres md5 sameuser
  sudo /etc/init.d/postgresql-8.3 restart
• The above configuration is not complete by any means.

Please refer the PostgreSQL Administrator’s Guide to configure

more parameters.

Resources

• As mentioned above the Administrator’s Guide is an excellent
• resource. The guide is also available in the postgresql-doc-8.3
• package. Execute the following in a terminal to install the package:

sudo apt-get install postgresql-doc-8.3

To view the guide enter

file:///usr/share/doc/postgresql-doc-8.3/html/index.html

into the address bar of your browser. For general SQL information see

Using SQL Special Edition by Rafe Colburn.