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.
