postgresql数据库|数据库实操----表复制详解

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: postgresql数据库|数据库实操----表复制详解

前言:

通常情况下,我们对数据库的增删改查的时候,为了确保数据的安全,需要备份表,那么,一种方法是通过pg_dump 这个工具做SQL转储操作,此方法比较复杂,麻烦,但十分的安全,可靠性也基本是百分百,但对于大表备份,十分耗时并且可能有锁表的情况发生,另一种常用方法是复制原表,当表数据不是很大的时候,此方式非常方便,快捷。

postgresql的表复制是和oracle或者MySQL有所不同的,但,差异不是太大。

本文将就表复制这一操作做一个详细的解读,计划使用常用的emp表来做示例。

一,

示例的emp表的建立

####注:此建表SQL语句适用于postgresql

-- 创建数据表
CREATE TABLE dept (
deptno    NUMERIC CONSTRAINT PK_DEPT PRIMARY KEY,
dname    VARCHAR(14) ,
loc        VARCHAR(13) 
) ;
CREATE TABLE emp (
empno    NUMERIC CONSTRAINT PK_EMP PRIMARY KEY,
ename    VARCHAR(10),
job        VARCHAR(9),
mgr        NUMERIC,
hiredate    DATE,
sal        NUMERIC(7,2),
comm    NUMERIC(7,2),
deptno    NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT 
);
CREATE TABLE bonus (
enamE    VARCHAR(10)    ,
job        VARCHAR(9)  ,
sal        NUMERIC,
comm    NUMERIC 
) ;
CREATE TABLE salgrade ( 
grade        NUMERIC,
losal        NUMERIC,
hisal        NUMERIC 
);
-- 插入测试数据 —— dept
INSERT INTO dept VALUES    (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES    (30,'SALES','CHICAGO');
INSERT INTO dept VALUES    (40,'OPERATIONS','BOSTON');
-- 插入测试数据 —— emp
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-07-87','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-07-87','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
-- 插入测试数据 —— salgrade
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
-- 事务提交
COMMIT;

二,

示例表的介绍

观察emp表,关注此表都有哪些约束,外键,主键

emp表创建在test这个数据库内

test=# \c
You are now connected to database "test" as user "postgres".
test=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | bonus    | table | postgres
 public | dept     | table | postgres
 public | emp      | table | postgres
 public | salgrade | table | postgres
 public | tmp      | table | postgres
(5 rows)

有两个索引:

test=# \di
              List of relations
 Schema |  Name   | Type  |  Owner   | Table 
--------+---------+-------+----------+-------
 public | pk_dept | index | postgres | dept
 public | pk_emp  | index | postgres | emp

emp表有一个btree主键,一个关联dept(部门)的外键,empno字段有一个非空约束。

test=# \d emp
                        Table "public.emp"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           | not null | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
Indexes:
    "pk_emp" PRIMARY KEY, btree (empno)
Foreign-key constraints:
    "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

OK,emp的结构现在基本是清晰明了的了。

三,

复制表的六种方式

第一种

as select

全表复制----复制表结构和表数据,但不包括约束,主键,索引,外键

###此种方法会锁表,请谨慎操作大表

test=# create table emp1 as select * from emp;
SELECT 14

查看复制出来的emp1表各项数据:

test=# select * from emp1;
 empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno 
-------+--------+-----------+------+------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10
(14 rows)

可以看到,emp1复制成功了emp的表结构和表数据,但索引,主键,外键,约束没有了

由此,我们可以得出结论,该表复制方法并不能做到百分百的复制,索引和主外键以及约束是不复制的,表数据和表结构都会完整复制。

究其原因,索引和主外键以及约束的名称是不可重复的,postgresql是没有办法复制的。

test=# \d emp1
                        Table "public.emp1"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           |          | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
test=# alter table emp1 add constraint pk_emp primary key(empno);
ERROR:  relation "pk_emp" already exists

第二种表复制

as table

全表复制-----复制表结构和表数据,但不包括约束,主键,索引,外键

test=# create table emp2 as table emp;
SELECT 14

和as select基本没有区别,可以只复制表结构而不复制表数据---with no data:

test=# create table emp3 as  table emp with no data;
CREATE TABLE AS
test=# 
test=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | bonus    | table | postgres
 public | dept     | table | postgres
 public | emp      | table | postgres
 public | emp1     | table | postgres
 public | emp2     | table | postgres
 public | emp3     | table | postgres
 public | salgrade | table | postgres
 public | tmp      | table | postgres
(8 rows)
test=# select * from emp3;
 empno | ename | job | mgr | hiredate | sal | comm | deptno 
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

第三种表复制:

into复制

全表复制----复制表结构和表数据,但不包括约束,主键,索引,外键

###此种方法会锁表,请谨慎操作大表

test=# select * into emp4 from emp;
SELECT 14
test=# \d emp4
                        Table "public.emp4"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           |          | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
test=# select * from emp4;
 empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno 
-------+--------+-----------+------+------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10
(14 rows)

第四种表复制:

llike复制

----此方法非常重要,可以include 指定复制索引,主键,约束,但不包含外键,只复制表结构,不复制表数据

est=# create table emp5 (like emp);
CREATE TABLE
test=# \d emp5
                        Table "public.emp5"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           | not null | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
test=# select * from emp5;
 empno | ename | job | mgr | hiredate | sal | comm | deptno 
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

如果希望索引、主键约束和唯一约束被复制的话,那么需要指定INCLUDING INDEXES

  • including constraints :CHECK约束
  • including indexes :主键约束 和索引约束
  • including comments:注释
  • including defaults:复制序列 (复制default now(),sequence这类)
test=# create table emp7 (like emp INCLUDING INDEXES INCLUDING DEFAULTS);
CREATE TABLE
test=# select * from emp7;
 empno | ename | job | mgr | hiredate | sal | comm | deptno 
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)
test=# \d emp7
                        Table "public.emp7"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           | not null | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
