Installing PostgreSQL - Benjamin Arai

arizonahoopleData Management

Nov 28, 2012 (4 years and 10 months ago)

263 views

Installing PostgreSQL

By: Benjamin Arai


Installing PostgreSQL is an easy task to set up for simple web pages and other web based applications.
However, for large
-
scale, reliable and fast systems it can be a painful task. Hopefully, this lab will give
y
ou an adequate introduction to PostgreSQL, allowing you to create more complex scalable systems in
the future. To make our live easier we are going to be using RedHat for the installation.


Instructions


1.

To install PostgreSQL run the following command(s)

as root to install the database.




apt
-
get update



apt
-
get install postgresql



apt
-
get install postgresql
-
server



/sbin/service postgresql start


Several packages may be downloaded with PostgreSQL. This is O.K., they are all requirement
for PostgreSQL to run

and function correctly.


2.

At this point the package is installed but un
-
configured. So, to become familiar with the
application look at the configuration files under “/var/lib/pgsql/data/”. There should be a few
configuration files:




pg_hba.conf



pg_ident
.conf



postgresql.conf


3.

For the purposes of the lab the “pg_hba.conf” file is going to be modified to allow connections
for users other then just the local host.
The current setting allows all system users to access the
PostgreSQL server as themselves as l
ong as they are local to the machine
. Make sure the two
following lines are present and uncommented in you configuration file.




host all all 0.0.0.0 0.0.0.0 trust



local all

all ident sameuse
r


In practice you could replace the keyword “password” above and replace it with “md5” in
-
order
to increase security of your authentication but for purpose of the lab it is not necessary. The
second line you are adding is for applications accessing the d
atabase from the local machine.
Using this parameter allows any user logged in locally to connect as the same users to the
database.


4.

The file “pg_ident.conf” file does not need to be changed because the authentication we are
using (password) does not req
uire any ident (retarded authentication) information. Ident
authentication is one of the choices for authentication that is built into PostgreSQL and can be
used in place of password, crypt or md5 authentication.


5.

The file “postgresql.conf” is where all t
he magic happens. This is where you allocate resources,
set connection types, encrypt your connection and just about everything else that PostgreSQL has
to offer. For the lab you need to allow TCP/IP connections, so it will be accessible for future
labs
to other VMware sessions.




tcpip_socket = true
Allows TCP/IP connections to your database.


6.

At this point you PostgreSQL server should be configured correctly. You need to restart the
server to make the changes take effect. Run the following command as

root:




/sbin/service postgresql restart


If the last line says it was started correctly, then it worked. If not, then there is some error in one
of your configuration files and you will need to go back and fix it/them before continuing.


7.

Run this command

to shutdown the firewall:




/sbin/service iptables stop


This allows other machines access you PostgreSQL installation from the virtual VMware
network.


8.

Now you need to create a user that will be able to access your database. This is essentially an
accoun
t a user would get who is going to be either a client or developer on the database. To add
a user:




su postgres
The first user has to be added as user “postgres”.



createuser

a
--
createdb
--
password cs183
You will be prompted for a password the new
user
will use.



exit


The “
-
a” gives the user super user privileges allowing them to delete and add users and etc. For
the purposes of the lab this is fine but for future usage, consults the man page for further
information regarding user restrictions. The “
-
P” causes the command to prompt the user to
enter a password. This is the same password the new user will use to log onto the database
system.


9.

In order to test the installation you are going to have to add a database. Run the following
commands:




su pos
tgres
The first user has to be added as user “postgres”.



psql template1
A prompt should appear. You are now in the interactive PostgresSQL
console.



CREATE DATABASE testcs183;
You’ve just created your own database. Make sure
you have the semi
-
colon at th
e end.



\
q
Exit PostgreSQL interactive console



exit
Exit postgres user.


10.


Run the following command to test the PostgreSQL installation:




psql
--
host 127.0.0.1
--
dbname “your_db”
--
username “your_pg_username”


“your_pg_username” is the username you created
previously and “your_db” is the name of the
database you created previously for your PostgresSQL database.
If a prompt appears then you
are done!