edit

Installing Postgres SQL Database

Posted by btr Sun, 04 Jul 2004 02:28:35 GMT

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

1. /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.