PostgreSQL新手入门

作者: 阮一峰

日期: 2013年12月22日

自从MySQL被Oracle收购以后,PostgreSQL逐渐成为开源关系型数据库的首选。

本文介绍PostgreSQL的安装和基本用法,供初次使用者上手。以下内容基于Debian操作系统,其他操作系统实在没有精力兼顾,但是大部分内容应该普遍适用。

postgresql

一、安装

首先,安装PostgreSQL客户端。

sudo apt-get install postgresql-client

然后,安装PostgreSQL服务器。

sudo apt-get install postgresql

正常情况下,安装完成后,PostgreSQL服务器会自动在本机的5432端口开启。

如果还想安装图形管理界面,可以运行下面命令,但是本文不涉及这方面内容。

sudo apt-get install pgadmin3

二、添加新用户和新数据库

初次安装后,默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是,同时还生成了一个名为postgres的Linux系统用户。

下面,我们使用postgres用户,来生成其他用户和新数据库。好几种方法可以达到这个目的,这里介绍两种。

第一种方法,使用PostgreSQL控制台。

首先,新建一个Linux新用户,可以取你想要的名字,这里为dbuser。

sudo adduser dbuser

然后,切换到postgres用户。

sudo su - postgres

下一步,使用psql命令登录PostgreSQL控制台。

psql

这时相当于系统用户postgres以同名数据库用户的身份,登录数据库,这是不用输入密码的。如果一切正常,系统提示符会变为"postgres=#",表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。

第一件事是使用\password命令,为postgres用户设置一个密码。

\password postgres

第二件事是创建数据库用户dbuser(刚才创建的是Linux系统用户),并设置密码。

CREATE USER dbuser WITH PASSWORD 'password';

第三件事是创建用户数据库,这里为exampledb,并指定所有者为dbuser。

CREATE DATABASE exampledb OWNER dbuser;

第四件事是将exampledb数据库的所有权限都赋予dbuser,否则dbuser只能登录控制台,没有任何数据库操作权限。

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

最后,使用\q命令退出控制台(也可以直接按ctrl+D)。

\q

第二种方法,使用shell命令行。

添加新用户和新数据库,除了在PostgreSQL控制台内,还可以在shell命令行下完成。这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户dbuser和数据库exampledb为例。

首先,创建数据库用户dbuser,并指定其为超级用户。

sudo -u postgres createuser --superuser dbuser

然后,登录数据库控制台,设置dbuser用户的密码,完成后退出控制台。

sudo -u postgres psql

\password dbuser

\q

接着,在shell命令行下,创建数据库exampledb,并指定所有者为dbuser。

sudo -u postgres createdb -O dbuser exampledb

三、登录数据库

添加新用户和新数据库以后,就要以新用户的名义登录数据库,这时使用的是psql命令。

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

上面命令的参数含义如下:-U指定用户,-d指定数据库,-h指定服务器,-p指定端口。

输入上面命令以后,系统会提示输入dbuser用户的密码。输入正确,就可以登录控制台了。

psql命令存在简写形式。如果当前Linux系统用户,同时也是PostgreSQL用户,则可以省略用户名(-U参数的部分)。举例来说,我的Linux系统用户名为ruanyf,且PostgreSQL数据库存在同名用户,则我以ruanyf身份登录Linux系统后,可以直接使用下面的命令登录数据库,且不需要密码。

psql exampledb

此时,如果PostgreSQL内部还存在与当前系统用户同名的数据库,则连数据库名都可以省略。比如,假定存在一个叫做ruanyf的数据库,则直接键入psql就可以登录该数据库。

psql

另外,如果要恢复外部数据,可以使用下面的命令。

psql exampledb < exampledb.sql

四、控制台命令

除了前面已经用到的\password命令(设置密码)和\q命令(退出)以外,控制台还提供一系列其他命令。

  • \h:查看SQL命令的解释,比如\h select。
  • \?:查看psql命令列表。
  • \l:列出所有数据库。
  • \c [database_name]:连接其他数据库。
  • \d:列出当前数据库的所有表格。
  • \d [table_name]:列出某一张表格的结构。
  • \du:列出所有用户。
  • \e:打开文本编辑器。
  • \conninfo:列出当前数据库和连接的信息。

五、数据库操作

