CS 491/591 Linux
Administration and Security
What is PostgreSQL?
PostgreSQL is an Object
Management System (ORDBMS) that has
been developed in various forms since 1977.
It’s an open source project. It means that
you can obtain the source code, use the
program, and modify it freely without the
confines of proprietary software.
PostgreSQL provides a wealth of features
that are usually only found in commercial
databases such as Oracle. The following is a
brief listing of some of these core features:
It supports user
defined operators, functions,
access methods, and data types.
It supports the core SQL99 specification.
It supports referential integrity, which is used
to insure the validity of a database's data.
The flexibility of the PostgreSQL API has
allowed vendors to provide development
support easily for the PostgreSQL RDBMS.
These interfaces include Object Pascal, Python,
Perl, PHP, ODBC, Java/JDBC, Ruby, TCL,
C/C++, and Pike.
It uses a process
The PostgreSQL feature known as
increases the reliability of the database
by logging changes before they are written to
the database. This ensures that, in the unlikely
occurrence of a database crash, there will be a
record of transactions from which to restore.
This can be greatly beneficial in the event of a
crash, as any changes that were not written to
the database can be recovered by using the data
that was previously logged. Once the system is
restored, a user can then continue to work from
the point that they were at before the crash
First, you can check if you have PostgreSQL
database server already installed. Type the
following command in your terminal.
qa | grep postgresql
If you get postgresql
or something alike when the command is
executed, it means that you already have
PostgreSQL database software installed.
If you don't have PostgreSQL installed, you will
need to download PostgreSQL from binary RPM
You can download the latest version of PostgreSQL
from the following address:
You will need to install postgresql
If you want to run server, install postgresql
server and postgresql
rpms. The other packages
are for developing applications, docs, tcl, perl, etc
Run the following commands to install:
First check whether PostgreSQL is running:
get some information back saying that is it
running like this "postmaster (pid 1156 790) is
The user "postgres" should have already been
configured by the installation of the RPMs. A
password will be missing. So, we need to assign a
password for user postgres:
Login as a user postgres:
This will execute the profile:
To initialize PostgreSQL database server:
This creates a bunch of directories, a template directory
and sets up the postgres configuration in the user
To start the database server. As root:
“service postgresql start”
To create a database:
To connect to database:
Now, you will be at the PostgreSQL command
Welcome to psql, the PostgreSQL interactive terminal.
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
Basic Network and Security
To allow PostgreSQL to accept TCP/IP
connections from the JDBC driver:
PostgreSQL allows anyone from the local
machine to connect to the databases
without a password challenge. So, if other users
are using your computer
go to the
end of the file, and change the authorization
“trust” to “password”
Start server again:
“service postgresql start”