Thursday, August 9, 2007

Add user account to postgreSQL database

step:1 Add user to unix/linux - debian system



First you have issue following command to add unix/linux user..

# adduser user

It will ask name, password and bla bla.........

You can change password by issuing command like this...

# passwd password

step:2 Becomming a super user...



# su - postgres

Then u can connect to the database..

# psql template1

or

# psql -d template1 -U postgres

step:3 Add user....



It creates user called "user" and password called "password"

template1=# CREATE USER user WITH PASSWORD 'password';


step:4 Add a database called testdb....



template1=# CREATE DATABASE testdb;

step:5 Grant privileges to that database...



template1=# GRANT ALL PRIVILEGES ON DATABASE testdb to user;

exit from psql..

template1=# \q


step:6 Try to connect database ....



Try to connect to the database "testdb" user "user"

# su - user
# psql -d testdb -U user

enjoy......

How to connect to postgreSQL database through the network (linux - debian system)

First you have start the postgreSQL server....

# /etc/init.d/postgresql start

step:1 Allow remote access to postgresql database....


First you open the file called /etc/postgresql/pg_hba.conf.

# vim /etc/postgresql/8.1/main/pg_hba.conf

Now append the following line.

host all all ip_address net_mask auth_method


"ip_address" means your ip. "net_mask" means net mask. "auth_method" means authentication method. There are few authentication methods. such as "password, trust, md5" like that...

eg:
host all all 192.168.0.2 255.255.255.0 trust

When we add auth_method as 'trust', think as trust user. If we add as 'md5', have to issue password while connect to this machine.

more details on postgresql doc

then save and close

step:2 Allow TCP/IP communication



open the file called /etc/postgresql/8.1/main/postgresql.conf. (8.1 means version. It may be vary)

Uncomment "listen_addresses" option.

It's default value is "localhost" and change to '*'.


Finally restart the postgresql database server..

# /etc/init.d/postgresql restart

step:3 Try to connect from remote machine



# psql -h 192.168.0.21 -U postgres d- test

-h means host
-U means user name
-d means database name

enjoy...........

Tuesday, May 15, 2007

Backup and Restore Database.....

There are three approaches to backing up Postgresql database..

1.SQL dump
2.File system level backups
3.On-line backups

1.SQL dump

First log as postgres user..

#su - postgres or su -l postgres

After that you have issue these ..

#pg_dump dname > dump_name

"dname" is the database name,you are going to backup.

"dump_name" is dump name.

2.File system level backups
3.On-line backups


comming soon...

Thursday, May 10, 2007

Alter table - Modify table

Postgresql doesnot provide to modify tables, like Oralcle

To modify table first you have to drop column and then ADD column again..

test2=# ALTER TABLE tbl1 DROP column col1;

test2=# ALTER TABLE tbl1 ADD column col_new VARCHAR(10);

How to create foreign key reference to part of composite key .......

Just say two tables tbl1 and tbl2. tbl1 contains composite key (A,B,C). Then tbl2 contains composite key (A,B). When we want to create foriegn key to table tbl1(A,B) from tbl2(A,B), it gives error....

Because there is not a unique key in tbl1...

eg:

tbl1

A B C
----------
sa tt w
sa tt q

tbl2

A B
------
sa tt

In that case, there is no unique key on tbl1 that match to tbl2.

solution is, you have to create unique index to that A and B feilds in tbl1 table.

eg:

CREATE UNIQUE INDEX tbl1_A_B ON tbl1 (A,B);

Wednesday, May 9, 2007

create a index in postgreSQL................

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.


Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.



CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]


Parameters

UNIQUE


Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

name

The name of the index to be created. No schema name can be included here; the index is always created in the same schema as its parent table.

table

The name (possibly schema-qualified) of the table to be indexed.

method

The name of the method to be used for the index. Choices are btree, hash, rtree, and gist. The default method is btree.

column

The name of a column of the table.

expression

An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses may be omitted if the expression has the form of a function call.

opclass

The name of an operator class. See below for details.

tablespace

The tablespace in which to create the index. If not specified, default_tablespace is used, or the database's default tablespace if default_tablespace is an empty string.

predicate

The constraint expression for a partial index.


A Unique Index

Creates a unique index on a table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name ON table_name (column_name)

The "column_name" specifies the column you want indexed.

A Simple Index

Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.

CREATE INDEX index_name ON table_name (column_name)

The "column_name" specifies the column you want indexed.

Examples

To create a B-tree index on the column title in the table films:

CREATE UNIQUE INDEX title_idx ON films (title);

To create an index on the column code in the table films and have the index reside in the tablespace indexspace:

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

Monday, April 16, 2007

What is postgreSQL?

postgreSQL is an object-relational database management system(ORDBMS) based on postgres.It is an open-source product.It supports a large part of sql standards and offers many modern features.

* complex queries
* foreign keys
* triggers
* views
* transactional integrity
* multiversion concurrency control

Also, postgreSQL can be extended by the user in many ways, for example by adding new

* data types
* functions
* operators
* aggregate functions
* index methods
* procedural languages

There is a extension called postGIS to the postgreSQL. postGIS supports for geographical objects to the postgreSQL database, allowing it to be backend database for geographic information systems(GIS).


And because of the liberal license, postgreSQL can be used, modified, and distributed by everyone free of charge for any purpose, be it private, commercial, or academic.


How can we install postgresql in debian system.....

Just search in your cache for postgreSQL by typing following in command line


apt-cache search postgresql

then list latest version of the postgresql. For an example, we are going to install postgresql-8.2.To install type following ..

apt-get install postgresql-8.2


During installation it creates a user called postgres. That user can work with postgresql database.

To connect to the database...

First you have to log as postgres user

Initially there are three databases

postgres
template0
template1

To connect postgres database

$ psql postgres


to get help with SQL command
\h

to get help with psql command
\?


Official site for postgreSQL
http://www.postgresql.org/

Online Documentation for postgreSQL
http://www.postgresql.org/doc/

Information about postGIS
postGIS