基本的数据库操作,就是使用一般的SQL语言。

# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

# 选择记录
SELECT * FROM user_tbl;

# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';

# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;

# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);

# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;

# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;

# 删除表格
DROP TABLE IF EXISTS backup_tbl;

(完)

珠峰培训

简寻

留言(41条)

PostgreSQL要崛起啊,我们一直在用他了;MySQL要被Oracle买了再给灭了。

Debian 现在默认安装的数据库已经是 postgres 了。

能不能讲一讲mysql 被oracle收购了为什么有人认为它会衰落?因为oracle重点是oracle数据库的原因吗,这样收购mysql难道是为了把mysql弄死不成?

暑期参加oschina的开源源创会,阿里核心系统数据库组的褚霸同学说如果回到08年,他会建议集团使用PostgreSQL,而不是MySQL.PostgreSQL诞生于学院派,没有局限于当时的一些硬件条件,MySQL发展自程序员的业余作品,为当时的小内存小外存的硬件条件做了特殊的处理,随着硬件的不断发展,这些处理在现在看来倒成了一个缺点了;当然褚霸同学不仅仅说了这么一个点,我只是把印象最深的一点写出来

maria 呢?

除了用法有点奇怪,还是很好的

mariadb才是代替者吧,看看wikipedia

MySQL和Postgresql的最大区别应该就是多线程和多进程设计的对决吧。虽然双方的出发点不一样,也各有优势,但是个人觉得多进程相对于多线程来说,管理和通信方面要方便不少,理解上也更简洁明了。

没有干货!

写得很好,我也正想从mysql转过来。

有个 enterpriseDB 上面有打包好的 PostgreSQL 可以免费下,各个os下的都有。可以省去这样的安装了。

PostgreSQL大名如雷贯耳,看来是大势所趋了,有空得多看看。
看了不少文章,颇有收获,谢阮兄。

写得很好,感谢分享。

pgsql 应该算是买对对象的数据库

试了好几次,数据库用户都没创建成功,最后发现是sql语句没加分号...

pg的历史比mysql的长很多,在国外是很流行的,国内嘛,因为拿来主义的习惯,少人用,在数据量不大,连接数不多时,使用mysql是很好的选择(其实我觉得对于小应用sqlite更好),但上规模时,例如同时有成百上千个连接时,mysql就疲态了,而pg基本都能适应。但pg毕竟是“学院派”数据库,很多时候理论性强,甚至对一些基本的东西不做优化,例如count(*),可是会做全表扫描的,对于数百万条记录的表,这将消耗大量时间。
如果你的是小应用,用sqlite吧,真的是又快又好,而且无处不在,HTML5,android等都内置了它,在计算机界就相当mdb(Access)于windows。而且性能很多时候比mdb要好。
如果是大型应用,又不差钱,还是用商业数据库吧,毕竟数据的价值和买数据库软件的钱比起来还是九牛一毛,如果又缺钱又要好,就用pg。对于互联网应用,作为设计者,还应该考虑NoSQL数据库,例如mongoDB,有点“对象关系”的意思,速度更快,数据库可分布,数据结构灵活,不用像SQL数据库一样字斟句酌地设计表、关系、触发器、过程等,应用端可以更灵活。

可以装上试试看,很多的开源程序在用postgresql做数据库 这篇文章提供一个基本的入门足够了,到真正用的时候再去查询详细用法。主要是开阔思路,开阔视野

PgSQL火爆前的预告

2013年最后一天,希望阮大哥能对这一年做个总结。2011和12年都错过了,今年和大家分享一下感受吧。

阮老师好!
里面修改postgres用户密码时,应该使用passwd命令。文中是password。您检查一下。

引用D瓜哥的发言:

修改postgres用户密码时,应该使用passwd命令。文中是password。您检查一下。

passwd是shell命令,这里是指postgresql内部的命令,\password没错。

引用冰与火的发言:

2013年最后一天,希望阮大哥能对这一年做个总结。2011和12年都错过了,今年和大家分享一下感受吧。

不好意思,不会有年度总结,至少今年不会。我有自己的苦衷,请谅解。

不错,希望能多写一点分享一下

FYI,debian/ubuntu 官方原裏版本一般比較老,postgres 官方維護了一個 apt 源 https://wiki.postgresql.org/wiki/Apt 一直在更新的,有 bugfix 一般都第一時間出來

