Postgres invalid command data recovery processing

My colleague did a single table recovery work, and the data was more than 1000 W. he said that the error could not be imported. The environment and process are as follows:

OS:CnetOS 5

DB:Postgres 9.2.4

recovery steps:

1.Export statement
pg_dump -h xxxxx -p 5432 -U postgres -b -Fp db_test -t t_kenyon -f /var/t_kenyon.bak

2. Import statement
psql -h xxxx -d new_db -U postgres < /var/t_kenyon.bak

3. error message, a bunch of screen like
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
........

analysis and processing:

Since it is a logical export without compression of the customized files, you can view the backup contents
[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 111        \N      708     Kenyon    HZ      222    HZ01    HELLO    \N      9       \N
3315934 \N      1       5.00    \N      \N      \N      2011-01-12 09:13:17+08  1       4130000 111        \N      708     kenyon    HZ      222    
..........10k words are omitted here

It seems that the error information is all in the space. After checking the Postgres log, we found that several pieces of information are very interesting

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...)..

It seems that the table creation before importing data failed, because the sequence does not exist, and the subsequent copy operation directly reported more than n errors. Try to create the index first, and then import it again

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, the import is successful. But there is a question, why PG_ When the dump is exported, the sequence is not brought out?Check it out

[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 ~]$

It is found that the sequence generated by serial can be exported and imported. Looking back at the abnormal table, it is found that the table field is not serial. Simulate PG of non serial field_ Dump export

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
--

There is really no sequence derived, check it, pg_ In depend, the sequence is not associated with the table, that is to say, such a table is independent of the sequence. You can use the following SQL to verify the association between the table and the 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';

summary:

If the whole database is backed up and then restored, it should be OK. Later, it was verified that this is the case. Therefore, for the whole database restoration, we need not consider this problem. For the single table restoration, we need to pay attention to it.

DebugAH

How to Solve Your Programmer Error

Postgres invalid command data recovery processing

Similar Posts: