PostgreSQL

./configure
gmake // ln -s /usr/bin/make /usr/bin/gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres // sudo 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

stop postgresql

su postgres or sudo su postgres 
/usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data/

注意: postgres在默认是不接受tcp/ip连接的。有两种方式来控制它接受tcp/ip连接，一种是用启动参数 -i ，另一种方式是修改数据库目录里的文件：postgresql.conf中的参数，将tcpip_socket = false改为true port = 5432 前面的注释符号去掉。表示接受tcp/ip在5432的连接。(The tcpip_socket variable from postgres 7.4 is not recognized with postgres 8.1)

vi pg_hba.conf

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1 255.255.255.255 trust
host all postgres 127.0.0.1 255.255.255.255 password

修改后重启:

/etc/init.d/postgresql-7.4 restart

创建一测试用户: test

# edit file /etc/postgresql/pg_hba.conf, and lines at file top:
local   test       test                                          md5
host    test       test     127.0.0.1         255.255.255.255    md5
# su - postgres
$ createuser -ADPE test
$ createdb -O test test
$ createlang plpgsql test
$ exit
# psql -h 127.0.0.1 -U test test

Examples

http://pgfoundry.org/projects/dbsamples/ - A collection of sample databases for PostgreSQL.

请下载 dellstore2 ,大家可用这些数据做实验 :)

如 Dell Store2 database http://linux.dell.com/dvdstore/ , dbsamples 对 dvdstore database 做了 PosgreSQl port

Dell Store2 Physical Data Diagram

createdb -E LATIN1 dellstore2 -U postgres
createlang plpgsql dellstore2 -U postgres
psql -f dellstore.sql dellstore2 -U postgres

对其它数据库，如 mysql 请下载 ds2.tar.gz 和 ds2_mysql.tar.gz, 并解压到同一目录下, 并事先创建 DS2 数据库和 web/web 的用户信息

命令

PostgreSQL 体系结构和进程概况

createdb—Creates a new database in postgreSQL. A database name is required when using this command.
createuser—Creates a postgreSQL user. A user name is required when using this command.
dropdb—Removes a database from postgreSQL. A database name is required when using this command.
dropuser—Removes a postgreSQL user. A user name is required when using this command.
initdb—Creates a new postgreSQL database installation. This command is used only once when the database is installed. It creates the base directory. This command cannot be used again.
psql—SQL interface to the postgreSQL databases. A database name is required when using this command.
vacuumdb—Cleans and analyzes a postgreSQL database.

常用用法

$ createdb mydb
$ dropdb mydb
$ cd src/tutorial
$ export PATH=$PATH:/usr/local/pgsql/bin
$ psql -s mydb
alter user postgres with password 'postgres';
mydb=> \dt 命令是显示表
mydb=> \di 显示索引
mydb=> \ds 显示序列
mydb=> \dv 显示视图
mydb=> \dp 显示权限
mydb=> \dS 显示系统表
mydb=> \dl 显示 lobjects
mydb=> \d tablename 显示表的结构/索引/序列
mydb=> \d indexname 显示索引的详细信息
mydb=> \i basics.sql or basics.source 导入表
mydb=> SELECT version();
mydb=> show all; // 显示运行时参数的数值
pg_dump -x databasename > outfile
psql -e database < outfile

CREATE TABLE weather (
   city            varchar(80),
   temp_lo         int,           -- 最低气温
   temp_hi         int,           -- 最高气温
   prcp            real,          -- 降水量
   date            date
);
DROP TABLE tablename;

postgres@debian:~$ psql -l
       List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
 template0 | postgres | EUC_CN
 template1 | postgres | EUC_CN
 test      | postgres | EUC_CN
(3 rows)

postgres@debian:~$ psql template1
template1=# select * from pg_tables;
template1=# create user huihoo password 'huihoo' createdb;

mydb=# select md5('abc');
mydb=# select btrim('xyxtrimyyx','xy');
mydb=# select ltrim('zzzytrim','xyz');

http://docs.huihoo.com/postgresql/postgresql-doc-7.4-zh_CN/functions-string.html

PostgreSQL Limits

Limit Value

Maximum Database Size Unlimited
Maximum Table Size 32 TB
Maximum Row Size 1.6 TB
Maximum Field Size 1 GB
Maximum Rows per Table Unlimited
Maximum Columns per Table 250 - 1600 depending on column types
Maximum Indexes per Table Unlimited

PostgreSQL derived databases

Amalgamated Insight ... proprietary ... Fork of TelegraphCQ
Bizgres ... BSD ... PostgreSQL + BI features
Bizgres MPP ... proprietary ... PostgreSQL + BI features
EnterpriseDB ... proprietary ... PostgreSQL + Oracle compatibility
ExtenDB ... proprietary ... PostgreSQL + BI Features
Great Bridge PostgreSQL ... BSD ... PostgreSQL re-distribution
Mammoth ... BSD ... PostgreSQL + contrib modules
Netezza ... proprietary ... Appliance based on PostgreSQL SQL engine
NuSphere UltraSQL ... proprietary ... Native Win32 port of PostgreSQL
parACCEL ... proprietary ... PostgreSQL + BI features
Pervasive PostgreSQL ... BSD ... PostgreSQL re-distribution
PowerGres ... proprietary ... Native Win32 port of PostgreSQL
PowerGres Plus ... proprietary ... PostgreSQL + custom storage engine
PostgreSQL for Solaris 10 ... BSD ... PostgreSQL re-distribution
Red Hat Database ... BSD ... PostgreSQL re-distribution
TelegraphCQ ... BSD ... Data Stream oriented fork of PostgresSQL

from http://www.postgresql.org/docs/techdocs.62

Graphical Clients

SQuirreL SQL Client
pgAdmin III - Cross-platform administration tool
PhpPgAdmin - Web-based PostgreSQL administration tool
pgDesigner - Datamodel designer for PostgreSQL

Replication

PostGIS

PostGIS在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力，相当于Oracle的spatial部分。PostGIS最大的特点是符合并且实现了OpenGIS的一些规范，是最著名的开源GIS数据库。

Cluster/HPC

Storage

Sun Data Warehouse Appliance

Powered by best-in-class open source software including the Solaris Operating System (OS), PostgreSQL, and ZFS.

http://www.greenplum.com/solutions/sun_Appliance.php

MonoSphere

Java

http://jdbc.postgresql.org/

wget http://jdbc.postgresql.org/download/pg74.216.jdbc3.jar // JDK 1.4, 1.5
wget http://jdbc.postgresql.org/download/postgresql-8.1-409.jdbc3.jar // JDK 1.4 1.5

商业支持

Fujitsu Supported PostgreSQL - http://postgresql.fastware.com
PostgreSQL for Solaris 10 - http://www.sun.com/software/solaris/postgresql.jsp

成功应用

http://reddit.com - http://programming.reddit.com/info/tgj7/comments/ctgt8

在线文档

PostgreSQL 8.1 中文文档 - http://docs.huihoo.com/postgresql/pgsqldoc-8.1c/
PostgreSQL 8.0.0 中文文档 - http://docs.huihoo.com/postgresql/postgresql-doc-8.0-zh_CN/
PostgreSQL 7.4 中文文档 - http://docs.huihoo.com/postgresql/postgresql-doc-7.4-zh_CN/
PostgreSQL 7.3.3 中文文档 - http://docs.huihoo.com/postgresql/pgsql-doc-7.3/
PostgreSQL 7.1.1 中文文档 - http://docs.huihoo.com/postgresql/pgsqldoc-7.1C/postgres.html

http://docs.huihoo.com/postgresql/

Buttons

精彩图集