Use PostgreSQL database to do PHP development

Description: The following content is more about how to install the environment, the actual development of the content is almost no, you can consider whether to read this article.

PostgreSQL database should be regarded as the Venus of the database industry, some of them even more than Oracle and other business databases, in the open source world, can be said to be very good, even MySQL is very far from it. It supports ways to link databases including JDBC, ODBC, DBI, and so on, and can basically interact with all the popular programming languages at the moment, and is a good fit for any programming developer.

The official introductions may be more descriptive: http://www.pgsqldb.org/pgsqldoc-8.0c/preface.html
----------------------------------
What is PostgreSQL?
PostgreSQL is an object-relational database management system (ORDBMS) based on version 4.2, developed by the University of California, Berkeley Computer Department (POSTGRES). Many of the concepts leading to POSTGRES only appear in the commercial database very late.

PostgreSQL is an open source heir to the original Berkeley code. It supports most sql:2003 standards and offers many other modern features:

Complex queries
FOREIGN key
Trigger
View
Transactional integrity
Multi-version concurrency control
Similarly, PostgreSQL can be extended in many ways, such as by adding new:

Data type
Function
Operator
Aggregate functions
Indexing method
Process language

And, because of the flexibility of the license, anyone can use, modify, and distribute PostgreSQL for any purpose, whether for private, commercial, or academic research.
-------------------------------------

Today I want to try PHP to use PostgreSQL to do development feeling, so hurriedly install a bar.

The first consideration is to install the latest version 8.0.3 in Windows, but think about it, the actual development platform is more in the unix/linux, so ready to do development under FreeBSD, just yesterday to upgrade the FreeBSD5.4, hurriedly ports a back.

Cd/usr/ports/database/postgresql80-server
Cat Makefile
A look, the original latest version is 8.0.3, oh, just ah, hurriedly installed.
Make install
Long wait for the process, it is going to download what gmake, but also to download postgresql-8.0.3.tar.bz2 and other documents, and then loaded. Oh, very excited to start a look at:
/usr/local/etc/rc.d/postgresql.sh start (this file may not be the same as your filename)
Dizzy, what's wrong? Anyway is not start, it seems to have to manually install it, first uninstall off it:
Cd/usr/ports/database/postgresql80-server
Make Deinstall
Oh, cuff the papers:
Cd/usr/ports/distfiles
mv./postgresql-8.0.3.tar.bz2/usr/local/src
Decompression:
Cd/usr/local/src
bunzip2-d./postgresql-8.0.3.tar.gz2
Tar xvf./postgresql-8.0.3.tar
Decompression succeeded:
CD./postgresql-8.0.3
configuration file, installed into our old directory:
./configure--prefix=/usr/local/pgsql
Gmake
Wait for the process, to later, Halo, how wrong?
Prompt file system is full ... -_-#
I am a virtual machine, the allocation of space is unreasonable, see if you can add more space to/usr.
Go to Google crazy search data, only to find a tunefs command to solve this problem, read a half-day also did not understand, Link:
Http://www.freebsd.org.cn/snap/doc/zh_CN.GB2312/books/handbook/configtuning-disk.html
Think about it or forget it, loaded to other partitions will not be over, look at the space situation:
%df
FileSystem 1k-blocks Used Avail Capacity mounted on
/DEV/AD0S1A 507630 55060 411960 12%/
DEVFS 1 1 0 100%/dev
/dev/ad0s1e 507630 70072 396948 15%/tmp
/dev/ad0s1f 2025982 1960440-96536 105%/usr
/dev/ad0s1d 507630 76340 390680 16%/var
Then put it under the/var, hehe:
./configure--prefix=/var/pgsql
Gmake
Gmake Install
The process is smooth, OK, add a user running PG:

AddUser
Username:postgres
Full name:postgresql Server User
Uid (Leave empty for default):
Login Group [p Ostgres]:
Login Group is postgres. Invite Postgres into the other groups? []:
Login class [default]:
Shell (sh csh tcsh bash nologin) [sh]:/bin/csh
Home Directory [/home/postgres]:
use password-based authentication? [Yes]:
Use a empty password? (yes/no) [No]: Yes
Lock out, after creation? [No]:
username  : Postgres
password  : <blank>
Full name : PostgreSQL Server User
uid       : 1004
class     :
groups    : Postgres
home      :/home/postgres
Shell      :/bin/csh
locked    : no

Establish a directory to hold the database:
Mkdir/var/pgsql/data
Take Ownership to Postgres User:
chown postgres/var/pgsql/data
Switch to user postgres:
Su-postgres
Initializes the database:
/var/pgsql/bin/initdb-d/var/pgsql/data
starts running:
/var/pgsql/bin/postmaster-d/ Var/pgsql/data >logfile 2>&1 and
Halo, how a bunch of mistakes!!!
Postmaster does not know where to find the server configuration file.
You must specify the--config-file or-d invocation option or set the PGDATA environment. The
does not specify a profile, so quickly look at the configuration file.
Find/-name "postgresql.conf"
/var/pgsql/data/postgresql.conf
Oh, the original initialization of the database when the configuration file, hurriedly write:
. Postmaster--config-file=/var/pgsql/data/postgresql.conf
Halo, how come new mistakes?
Oh, originally did not configure the content of the postgresql.conf, hurriedly put the important configuration, the contents are configured as follows:

