Install PostgreSQL on Linux

MySQL is a brisk little dolphin, but lacks many characteristics that modern relational databases should possess, such as integrity of reference, views, and triggers. Therefore, if you need to develop an e-commerce website and need these features, you may have to consider PostgreSQL. This article will briefly introduce its usage through its installation process on Red Hat 7.1.

 

Official PostgreSQL:

Ftp://ftp.postgresql.org/pub/v7.1.3/postgresql-7.1.3.tar.gz
Http://www.postgresql.org/

 

To download the latest development version, you need to download and install Flex (version later than 2.5.4) and BISON (version later than 1.28)

 

For the sake of security, the designer cannot run PostgreSQL as the root user, so the corresponding user and group must be created.

1.

# Useradd postgre (automatically create a postgre Group)

2.

The installation process is not complex. The installation method for other source code versions is similar:
Decompress the package to/usr/local/src:
# Tar xvfz postgresql-7.1.3.tar.gz
# Cd postgresql-7.1.3
#./Configure -- prefix =/usr/local/pgsql
# Make
# Make install
# Chown-r postgre. postgre/usr/local/pgsql

3.

After the installation is completed, it is not all that's done, and there are some finishing work to do:
# Vi ~ Postgre/. bash_profile
Add:

 

Pglib =/usr/local/pgsql/lib
Pgdata = $ home/Data
Path = $ path:/usr/local/pgsql/bin
Manpath = $ manpath:/usr/local/pgsql/man
Export pglib pgdata path manpath

 

Log On As an ipvs user,
# Su-postgre
Create a database directory:
$ Mkdir data

 

Start the database engine:

 

$ Initdb
[Postgre @ WWW postgre] $ initdb
This database system will be initialized with username "postgre ".
This user will own all the data files and must also own the server process.

 

Fixing permissions on pre-existing data directory/home/postgre/Data
Creating Database System directory/home/postgre/data/base
Creating Database xlog directory/home/postgre/data/pg_xlog
Creating template database in/home/postgre/data/base/template1
Creating global relations in/home/postgre/data/base
Adding template1 database to pg_database

 

Creating view pg_user.
Creating view pg_rules.
Creating view pg_views.
Creating view pg_tables.
Creating view pg_indexes.
Loading pg_description.
Vacuuming database.

 

Success. You can now start the database server using:

 

/Usr/local/pgsql/bin/postmaster-D/home/postgre/Data
Or
/Usr/local/pgsql/bin/pg_ctl-D/home/postgre/Data start
$ Postmaster-I-d ~ /Data &
[1] 22603
[Postgre @ WWW postgre] $ Debug: data base system is starting up at THU Jan 31 02:00:44 2002
Debug: data base system was shut down at THU Jan 31 01:57:58 2002
Debug: data base system is in production state at THU Jan 31 02:00:44 2002

 

In this way, PostgreSQL uses a database located in/usr/local/pgsql/data to allow Internet users to connect (-I) and run it in the background.

4.

Create a database
$ Createdb mydb
PostgreSQL returns the "Created database" information, indicating that the database has been created.
$ Psql mydb
Go to the interactive Psql tool and create a table:

 

Create Table mytable (
Id varchar (20 ),
Name varchar (30 ));

 

After the creation is complete, a "created" message is displayed, indicating that the creation is successful. Insert a data entry:

 

Insert into mytable values ('author', 'xu yongjiu ');

 

Psql returns insert 18732 1 to check whether the insert is successful:

 

Select * From mytable;

 

Exit Psql and run \ Q.

5.

Configure JDBC remote connection

# Vi postgre/data/pg_mirror.conf

Change
Host All all 192.168.1.1 255.255.255.0 MD5

# Vi postgre/data/PostgreSQL. conf
Port = 5432
Max_connections = 100
Shared_buffers = 300 (at least twice the value of max_connections)

Listen_addresses = '*'

6.

Start Service
/Usr/local/pgsql/bin/pg_ctl-D/data/pgsqldata-L/data/pgsqldata/logfile start

7.

Database creation
Createuser-H 127.0.0.1-a-d-P Test
Createdb-H 127.0.0.1-e Unicode-o Test
Createlang-H 127.0.0.1 plpgsql Test

8.

Configure Tomcat
VI server. conf
Add
<Context Path = "/test" docbase = "test" DEBUG = "4"
Defaultsessiontimeout = "200"
Reloadable = "true">

<Logger classname = "org. Apache. Catalina. Logger. filelogger"
Prefix = "localhost_test_log." suffix = ". txt"
Timestamp = "true"/>
<Resource Name = "JDBC/test" auth = "Container"
Type = "javax. SQL. datasource"/>

<Resourceparams name = "JDBC/test">
<Parameter>
<Name> factory </Name>
<Value> org. Apache. commons. DBCP. basicperformancefactory </value>
</Parameter>
<Parameter>
<Name> removeabandoned </Name>
<Value> true </value>
</Parameter>

<Parameter>
<Name> removeabandonedtimeout </Name>
<Value> 60 </value>
</Parameter>
<Parameter>
<Name> driverclassname </Name>
<Value> org. PostgreSQL. Driver </value>
</Parameter>
<Parameter>
<Name> URL </Name>
<Value> JDBC: PostgreSQL: // 192.168.1.5: 5432/test </value>
</Parameter>
<Parameter>
<Name> username </Name>
<Value> test </value>
</Parameter>
<Parameter>
<Name> password </Name>
<Value> test </value>
</Parameter>
<Parameter>
<Name> maxactive </Name>
<Value> 100 </value>
</Parameter>
<Parameter>
<Name> maxidle </Name>
<Value> 30 </value>
</Parameter>
<Parameter>
<Name> maxwait </Name>
<Value> 120 </value>
</Parameter>
</Resourceparams>
</Context>

VI web. xml
Add JDBC connection pool
<Resource-ref>
<Description> dB connection </description>
<Res-ref-Name> JDBC/test </RES-ref-Name>
<Res-type> javax. SQL. datasource </RES-type>
<Res-auth> container </RES-auth>
</Resource-ref>