postgresql-7.0.2 (RDBMS/SQL/ODBC)

Prepare RPM files

  • Download RPM files from Redhat site http://www.redhat.com/ or Copy from Redhat 7.0 cdrom

    Install

  • Install Postgre SQL 7.0.2 
    # rpm -ihv postgresql-7.0.2-17.i386.rpm
# rpm -ihv postgresql-server-7.0.2-17.i386.rpm
# rpm -ihv postgresql-devel-7.0.2-17.i386.rpm
  • Install an optional module for ODBC 
    # rpm -ihv postgresql-odbc-7.0.2-17.i386.rpm
  • Install optional modules for PHP 
    # rpm -ihv php-4.0.1pl2-9.i386.rpm
# rpm -ihv mod_php-4.0.1pl2-9.i386.rpm
# rpm -ihv php-pgsql-4.0.1pl2-9.i386.rpm
# rpm -ihv php-manual-4.0.1pl2-9.i386.rpm

    Configure

  • Type 
    # su - postgres
$ cd data
$ chmod u+w pg_hba.conf
  • Edit /usr/local/pgsql/data/pg_hba.conf 
    ...
# The above allows any user from any host with IP address 192.168.0.x to
# connect to database template1 as the same username that ident on that host
# identifies him as (typically his Unix username).  

#host         all        192.168.0.1   255.255.255.255   reject
host         all        0.0.0.0       0.0.0.0           trust

# The above would allow anyone anywhere except from 192.168.0.1 to connect to
# any database under any username.
...

    How to start

  • Start (RedHat7) 
    # /etc/init.d/postgres start

    Check a Postgre SQL process

  • Check a process 
    # ps ax | grep postmaster
  537 ?        S      0:00 /usr/local/pgsql/bin/postmaster -S -D/usr/local/pgsql

    How to create user

  • Create dbuser 
    # su - postgres
$ createuser dbuser
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

    How to create database

    % createdb tmpdb

    How to login

  • Login the tmpdb Datebase 
    % psql tmpdb

    Command list

     \?           -- help
 \a           -- toggle field-alignment (currently on)
 \C [<captn>] -- set html3 caption (currently '')
 \connect <dbname|-> <user> -- connect to new database (currently 'hoge')
 \copy table {from | to} <fname>
 \d [<table>] -- list tables and indices, columns in <table>, or * for all
 \da          -- list aggregates
 \dd [<object>]- list comment for table, field, type, function, or operator.
 \df          -- list functions
 \di          -- list only indices
 \do          -- list operators
 \ds          -- list only sequences
 \dS          -- list system tables and indexes
 \dt          -- list only tables
 \dT          -- list types
 \e [<fname>] -- edit the current query buffer or <fname>
 \E [<fname>] -- edit the current query buffer or <fname>, and execute
 \f [<sep>]   -- change field separater (currently '|')
 \g [<fname>] [|<cmd>] -- send query to backend [and results in <fname> or pipe]
 \h [<cmd>]   -- help on syntax of sql commands, * for all commands
 \H           -- toggle html3 output (currently off)
 \i <fname>   -- read and execute queries from filename
 \l           -- list all databases
 \m           -- toggle monitor-like table display (currently off)
 \o [<fname>] [|<cmd>] -- send all query results to stdout, <fname>, or pipe
 \p           -- print the current query buffer
 \q           -- quit
 \r           -- reset(clear) the query buffer
 \s [<fname>] -- print history or save it in <fname>
 \t           -- toggle table headings and row count (currently on)
 \T [<html>]  -- set html3.0 <table ...> options (currently '')
 \x           -- toggle expanded output (currently off)
 \w <fname>   -- output current buffer to a file
 \z           -- list current grant/revoke permissions
 \! [<cmd>]   -- shell escape or command

    How to Create tables

    tmpdb=> create table emp (
tmpdb=> empono int, ename varchar(20), job varchar(10));
CREATE
tmpdb=>
  • Read and execute queries from filename
    emp.sql 
    create table emp (
    empono int,
    ename varchar(20),
    job varchar(10)
);

    tmpdb=> \i emp.sql
create table emp (
    empono int,
    ename varchar(20),
    job varchar(10)
);
CREATE
EOF
tmpdb=>

    How to insert a record

    tmpdb=> insert into emp values ( 1003, 'hoge bar','Manager');
INSERT 17451 1

    How to select

    tmpdb=> select * from emp;
empono|ename           |job
------+----------------+-------
  1003|hoge bar        |Manager
(1 row)

    How to programming with perl

  • Select(make emp01.pl) 
    #!/usr/bin/perl -w

use Pg;
$dbname = 'tmpdb';
$SIG {PIPE} = sub { print "broken pipe \n" };

$conn = Pg::connectdb ("dbname=$dbname");

$result = $conn-> exec ("SELECT * FROM emp");

while (@row = $result-> fetchrow) {
        print " $row[0]  $row[1] ";
        print ("\n");
}
  • Add a executable permittion 
    % chmod u+x emp01.pl
  • Run 
    $ ./emp01.pl
 1003  hoge bar
  • Update(make emp02.pl) 
    #!/usr/bin/perl -w

