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