Indexes:
    "emp7_pkey" PRIMARY KEY, btree (empno)

第五种复制

继承复制

这种复制表的方法和其他方法有所区别,任何针对父表的修改通常也会自动修改子表,可以简单理解为单向映射,不可对子表更改,子表存在的时候不可以删除父表。

test=# create table emp6 (note text not null) inherits (emp); 
CREATE TABLE
test=# \d emp6
                        Table "public.emp6"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           | not null | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
 note     | text                  |           | not null | 
Inherits: emp
test=# select * from emp6;
 empno | ename | job | mgr | hiredate | sal | comm | deptno | note 
-------+-------+-----+-----+----------+-----+------+--------+------
(0 rows)

此时对父表的更改将会作用到继承表:

test=# alter table emp add column city varchar;
ALTER TABLE
test=# \d emp6
                        Table "public.emp6"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           | not null | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
 note     | text                  |           | not null | 
 city     | character varying     |           |          | 
Inherits: emp
test=# alter table emp6 drop column city;
ERROR:  cannot drop inherited column "city"
test=# drop table emp;
ERROR:  cannot drop table emp because other objects depend on it
DETAIL:  table emp6 depends on table emp
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

第六种表复制:

insert 复制

----也就是填充表数据的方法,此方法需要复制目标存在,因此,可以利用第四种方法 ,  先  生成表结构,然后填充数据

以上面第四种方法创建的emp7表为例,填充emp表的数据到emp7表内:

test=# insert into emp7 select * from emp;
INSERT 0 14
test=# select * from emp7;
 empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno 
-------+--------+-----------+------+------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10
  7566 | JONES1 | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20
(14 rows)
test=# \d emp7
                        Table "public.emp7"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 empno    | numeric               |           | not null | 
 ename    | character varying(10) |           |          | 
 job      | character varying(9)  |           |          | 
 mgr      | numeric               |           |          | 
 hiredate | date                  |           |          | 
 sal      | numeric(7,2)          |           |          | 
 comm     | numeric(7,2)          |           |          | 
 deptno   | numeric(2,0)          |           |          | 
Indexes:
    "emp7_pkey" PRIMARY KEY, btree (empno)

可以看到,主键名称是表名,是btree类型的,约束存在的,只是缺少一个外键,因此,重新创建一个外键,就基本是%100复制了。

总结:

复制表推荐使用第四个方法和第六个方法结合,会省掉一点点麻烦,如果需要复制表强一致的话。

需要注意,表复制会导致锁表,因此,大表需要谨慎操作。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据库
PostgreSQL的逻辑存储结构涵盖数据库集群、数据库、表、索引、视图等对象,每个对象有唯一的oid标识。数据库集群包含多个数据库,每个数据库又包含多个模式,模式内含表、函数等。通过特定SQL命令可查看和管理这些数据库对象。
|
3月前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
16天前
|
弹性计算 关系型数据库 数据库
自建数据库迁移到云数据库实操
本课程详细介绍了自建数据库迁移到阿里云RDS的实操步骤。主要内容包括:创建实例资源、安全设置、配置自建的MySQL数据库、数据库的迁移、从自建数据库切换到RDS以及清理资源。通过这些步骤,学员可以掌握如何将自建数据库安全、高效地迁移到云端,并确保应用的正常运行。
93 26
|
2月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据库集群
PostgreSQL的逻辑存储结构涵盖了数据库集群、数据库、表、索引、视图等对象,每个对象都有唯一的oid标识。数据库集群是由单个PostgreSQL实例管理的所有数据库集合,共享同一配置和资源。集群的数据存储在一个称为数据目录的单一目录中,可通过-D选项或PGDATA环境变量指定。
|
2月前
|
关系型数据库 分布式数据库 数据库
PostgreSQL+Citus分布式数据库
PostgreSQL+Citus分布式数据库
72 15
|
4月前
|
SQL 关系型数据库 数据库
PostgreSQL数据库报错 ERROR: multiple default values specified for column "" of table "" 如何解决?
PostgreSQL数据库报错 ERROR: multiple default values specified for column "" of table "" 如何解决?
411 59
|
2月前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
443 1
|
2月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
209 4
|
3月前
|
SQL 关系型数据库 数据库
使用 PostgreSQL 和 Python 实现数据库操作
【10月更文挑战第2天】使用 PostgreSQL 和 Python 实现数据库操作
|
4月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
727 2