PostgreSQL

来自开放百科 - 灰狐
2012年10月28日 (日) 00:2694.23.1.28 (讨论)的版本

跳转到： 导航, 搜索

Postgresql.jpg PostgreSQL: 世界上最高级的开源数据库

Serengeti Elephant

目录

新闻

Wikipedia-35x35.png 您可以在Wikipedia上了解到此条目的英文信息 PostgreSQL Thanks, Wikipedia.

自http://www.postgresql.org/news.rss加载RSS失败或RSS源被墙 自http://planet.postgresql.org/rss20.xml加载RSS失败或RSS源被墙

I am forever indebted to you for this ifnormation.

9.x

PostgreSQL 9.0 正式版发布

Feature Matrix

安装指南

./configure --with-perl // Pl/PerlU
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/postgres -D /usr/local/pgsql/data  & or
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
netstat -plunt | grep 5432

gmake 可能出现的问题 /usr/bin/ld: cannot find -lperl

apt-get install libperl-dev

可能遇见的问题：could not bind IPv4 socket: Cannot assign requested address

修改 /usr/local/pgsql/data/postgresql.conf

listen_addresses = 'localhost,192.168.1.101'

error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory 

 ldd /home/pgsql/bin/psql

linux-gate.so.1 => (0xf76ec000) libpq.so.5 => not found 

vi /etc/ld.so.conf，加入
/home/pgsql/lib

 然后执行：ldconfig OK:)

Mac

Mac OS X packages

安装 EnterpriseDB 很方便。

Windows

在Windows下使用非安装版, 如 postgresql-9.0.0-1-windows-binaries.zip

先解压到PostgreSQL包到 D:\pgsql

在命令行输入 runas /usr:postgres cmd // 切换到 postgres 账号 

创建 data 的文件夹 D:\pgsql\mkdir data
D:\pgsql\bin\initdb.exe -D D:\pgsql\data -E UTF8 --locale=C 
D:\pgsql\bin\pg_ctl.exe register -D d:\pgsql\data -N pg9.0 // 注册Windows服务
net start pg9.0 （启动数据库）
net stop pg9.0 （关闭数据库）

8.3

debian 5.0.3 readline not found 

apt-get install bison,flex,libreadline5-dev 问题解决
apt-get install zlib1g-dev 解决 zlib没安装的问题

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)version 8.0, this is controlled using the "listen_addresses"parameter in the postgresql.conf. 

listen_addresses = 'localhost'
listen_addresses = '*' // * = all
listen_addresses = '192.168.1.2','192.168.1.3'

vi pg_hba.conf 

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 192.168.1.100 255.255.255.0 password
host all all 127.0.0.1 255.255.255.255 trust
host all postgres 127.0.0.1 255.255.255.255 password
host all all 0.0.0.0/0 md5
host all all 192.168.1.0/16 trust 
or 
host all all 127.0.0.1/32 md5
host all all 192.168.0.0/24 md5
host all all 172.18.0.0/16 md5
or
host all all 172.18.0.0/16 trust

修改后重启: 

/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 or psql -f dellstore.sql dellstore2 postgres // windows

对其它数据库，如 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.

I could watch Schindler's List and still be happy after rdaeing this.

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

PostGIS

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

HA,Scaleable

Deep thoghut! Thanks for contributing.

C,C++

PHP

Postgresql-php.gif

Python

Java

driver: org.postgresql.Driver
url: jdbc:postgresql://host:port/database
database.url = jdbc:postgresql://localhost:5432/test?charSet=utf8
// database.url = jdbc:mysql://localhost:3306/test?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true

JavaScript

PostgreSQL for JavaScript: 一个实现了PostgreSQL前/后端协议的JavaScript库。

迁移

将用户从 Oracle, DB2, Microsoft SQL Server, MySQL 等数据库迁移到 PostgreSQL

Oracle

DB2

SQL Server

MySQL

相关项目

More Projects:

商业支持

成功应用

hi5 includes multiple terabytes of data and media files stored across an array of PostgreSQL databases and an EXT3 file system. The hi5 application was written in Java and runs on hundreds of Resin application servers and Apache web servers. The data center is filled with machines ranging from Dell Blades, SuperMicros, Dell 8-way Servers – most of which run SuSE Linux.

  • yahoo

雅虎数据库技术最初出自于2005年底被雅虎低调收购的Mahat技术公司。雅虎使用的数据库是深度改良的PostgreSQL，用为其商用硬件集群专门设计的代码来取代查询处理层。传统的数据排列方式是按照行进行的，而雅虎则选择用分布式列来存储数据。

在线文档

更多文档>>>

Mailing Lists

相关链接

OpenSolaris

PostgreSQL for Solaris Blogs on PostgreSQL

PostgreSQL on Solaris Wiki: http://wikis.sun.com/display/DBonSolaris/PostgreSQL

MySQL to PostgreSQL

Converting from other Databases to PostgreSQL

PostgreSQL and MySQL

PostgreSQL will eat MySQL for lunch :)

Ten times out of ten, PostgreSQL will eat MySQL for lunch (and twice on Sunday).I don't benefit from this at all, but, if you're a PostgreSQL user, DBA, programmer, hacker, etc., and like that logo

from http://people.freebsd.org/~seanc/

Buttons

Postgresql 80x15.png Postgresql-137x30.png

Powered by

书籍

  • Postgresql-90-high-performance.jpg

Comment-32x32.png

<discussion>characters_max=300</discussion>

来自“http://wiki.huihoo.com/wiki/?title=PostgreSQL&oldid=122433”
分享您的观点
个人工具
名字空间

变换
操作
导航
工具箱