PostgreSQL

: The world's most advanced open source database

安装指南

./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

精彩图集

PostgreSQL 体系结构和进程概况