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

% 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

Download from http://odbc.postgresql.org/ftpsite/odbc/versions/msi/
Run a ODBC Driver(postdrv.exe)
Click [Yes] Button
Click [Next] Button
Click [Finish] Button
Click [OK] Button
Click [32bit ODBC] icon in a Control Panel
Click [System DSN] tab and [Add] Button
Click [Finish]

Authentication Configuration

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

Open /var/lib/pgsql/data/postgresql.conf and edit as below.

#tcpip_socket = false
tcpip_socket = true
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

Hosted by www.Geocities.ws