Installing Postgres SQL Database

Here’re the steps to install a Postgres SQL database on RedHat 9.0.

1. login as root
2. su postgres
3. mkdir /home/db
4. chown postgres /home/db and chgrp postgres /home/db
5. export PGDATA=/home/db
6. initdb
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
This locale setting will prevent the use of indexes for pattern matching
operations. If that is a concern, rerun initdb with the collation order
set to “C”. For more information see the Administrator’s Guide.

Fixing permissions on existing directory /home/db… ok
creating directory /home/db/base… ok
creating directory /home/db/global… ok
creating directory /home/db/pg_xlog… ok
creating directory /home/db/pg_clog… ok
creating template1 database in /home/db/base/1… ok
creating configuration files… ok
initializing pg_shadow… ok
enabling unlimited row size for system tables… ok
initializing pg_depend… ok
creating system views… ok
loading pg_description… ok
creating conversions… ok
setting privileges on built-in objects… ok
vacuuming database template1… ok
copying template1 to template0… ok

Success. You can now start the database server using:

/usr/bin/postmaster -D /home/db
or
/usr/bin/pg_ctl -D /home/db -l logfile start

7. /usr/bin/pg_ctl -D /home/db -l logfile start

bash-2.05b$ /usr/bin/pg_ctl -D /home/db -l logfile start
postmaster successfully started

bash-2.05b$ createdb test
CREATE DATABASE

bash-2.05b$ psql test
Welcome to psql 7.3.2, 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

test=#

