The PostgreSQL Database - Installation and Configuration

The PostgreSQL Database - Installation and Configuration:

Red Hat 7.2 example:

* Install (rpm -ivh package) or check for the following PostgreSQL RPMs (rpm -q package):
o postgresql-7.1.3-2
o postgresql-libs-7.1.3-2
o postgresql-server-7.1.3-2
o postgresql-perl-7.1.3-2
Other packages available:
o postgresql-odbc
o postgresql-jdbc
o postgresql-tcl
o postgresql-tk
o postgresql-python
o postgresql-devel

Install: rpm -ivh postgresql-7.1.3-2.i386.rpm postgresql-libs-7.1.3-2.i386.rpm postgresql-server-7.1.3-2.i386.rpm postgresql-perl-7.1.3-2.i386.rpm

* The user "postgres" should have already been configured by the installation of the RPMs. Info:
o User: postgres
o Home directory: /var/lib/pgsql
o Default shell: /bin/bash
A password will be missing. As root issue the command: passwd postgres to assign a password for user postgres.

* Login as user postgres: su - postgres
This will execute the profile: /var/lib/pgsql/.bash_profile

PGLIB=/usr/lib/pgsql
PGDATA=/var/lib/pgsql/data
export PGLIB PGDATA


* Initialize PostgreSQL database server: initdb --pgdata=/var/lib/pgsql/data
This creates a bunch of directories, a template directory and sets up the postgres configuration in the user directory /var/lib/pgsql/. Red Hat start command (service)/script (rc script) will perform this task if it has not already been performed. See next step - Starting the database.

* Starting the database server: As root. (from most to least favorite method)

service postgresql start
(If the database has not already been initialized with initdb, this will be performed by the command)
OR
/etc/rc.d/init.d/postgresql start
(If the database has not already been initialized with initdb, this will be performed by the script)
OR
/usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start &
OR
/usr/bin/postmaster -D /var/lib/pgsql/data &

Notes:
o Configuration file: /var/lib/pgsql/data/postgresql.conf
By default there is no network access. See the directive tcpip_socket. (Required for ODBC,JDBC) Also see the postmaster directive "-i". Logging and tuning parameters are specified here.
o Host Access file: /var/lib/pgsql/data/pg_hba.conf
o Authentication/Identification file: /var/lib/pgsql/data/pg_ident.conf

* Create a database: createdb bedrock
(As Linux user postgres)

* Connect to the database: psql bedrock
Execute command as Linux user postgres
You will now be at the PostgreSQL command line prompt.

[prompt]$ psql - or "psql bedrock"
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

bedrock=#
bedrock=# \c bedrock
You are now connected to database bedrock.
bedrock=# create table employee (Name char(20),Dept char(20),jobTitle char(20));
CREATE
bedrock=# INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
INSERT 18733 1
bedrock=# INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
bedrock=# INSERT into employee values ('Barney Rubble','Sales','Neighbor');
bedrock=# INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
bedrock=# SELECT * from employee;
name | dept | jobtitle
----------------------+----------------------+----------------------
Fred Flinstone | Quarry Worker | Rock Digger
Wilma Flinstone | Finance | Analyst
Barney Rubble | Sales | Neighbor
Betty Rubble | IT | Neighbor
(4 rows)

bedrock=#
bedrock=# \q


* Database discovery / Examine a database (as user postgres: su - postgres):
[postgres]$ psql
o \l :List databases
o \c database-name :List databases
o \d :List tables in database
o \d table-name :Describe table
o select * from table-name :List table contents

* More info:
o Create a user:
+ Command line: [prompt]$ createuser dude
+ SQL: CREATE USER dude WITH PASSWORD 'supersecret';
Change with ALTER USER
o Grant priveliges:
+ SQL: GRANT UPDATE ON table-name to dude
+ SQL: GRANT SELECT ON table-name to dude
+ SQL: GRANT INSERT ON table-name to dude
+ SQL: GRANT DELETE ON table-name to dude
+ SQL: GRANT RULE ON table-name to dude
+ SQL - Do it all: GRANT ALL PRIVILEGES ON table-name to public
o Delete a user:
+ Command line: [prompt]$ dropuser SuperDude
o Delete a database:
+ Command line:
# [prompt]$ destroydb bedrock
# [prompt]$ dropdb bedrock
+ SQL: DROP DATABASE bedrock;
o Create a database:
+ Command line: [prompt]$ createdb bedrock -U user-name -W
You will be prompted for a password. (or execute as Linux user postgres without -U and -W options)
+ SQL: CREATE DATABASE bedrock
o Backup a database:
+ [prompt]$ pg_dumpall > outfile
+ [prompt]$ pg_dumpall -Fc dbname > outfile
o Version Upgrades:
+ Dump: [prompt]$ postgresql-dump -t /var/lib/pgsql/backup/db.bak -p /var/lib/pgsql/backup/old -d
+ Restore: [prompt]$ psql -e template1 < /var/lib/pgsql/backup/db.bak
The table template1 is the default administrative database.

If one would like to generate a GUI interface to the database, I would recommend a web form to a Java servlet back-end or use a JAVA GUI program and JDBC. See the YoLinux tutorial on accessing PostgreSQL with JDBC in a Tomcat servlet example.

Links/Info:

* PostgreSQL documentation:
o /usr/share/doc/postgresql-7.1.3/html/index.html (local html)
o /usr/share/doc/postgresql-7.1.3/ (local directory)
* Resources: /usr/share/pgsql/