use Pg;
$dbname = 'tmpdb';
$SIG {PIPE} = sub { print "broken pipe \n" };

$conn = Pg::connectdb ("dbname=$dbname");

$result = $conn-> exec ("UPDATE emp SET ename = 'Wangdi' WHERE empono=1003");

if ( $ result-> resultStatus == PGRES_COMMAND_OK ){
        print "OK\n"
}
else {
        print "NG: $result-> resultStatus \n"
}
  • Run 
    $ ./emp02.pl
OK

    How to use on web by Perl

  • Add a nobody user to the PostgreSQL 
    $ createuser
Enter name of user to add: nobody
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
  • Grant select, update permittion 
    $ psql tmpdb
tmpdb=> grant select, update on emp to nobody;
  • select.pl 
    #!/usr/bin/perl -w

# use PostgreSQL perl library
use Pg;

# database name
$dbname = 'tmpdb';

$SIG {PIPE} = sub { print "broken pipe \n" };

$conn = Pg::connectdb ("dbname=$dbname");

$result = $conn-> exec ("SELECT * FROM emp;");

print "Content-type: text/html\n\n";
print "<html>\n";
print "<head><title>Sample</title></head>\n<body>\n";

while (@row = $result-> fetchrow) {
        print " $row[0]    $row[1]    $row[2] <br>\n";
        print ("\n");
}

print "</body></html>";

    How to use on a web by PHP

  • test.php 
    <html>
<body>
<?php

// Connect to Database
$conn = pg_connect("dbname=tmpdb user=nobody");

// Select
$result = pg_exec("select * from emp");

$rows  = pg_numrows($result);
$columns = pg_numfields($result);

echo "<table border=1>";
for ($r=0;$r<$rows;$r++) {
        print "<tr>";
        print "<td>" . pg_result($result,$r,0);
        print "<td>" . pg_result($result,$r,1);
        print "<td>" . pg_result($result,$r,2);
        print "</tr>\n";
}
echo "</table>";

// Close Database
pg_close($conn);
?>

</body>
</html>

    How to install a ODBC Driver

    1. Download from http://odbc.postgresql.org/ftpsite/odbc/versions/msi/
    2. Run a ODBC Driver(postdrv.exe)

    3. Click [Yes] Button

    4. Click [Next] Button

    5. Click [Finish] Button

    6. Click [OK] Button

    7. Click [32bit ODBC] icon in a Control Panel

    8. Click [System DSN] tab and [Add] Button

    9. Click [Finish]

    Authentication Configuration

    1. Open /var/lib/pgsql/data/pg_hba.conf and edit as below. It allows access to all database from 192.168.0.0/255.255.255.0 network with password authentication
      ...
host         all         192.168.0.0   255.255.255.0       password
    2. Open /var/lib/pgsql/data/postgresql.conf and edit as below. 
      #tcpip_socket = false
tcpip_socket = true
    3. Set password for user hoge (There should be hoge user for both linux and postgres and hoge database) 
      [hoge@server hoge]$ psql
hoge=# alter user hoge with password 'hogeword';

    Daily backup

  • Make /etc/cron,daily/postgres.cron
    This script cleans out records and makes daily backup files for seven days. 
    #!/bin/bash

BACKUPDAY=7
DBNAME=dbtest1
BACKUPFILE=/var/lib/pgsql/backups/$DBNAME`/bin/date '+%Y%m%d'`

# Vacuum cleaner
su - postgres -c "/usr/bin/vacuumdb --analyze $DBNAME"

# Backup
/bin/rm -f $BACKUPFILE
su - postgres -c "/usr/bin/pg_dump $DBNAME > $BACKUPFILE"

# Compress a backup file
/bin/rm -f $BACKUPFILE.gz
su - postgres -c "/usr/bin/gzip $BACKUPFILE"

# Remove old file
find  /var/lib/pgsql/backups -mtime +$BACKUPDAY -exec rm -f {} \;
  • Backup all databases 
    $ pg_dumpall > backup_file_name
  • Clean(drop) schema prior to create 
    $ pg_dumpall -c > backup_file_name
  • Dump data as proper insert strings 
    $ pg_dumpall -D > backup_file_name
  • How to restore the backup file. 
    $ createdb dbname
$ psql -e dbname < backup_file_name

    Start up options

  • How to change a start up options
    1. Add a write permission to ~/data/postmaster.opts.default
    2. Modify ~/data/postmaster.opts.default
    3. Restart Postgre SQL
  • Shared memory disk buffers
    -B nBuffers

    Sets the number of shared-memory disk buffers for the postmaster to allocate for use by the backend server processes that it starts. This value defaults to 64 buffers, where each buffer is 8k bytes (or whatever BLCKSZ is set to in src/include/config.h).

    PGOPTS="-B 1025 -i -S -D/usr/local/pgsql/data"
  • Sort buffers

    Sets the number of sort buffers. This value defaults to 512 buffers, where each buffer is 512k bytes.

    PGOPTS="-o '-S 1024' -i -S -D/usr/local/pgsql/data"
  • fsync 
    PGOPTS="-o '-F' -i -S -D/usr/local/pgsql/data"
    Back
    Google
    Web www.grape-info.com