同事做一个单表恢复工作，数据在1000多W，说是报了错误导不进去，环境与流程见下：
OS:CnetOS 5
DB:Postgres 9.2.4
**恢复步骤：**
~~~
1.导出语句
pg_dump -h xxxxx -p 5432 -U postgres -b -Fp db_test -t t_kenyon -f /var/t_kenyon.bak
2.导入语句
psql -h xxxx -d new_db -U postgres < /var/t_kenyon.bak
3.报错信息，屏幕上一堆的诸如
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
........
~~~
**分析处理：**
~~~
因为是逻辑导出没有压缩定制的文件，故可以查看备份内容
[postgres@localhost ~]$ more t_kenyon.bak
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: t_kenyon; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE t_kenyon (
col1 integer DEFAULT nextval('t_kenyon_col1_seq'::regclass) NOT NULL,
col2 ..
col3 ..
);
ALTER TABLE public.t_kenyon OWNER TO postgres;
--
-- Name: COLUMN t_kenyon.col; Type: COMMENT; Schema: public; Owner: postgres
--
--
-- Data for Name: t_kenyon; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY t_kenyon (col1,col2,col3....) FROM stdin;
3315866 \N 1 5.00 \N \N \N 2011-01-12 08:37:07+08 1 4130000 爱我中话 \N 708 Kenyon HZ 雅安加油 HZ01 HELLO \N 9 \N
3315934 \N 1 5.00 \N \N \N 2011-01-12 09:13:17+08 1 4130000 我哎中华 \N 708 kenyon HZ 雅安加油
..........此处略去1W字
~~~
看起来报错的信息都是\N即空格的地方出错了，检查了一下postgres的日志，有几条信息发现很有意思
~~~
2013-04-23 00:16:23.149 PDT,"postgres","postgres",24738,"[local]",51763545.60a2,4,"CREATE TABLE",2013-04-23 00:16:21 PDT,2/331,1856,ERROR,42P01,"relation ""t_kenyon_col1_seq"" does not exist",,,,,,"CREATE TABLE t_kenyon (col1,col2...)..
~~~
这看起来是导数据之前的建表失败了，因为sequence不存在，后面的copy操作直接就报了N多的错误，尝试先建一下索引，再重新导入
~~~
postgres=#CREATE SEQUENCE t_kenyon_col1_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 17354062 CACHE 1;
CREATE SEQUENCE
postgres=# \q
[postgres@localhost ~]$ psql -d postgres -U postgres < /var/t_kenyon.bak
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COMMENT
ALTER TABLE
CREATE INDEX
CREATE INDEX
[postgres@localhost ~]$
~~~
OK,导入成功。但是有一个问题，为什么pg_dump导出的时候没有把sequence带出来呢？验证一下
~~~
[postgres@localhost ~]$ psql
psql (9.2.4)
Type "help" for help.
postgres=# \d
No relations found.
postgres=# create table d_kenyon(id serial,vname varchar(30));
NOTICE: CREATE TABLE will create implicit sequence "d_kenyon_id_seq" for serial column "d_kenyon.id"
CREATE TABLE
postgres=# insert into d_kenyon(vname) select generate_series(1,10)||'Hi,Kenyon!';
INSERT 0 10
postgres=# select * from d_kenyon;
id | vname
----+--------------
1 | 1Hi,Kenyon!
2 | 2Hi,Kenyon!
3 | 3Hi,Kenyon!
4 | 4Hi,Kenyon!
5 | 5Hi,Kenyon!
6 | 6Hi,Kenyon!
7 | 7Hi,Kenyon!
8 | 8Hi,Kenyon!
9 | 9Hi,Kenyon!
10 | 10Hi,Kenyon!
(10 rows)
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+----------
public | d_kenyon | table | postgres
public | d_kenyon_id_seq | sequence | postgres
[postgres@localhost ~]$ pg_dump -U postgres -b -Fp postgres -t d_kenyon -f d_kenyon.bak
[postgres@localhost ~]$ more d_kenyon.bak
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: d_kenyon; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE d_kenyon (
id integer NOT NULL,
vname character varying(30)
);
ALTER TABLE public.d_kenyon OWNER TO postgres;
--
-- Name: d_kenyon_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE d_kenyon_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.d_kenyon_id_seq OWNER TO postgres;
--
-- Name: d_kenyon_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE d_kenyon_id_seq OWNED BY d_kenyon.id;
--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY d_kenyon ALTER COLUMN id SET DEFAULT nextval('d_kenyon_id_seq'::regclass);
--
-- Data for Name: d_kenyon; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY d_kenyon (id, vname) FROM stdin;
1 1Hi,Kenyon!
2 2Hi,Kenyon!
3 3Hi,Kenyon!
4 4Hi,Kenyon!
5 5Hi,Kenyon!
6 6Hi,Kenyon!
7 7Hi,Kenyon!
8 8Hi,Kenyon!
[postgres@localhost ~]$ psql
psql (9.2.4)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+----------
public | d_kenyon | table | postgres
public | d_kenyon_id_seq | sequence | postgres
(2 rows)
postgres=# drop table d_kenyon;
DROP TABLE
postgres=# \q
[postgres@localhost ~]$ psql < d_kenyon.bak
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
setval
--------
10
(1 row)
[postgres@localhost ~]$
~~~
发现用serial产生的sequence是可以导出并导入的，回过头再去看异常的表，发现该表字段不是serial，模拟一下非serial字段的pg_dump导出情况
~~~
postgres=# create table d_test as select * from d_kenyon;
SELECT 10
postgres=# alter table d_test alter column id set default nextval('d_kenyon_id_seq'::regclass);
ALTER TABLE
postgres=# \d d_test
Table "public.d_test"
Column | Type | Modifiers
--------+-----------------------+----------------------------------------------
id | integer | default nextval('d_kenyon_id_seq'::regclass)
vname | character varying(30) |
postgres=# \q
[postgres@localhost ~]$ pg_dump -U postgres -b -Fp postgres -t d_test -f d_kenyon.bak
[postgres@localhost ~]$ more d_kenyon.bak
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: d_test; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE d_test (
id integer DEFAULT nextval('d_kenyon_id_seq'::regclass),
vname character varying(30)
);
ALTER TABLE public.d_test OWNER TO postgres;
--
-- Data for Name: d_test; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY d_test (id, vname) FROM stdin;
1 1Hi,Kenyon!
2 2Hi,Kenyon!
3 3Hi,Kenyon!
4 4Hi,Kenyon!
5 5Hi,Kenyon!
6 6Hi,Kenyon!
7 7Hi,Kenyon!
8 8Hi,Kenyon!
9 9Hi,Kenyon!
10 10Hi,Kenyon!
\.
--
-- PostgreSQL database dump complete
--
~~~
确实是没有sequence导出来的，查了一下，pg_depend里序列与表并没有关联上，也就是说这样的表与sequence是独立的，可以用以下SQL验证一下表与sequence的关联关系
~~~
WITH fq_objects AS (SELECT c.oid,c.relname AS fqname ,c.relkind, c.relname AS relation FROM
pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),
tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT s.fqname AS sequence,'->' as depends,t.fqname AS table
FROM pg_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
WHERE d.deptype = 'a' and t.fqname = 'd_kenyon';
~~~
**总结：**
扩展开来想，这种备份如果是整个库备份再恢复，应是OK的，后来验证确实如此，故对于整库恢复是不用考虑这个问题的，单表恢复则需要注意一下。
- 数据表
- 模式Schema
- 表的继承和分区
- 常用数据类型
- 函数和操作符-一
- 函数和操作符-二
- 函数和操作符-三
- 索引
- 事物隔离
- 性能提升技巧
- 服务器配置
- 角色和权限
- 数据库管理
- 数据库维护
- 系统表
- 系统视图
- SQL语言函数
- PL-pgSQL过程语言
- PostgreSQL 序列（SEQUENCE）
- PostgreSQL的时间-日期函数使用
- PostgreSQL 查看数据库，索引，表，表空间大小
- 用以查询某表的详细 包含表字段的注释信息
- PostgreSQL 系统表查看系统信息
- postgre存储过程简单实用方法
- PostgreSQL实用日常维护SQL
- PostgreSQL的时间函数使用整理
- 命令
- pg_ctl控制服务器
- initdb 初始化数据库簇
- createdb创建数据库
- dropdb 删除数据库
- createuser创建用户
- dropuser 删除用户
- psql交互式工具
- psql命令手册
- pg_dump 数据库转储
- pg_restore恢复数据库
- vacuumdb 清理优化数据库
- reindexdb 数据库重创索引
- createlang 安装过程语言
- droplang 删除过程语言
- pg_upgrade 升级数据库簇
- 调试存储过程
- 客户端命令-一
- 客户端命令-二
- 使用技巧
- PostgreSQL删除重复数据
- postgresql 小技巧
- PostgreSQL的10进制与16进制互转
- PostgreSQL的汉字转拼音
- Postgres重复数据的更新一例
- PostgreSQL使用with一例
- PostgreSQL在函数内返回returning
- PostgreSQL中的group_concat使用
- PostgreSQL数据库切割和组合字段函数
- postgresql重复数据的删除
- PostgreSQL的递归查询(with recursive)
- PostgreSQL函数如何返回数据集
- PostgreSQL分区表（Table Partitioning）应用 - David_Tang - 博客园
- PostgreSQL: function 返回结果集多列和单列的例子
- 利用pgAgent创建定时任务
- 浅谈 PostgreSQL 类型转换类似Oracle
- postgresql在windows(包括win7)下的安装配置
- PostgreSQL简介、安装、用户管理、启动关闭、创建删除数据库 (2010-11-08 12-52-51)转载▼标签： 杂谈分类： PostgreSQL
- PostgreSQL的generate_series函数应用
- PostgreSQL 8.3.1 全文检索(Full Text Search)
- postgresql record 使用
- 备份恢复
- PostgreSQL基于时间点恢复(PITR)
- Postgresql基于时间点恢复PITR案例(二)
- Postgres逻辑备份脚本
- Postgres invalid command \N数据恢复处理