YoLinux Tutorial: The PostgreSQL Database and Linux

This tutorial covers the installation and use of the PostgreSQL database on Linux This tutorial will also cover the generation and use a simple database. The interface language of the PostgreSQL database is the standard SQL (Standard Query Language) which allows for inserts, updates and queries of data stored in relational tables. The SQL language is also used for the administration of the database for the creation and modification of tables, users and access privileges. Tables are identified by unique names and hold data in a row and column (record) structure. A fixed number of named columns are defined for a table with a variable number of rows.

Related YoLinux Tutorials:

°MySQL

°SQLite

°Linux Networking

°Linux Sys Admin

°Internet Security

°Web site configuration

°YoLinux Tutorials Index




Free Information Technology Magazines and Document Downloads
TradePub link image


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):
    • postgresql-7.1.3-2
    • postgresql-libs-7.1.3-2
    • postgresql-server-7.1.3-2
    • postgresql-perl-7.1.3-2
    Other packages available:
    • postgresql-odbc
    • postgresql-jdbc
    • postgresql-tcl
    • postgresql-tk
    • postgresql-python
    • 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:
    • User: postgres
    • Home directory: /var/lib/pgsql
    • 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:
    • 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.
    • Host Access file: /var/lib/pgsql/data/pg_hba.conf
    • 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
    • \l :List databases
    • \c database-name :List databases
    • \d :List tables in database
    • \d table-name :Describe table
    • select * from table-name :List table contents

  • More info:
    • Create a user:
      • Command line: [prompt]$ createuser dude
      • SQL: CREATE USER dude WITH PASSWORD 'supersecret';
        Change with ALTER USER
    • 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
    • Delete a user:
      • Command line: [prompt]$ dropuser SuperDude
    • Delete a database:
      • Command line:
        • [prompt]$ destroydb bedrock
        • [prompt]$ dropdb bedrock
      • SQL: DROP DATABASE bedrock;
    • 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
    • Backup a database:
      • [prompt]$ pg_dumpall > outfile
      • [prompt]$ pg_dumpall -Fc dbname > outfile
    • 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 Man Pages:

  • createdb - Create a new PostgreSQL database
  • createlang - Add a new programming language to a PostgreSQL database
  • createuser - Create a new PostgreSQL user
  • dropdb - Remove an existing PostgreSQL database
  • droplang - Remove a programming language from a PostgreSQL database
  • dropuser - Drops (removes) a PostgreSQL user
  • pg_dump - Extract a PostgreSQL database into a script file
  • pg_dumpall - Extract all PostgreSQL databases into a script file
  • psql - PostgreSQL interactive terminal
  • vacuumdb - Clean and analyze a PostgreSQL database

PostgreSQL SQL Statement Man Pages:


Links:


Books:

PostgreSQL
by Korry Douglas, Susan Douglas
ISBN #0735712573, New Riders

Amazon.com
PostgreSQL Essential Reference
by Barry Stinson
ISBN #0735711216, New Riders

Amazon.com
PostgreSQL: Developer's Handbook
by Ewald Geschwinde, Hans-Juergen Schoenig, Hans-Jurgen Schonig
ISBN #0672322609, SAMS

Amazon.com
Practical PostgreSQL
John C. Worsley, Joshua D. Drake
ISBN #1565928466, O'Reilly

Amazon.com
Beginning Databases with PostgreSQL
by Richard Stones, Neil Matthew
ISBN #1861005156, Wrox Press Inc

Amazon.com
   

    Bookmark and Share


Advertisements




Copyright © 2002 by Greg Ippolito