用其他数据库的数据替换除数据库之外的数据库的所有表 [英] Replace all tables of a database except one, with data from another db

问题描述

我有一个Django应用程序，数据库正在积极的手动开发（它是一个语言学习应用程序，所以它存储词汇，语法概念等）。我更喜欢在本地django / postgres环境中进行这种开发。

但是，我不想不断地从实时版本中擦除User表！

我对Postgres很新，所以请不要以为我知道我在做什么 - 这样的一种模式是正确的吗？

解决方案

要仅仅备份一张表，请使用 COPY ：

  COPY user_tbl TO'/ path / to / file'; 
  

或 pg_dump 从shell：

  pg_dump -t user_tbl mydb> user_tbl.sql 
  

然后删除数据库，恢复新版本，空 user_tbl 并使用 COPY FROM 恢复一个表：

  COPY user_tbl FROM'/ path / to / file'; 
  

或使用 psql ：

  psql -f user_tbl.sql mydb 
  

确定依赖表

快速肮脏

没有COPY ...级联。识别依赖表的最简单的方法是启动一个事务，调用 TRUNCATE tbl CASCADE 并记录你得到的通知：

  BEGIN; 
 TRUNCATE user_tbl CASCADE; 
 
注意：截断级联到表tbl1
注意：截断级联到表tbl2
注意：截断级联到表tbl3
  

然后回滚事务 - 所以没有什么实际的变化：

  ROLLBACK; 
  

小心点。如果您 COMMIT 截断通过。

缓慢而确定

$ b $嗯，实际上并不是慢，但代码复杂得多。但是，这并不会对涉及的表进行排他性的锁定，所以它更清洁和更安全：

  WITH RECURSIVE x AS（
 SELECT conrelid :: regclass 
 FROM pg_constraint 
 WHERE confrelid ='user_tbl':: regclass 
 
 UNION 
 SELECT p.conrelid :: regclass 
 FROM x 
 JOIN pg_constraint p ON p.confrelid = x.conrelid 
）
 SELECT conrelid :: text AS tbl 
 FROM x; 
  

返回：

 code> tbl 
 ------ 
 tbl1 
 tbl2 
 tbl3 
  

b
$ b

我使用 递归CTE （需要PostgreSQL 8.4或更高版本）目录表 pg_constraint ，因为每个表都可以依次依赖。

使用 UNION ，而不是 UNION ALL 以避免可能直接或间接与多个外键链接的表的多个评估。

I have a Django app, the database for which is under active manual development (it's a language-learning app, so it stores vocabulary, grammatical concepts, etc). I'd prefer to do that development in my local django/postgres environment.

However, I don't want to be constantly wiping out the User table from the live version!

I'm very new to Postgres, so please don't assume I know what I'm doing here - would some kind of schema be the right approach here?

解决方案

To just backup the one table, use COPY from inside the database:

COPY user_tbl TO '/path/to/file';

or pg_dump from the shell:

pg_dump -t user_tbl mydb > user_tbl.sql

Then drop the database, restore your new version, empty user_tbl and use COPY FROM to restore the one table:

COPY user_tbl FROM  '/path/to/file';

or restore the backup with the one table from the shell with psql:

psql -f user_tbl.sql mydb

Identify depending tables

Quick and dirty

There is no such thing as "COPY ... CASCADE". The simplest method to identify depending tables would be to start a transaction, call TRUNCATE tbl CASCADE and record the notices you get:

BEGIN;
TRUNCATE user_tbl CASCADE;

NOTICE:  truncate cascades to table "tbl1"
NOTICE:  truncate cascades to table "tbl2"
NOTICE:  truncate cascades to table "tbl3"

Then roll back the transaction - so nothing actually changes:

ROLLBACK;

Careful with that. If you COMMIT the truncate goes through.

Slow and sure

Well, not actually "slow", but the code is a lot more complex. This doesn't take an exclusive lock on the involved tables, though, so it's a lot cleaner and safer:

WITH RECURSIVE x AS (
    SELECT conrelid::regclass
    FROM   pg_constraint
    WHERE  confrelid = 'user_tbl'::regclass

    UNION
    SELECT p.conrelid::regclass
    FROM   x
    JOIN   pg_constraint p ON p.confrelid = x.conrelid
    )
SELECT conrelid::text AS tbl
FROM   x;

Returns:

tbl
------
tbl1
tbl2
tbl3

I use a recursive CTE (requires PostgreSQL 8.4 or later) on the catalog table pg_constraint, because each table can have dependencies in turn.
Use UNION, not UNION ALL to avoid multiple evaluation of tables that might be linked with multiple foreign keys directly or indirectly.

这篇关于用其他数据库的数据替换除数据库之外的数据库的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！