Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to back up a PostgreSQL database with only a fraction of the data (1000 Rows from each table)?

I am able to back up that database using pg_dump. In some case I need db with minimum data.

pg_dump --host=localhost --port=5432 --username=postgres --password 
        --column-inserts --schema=test  testdb > test_backup.sql

How can I modify the above command to take the back up with 1000 number of data??

share|improve this question
    
That's not possible with Postgres. You might want to take a look at Jailer which promises to be able to do this honoring FK constraints: sourceforge.net/projects/jailer –  a_horse_with_no_name Dec 20 '12 at 9:53
add comment

migrated from stackoverflow.com Dec 21 '12 at 0:02

This question came from our site for professional and enthusiast programmers.

3 Answers

As stated in the other answers, you cannot do this with pg_dump. And there is an additional problem, too: if you have foreign keys between your tables, you have to retain the corresponding rows.

However, besides third-party tools (one of them was suggested by a_horse_with_no_name above), I would try the following:

  1. create a full dump
  2. before restoring, change the foreign key definitions (if necessary) as ON DELETE CASCADE - you can achieve this by a well targeted sed command, for example
  3. restore to a new database
  4. identify the tables which has foreign key dependencies
  5. delete from those everything except the rows to be retained, this will cascade to the dependencies
  6. now you have a database with the desired data only, so make a data-only dump
  7. create a schema-only dump from the original database, restore it to a new database
  8. restore the data-only dump to this database
  9. if this didn't work as expected, blame me

I haven't tried it yet, any suggestions or corrections are welcome. I see a problematic point in the 4th point: if your dependency tree consist disjoint subtrees, finding the corresponding row sets in all of them can be tricky.

share|improve this answer
add comment

You can not achieve this only with pg_dump command.

You can do it by:

Dump only the structure of the whole database, and use the copy command to save 1000 row from eatch table.

For example: dump only structure:

pg_dump --host=localhost --port=5432 --username=postgres --password --schema-only  testdb > test_backup.sql

And list of copy commands called from stored procedure:

CREATE OR REPLACE FUNCTION _save_top_1000_row_tables(chemin file_path)
 RETURNS character varying AS
$BODY$declare 
_temps timestamp without time zone;
begin
execute 'copy (SELECT * FROM table1 limit 1000 offset 0) TO ''' || file_path||'table1.txt'''; 
execute 'copy (SELECT * FROM table2 limit 1000 offset 0) TO ''' || file_path||'table2.txt'''; 
execute 'copy (SELECT * FROM table3 limit 1000 offset 0) TO ''' || file_path||'table3.txt'''; 

return ('OK');
end;$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;   

References: dump, copy

share|improve this answer
    
But that won't take foreign keys into account.... –  a_horse_with_no_name Dec 20 '12 at 9:52
    
Can you update your question by giving more informations about your database shema? I agree that it's not simple if there is lot of foreign keys. –  Houari Dec 20 '12 at 10:01
    
You'll get ERROR: must be superuser to COPY to or from a file unless you do this as a superuser. –  dezso Dec 21 '12 at 2:56
add comment

I think you cannot just export top NNN rows per table using only pg_dump, but you probably may write some awk script in order to filter out the unrequested records. Once you tested the awk script, you may just pipe its input from pg_dump, so you will not have temporary large file for dump. Sadly, you still will dump all your data.

This may be used as a simple AWK you may use as an example:

BEGIN {incopy=0; FS=""}
incopy==0 && $0~/^COPY .*$/ { print; incopy=1; counter=0; next}
incopy==1 && $0~/^\\\.$/ { print; incopy=0; next}
incopy==1 && counter>1000 {next}
incopy==1 {counter++}
{print}

please note that this does not work if your first column on any record starts with "COPY ".

Usage: pg_dump databasename | awk -f scriptname > dumpfile
share|improve this answer
    
This has the same problem mentioned in a comment earlier: how do you take foreign keys into account? –  dezso Dec 21 '12 at 10:11
    
In the same way: you get errors :-) –  eppesuig Dec 21 '12 at 10:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.