smallfish logs

COPY 命令可以快速的导入数据到 PostgreSQL 中，文件格式类似CVS之类。适合批量导入数据，比 \i 和恢复数据表快。

导出表数据到文件或 STDOUT ：

COPY tablename [(column [, ...])]
   TO {'filename' | STDOUT}
   [[WITH]
      [BINARY]
      [OIDS]
      [DELIMITER [AS] 'delimiter']
      [NULL [AS] 'null string']
      [CSV [HEADER]
         [QUOTE [AS] 'quote']
         [ESCAPE [AS] 'escape']
         [FORCE NOT NULL column [, ...]]

导入文件或者 STDIN 到表中：

COPY tablename [(column [, ...])]
   FROM {'filename' | STDIN}
   [[WITH]
      [BINARY]
      [OIDS]
      [DELIMITER [AS] 'delimiter']
      [NULL [AS] 'null string']
      [CSV [HEADER]
         [QUOTE [AS] 'quote']
         [ESCAPE [AS] 'escape']
         [FORCE QUOTE column [, ...]]

导出表 employee 到默认输出 STDOUT：

psql> COPY employee TO STDOUT;
1       JG100011        Jason Gilmore         jason@example.com
2       RT435234        Robert Treat          rob@example.com
3       GS998909        Greg Sabino Mullane   greg@example.com
4       MW777983        Matt Wade             matt@example.com

导出表 employee 到 sql 文件：

psql> COPY employee TO '/home/smallfish/employee.sql';

从文件导入数据：

psql> COPY employeenew FROM '/home/smallfish/employee.sql';
psql> SELECT * FROM employeenew;
employeeid  | employeecode |     name            |       email
------------+--------------+---------------------+---------------
          1 | JG100011     | Jason Gilmore       | jason@example.com
          2 | RT435234     | Robert Treat        | rob@example.com
          3 | GS998909     | Greg Sabino Mullane | greg@example.com
          4 | MW777983     | Matt Wade           | matt@example.com
(4 rows)

输出对象ID（OIDS）：

psql> COPY employee TO STDOUT OIDS;
24627  1       GM100011        Jason Gilmore         jason@example.com
24628  2       RT435234        Robert Treat          rob@example.com
24629  3       GS998909        Greg Sabino Mullane   greg@example.com
24630  4       MW777983        Matt Wade             matt@example.com

指定导出间隔符，默认是 \t ，这里为 | ：

psql>COPY employee TO STDOUT DELIMITER '|';
1|GM100011|Jason Gilmore|jason@example.com
2|RT435234|Robert Treat|rob@example.com
3|GS998909|Greg Sabino Mullane|greg@example.com
4|MW777983|Matt Wade|matt@example.com

导入文件数据，指定间隔符为 | ：

psql> COPY employeenew FROM '/home/smallfish/employee.sql' DELIMITER |;

导出指定字段的数据：

psql> COPY employee (name,email) TO STDOUT;
Jason Gilmore         jason@example.com
Robert Treat          rob@example.com
Greg Sabino Mullane   greg@example.com
Matt Wade             matt@example.com

为 NULL 字段设置默认值：

psql> COPY employee TO STDOUT NULL 'no email';
Jason Gilmore         no email
Robert Treat          rob@example.com
Greg Sabino Mullane   greg@example.com
Matt Wade             no email

导出为CVS格式：

psql> COPY employee (name, email) TO '/home/smallfish/employee.csv' CSV HEADER;

参考资料：Apress Beginning PHP and PostgreSQL 8: From Novice to Professional

测试环境：PostgreSQL 8.4

默认PostgreSQL是木有UUID函数可使用，而不像MySQL提供uuid()函数，不过在contrib里有，只需要导入一下uuid-ossp.sql即可。（PS：注意权限问题，要Pg可读改文件。）

导入很简单，下面是win下面测试，其他平台类似该操作：

D:\>psql -U postgres -h localhost -f D:\PostgreSQL\8.4\share\contrib\uuid-ossp.sql
Password for user postgres:
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

进入psql，执行：

postgres=# select uuid_generate_v1();
           uuid_generate_v1
--------------------------------------
 86811bd4-22a5-11df-b00e-ebd863f5f8a7
(1 row)

postgres=# select uuid_generate_v4();
           uuid_generate_v4
--------------------------------------
 5edbfcbb-1df8-48fa-853f-7917e4e346db
(1 row)

主要就是uuid_generate_v1和uuid_generate_v4，当然还有uuid_generate_v3和uuid_generate_v5。其他使用可以参见PostgreSQL官方文档 uuid-ossp。

测试环境：REHL 5.3
PostgreSQL版本：8.4.2

1. 首先检查下是否已经有PostgreSQL安装程序(俺的机器有pg-libs 8.1，无视之)

shell> rpm -qa | grep postgres

2. 下载最新的8.4.2RPM安装包，这个FTP速度挺快的。:)

shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-server-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-contrib-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-libs-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-devel-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-plpython-8.4.2-1PGDG.rhel5.x86_64.rpm

3. 安装PostgreSQL(要注意下顺序)，首先需要更新pg-libs版本。
后面几个不需要的话可以不装。主要是一些扩展功能。

shell> rpm -ivh postgresql-libs-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> rpm -ivh postgresql-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> rpm -ivh postgresql-server-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> rpm -ivh postgresql-contrib-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> rpm -ivh postgresql-devel-8.4.2-1PGDG.rhel5.x86_64.rpm
shell> rpm -ivh postgresql-plpython-8.4.2-1PGDG.rhel5.x86_64.rpm

4. RPM安装完后，需要初始化PostgreSQL库。service初次启动会提示。
如果是源码安装这个过程就是对应的initdb -D，指定data目录。RPM默认对应目录是/var/lib/pgsql/data。

shell> service postgresql initdb

5. service启动PostgreSQL

shell> service postgresql start

到上面这一步基本是安装完成了。下面的是修改数据库用户密码和登陆相关。

6. 切换到postgres用户，修改数据库密码。(注意系统用户和数据库用户密码是两个概念，虽然名字都叫postgres)
修改完需要重启数据库，这里咱不重启，等修改完认证配置再一起重启。

shell> su - postgres
shell> psql
postgres=# ALTER USER postgres WITH PASSWORD '123456';
postgres=# \q

7. 修改认证文件/var/lib/pgsql/data/pg_hba.conf，登陆使用密码。md5格式。

shell> vi /var/lib/pgsql/data/pg_hba.conf
修改ident为md5 (local, host)

8. service重启PostgreSQL

shell> service postgresql restart

9. 再次进入测试，应该会提示输入密码鸟 :)

shell>psql -U postgres

对比下一些两个数据库常用的操作。分别使用自带的client程序。

MySQL命令行：

mysql -u 用户名 -h 主机地址 -P 端口号 数据库名 -p

PostgreSQL命令行：

psql -U 用户名 -h 主机地址 -p 端口号 数据库名

操作对比：

mysql                      psql

SHOW DATABASES;           \l
USE db-name;              \c db-name
SHOW TABLES;              \d
SHOW USERS;               \du
SHOW COLUMNS;             \d table-name
SHOW PROCESSLIST;         SELECT * FROM pg_stat_activity;
SELECT now()\G            \x 可以打开和关闭类似\G功能
SOURCE /path.sql          \i /path.sql
LOAD DATA INFILE ...      \copy ...
\h                        \?

测试版本：pg 8.3 (ubuntu)

在pg里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。

创建表分区步骤如下：

1. 创建主表

CREATE TABLE users ( uid int not null primary key, name varchar(20));

2. 创建分区表(必须继承上面的主表)

CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users);
CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users);

3. 在分区表上建立索引，其实这步可以省略的哦

CREATE INDEX users_0_uidindex on users_0(uid);
CREATE INDEX users_1_uidindex on users_1(uid);

4. 创建规则RULE

CREATE RULE users_insert_0 AS
    ON INSERT TO users WHERE
        (uid >= 0 and uid < 100)
    DO INSTEAD
        INSERT INTO users_0 VALUES (NEW.uid,NEW.name);

CREATE RULE users_insert_1 AS
    ON INSERT TO users WHERE
        (uid >= 100)
    DO INSTEAD
        INSERT INTO users_1 VALUES (NEW.uid,NEW.name);

下面就可以测试写入数据啦：

postgres=# INSERT INTO users VALUES (100,'smallfish');
INSERT 0 0
postgres=# INSERT INTO users VALUES (20,'aaaaa');
INSERT 0 0
postgres=# select * from users;
uid  |   name
-----+-----------
20   | aaaaa
100  | smallfish
(2 笔资料列)
postgres=# select * from users_0;
uid  | name
-----+-------
20   | aaaaa
(1 笔资料列)

postgres=# select * from users_1;
uid  |   name
-----+-----------
100  | smallfish
(1 笔资料列)

到这里表分区已经可以算完了，不过还有个地方需要修改下，先看count查询把。

