Thread: Is there any way to import a portion of a large database
I have a production Postgresql 9 database of 2 TB+. For development purpose, I have to import this database in development server where I have only 1 TB of disk space. No more space can be added at present. Is there any way so that I might import the whole schema definition of the database with a portion of data in my development server?
On 09/20/2011 02:35 PM, AI Rumman wrote: > I have a production Postgresql 9 database of 2 TB+. For development > purpose, I have to import this database in development server where I > have only 1 TB of disk space. No more space can be added at present. > Is there any way so that I might import the whole schema definition of > the database with a portion of data in my development server? You can do a schema-only dump and restore that, but as for the data ... that's something you really have to figure out yourself. PostgreSQL can't just randomly select rows to export, because there will be foreign key relationships that would prevent successful restoration. Additionally, in most databases some tables will have to be exported in their entirety, as they'll contain lookup tables or other data the app requires. There are ETL tools like Talend that may be able to help simplify this. I haven't looked into them. Personally for simpler databases I'd probably just hack something together using an appropriate scripting language and COPY (SELECT...). -- Craig Ringer
On 09/19/11 11:35 PM, AI Rumman wrote: > I have a production Postgresql 9 database of 2 TB+. For development > purpose, I have to import this database in development server where I > have only 1 TB of disk space. No more space can be added at present. > Is there any way so that I might import the whole schema definition of > the database with a portion of data in my development server? well, you certainly can dump and import just the schema... how are you going to have this hypothetical partial dump program decide WHAT part of the various database tables to dump? is the bulk of this data in 1 or 2 very large tables? you could dump everything but those 1 or 2 large tables, then use some sort of COPY to CSV on those specific tables that only selects some of the data (maybe by date, or by serial number, or some other suitable key). -- john r pierce N 37, W 122 santa cruz ca mid-left coast