Postgres dump of only parts of tables for a dev snapshot

Postgres dump of only parts of tables for a dev snapshot

On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 gigs in size.

The challenge is that the data for our business entities are scattered across many tables. We want to create some sort of filtered snapshot so that only some of the entities are included in the dump. That way we can get fresh snapshots every month or so for dev and testing.

For example, let's say we have entities that have these many-to-many relationships:

• Company has N Divisions
• Division has N Employees
• Employee has N Attendance Records

There are maybe 1000 companies, 2500 divisions, 175000 employees, and tens of millions of attendance records. We want a replicable way to pull, say, the first 100 companies and all of its constituent divisions, employees, and attendance records.

We currently use pg_dump for the schema, and then run pg_dump with --disable-triggers and --data-only to get all the data out of the smaller tables. We don't want to have to write custom scripts to pull out part of the data because we have a fast development cycle and are concerned the custom scripts would be fragile and likely to be out of date.

How can we do this? Are there third-party tools that can help pull out logical partitions from the database? What are these tools called?

Any general advice also appreciated!

On your larger tables you can use the COPY command to pull out subsets...

COPY (SELECT * FROM mytable WHERE ...) TO '/tmp/myfile.tsv'

COPY mytable FROM 'myfile.tsv'

https://www.postgresql.org/docs/current/static/sql-copy.html

You should consider maintaining a set of development data rather than just pulling a subset of your production. In the case that you're writing unit tests, you could use the same data that is required for the tests, trying to hit all of the possible use cases.

I don't know about any software which already does this, but I can think of 3 alternative solutions. Unfortunately, they all require some custom coding.

1. 모든 스키마로 집합만 합니다.INSERT INTO copy.tablename SELECT * FROM tablename WHERE ...그리고 그것을 버려라.

2. SQL 문으로 데이터를 덤프하기 위한 스크립트를 직접 작성합니다.저는 과거에 이 접근법을 사용했고 그것은 단지 20-30줄의 PHP만을 사용했습니다.

3. 단일 테이블을 덤프할 때 -t 스위치와 함께 조건을 수락하도록 pg_dump를 수정합니다.

http://jailer.sourceforge.net/ 은 이렇게 합니다.

언급URL : https://stackoverflow.com/questions/1745105/postgres-dump-of-only-parts-of-tables-for-a-dev-snapshot