PostgreSQL 官网上面怎么没找到redhat下面安装说明?还是您写的debian安装说明通俗易懂

引用fgh的发言:

pg的历史比mysql的长很多,在国外是很流行的,国内嘛,因为拿来主义的习惯,少人用,

国内也有人在研究PG的。

user_tbl 不知道为什么你的表明加下划线,其实表名是没有下划线的
还有我按照你的方法添加用户和数据库
但是按照psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432 连接却提示:
FATAL: Ident authentication failed for user "dbuser"

我也很喜欢Debian,博主什么写个硬盘安装的教程吧。

创建表的时候表名是:usertbl
为啥选择表的时候变成了:user_tbl?

@codeyu:

刚发现,markdown自动把同一行的两个下划线,解释为斜体。

我一直搞不懂Linux用户和数据库用户是什么关系。。。学习了

引用陆贇的发言:

MySQL和Postgresql的最大区别应该就是多线程和多进程设计的对决吧。虽然双方的出发点不一样,也各有优势,但是个人觉得多进程相对于多线程来说,管理和通信方面要方便不少,理解上也更简洁明了。

多进程的优势是可以把多核处理器跑满

引用fgh的发言:

pg的历史比mysql的长很多,在国外是很流行的,国内嘛,因为拿来主义的习惯,少人用,在数据量不大,连接数不多时,使用mysql是很好的选择(其实我觉得对于小应用sqlite更好),但上规模时,例如同时有成百上千个连接时,mysql就疲态了,而pg基本都能适应。但pg毕竟是“学院派”数据库,很多时候理论性强,甚至对一些基本的东西不做优化,例如count(*),可是会做全表扫描的,对于数百万条记录的表,这将消耗大量时间。
如果你的是小应用,用sqlite吧,真的是又快又好,而且无处不在,HTML5,android等都内置了它,在计算机界就相当mdb(Access)于windows。而且性能很多时候比mdb要好。

count(*)这个不是pg的问题,任何mvcc的数据库在count(*)时都是扫全表的。 mysql之所以快是因为myisam引擎相当不严谨(ACID方面,不支持事务),它对表row总行数有个计数器。而mysql支持(准确说是部分支持)事务的innodb引擎做count(*)就是扫全表照样满了——我还不如用pgsql呢,mysql那垃圾explain优化基本就没啥用,而pg的explain/explain analize信息非常有用

PostgreSQL 的提示符不只 postgres=# 一种。
不同的提示符,有不同的含义。
参考:http://stackoverflow.com/questions/19668825/meaning-of-different-command-line-prompts-in-postgresql

对我来说,这是很好的入门资料 谢谢了

您好,我想问一下,
我现在想在sql编辑器中使用脚本类修改连接的数据库,就是和sql的USE[数据库名]一样的功能,
该怎么实现?

为什么要创建额外的系统用户 dbuser?
有时间,去看了psql文档,可能能自解。

听说,“对于数据库访问提供了强大的安全性保证,充分利用了企业安全工具,如Kerberos与OpenSSL等” (http://www.infoq.com/cn/news/2013/12/mysql-vs-postgresql)
阮老师,有没有计划,写个psql安全配置的专文?


非常感谢。你的文章非常用心,所以非常易懂。

引用tewuapple的发言:

user_tbl 不知道为什么你的表明加下划线,其实表名是没有下划线的
还有我按照你的方法添加用户和数据库
但是按照psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432 连接却提示:
FATAL: Ident authentication failed for user "dbuser"

我的也是这个错误?怎么解决

简洁,易懂,透彻,感谢。

添加dbuser用户之后,还需要设置dbuser密码吧

2016年10月26、27日,上海浦东,Postgres中国用户大会2016(PG大象会)已经圆满落幕了,大会现场大咖分享视频已经正式上线(含嘉宾演讲PPT),你可直接微信搜索公众号“IT大咖说”(公众号ID:itdakashuo)观看视频。
PG中国社区的发展,需要你的成长与实践,好好努力吧,也可转发朋友圈进行分享,让更多的朋友来了解和学习postgres,为postgreSQL在中国的成长贡献一份力量!谢谢!

我要发表看法

«-必填

«-必填,不公开

«-我信任你,不会填写广告链接