#---------------------------------
# FILE Locations
#---------------------------------
 
# The default values of these are variables are driven from the-d command line
# switch or pgdata environment, variable esented here as Configdir.
# data_directory = ' configdir '           # Use data in another Directory
Data_directory = '/var/pgsql/data '
# hba_file = ' configdir/pg_hba.conf '     # The host-based Authentication file
Hba_file = '/var/pgsql/data/pg_hba.conf '
# ident_file = ' configdir/pg_ident.conf '   # The IDENT configuration file
Ident_file = '/var/pgsql/data/pg_ident.conf '

See, that is, you specified a database file directory, the path to two configuration files.
Reboot again:
./postmaster--config-file=/var/pgsql/data/postgresql.conf
Log:database system is shut down at 2005-07-15 01:20:24 CST
Log:checkpoint's at 0/A38D20
Log:redo's at 0/A38D20; Undo record are at 0/0; Shutdown TRUE
Log:next transaction id:678; Next oid:17231
Log:database System is ready

OK, success. OK, let's see if I can get access from where I want to access the default port 5432:
Telnet 192.168.0.215 5432
What's going on, can't connect? Google quickly, oh, the original is the default is to allow local links, to modify the configuration file:
Vi/var/pgsql/data/postgresql.conf

Modify the following:
#-----------------------------------
# Connections and authentication
#-----------------------------------

#-Connection Settings-

#listen_addresses = ' localhost ' # what IP interface (s) to listen on;
# defaults to localhost, ' * ' = any
listen_addresses = ' * '
#port = 5432
See no, is the listening address of the default localhost to *, is to allow all. After the start of Telnet, oh, dark screen appears, no problem.
Haha, now finally can use PHP to connect it.

Open php pgsql extension, Output phpinfo () to see if you can support Pgsql, a look, can, hurriedly write code to see:

<?php
$link _str = "host=192.168.0.215 port=5432 dbname=test user=postgres password=postgres";
$link _id = pg_connect ($link _str);
Print_r ($link _id);
?>

Save As pgsql.php, open the browser: http://localhost/pgsq.php, haha, waiting for the link ID bar!
Oh, what's going on? Error occurred:

Warning:pg_connect () [Function.pg-connect]: Unable to connect to PostgreSQL Server:FATAL:no pg_hba.conf the for host "192.168.0.216", User "Postgres", Database "test" in E:\Web\www\Temp\pgsql\pg.php on line 3

Look carefully, pg_hba.conf entry for host "192.168.0.216", Oh, the original is this configuration file does not allow me to access this IP address, is really safe!
It seems that this pg_hba.conf must also be set in order to quickly open:
Vi/var/pgsql/data/pg_hba.conf
Added a section of IP address, as follows:
# TYPE DATABASE USER cidr-address method

# "Local" to Unix domain socket connections only
Local All
# IPV4 Local connections:
Host All 127.0.0.1/32 Trust
Host All 192.168.0.216 Trust
# IPV6 Local connections:
Host All:: 1/128
See no, I added:
Host All 192.168.0.216 Trust
If you want to allow a segment of the IP, you can add into 192.168.0.1/24 or something.

Then start PostgreSQL, I browser again connect, browser output:
Resource ID #1
^_^, it's a success!

Hurriedly next Windows management tools come, go: http://www.pgadmin.org/download a Pgadmin III back, after installation, start, add server, success. Good to use, with MYSQLCC, but also support Chinese.

Basically, there's nothing here. Oh, in addition, said a pgsql characteristics, that is, if the same user on multiple IP landing on the server, then only allow a user to perform, operations, other users can not perform, or there are errors, such as my XP on my use Pgadmin landing, I went to the server side to perform the operation:
/var/pgsql/bin/createdb DB1
Createdb:database creation Failed:ERROR:source Database "Template1" is being accessed by other users
See, there's a mistake.

From the above, Pgsql is really safe, a lot of security problems are nipped in the cradle, but also very rich configuration, although I install a little more complicated. In fact, look at the manual to understand very much, only blame me not to read the manual.

Halo, said for a long time, did not say how to do the development of PHP, OH, the environment is more important, as for the development and pgsql of its own characteristics, and slowly to study.

Add something good:

The official recommended installation process, but the actual is not good:
---------------------------------------------
./configure
Gmake
Su
Gmake Install
AddUser Postgres
Mkdir/usr/local/pgsql/data
Chown Postgres/usr/local/pgsql/data
Su-postgres
/usr/local/pgsql/bin/initdb-d/usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster-d/usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb Test
/usr/local/pgsql/bin/psql Test
---------------------------------------------

PostgreSQL Chinese Manual: Http://www.pgsqldb.org/twiki/bin/view/PgSQL/PgDocList
PostgreSQL Chinese Forum: http://bbs.pgsqldb.com
PostgreSQL Official website: http://www.postgresql.org

Author:heiyeluren
Writetime:2005-7-15 16:30