the PostgreSQL DB Administration Guide - Trend Micro

assistantashamedData Management

Nov 29, 2012 (4 years and 8 months ago)

319 views

IWSS2.0 Solaris/Linux
-

PostgreSQL database
administration


IWSS2.0 Solaris/Linux release provides two scripts to cleanup and recreate PostgreSQL
database. They, cleanup
-
postgres
-
env.sh and setup
-
postgres
-
env.sh, are inclu
ded in the
installation package
.


What cleanup
-
postgres
-
env.sh does is to remove all the tables and stored functions in the
PostgreSQL database. What setup
-
postgres
-
env.sh does is to remove tables (all the existing
data in the IWSS related tables will
be removed as well) and then re
cre
ate tables, stored
procedures and the initial data.


There are two ways to
execute

the SQL query statements in PostgreSQL.




With psql on IWSS


1.

Make a
telnet
connection
to IWSS server

2.

G
o to /etc/iscan/PostgreSQL
/bin

directory

3.

E
xecute the command below


./ps
ql

U sa

d iwss


4.

The PostgreSQL interactive terminal shows. You can execu
te the SQL commands
from here.


Note: Postgre
SQL has its own slash commands. You have to end your SQL
command with a semicolon or
\
g. For example,


i
wss => select * from tb_policy
;



or


i
wss => select * from tb_policy

i
wss =>
\
g




With a ODBC data source


1.

Download the latest version of ODBC driver for PostgreSQL from


ftp://ftp.postgresql.org/pub/od
bc/versions/msi


2.

Install the ODBC driver
for PostgreSQL
on your Windows platform.

3.

Create a Data Source Name for connecting to PostgreSQL

database

from your
Windows platform.


a)

Open the ODBC Data Source Administrator by going from Start >
Programs > Ad
ministrative Tools > Data Sources (ODBC).

b)

Click on the System DSN
tab;

add a name for the PostgreSQL database by
clicking on the Add button.

c)

In the Create New Data Source window, select “PostgreSQL” as the driver
name

and click on Finish.

d)

In the PostgreSQL

ODBC Driver (psqlODBC) Setup window, enter the
required database information and click on Save.

e)

Click on OK to complete the creation of DSN for PostgreSQL.


4.

Use database query tool, for example WinSQL or MS Access, to connect to
PostgreSQL database and ma
nipulate data.