I have been through the same problem recently and can comment on some attempts and my conclusions after a lot of research into it.
In my case I had to partition some data that would be distributed in different databases. But note that we are dealing with the same fundamental problem that is partial data migration.
The first problem is regarding data extraction,
pg_dump does not provide any dynamic and consistent partial data extraction mechanism.
You can do a full backup and try to erase data you don't want to use, but as you have already realized, this is a costly task and demands a lot of runtime for large volumes of data. And depending on the complexity of your database structure, assembling the delete scripts can be quite difficult. What's more, if your bank is constantly evolving this method is not reusable if you want to extract partial data later.
You will find many suggestions from people saying to use the
COPY feature, but all in all I can say that it is extremely limited to the complexity of this task. Because it would be necessary to assemble a data select from all tables consistently and then execute them in a single transaction. Again, since your bank is dynamic and constantly evolving, this method is not reusable either. Because you would have to rewrite all selects every time you were extracting partial data.
Usually the partial data migration comes from a specific table that will restrict the other data, we will name it as a restrictive table. That said we can consider some more possibilities.
Let's assume a case where your primary bank has a table called user (restrictive table) with 100 records and you want to migrate all your bank records that link to the first 10 records in that table.
- We can only dump/restore the structure.
- We can generate a sql with the data from the 10 user table records
we need and insert it into the database.
- Then we can dump all data in simple sql disregarding the user table.
We can run this sql script without transaction block and let the key
constraint check do the dirty work.
- As in the previous case, this can take a long execution time
depending on the volume of data, but this solution is functional and
Note that your database cannot contain cyclic key problems (This is the condition to dump data only correctly).
Also note that the dependency level of your restrictive table must be the lowest minimum, otherwise an adaptation would have to be made to this methodology, which I will not write because the text is already getting too large.
That said, what really worked for me was using the Jailer tool. This tool is developed in java and using ORM it can map the key constraint set of your database and assemble a data extraction model as you configure it.
The output is a simple sql script with partial data consistent with the restrictive rules that have been set.
If you have any questions about its use I can help you understand and set up your configuration environment.
A similar question was asked in Stack Overflow.