PostgreSQL

2006年12月6日 (三) 11:01的版本

: The world's most advanced open source database

最新版：8.2 http://blogs.huihoo.com/?p=307

安装指南

./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的连接。

命令

• 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
$ psql mydb
mydb=> SELECT version();
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;

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发行版

• EnterpriseDB http://www.enterprisedb.com/
• Pervasive http://www.pervasive.com/

在线文档

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

相关链接

• http://www.postgresql.org
• pgAdmin http://www.pgadmin.org/
• phpPgAdmin http://phppgadmin.sourceforge.net
• PostgreSQL 中文网 http://www.pgsqldb.org/

精彩图集

PostgreSQL 体系结构和进程概况