初始化数据库:

su - postgres -c "initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'"

解释:使用 postgres 用户进行登录并切换到该用户的目录,并执行命令:initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'
其中参数 -D 指定数据库存放的目录(注:在 Arch Linux 中,postgresql 默认的目录为 /var/lib/postgres,而在 CentOS 中,是在 /var/lib/pgsql

创建用户/角色
有两种方式创建,一种是使用 createuser 命令,一种是使用 SQL 语句:

createuser <USERNAME>
CREATE USER <USERNAME> [WITH ...];
-- 或者
CREATE ROLE <ROLENAME> [WITH ...];

在上面的命令中,使用 createuser 或者 CREATE USER ...,与 CREATE ROLE ... 的区别是:前者创建的用户/角色有 LOGIN 权限,而后者为 NOLOGIN 即没有登录的权限(当然,也可以通过后面的 WITH 添加 LOGIN 权限)。

删除用户/角色:

dropuser <USERNAME>
DROP USER [IF EXISTS] <USERNAME>;
-- 或者
DROP ROLE [IF EXISTS] <ROLENAME>;

创建数据库:

createdb <DBNAME>
CREATE DATABASE <DBNAME>

删除数据库:

dropdb <DBNAME>
DROP DATABASE <DBNAME>

列出所有数据库:

使用 psql 登入后,使用:

\l

mysql 可以使用以下 SQL 语句:

show DATABASES;

psql 里使用(连接)其他的数据库(类似 mysql 里的 use <DBNAME>;):

\c <DBNAME>

修改 user/role 密码:

\password [USERNAME]

或者:

ALTER ROLE XXX WITH PASSWORD 'XXXXXX';

显示表的列名(attribute):

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS where table_name = '<name of table>';

或者:

\d+ <name of table>

其中 <name of table> 替换为具体需要查询的表名

在 psql 中,将查询结果复制到文件中:

\copy (SELECT * FROM foo) To '/tmp/test.csv' WITH FORMAT csv

可以将 SELECT * FROM foo 替换成你需要查询的语句,/tmp/test.csv 替换成需要保存的位置,FORMAT 支持三种:textcsvbinary

重置 SERIAL 计数:

SELECT setval('<seqname>',<next_value>);

<seqname> 替换成具体的 sequence name,<next_value> 替换成具体的计数值 (注:如果 next_value 为 1,则新插入的下一个值为 2)

也可以使用 ALTER 语句:

ALTER SEQUENCE <seqname> RESTART WITH 1

<seqname> 替换成具体的 sequence name

移除某个 constraint:

ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>;

SQL SELECT 语句的逻辑执行顺序:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

从另一个表导入数据:

INSERT INTO author(original_id, name)
SELECT author_id, author_name
FROM (
    SELECT DISTINCT author_id, author_name FROM original
) AS t
ORDER BY author_id::int ASC;

导出数据库:

pg_dump -U {USERNAME} {DBNAME} > db.pgsql

pg_dump 导出的数据默认带有 ownership(即面里有一个 ALTER 语句修改 table 的 owner 为原来数据库里对应的 user/role),如果该数据库的 owner(user/role)在导入的 postgresql 中不存在时,可能会发生错误。可以添加 --no-owner 选项,使导出的数据不带有 onwership

导入数据库:

psql -U {USERNAME} {DBNAME} < db.pgsql

默认时,当导入数据库有错误时,psql 会忽略它并继续导入,可以使用 --set ON_ERROR_STOP=on 选项来阻止:

psql --set ON_ERROR_STOP=on -U {USERNAME} {DBNAME} < db.pgsql

如果需要导出指定的表可以使用 -t {TABLE_NAME},多个表就加上多个的 -t {TABLE_NAME}。如果多个表的数据太大,可以使用 -j N(其中 N 为具体并行的数字)来进行并行导出,但该参数只能用于 -F d,即导出为为目录(里面每个文件对应相应的表)。通过该方式导出的数据,需要使用 pg_restore 来导入到新的数据库中。

pg_dump -t account -t author -t book_category  -t book_category_mobile -t book_img -t book_info -t book_volume -t book_chapter -U postgres -O -h localhost -p 15432 -d novel -j 8 -Fd -f db

导入:

pg_restore -U postgres -O -h localhost --role {ROLE} -p 15432 -d novel -j8 -Fd db

其中 --role {ROLE} 可以在恢复数据前使用 SET ROLE {ROLE}{ROLE} 设置当前 session 的 user id。这样,在恢复数据时将使用 {ROLE} 而不是 postgres(登录用户)来操作(其权限为 {ROLE} 所拥有的)。