postgres=# EXPLAIN SELECT count(*) FROM users where uid<100;
QUERY PLAN
------------------------------------------------
Aggregate  (cost=62.75..62.76 rows=1 width=0)
    ->  Append  (cost=6.52..60.55 rows=879 width=0)
    ->  Bitmap Heap Scan on users  (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
    ->  Bitmap Index Scan on users_pkey  (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
    ->  Bitmap Heap Scan on users_0 users  (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
    ->  Bitmap Index Scan on users_0_uidindex  (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
    ->  Bitmap Heap Scan on users_1 users  (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
    ->  Bitmap Index Scan on users_1_uidindex  (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
(14 笔资料列)

按照本来想法，uid小于100，理论上应该只是查询users_0表，通过EXPLAIN可以看到其他他扫描了所有分区的表。

postgres=# SET constraint_exclusion = on;
SET

postgres=# EXPLAIN SELECT count(*) FROM users where uid<100;
QUERY PLAN
------------------------------------------------
Aggregate  (cost=41.83..41.84 rows=1 width=0)
    ->  Append  (cost=6.52..40.37 rows=586 width=0)
    ->  Bitmap Heap Scan on users  (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
    ->  Bitmap Index Scan on users_pkey  (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
    ->  Bitmap Heap Scan on users_0 users  (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
    ->  Bitmap Index Scan on users_0_uidindex  (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
(10 笔资料列)

到这里整个过程都OK啦！

pgsql允许管理员在文件系统里定义表空间存储位置，这样创建数据库对象时候就可以引用这个表空间了。好处就不用多说了，可以把数据库对象存储到不同的分区上，比如更好的存储之类。默认initdb之后会有两个表空间pg_global和pg_default。

查看pgsql当前表空间有哪些可以试试下面：

postgres=> SELECT spcname FROM pg_tablespace;
  spcname
------------
 pg_default
 pg_global
(2 rows)

或：

postgres=> \db
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |

建立表空间需要注意的主要的是权限问题，而且要在新的空目录上建立，权限属于数据库管理员比如默认postgres。

1. 建立目录

$ mkdir /home/smallfish/pgdata
$ sudo chown -R postgres:postgres /home/smallfish/pgdata

2. 进入psql

$ psql -U postgres -h 192.168.0.122

如果权限没设置好下面语句会报错

postgres=> CREATE TABLESPACE space1 LOCATION '/home/smallfish/pgdata';

建测试表

postgres=> CREATE TABLE foo(i int) TABLESPACE space1;

可以查看表空间目录下多了文件

postgres=> \! ls /home/smallfish/pgdata

删除表空间，需要注意的是先要删除所有该表空间里的对象

postgres=> DROP TABLESPACE space1;

ok，到这里已经建立好表空间了。当然每次建表都指定TABLESPACE也有点麻烦，来点默认的把。

postgres=> SET default_tablespace = space1;
postgres=> CREATE TABLE foo(i int);