postgresql导出表结构以及数据到mysql

待我称王封你为后i 2021-12-01 23:20 1062阅读 0赞

postgresql导出的表结构在语句上会和mysql有些差异，因此当我们在mysql命令行中执行的时候，会有警告和错误提示，但是最终还是会将表生成成功，这里将表结构和数据分别单独导出，而且使用的语法和方法都不一样。

导出表结构直接使用postgresql命令pg\_dump，而导出数据使用psql命令的copy。在mysql中导入表结构，我们执行source /path/to/table.sql，我们导入的表数据是单独的，而且是格式化的数据，我们通过load data local infile语句导入，需要指定列分隔符，以及行分隔符。

1、检查表结构和数据

2、导出表结构

[postgres@server ~]$ pg_dump --verbose --schema-only --table=xx_user --db=test --file=/home/postgres/user.sql
    pg_dump: last built-in OID is 16383
    pg_dump: reading extensions
    pg_dump: identifying extension members
    pg_dump: reading schemas
    pg_dump: reading user-defined tables
    pg_dump: reading user-defined functions
    pg_dump: reading user-defined types
    pg_dump: reading procedural languages
    pg_dump: reading user-defined aggregate functions
    pg_dump: reading user-defined operators
    pg_dump: reading user-defined access methods
    pg_dump: reading user-defined operator classes
    pg_dump: reading user-defined operator families
    pg_dump: reading user-defined text search parsers
    pg_dump: reading user-defined text search templates
    pg_dump: reading user-defined text search dictionaries
    pg_dump: reading user-defined text search configurations
    pg_dump: reading user-defined foreign-data wrappers
    pg_dump: reading user-defined foreign servers
    pg_dump: reading default privileges
    pg_dump: reading user-defined collations
    pg_dump: reading user-defined conversions
    pg_dump: reading type casts
    pg_dump: reading transforms
    pg_dump: reading table inheritance information
    pg_dump: reading event triggers
    pg_dump: finding extension tables
    pg_dump: finding inheritance relationships
    pg_dump: reading column info for interesting tables
    pg_dump: finding the columns and types of table "public.xx_user"
    pg_dump: finding default expressions of table "public.xx_user"
    pg_dump: flagging inherited columns in subtables
    pg_dump: reading indexes
    pg_dump: reading indexes for table "public.xx_user"
    pg_dump: flagging indexes in partitioned tables
    pg_dump: reading extended statistics
    pg_dump: reading constraints
    pg_dump: reading triggers
    pg_dump: reading rewrite rules
    pg_dump: reading policies
    pg_dump: reading row security enabled for table "public.xx_user_id_seq"
    pg_dump: reading policies for table "public.xx_user_id_seq"
    pg_dump: reading row security enabled for table "public.xx_user"
    pg_dump: reading policies for table "public.xx_user"
    pg_dump: reading publications
    pg_dump: reading publication membership
    pg_dump: reading publication membership for table "public.xx_user"
    pg_dump: reading subscriptions
    pg_dump: reading dependency data
    pg_dump: saving encoding = UTF8
    pg_dump: saving standard_conforming_strings = on
    pg_dump: saving search_path = 
    pg_dump: creating TABLE "public.xx_user"
    pg_dump: creating SEQUENCE "public.xx_user_id_seq"
    pg_dump: creating SEQUENCE OWNED BY "public.xx_user_id_seq"
    pg_dump: creating DEFAULT "public.xx_user id"
    pg_dump: creating CONSTRAINT "public.xx_user xx_user_pkey"

我们可以看看生成的sql语句：

--
    -- PostgreSQL database dump
    --
    
    -- Dumped from database version 11.4
    -- Dumped by pg_dump version 11.4
    
    -- Started on 2019-07-21 08:33:09 CST
    
    SET statement_timeout = 0;
    SET lock_timeout = 0;
    SET idle_in_transaction_session_timeout = 0;
    SET client_encoding = 'UTF8';
    SET standard_conforming_strings = on;
    SELECT pg_catalog.set_config('search_path', '', false);
    SET check_function_bodies = false;
    SET xmloption = content;
    SET client_min_messages = warning;
    SET row_security = off;
    
    SET default_tablespace = '';
    
    SET default_with_oids = false;
    
    --
    -- TOC entry 197 (class 1259 OID 16387)
    -- Name: xx_user; Type: TABLE; Schema: public; Owner: postgres
    --
    
    CREATE TABLE public.xx_user (
        id integer NOT NULL,
        name character varying(20),
        mobile character varying(20),
        birth date
    );
    
    
    ALTER TABLE public.xx_user OWNER TO postgres;
    
    --
    -- TOC entry 196 (class 1259 OID 16385)
    -- Name: xx_user_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
    --
    
    CREATE SEQUENCE public.xx_user_id_seq
        AS integer
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;
    
    
    ALTER TABLE public.xx_user_id_seq OWNER TO postgres;
    
    --
    -- TOC entry 3086 (class 0 OID 0)
    -- Dependencies: 196
    -- Name: xx_user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
    --
    
    ALTER SEQUENCE public.xx_user_id_seq OWNED BY public.xx_user.id;
    
    
    --
    -- TOC entry 2957 (class 2604 OID 16390)
    -- Name: xx_user id; Type: DEFAULT; Schema: public; Owner: postgres
    --
    
    ALTER TABLE ONLY public.xx_user ALTER COLUMN id SET DEFAULT nextval('public.xx_user_id_seq'::regclass);
    
    
    --
    -- TOC entry 2959 (class 2606 OID 16392)
    -- Name: xx_user xx_user_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
    --
    
    ALTER TABLE ONLY public.xx_user
        ADD CONSTRAINT xx_user_pkey PRIMARY KEY (id);
    
    
    -- Completed on 2019-07-21 08:33:09 CST
    
    --
    -- PostgreSQL database dump complete
    --

