YoLinux Tutorial: The PostgreSQL Database and Linux

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

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 documentation:
  • /usr/share/doc/postgresql-7.1.3/html/index.html (local html)
  • /usr/share/doc/postgresql-7.1.3/ (local directory)
• Resources: /usr/share/pgsql/

• 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:

• Postgresql.org
• pgAdmin.org - GUI admin tool

Books:

	PostgreSQL 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