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:

°PostgreSQL, Transactions and Foreign Keys

°PostgreSQL C Programming API

°SQL Workbench/J

°List of Linux Databases

°MySQL

°SQLite

°Linux Networking

°Linux Sys Admin

°Internet Security

°Apache, Tomcat, PostgreSQL and Java

°Web site configuration

°YoLinux Tutorials Index




Free Information Technology Magazines and Document Downloads
TradePub link image


The PostgreSQL Database - Installation and Configuration:

Ubuntu 12.04: Install: sudo apt-get install postgresql

  • postgresql-9.1 - libraries and SQL
  • postgresql-common - the database program
  • postgresql-client-9.1 - utility programs and man pages
  • postgresql-client-common - utility programs and man pages

Starting the database: sudo service postgresql start


Red Hat Enterprise Linux 6 RPM packages:

  • postgresql-8.4.11-1.el6_2.x86_64 - commands, HTML docs and man pages
  • postgresql-server-8.4.11-1.el6_2.x86_64 - DB server and locale based messages
  • postgresql-libs-8.4.11-1.el6_2.x86_64 - libraries and locale based messages
  • postgresql-docs-8.4.11-1.el6_2.x86_64 - tutorials, examples and a monster PDF manual
Other RPM packages:
  • postgresql-test - lots of examples.
  • postgresql-jdbc - Java connectivity
  • postgresql-plperl - Perl connectivity
  • postgresql-plpython - Python connectivity
  • postgresql-devel - C language connectivity

Starting the database (as root): service postgresql start

The first time this is run you will get the following error:
/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
[FAILED]
To initialize the system for the first run (as root): service postgresql initdb
Initializing database: [ OK ]
Once the database initialization has occured, one can then start the database (as root): service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
       

Red Hat 7.2 packages:

  • 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

    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


Using the PostgreSQL Database:
  • 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 :Connect to database
    • \c :Show the database your are connected to
    • \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.

User GUI interfaces to the database are often written with web based frameworks like PHP or Java with a servlet back-end. 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 - 2014 by Greg Ippolito