PostgreSQL Quick Startup
PostgreSQL
本文对PostgreSQL的配置和使用做了简单介绍，可以作为刚刚接触PostgreSQL的Quick Startup教程使用，让你马上将PostgreSQL跑起来，并且对配置中涉及到的一些概念做了说明
Quick Startup
-
在CentOS 6.4上快速安装配置PostgreSQL 8.4，如果你属于想要快速安装使用PostgreSQL的用户，那么你可以照着下面的命令敲到命令行中即可，但如果你是那种想要把一切东西搞透彻的用户，建议你先看完整篇文章后，根据自己的意愿进行安装配置
$ su - # yum install postgresql-server # service postgresql initdb # chkconfig postgresql on # sudo -u postgres psql postgres=# CREATE USER yourusername CREATEDB CREATEROLE; postgres=# CREATE DATABASE yourusername OWNER=yourusername;注：上面最后两行表示不再与shell交互，而是同另外一个程序交互，其中的yourusername一定要替换成为你自己在linux系统上的用户名，并且注意这两行命令都要以分号结尾
-
安装PostgreSQL，因不同系统安装方法不同，这里仅以我的系统CentOS 6.4为例（下文中出现的系统工具也是依赖于该系统的），对于其他系统这里不予以介绍，具体参见官网，CentOS系统可以使用
yum install postgresql-server安装PostgreSQL 8.4 -
PostgreSQl软件以Client/Server架构编写的，所以安装完成后会提供若干客户端程序（例如：psql, createdb, dropdb, createuser, dropuser等，想要查找所有的客户程序可以：
rpm -ql postgresql | less），以及若干服务端程序（例如：initdb, pg_ctl等，想要查询可以通过：rpm -ql postgresql-server | less），值得注意的是postgresql服务端程序是以系统服务形式安装的，也就是说也许你需要通过service和chkconfig命令对该服务进行管理安装完成后，运行以下命令以确保postgresql服务开启并初始化了数据库
service postgresql initdb确保开机时运行
chkconfig postgresql on -
安装完成后也许你觉得可以使用数据库了，你可以试着运行postgresql提供的客户端交互程序
psql，如果你的到的回复是：psql: FATAL: Ident authentication failed for user "yourusername" 或 psql: FATAL: database "yourusername" does not exist出现第一个错误的原因，Ident（一种数据库连接的认证机制，后文会有介绍）认证失败，因为Ident认证要求系统用户名有一个对应的数据库用户名（关于二者如何对应后文会有介绍），也就说如果你的系统用户名（运行psql程序的用户）映射到一个数据库账户的话授权就会通过，否则报错，显然，你刚刚安装成功什么都没干，怎么会有个对应的数据库账户给你用呢？
出现第二个错误的原因，连接认证通过了，但是想要使用的数据库"yourusername"是不存在的，你也许会奇怪，明明没有指定要使用的数据库，为什么默认会尝试使用以你用户名命名的数据库呢，这个数据库你根本没有创建过，怎么能使用呢？你可以通过
psql --help查看该命令帮助，选项-d用于指定要使用的数据库，如果你没指定默认就会使用以你用户名命名的数据库，显然，你还没有创建任何数据库，那你该指定哪个数据库呢？综上，我们得出的结论是，要想连接到数据库，就需要指定用哪个数据库账户连接，默认情况数据库应该有个超级账户的，也许我们可以用超级账户连接，那连接需要指定一个数据库，这个数据库必须是默认存在的，所以我们需要了解数据库服务中的默认账户和数据库是什么？
-
当安装完成后，系统上多了一个postgres用户（
cat /etc/passwd | grep postgres进行查看），同时postgresql数据库中有一个超级用户也叫作postgres，并且还有一个叫作postgres的数据库（该数据库用于存放账户信息，数据库信息，表信息等内容），正如你说看到的，两个账户和一个数据库都是同名的叫作postgres，这不是凑巧，而是由postgresql的连接认证机制决定的，系统安装后默认认证机制是ident的，该机制使用当前系统用户名作为数据库用户进行连接验证（除非存在ident映射，后文会有介绍），比如，你在系统上的用户名为james，当你使用psql连接数据库时，数据库端会查看有无叫作james的数据库账户，如果没有则认证失败（就是上面提到的第一个错误），如果有则继续进行查找有无叫作james的数据库（psql默认连接到同用户名的数据库），找不到数据库则报错（就是上面提到的第二个错误），所以说在默认的ident认证机制下，如果你要连接到数据库服务，你必须保证数据库中含有跟当前系统用户同名的账户和数据库，这就是你不能使用psql连接到数据库的原因，看来刚刚安装好的数据库需要postgres用户进行连接的，别的用户都会失败的（因为别的用户没有对应的同名数据库账户和数据库） -
先来看看postgres用户的属性
/etc/passwd中的信息 postgres❌26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash /etc/shadow中的信息 postgres:!!:16215::::::从上面可以看出，该用户是用于运行PostgreSQL Server的一个系统用户，虽然被分配了shell（/bin/bash），但是该用户却是被锁住的（也就是说该用户是无法像你使用的账户一样通过密码认证的方式进行登录，锁住用户的详细介绍），好了，你只要知道该用户无法使用密码进行登录即可，你可以
su - postgres试一试，提示你输入密码，可是密码是什么你根本不知道，总之，这样你是无法登录的 -
那么如何使用postgres进行连接呢？通过
psql --help查看，-U选项可以指定数据库账户名，是不是psql -U postgres就可以连接到数据库的？，别高兴的太早，即便你指定了数据库的账户名，但是当前用户名跟该数据库账户名没有任何对应关系（就是进行ident映射设置，默认只会对相同的系统名和数据库账户名建立映射）认证还是会失败的，那该怎么办呢？下面提出几种思路：-
因为默认系统用户名postgres跟数据库用户名postgres映射成功，并且已有数据库postgres可用，所以如果我们能够以用户postgres的身份运行
psql，问题就迎刃而解了 -
建立当前用户到数据库postgres用户的映射关系
-
不使用ident的连接认证模式，而是使用trust，因为后者允许所有用户连接
-
-
上面提及思路的具体实现方案（推荐方案为首选，如果喜欢折腾可以试试别的）
-
以postgres用户的身份运行
psql-
sudo -u postgres psql（推荐方案） -
再介绍一个曲折的方法，我打算先切换到postgres用户再运行psql命令，但是这里有个问题，前面曾提到postgres用户是无法使用密码登录的，那如何切换到该用户呢？因为root用户切换到任何用户都是无需密码的，所以你可以先切换到root用户，再切换到postgres，然后再运行psql命令，也可连接成功（千万不要通过更改postgres用户的密码来进入该账户，这样做使得不具登录权限的postgres用户获得了登录权限，存在较大的安全隐患）
-
-
建立映射关系需要涉及到数据库运行配置文件，这里将我的文夹列出
# 默认的/etc/lib/pgsql/data/pg_hba.conf文件内容 # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident # 默认的/etc/lib/pgsql/data/pg_ident.conf文仅内容 # MAPNAME SYSTEM-USERNAME PG-USERNAME # 修改后的/etc/lib/pgsql/data/pg_hba.conf文件内容 # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident map=mymapname # IPv4 local connections: host all all 127.0.0.1/32 ident map=mymapname # IPv6 local connections: host all all ::1/128 ident map=mymapname # 修改后的/etc/lib/pgsql/data/pg_ident.conf文仅内容 # MAPNAME SYSTEM-USERNAME PG-USERNAME mymapname james postgres可以看出对pg_ident.conf添加了一行内容，其中MAPNAME的列名字任取，SYSTEM-USERNAME是你想要进行映射的系统用户，PG-USERNAME这里只能为postgres（因为刚刚安装的数据库仅有这个用户），文件pg_hba.conf的METHOD列多了一点内容，每行尾部都添加了map=mymapname（即pg_ident.conf中定义的MAPNAME），修改配置文件并保存后，运行如下命令：
$ service postgresql restart # 重启服务使得配置生效 $ psql -U postgres # 务必指定数据库用户名postgres -
同样该方案需要修改配置文件/etc/lib/pgsql/data/pg_hba.conf，将其中的METHOD列均改为trust，修改配置文件并保存后，运行如下命令：
$ service postgresql restart # 重启服务使得配置生效 $ psql -U postgres -d postgres # 务必指定数据库用户名postgres和要使用的数据库名因为当前用户在数据库上没有对应账户并且跟用户同名的数据库存在，所以一定要在psql选项中明确指出，如果没有指定会有报错如下：
psql: FATAL: role "yourusername" does not exist # 没有指定postgres用户 psql: FATAL: database "yourusername" does not exist # 没有指定postgres数据库
-
-
连接到数据库后要干些什么？官方表示不推荐直接使用postgres用户，因为该用户是superuser，权限太大，万一失误则不可恢复，这就如同在linux系统上不推荐使用root用户一样，建议新建一个具有CREATEDB和CREATEROLE权限的用户，也就是即可操纵数据库又可以管理其他用户的账户，首先通过上面的方法进入psql命令交互，后输入如下SQL语句（注意用分号结尾）：
CREATE USER yourusername CREATEDB CREATEROLE; CREATE DATABASE yourusername OWNER=yourusername;通过上面两条命令创建了跟你系统用户同名的数据库账户以及同名的数据库，此时你再直接运行
psql，则会连接成功了 -
进入psql交互后的常用命令
\? 查询psql命令 \l 显示所有数据库 \dS 显示所有表 \d 显示所有列 \c user 切换到用户user
管理postgresql服务
查看服务状态
service postgresql status
启动服务
service postgresql start
重启服务
当你修改postgresql的配置文件后，为使得配置生效，也许你需要重启服务（当然，你也可使用附带安装的pg_ctl程序来使配置生效）
service postgresql restart
配置服务为开机启动
chkconfig postgresql on
PostgreSQL默认的安装目录
默认安装目录为：/var/lib/pgsql/，该目录中含有数据库的一切，包括配置文件，日志文件，数据文件等，如果想调整默认的数据库所在位置，可以通过编辑postgresql服务的启动配置文件（/etc/sysconfig/pgsql/postgresql）对其进行配置（当然配置需要服务重启后有效），配置示例：
PGDATA=/var/lib/pgsql/data
PGPORT=5432
PGLOG=/var/lib/pgsql/pgstartup.log
PGOPTS=
如果你想配置的是数据库运行时的参数，你要编辑的不是这个文件，而/var/lib/pgsql/data/postgresql.conf配置文件，后面会再做介绍
数据库运行参数的配置文件
/var/lib/pgsql/data/postgresql.conf，该文件中定义了许多的默认配置项，比如：监听地址，最大连接数目，日志记录等
数据库连接控制文件
postgresql对客户端连接有专门的连接控制文件，这使得数据库十分的安全，连接控制文件是/var/lib/pgsql/data/pg_hba.conf，在该文件中你可以具体的指定，允许来自什么域，什么IP的什么主机，使用什么账户连接到哪个数据库，默认该文件仅允许本地所有主机的所有用户通过ident授权（后面会有讲解）连接到所有的数据库，可以看到postgresql对数据库连接的控制精确到了用户级和具体某个数据库级别，所以我们说它是很安全的，连接控制文件的内容分为如下几列（建议阅读文件中的注释搞懂列名的意思）：
TYPE DATABASE USER CIDR-ADDRESS METHOD
**注：**如果控制多个ip连接，你需要同时修改/var/lib/pgsql/data/postgresql.conf中的listen_address项
数据库的授权模式
postgresql有多种授权模式，授权模式是基于具体连接进行指定的，就是说授权模式是在连接控制文件/var/lib/pgsql/data/pg_hba.conf中指定的，我只介绍，我用到的几种：trust, password, md5, ident
-
trust模式，顾名思义该模式下，不对连接用户应用任何授权验证，一律允许连接到数据库
-
password是普通的密码认证
-
md5模式，该模式下用户输入密码进行md5验证
-
ident模式，该模式下，将系统用户映射到数据库用户进行验证（后面会有详细的介绍）
**注：**如果你要某种语言连接到数据库，常常需要提供连接参数并且这种一般是需要密码的，也就是说只能采用password或md5，这种支持密码的连接方式，也许你需要设立一个特殊的账户（使用密码方式授权的用户）用于这类连接
ident授权模式
上面提到该模式是使用系统用户映射到数据库用户进行验证的，具体来说，当你运行psql之类客户端工具请求连接数据库服务时，假设你当前在系统中的用户名是james，那你的请求到达postgresql时，它会分析该用户在数据库中有无映射账户，如果有的话，连接成功，这样将系统用户直接映射到一个数据库用户使得很多情况下方便多了，那么映射是如何建立的呢？是通过文件/var/lib/pgsql/data/pg_ident.conf，该文件的内容有三列：
MAPNAME SYSTEM-USERNAME PG-USERNAME
默认该文夹中空的，此时系统用户和数据库用户进行同名映射，比如用户james连接数据库时，会查找数据库上是否有james账户，当然你也可将系统用户映射到不同名的数据库用户，MAPNAME随意命名，在连接控制文件中使用它，SYSTEM-USERNAME是系统用户名，PG-USERNAME是数据库中的用户名
使用映射的示例
# 文件/etc/lib/pgsql/data/pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all ident map=mymapname
# IPv4 local connections:
host all all 127.0.0.1/32 ident map=mymapname
# IPv6 local connections:
host all all ::1/128 ident map=mymapname
# 文件/etc/lib/pgsql/data/pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
mymapname james postgres
管理数据库role
这里之所以叫作role，而不是数据库账户的原因在与，因为role是对一个数据库账户或一组数据库账户的统称，你可以将role看成是进入数据库的账户
创建数据库role的SQL语句： CREATE ROLE name，或者直接使用附带安装的程序createuser来创建数据库用户
删除数据库role的SQL语句： DROP ROLE name;，或者直接使用附带安装的程序dropuser来创建数据库用户
列出所有用户的SQL语句： SELECT rolname FROM pg_roles，数据表pg_roles是由数据库自己维护的
数据库role的属性
创建具有登录权限的账户： CREATE ROLE name LOGIN or CREATE USER name;
创建super账户： CREATE ROLE name SUPERUSER，只有super账户可以创建super账户
创建可以新建数据库的账户： CREATE ROLE name CREATEDB
创建可以管理别的账户的账户： CREATE ROLE name CREATEROLE
创建账户时指定密码： CREATE ROLE name PASSWORD 'string'，注意只有password和md5授权模式需要密码
修改账户： ALTER ROLE name ...
推荐创建一个具有CREATEDB和CREATEROLE的账户进行数据库的日常管理，而不是使用一个super账户
对数据库内容权限
数据库权限，即指对数据库内容的读写权限，常见的权限有：SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE
默认情况下除了创建数据内容的账户（和super账户）外没有任何账户可以读写该数据，但是他可以将权限授予别人
将特定的权限授予别的账户： GRANT SELECT ON table_name TO james，该例子将对数据表table_name的SELECT权限授予了数据库账户james，用ALL取代SELECT表示将所有权限授予，用PUBLIC取代james表示对所有数据库账户授权
撤销权限： REVOKE ALL ON table_name FROM PUBLIC，该例子表示，撤销所有用户的所有权限
日常使用总结
删除user表
若直接使用常规删除表的方法 DROP TABLE user; 将会提示语法错误（或者可能是名称空间的问题导致，因为我的user表是public的，所以删除时需要 DROP TABLE public.user ），因为我这里的user表有一个关联表user_id_seq，要想删除这类表需要 DROP SEQUENCE user_id_seq CASCADE;，后面添加的CASCADE意思是删除该表并删除与之关联的表（存在外键引用这类的情况），默认模式为RESTRICT，表示当要删除的表有依赖表时拒绝删除该表
Xiao Wenbin
Natural Language Processing Engineer
My research interests include machine learning, information retrieval and natural language processing.