1. Home
  2. Tutorials
  3. Databases
  4. PostgreSQL
Yolinux.com Tutorial

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.

Characteristics favoring the PostgreSQL Database:
  • Close adherence to the SQL standard
  • Supports a procedural language (PL/pgSQL, PL/Tcl, PL/Perl, PL/Python) to allow for processing within the database architecture
  • Extensive geospatial support (PostgreSQL is a leader in mapping and geospatial applications)

Free Information Technology Magazines and Document Downloads
TradePub link image

The PostgreSQL Database - Installation and Configuration:
YoLinux.com Tux database image

Ubuntu 14.04: Install: sudo apt-get install postgresql

  • postgresql-9.3 - libraries and SQL
  • postgresql-common - the database program
  • postgresql-client-9.3 - utility programs and man pages
  • postgresql-client-common - utility programs and man pages
  • libpq5 - network client libraries

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.

To initialize the system for the first run (as root): service postgresql initdb
Initializing database: [ OK ]

Once the database initialization has occurred, one can then start the database (as root): service postgresql restart

Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

  • 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
    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)
    /etc/rc.d/init.d/postgresql start
    (If the database has not already been initialized with initdb, this will be performed by the script)
    /usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start &
    /usr/bin/postmaster -D /var/lib/pgsql/data &

    • 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=# \c bedrock
    You are now connected to database bedrock.
    bedrock=# create table employee (Name char(20),Dept char(20),jobTitle char(20));
    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=# \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.


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:



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

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

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

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

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



    Bookmark and Share