Geek Answers Handbook

How to duplicate the amount of data in a PostgreSQL database?

To evaluate the loading of our platform (django + postgresql), I would like to literally duplicate the amount of data in the system. Its a little harder to create mocks that could emulate different types of objects (since we have a very complex data model). Is there a way to create a duplicate database, redefine primary keys and unique fields for unused ones, combine them with the original?

+4
source share
1 answer

(I) Explanation of principle

To illustrate the principle in a clear way, this explanation assumes the following:

  • Each table has a bigserial primary key column named id
  • ( )
  • .

:

  • , . , , ​​ ( ).
  • script (3)

  • <table_schema > . <table_name > (2) :

    /*
Creating a lookup table which contains ordered pairs (id_old, id_new). 
For every existing row in table <table_schema>.<table_name>, 
new row with id = new_id will be created and with all the other fields copied. Nextval of sequence <table_schema>.<table_name>_id_seq is fetched to reserve id for a new row.
*/
CREATE TABLE _l_<table_schema>_<table_name> AS 
SELECT id as id_old, nextval('<table_schema>.<table_name>_id_seq') as id_new
FROM <table_schema>.<table_name>;

/*
This part is for actual copying of table data with preserving of referential integrity.

Table <table_schema>.<table_name> has the following fields:
id - primary key
column1, ..., columnN - fields in a table excluding the foreign keys; N>=0;
fk1, ..., fkM - foreign keys; M>=0;

_l_<table_schema_fki>_<table_name_fki> (1 <= i <= M) - lookup tables of parent tables. We use LEFT JOIN because foreign key field could be nullable in general case.

*/
INSERT INTO <table_schema>.<table_name> (id, column1, ... , columnN, fk1, ..., fkM)
SELECT tlookup.id_new, t.column1, ... , t.columnN, tablefk1.id_new, ..., tablefkM.id_new
FROM <table_schema>_<table_name> t
INNER JOIN _l_<table_schema>_<table_name> tlookup ON t.id = tlookup.id_old
LEFT JOIN _l_<table_schema_fk1>_<table_name_fk1> tablefk1 ON t.fk1 = tablefk1.id_old
...
LEFT JOIN _l_<table_schema_fkM>_<table_name_fkM> tablefkM ON t.fkM = tablefkM.id_old;

  • .

(II)

, "" github .

+1

More articles:


All Articles