[ Prev ] [ Index ] [ Next ]

Postgresql

Created 星期四 18 三月 2010
postgresql:libpq postgresql:命令 postgresql:类型及函数 postgresql:查询 postgresql:查询例子 数据定义

使用帮助

\h sql语名帮助
\? psql命令帮助

服务器

服务器为postgres,打开时要指定数据库目录，例如

/usr/lib/postgresql/8.3/bin/postgres -D /data/pdata


打开多个服务器例程时要指定端品，参数为

-p 端口值


初次使用


1 创建一个目录，使用该目录用postgresql:initdb创建数据库集群。例如数据库目录为 /data/pdata

/usr/lib/postgresql/8.3/bin/initdb -D /data/pdata

可以用chown更改文件夹所有者。

2 运行服务器postgres

/usr/lib/postgresql/8.3/bin/postgres -D /data/pdata&
为了开机启动，把命令“su -u xun /usr/lib/postgresql/8.3/bin/postgres -D /data/pdata&”添加到了/etc/init.d/postgresql-8.3 的start下。
确保xun 在用户组postgres中.

3 创建postgresql:用户
4 创建数据库

查询

1 数据为文本时，可以使用postgresql:模式匹配
2 在集合中使用in ,比如 1 in (1,2,3)为真。
3 查询包含指定文本

例如查询内容中包含"生产"字符串的记录，列出编号和标题
select 编号,标题 from 记录 where 内容~'生产';


使用libpq

编绎 -I/usr/include/postgresql -lpq
加头文件libpq-fe.h
见postgresql:libpq

使用外键

编号 bigint references 记.数学(编号);

备份和恢复

pg_dump dbname >outfie
psql dbname <infile

用copy备份表格

转储时注意拥有者

作为服务器

运行服务器时加 -i 选项

$postgres -i -D /data/pdata &
修改postgresql.conf
listen_addresses = 'localhost,192.168.1.101'

listen_addresses='localhost,*'
监听 全部可用的
修改pg_hba.conf
加
host database user 192.168.1.100/32 md5
这是一台主机

192.168.1.0/24 指的是一个网络 

# Database administrative login by UNIX sockets
local   all         postgres                          ident

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
#有密码就可以
local	xun	    xun					md5	
#这样只有与系统用户名相同的用户才可认证通过
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host	xun	xun	192.168.1.0/24	md5


文件在数据目录所在地

通过修改配置文件指定数据库库集群群

修改/etc/postgresql/8.4/main/postgresql.conf

data_directory = '/data/postdata-8.4' # use data in another directory

改变该用户的目录的所有者为postgres

在/data/postdata-8.4中添加链接: 

lrwxrwxrwx 1 root     root       36 10月 27 11:48 server.crt -> /etc/ssl/certs/ssl-cert-snakeoil.pem
lrwxrwxrwx 1 root     root       38 10月 27 11:48 server.key -> /etc/ssl/private/ssl-cert-snakeoil.key

这样可以通过/etc/init.d/postgresql start 来启动


表空间

使用表空间可以将数据存放到多个磁盘分区
要求超级用户建表空间，且目录的所有者为该用户

建表空间
create tablespace tb_smartx location '/data/postdata/smartx';
NTFS格式的盘，比如/winE/postdata/pdata,建表空间时会报没有权限的错误，不知是不是与NTFS格式的盘有关。

然后级别的用户赋权限

grant create on tablespace tb_smartx to xun;

然后建表的时候使用表空间 

CREATE TABLE file."资源库"
(
  "编号" integer NOT NULL DEFAULT nextval('file."资源库_编号_seq"'::regclass),
  "名称" character varying(40) NOT NULL,
  "目录" character varying(255) NOT NULL,
  "类型" character varying(20) NOT NULL,
  "说明" character varying(512),
  CONSTRAINT "资源库_pkey" PRIMARY KEY ("编号")
)
WITH (
  OIDS=FALSE
)
TABLESPACE tb_smartx;
ALTER TABLE file."资源库" OWNER TO xun;


postgresql-8.4错误输出的编码

采用UTF8服务端无法输出中文的错误消息，解决的方案之一是让其输英文
修改postgresql.conf 

# These settings are initialized by initdb, but they can be changed.
#lc_messages = 'zh_CN.UTF8'			# locale for system error message
					# strings
#lc_monetary = 'zh_CN.UTF8'			# locale for monetary formatting
#lc_numeric = 'zh_CN.UTF8'			# locale for number formatting
#lc_time = 'zh_CN.UTF8'				# locale for time formatting

采用SSL

需要服务器的公钥和认证(server.key,server.crt)，放置数据库据在地。

使用openSSL制作，制作步骤：
1，生成认证申请书和公钥
OpenSSL> req -new -text -out server.req
输入密码,及一些个人信息

2, 利用私钥产生公钥
OpenSSL>rsa -in privkey.pem -out server.key

3，删除私钥 ,不然启动时需要密码
rm privkey.pem

4, 利用公钥和认证申请生成 签名认证
req -x509 -in server.req -text -key server.key -out server.crt

重新装postgresql无法登录

可以用psql登录，libpqxx也可以编写的pedit也可以。但是第三方软件不可以，比如pgAdmin。

解决方法是修改密码
xun=>alter user xun with password 'password';

使用python

可apt 安装psycopg2

9.6解决外部连接不上

只要改配置文件，启动方式不动
不能写
listen_addresses='localhost,*'

写成
listen_addresses='*'

mac启动postgresql

pg_ctl -D /usr/local/var/postgres start








Backlinks: sql