test=# CREATE TABLE films (
test(# code char(5) CONSTRAINT firstkey PRIMARY KEY,
test(# title varchar(40) NOT NULL,
test(# did integer NOT NULL,
test(# date_prod date,
test(# kind varchar(10),
test(# len interval hour to minute
test(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index ‘firstkey’ for table ‘films’
CREATE TABLE

test=# INSERT INTO films (code, title, did, date_prod, kind)
test-# VALUES (‘T_601’, ‘Yojimbo’, 106, DEFAULT, ‘Drama’);
INSERT 16981 1

test=# select * from films;
code | title | did | date_prod | kind | len
——-+———+—–+———–+——-+—–
T_601 | Yojimbo | 106 | | Drama |
(1 row)

bash-2.05b$ pg_ctl -D /home/db stop
waiting for postmaster to shut down……done
postmaster successfully shut down

starting it with networking

bash-2.05b$ pg_ctl -D /home/db -l logfile -o -i start
postmaster successfully started

bash-2.05b$ psql test
Welcome to psql 7.3.2, 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

test=# create user tester with password ‘tester’;
CREATE USER

create table for mono test

test=# CREATE TABLE “test” (
test(# “person” character varying(256) NOT NULL,
test(# “email” character varying(256) NOT NULL
test(# );
CREATE TABLE
test=# insert into test (person, email) values(‘user1′,’users@nomail.com’);
INSERT 16984 1
test=# insert into test (person, email) values(‘user2′,’user2@nomail.com’);
INSERT 16985 1
test=# select * from test;
person | email
——–+——————
user1 | users@nomail.com
user2 | user2@nomail.com
(2 rows)

I got this error

Npgsql.NpgsqlException:
No pg_hba.conf entry for host 127.0.0.1, user tester, database tester
Severity: FATAL
in <0x00061> Npgsql.NpgsqlConnection:CheckErrors ()
in <0x0004f> (wrapper remoting-invoke-with-check) Npgsql.NpgsqlConnection:CheckErrors ()
in <0x00447> Npgsql.NpgsqlConnection:Open ()

Well, the database name should be test, but it reported “tester”. I don’t know why. I’m testing dbpage1.aspx.

OK, I added a record in pg_hba.conf to allow host from 127.0.0.1 to connect to the database, but got this error instead

Npgsql.NpgsqlException:
Database “tester” does not exist in the system catalog.
Severity: FATAL

It still thinks the database is “tester”. I need to check the connect string one more time and will shutdown Apache to see if it changes anything.

While waiting for reboot, I downloaded pgadmin, a GUI program for managing Postgres database. Nice!

Finally, I got it running on dbpage1.aspx and dbpage2.aspx. It turned out that the connect string was wrong. The original connect string was “sever=127.0.0.1;user id=tester;password=tester;dbname=test”. The correct string should be using “database=test”!

Don’t forget granting permission to the tester on the database test!

Here’s the dbpage1.aspx sample.

Installing Postgres SQL Database

Here’re the steps to install a Postgres SQL database on RedHat 9.0.

1. login as root
2. su postgres
3. mkdir /home/db
4. chown postgres /home/db and chgrp postgres /home/db
5. export PGDATA=/home/db
6. initdb
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
This locale setting will prevent the use of indexes for pattern matching
operations. If that is a concern, rerun initdb with the collation order
set to “C”. For more information see the Administrator’s Guide.

Fixing permissions on existing directory /home/db… ok
creating directory /home/db/base… ok
creating directory /home/db/global… ok
creating directory /home/db/pg_xlog… ok
creating directory /home/db/pg_clog… ok
creating template1 database in /home/db/base/1… ok
creating configuration files… ok
initializing pg_shadow… ok
enabling unlimited row size for system tables… ok
initializing pg_depend… ok
creating system views… ok
loading pg_description… ok
creating conversions… ok
setting privileges on built-in objects… ok
vacuuming database template1… ok
copying template1 to template0… ok

Success. You can now start the database server using:

/usr/bin/postmaster -D /home/db
or
/usr/bin/pg_ctl -D /home/db -l logfile start

7. /usr/bin/pg_ctl -D /home/db -l logfile start

bash-2.05b$ /usr/bin/pg_ctl -D /home/db -l logfile start
postmaster successfully started

bash-2.05b$ createdb test
CREATE DATABASE

bash-2.05b$ psql test
Welcome to psql 7.3.2, 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

test=#

test=# CREATE TABLE films (
test(# code char(5) CONSTRAINT firstkey PRIMARY KEY,
test(# title varchar(40) NOT NULL,
test(# did integer NOT NULL,
test(# date_prod date,
test(# kind varchar(10),
test(# len interval hour to minute
test(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index ‘firstkey’ for table ‘films’
CREATE TABLE

test=# INSERT INTO films (code, title, did, date_prod, kind)
test-# VALUES (‘T_601’, ‘Yojimbo’, 106, DEFAULT, ‘Drama’);
INSERT 16981 1

test=# select * from films;
code | title | did | date_prod | kind | len
——-+———+—–+———–+——-+—–
T_601 | Yojimbo | 106 | | Drama |
(1 row)

bash-2.05b$ pg_ctl -D /home/db stop
waiting for postmaster to shut down……done
postmaster successfully shut down

starting it with networking

bash-2.05b$ pg_ctl -D /home/db -l logfile -o -i start
postmaster successfully started

bash-2.05b$ psql test
Welcome to psql 7.3.2, 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

test=# create user tester with password ‘tester’;
CREATE USER

create table for mono test

test=# CREATE TABLE “test” (
test(# “person” character varying(256) NOT NULL,
test(# “email” character varying(256) NOT NULL
test(# );
CREATE TABLE
test=# insert into test (person, email) values(‘user1′,’users@nomail.com’);
INSERT 16984 1
test=# insert into test (person, email) values(‘user2′,’user2@nomail.com’);
INSERT 16985 1
test=# select * from test;
person | email
——–+——————
user1 | users@nomail.com
user2 | user2@nomail.com
(2 rows)

I got this error

Npgsql.NpgsqlException:
No pg_hba.conf entry for host 127.0.0.1, user tester, database tester
Severity: FATAL
in <0x00061> Npgsql.NpgsqlConnection:CheckErrors ()
in <0x0004f> (wrapper remoting-invoke-with-check) Npgsql.NpgsqlConnection:CheckErrors ()
in <0x00447> Npgsql.NpgsqlConnection:Open ()

Well, the database name should be test, but it reported “tester”. I don’t know why. I’m testing dbpage1.aspx.

OK, I added a record in pg_hba.conf to allow host from 127.0.0.1 to connect to the database, but got this error instead

Npgsql.NpgsqlException:
Database “tester” does not exist in the system catalog.
Severity: FATAL

It still thinks the database is “tester”. I need to check the connect string one more time and will shutdown Apache to see if it changes anything.

While waiting for reboot, I downloaded pgadmin, a GUI program for managing Postgres database. Nice!

Finally, I got it running on dbpage1.aspx and dbpage2.aspx. It turned out that the connect string was wrong. The original connect string was “sever=127.0.0.1;user id=tester;password=tester;dbname=test”. The correct string should be using “database=test”!

Don’t forget granting permission to the tester on the database test!

Here’s the dbpage1.aspx sample.

Choosing a SQL database for Mono

I have got Mono ASP.NET working on Apache 2. Now I need to pick up a SQL database for some of the applications I need to run or develop. I’m running MoveableType which supports both MySQL and PostGresSQL. I also run PhpBB, which runs on Postgres currently. I would just need to pick one of them.

I’m using a reference for comparison.

Based on a two mintues of browsing the comparisons, I picked Postgres SQL as my database engine for Linux/Mono.

I installed Postgres a few years ago, but almost forgot everything about it. Here’s the site I’m checking on documentation.

Choosing a SQL database for Mono

I have got Mono ASP.NET working on Apache 2. Now I need to pick up a SQL database for some of the applications I need to run or develop. I’m running MoveableType which supports both MySQL and PostGresSQL. I also run PhpBB, which runs on Postgres currently. I would just need to pick one of them.

I’m using a reference for comparison.

Based on a two mintues of browsing the comparisons, I picked Postgres SQL as my database engine for Linux/Mono.

I installed Postgres a few years ago, but almost forgot everything about it. Here’s the site I’m checking on documentation.

Installing mod_mono for Apache 2

1. Download mod-mono from ftp.ximan.com and unzup it to /home/mono
2. cd /home/mono and ./configure –prefix=/usr
[root@localhost mod_mono-1.0]# ./configure –prefix=/usr
checking for a BSD-compatible install… /usr/bin/install -c
checking whether build environment is sane… yes
checking for gawk… gawk
checking whether make sets $(MAKE)… yes
checking for gcc… gcc
checking for C compiler default output… a.out
checking whether the C compiler works… yes
checking whether we are cross compiling… no
checking for suffix of executables…
checking for suffix of object files… o
checking whether we are using the GNU C compiler… yes
checking whether gcc accepts -g… yes
checking for gcc option to accept ANSI C… none needed
checking for style of include used by make… GNU
checking dependency style of gcc… gcc3
checking build system type… i686-pc-linux-gnu
checking host system type… i686-pc-linux-gnu
checking for ld used by GCC… /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld… yes
checking for /usr/bin/ld option to reload object files… -r
checking for BSD-compatible nm… /usr/bin/nm -B
checking for a sed that does not truncate output… /bin/sed
checking whether ln -s works… yes
checking how to recognise dependent libraries… pass_all
checking command to parse /usr/bin/nm -B output… ok
checking how to run the C preprocessor… gcc -E
checking for egrep… grep -E
checking for ANSI C header files… yes
checking for sys/types.h… yes
checking for sys/stat.h… yes
checking for stdlib.h… yes
checking for string.h… yes
checking for memory.h… yes
checking for strings.h… yes
checking for inttypes.h… yes
checking for stdint.h… yes
checking for unistd.h… yes
checking dlfcn.h usability… yes
checking dlfcn.h presence… yes
checking for dlfcn.h… yes
checking for ranlib… ranlib
checking for strip… strip
checking for objdir… .libs
checking for gcc option to produce PIC… -fPIC
checking if gcc PIC flag -fPIC works… yes
checking if gcc static flag -static works… yes
checking if gcc supports -c -o file.o… yes
checking if gcc supports -c -o file.lo… yes
checking if gcc supports -fno-rtti -fno-exceptions… yes
checking whether the linker (/usr/bin/ld) supports shared libraries… yes
checking how to hardcode library paths into programs… immediate
checking whether stripping libraries is possible… yes
checking dynamic linker characteristics… GNU/Linux ld.so
checking if libtool supports shared libraries… yes
checking whether to build shared libraries… yes
checking whether to build static libraries… yes
checking whether -lc should be explicitly linked in… no
creating libtool
checking if building mod_mono_old is requested… no
checking for –with-apxs… no
checking for –with-apr-config… not specified
checking for apxs2 in /usr/local/apache/sbin… no
checking for apxs in /usr/local/apache/sbin… no
checking for apr-config in /usr/local/apache/sbin… no
checking for apxs2 in /usr/local/apache2/bin… no
checking for apxs in /usr/local/apache2/bin… no
checking for apr-config in /usr/local/apache2/bin… no
checking for apxs2 in /usr/sbin… no
checking for apxs in /usr/sbin… found, we’ll use this. Use –with-apxs to specify another.
checking for apr-config in /usr/sbin… no
checking for apr-config… /usr/bin/apr-config
checking for Apache libexec directory… /usr/lib/httpd/modules
checking for Apache include directory… -I/usr/include/httpd
checking Apache version… 2.0
checking for pkg-config… /usr/bin/pkg-config
checking whether byte ordering is bigendian… no
checking for setenv… yes
checking for putenv… yes
checking for mono installation prefix… /usr
configure: creating ./config.status
config.status: creating Makefile
config.status: creating src/Makefile
config.status: creating man/mod_mono.8
config.status: creating man/Makefile
config.status: creating include/mod_mono_config.h
config.status: executing depfiles commands

Configuration summary for mod_mono

* Installation prefix = /usr
* Apache version = 2.0
* Apache modules directory = /usr/lib/httpd/modules
* apxs = /usr/sbin/apxs
* apr-config = /usr/bin/apr-config
* mono prefix = /usr
* Build old module: no

[root@localhost mod_mono-1.0]# make
Making all in src
make[1]: Entering directory `/home/mono/mod_mono-1.0/src’
source=’mod_mono.c’ object=’mod_mono.lo’ libtool=yes
depfile=’.deps/mod_mono.Plo’ tmpdepfile=’.deps/mod_mono.TPlo’
depmode=gcc3 /bin/sh ../depcomp
/bin/sh ../libtool –mode=compile gcc -DHAVE_CONFIG_H -I. -I. -I../include -g -O2 -I/usr/include/httpd -c -o mod_mono.lo `test -f ‘mod_mono.c’ || echo ‘./’`mod_mono.c
mkdir .libs
gcc -DHAVE_CONFIG_H -I. -I. -I../include -g -O2 -I/usr/include/httpd -c mod_mono.c -MT mod_mono.lo -MD -MP -MF .deps/mod_mono.TPlo -fPIC -DPIC -o .libs/mod_mono.lo
gcc -DHAVE_CONFIG_H -I. -I. -I../include -g -O2 -I/usr/include/httpd -c mod_mono.c -MT mod_mono.lo -MD -MP -MF .deps/mod_mono.TPlo -o mod_mono.o >/dev/null 2>&1
mv -f .libs/mod_mono.lo mod_mono.lo
/bin/sh ../libtool –mode=link gcc -g -O2 -I/usr/include/httpd -o libmod_mono.la -rpath /usr/lib -module mod_mono.lo
rm -fr .libs/libmod_mono.la .libs/libmod_mono.* .libs/libmod_mono.*
gcc -shared mod_mono.lo -Wl,-soname -Wl,libmod_mono.so.0 -o .libs/libmod_mono.so.0.0.0
(cd .libs && rm -f libmod_mono.so.0 && ln -s libmod_mono.so.0.0.0 libmod_mono.so.0)
(cd .libs && rm -f libmod_mono.so && ln -s libmod_mono.so.0.0.0 libmod_mono.so)
ar cru .libs/libmod_mono.a mod_mono.o
ranlib .libs/libmod_mono.a
creating libmod_mono.la
(cd .libs && rm -f libmod_mono.la && ln -s ../libmod_mono.la libmod_mono.la)
make[1]: Leaving directory `/home/mono/mod_mono-1.0/src’
Making all in man
make[1]: Entering directory `/home/mono/mod_mono-1.0/man’
make[1]: Nothing to be done for `all’.
make[1]: Leaving directory `/home/mono/mod_mono-1.0/man’
make[1]: Entering directory `/home/mono/mod_mono-1.0′
make[1]: Nothing to be done for `all-am’.
make[1]: Leaving directory `/home/mono/mod_mono-1.0′

[root@localhost mod_mono-1.0]# make install
Making install in src
make[1]: Entering directory `/home/mono/mod_mono-1.0/src’
/usr/sbin/apxs -i -a -n mono libmod_mono.la
/usr/lib/httpd/build/instdso.sh SH_LIBTOOL=’/usr/lib/httpd/build/libtool’ libmod_mono.la /usr/lib/httpd/modules
/usr/lib/httpd/build/libtool –mode=install cp libmod_mono.la /usr/lib/httpd/modules/
cp .libs/libmod_mono.so.0.0.0 /usr/lib/httpd/modules/libmod_mono.so.0.0.0
(cd /usr/lib/httpd/modules && rm -f libmod_mono.so.0 && ln -s libmod_mono.so.0.0.0 libmod_mono.so.0)
(cd /usr/lib/httpd/modules && rm -f libmod_mono.so && ln -s libmod_mono.so.0.0.0 libmod_mono.so)
cp .libs/libmod_mono.lai /usr/lib/httpd/modules/libmod_mono.la
cp .libs/libmod_mono.a /usr/lib/httpd/modules/libmod_mono.a
ranlib /usr/lib/httpd/modules/libmod_mono.a
chmod 644 /usr/lib/httpd/modules/libmod_mono.a
libtool: install: warning: remember to run `libtool –finish /usr/lib’
chmod 755 /usr/lib/httpd/modules/libmod_mono.so
[activating module `mono’ in /etc/httpd/conf/httpd.conf]
make[1]: Leaving directory `/home/mono/mod_mono-1.0/src’
Making install in man
make[1]: Entering directory `/home/mono/mod_mono-1.0/man’
make[2]: Entering directory `/home/mono/mod_mono-1.0/man’
make[2]: Nothing to be done for `install-exec-am’.
/bin/sh ../mkinstalldirs /usr/man/man8
mkdir -p — /usr/man/man8
/usr/bin/install -c -m 644 ./mod_mono.8 /usr/man/man8/mod_mono.8
make[2]: Leaving directory `/home/mono/mod_mono-1.0/man’
make[1]: Leaving directory `/home/mono/mod_mono-1.0/man’
make[1]: Entering directory `/home/mono/mod_mono-1.0′
make[2]: Entering directory `/home/mono/mod_mono-1.0′
make[2]: Nothing to be done for `install-exec-am’.
make[2]: Nothing to be done for `install-data-am’.
make[2]: Leaving directory `/home/mono/mod_mono-1.0′
make[1]: Leaving directory `/home/mono/mod_mono-1.0′

[root@localhost mod_mono-1.0]# libtool –finish /usr/lib
PATH=”$PATH:/sbin” ldconfig -n /usr/lib
———————————————————————-
Libraries have been installed in:
/usr/lib

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR’
flag during linking and do at least one of the following:
– add LIBDIR to the `LD_LIBRARY_PATH’ environment variable
during execution
– add LIBDIR to the `LD_RUN_PATH’ environment variable
during linking
– use the `-Wl,–rpath -Wl,LIBDIR’ linker flag
– have your system administrator add LIBDIR to `/etc/ld.so.conf’

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.

3. vi /etc/httpd/conf/httpd.conf and add these lines
Alias /demo “/usr/share/doc/xsp/test”
MonoApplications “/demo:/usr/share/doc/xsp/test”
and these lines

<br /> <location /demo><br /> SetHandler mono<br /> </location><br />
4. apachectl restart

Here’s the demo pages.

Data Grid sample.


Web Service sample.