这里使用的是postgresql11.4，通过pg\_dump导出表结构，从语句上看到，我们生成的表前面带有前缀"public."，这个前缀如果在mysql中执行会报错，因此我们需要将"public."这个前缀在sql文件中给去掉，暂时没有找到如何在导出的时候去掉这个前缀：“public.”，因此只能手工处理一下。

3、导出数据

[postgres@server ~]$ psql
    psql (11.4)
    Type "help" for help.
    
    postgres=# \c test
    You are now connected to database "test" as user "postgres".
    test=# copy xx_user to '/home/postgres/user.txt' with (delimiter ',');
    COPY 5
    test=#

导出5条记录，我们将导出的数据保存在/home/postgres/user.txt文本文件中，数据列之间用逗号“,”分隔。

[postgres@server ~]$ cat user.txt 
    1,aaa,13886604139,1987-08-24
    2,bbb,15342525980,1980-01-01
    3,ccc,18761598031,1992-09-29
    4,ddd,15910909870,1990-09-21
    5,eee,15900909890,1990-02-26

4、在mysql中导入表结构：

mysql> source /home/postgres/user.sql
    ERROR 1193 (HY000): Unknown system variable 'statement_timeout'
    ERROR 1193 (HY000): Unknown system variable 'lock_timeout'
    ERROR 1193 (HY000): Unknown system variable 'idle_in_transaction_session_timeout'
    ERROR 1193 (HY000): Unknown system variable 'client_encoding'
    ERROR 1193 (HY000): Unknown system variable 'standard_conforming_strings'
    ERROR 1305 (42000): FUNCTION pg_catalog.set_config does not exist
    ERROR 1193 (HY000): Unknown system variable 'check_function_bodies'
    ERROR 1193 (HY000): Unknown system variable 'xmloption'
    ERROR 1193 (HY000): Unknown system variable 'client_min_messages'
    ERROR 1193 (HY000): Unknown system variable 'row_security'
    ERROR 1193 (HY000): Unknown system variable 'default_with_oids'
    Query OK, 0 rows affected (0.02 sec)
    
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEQUENCE xx_user_id_seq
        AS integer
        START WITH 1
        INCREMENT BY 1
        N' at line 1
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEQUENCE xx_user_id_seq OWNED BY xx_user.id' at line 1
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx_user ALTER COLUMN id SET DEFAULT nextval('xx_user_id_seq'::regclass)' at line 1
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'public.xx_user
        ADD CONSTRAINT xx_user_pkey PRIMARY KEY (id)' at line 1
    mysql> desc xx_user;
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | id     | int(11)     | NO   |     | NULL    |       |
    | name   | varchar(20) | YES  |     | NULL    |       |
    | mobile | varchar(20) | YES  |     | NULL    |       |
    | birth  | date        | YES  |     | NULL    |       |
    +--------+-------------+------+-----+---------+-------+
    4 rows in set (0.05 sec)

这一步需要注意的是，我们在导出表结构的时候说过的问题，因为sql文件中，表名前面会带有"public."这个前缀，因此需要人为去掉，否则导入表结构会出现错误。

5、在mysql中导入数据

mysql> load data local infile '/home/postgres/user.txt' into table xx_user fields terminated by ',' lines terminated by '\n';
    Query OK, 5 rows affected (0.02 sec)
    Records: 5  Deleted: 0  Skipped: 0  Warnings: 0
    
    mysql> select * from xx_user;
    +----+------+-------------+------------+
    | id | name | mobile      | birth      |
    +----+------+-------------+------------+
    |  1 | aaa  | 13886604139 | 1987-08-24 |
    |  2 | bbb  | 15342525980 | 1980-01-01 |
    |  3 | ccc  | 18761598031 | 1992-09-29 |
    |  4 | ddd  | 15910909870 | 1990-09-21 |
    |  5 | eee  | 15900909890 | 1990-02-26 |
    +----+------+-------------+------------+
    5 rows in set (0.00 sec)