PostgreSQL 初学者应该学会的 11 个任务
一、安装
以 Ubuntu 为例，如果无需安装最新版本，只需用：
sudo apt-get install postgresql
如果要安装最新版本，以 Ubuntu Trusty(14.04) 为例。
- 先从 PostgreSQL 下载页 获取相应的 Apt 仓库信息，然后创建文件 /etc/apt/sources.list.d/pgdg.list， 命令为：
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
- 加载仓库的 GPG Key：
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
- 安装
sudo apt-get update && sudo apt-get install postgresql postgresql-contrib
二、打开命令行创建数据库
安装 PostgreSQL 后，会在数据库系统里自动创建名为 postgres 的用户名和角色名，同时在 Linux 系统中创建一个相同用户名的用户。
切换到 postgres 用户：
sudo su - postgres
开启 psql 终端：
psql
由于数据库系统中的 postgres 用户密码是随机产生的，如果想修改，在由 psql 打开的终端窗口中执行：
ALTER USER postgres WITH PASSWORD 'postgres';
如果想修改 Linux 系统中的 postgres 用户密码：
- 删除旧密码：
sudo passwd -d postgres
- 设置新密码：
sudo su - postgres
passwd
创建数据库
剧情设定： 蝙蝠侠想创建一个登记混蛋的数据库：
CREATE DATABASE villains;
创建数据库时也可以指定编码：
CREATE DATABASE villains ENCODING 'UTF-8';
三、创建用户
为确保安全，为每个数据库创建一个专门用户：
CREATE USER batman WITH PASSWORD 'Extremly-Secret-Password';
四、为用户授权权限
授权全部的权限：
GRANT ALL PRIVILEGES ON DATABASE villains to batman;
或者也可以授权指定的权限：
GRANT SELECT ON DATABASE villains to alfred;
能够授权的权限有：
- SELECT
- INSERT
- UPDATE
- DELETE
- RULE
- REFERENCES
- TRIGGER
- CREATE
- TEMPORARY
- EXECUTE
- USAGE
对于上面的创建数据库、创建用户和授权三步操作，其实可以用一步来完成：
CREATE DATABASE villains OWNER batman;
五、创建表
创建一个超级混蛋数据表：
CREATE TABLE super_villains (id serial PRIMARY KEY, name character varying(100), super_power character varying(100), weakness character varying(100));
再创建一个装备数据表：
CREATE TABLE equipment (id serial PRIMARY KEY, name character varying(100), status character varying(100), special_move character varying(100));
六、显示表的信息
查看表的列名、列类型等表描述信息：
\d+ equipment;
如果想在 PgAdmin 上查询的话：
SELECT *
FROM information_schema.columns
WHERE table_name = 'equipment';
该命令会显示关于表的更加详细的信息。
如果想列出当前数据库中有哪些表：
\d
该命令会列出数据库中全部的表，包括由系统创建的一些控制表，如 equipment_id_seq 等。
七、修改表格，使其序号能自增
系统已经为表格序号的自增创建了相应的控制表，如对应 equipment 表有 equipment_id_seq 表。
修改表格使得每次插入时无需填写 id 值，id 值能自动递增：
ALTER TABLE equipment ALTER id set default nextval('equipment_id_seq');
ALTER TABLE super_villains ALTER id set default nextval('super_villains_id_seq');
八、插入数据
INSERT INTO equipment(name, status, special_move) VALUES('Utility Belt', 'Nice and yellow', 'All kind of cool stuff in your waist');
INSERT INTO super_villains(name, super_power, weakness) VALUES('The Joker', 'Extra Crazy', 'Super punch');
九、查询
SELECT * from equipment;
SELECT * from super_villains;
SELECT weakness from super_villains;
SELECT special_move, name from equipment;
十、基本管理操作
允许远程访问
找到数据库的配置文件 postgresql.conf， Ubuntu 14.04 上的位置是 /etc/postgresql/9.5/main/postgresql.conf。
找到含有 #listen_address = 'localhost' 的行，修改成允许访问的远程 IP 地址，如： listen_addresses = '10.0.0.3'，或者允许所有地址： listen_addresses = '*'。
重启数据库服务器
在 Ubuntu 上：
sudo service postgresql restart
十一、备份与恢复
备份
$ pg_dump --username=name dbname > backup.sql
还原
$ sudo su - postgres
$ dropdb dbname # 先删除原来的数据库
$ createdb --owner=owner dbname
$ psql -d dbname -f backup.sql
参考文献：
