Postgres invalid command \N數據恢復處理 · postgresql手冊

同事做一個單表恢復工作，數據在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的，后來驗證確實如此，故對于整庫恢復是不用考慮這個問題的，單表恢復則需要注意一下。