Backup a sample of a PostgreSQL database

Question

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??

Answer 1

I had a similar problem, I wanted to copy the most recent rows from a few tables from one db to another hosted on a different server, and I ended up writing a bash script that executes the pg_dump command followed by various psql commands:

#!/bin/bash
declare -a arr=("my_table_1" "my_table_2" "my_table_3")
startdate=2014-12-01
enddate=2014-12-30
for table in  "${arr[@]}"
do
    echo -e \\n$table\\n$(for each in $(seq 1 ${#table}); do printf "-"; done)
    pg_dump -h host_name_1 --schema-only -t $table db_name_1 -U my_username -c | psql -h host_name_2 -U my_username db_name_2
    psql -h host_name_1 -U my_username db_name_1 -c "\copy (select * from $table where date >='$startdate' and date <='$enddate' order by date desc) to '/tmp/data.csv' csv header;"
    psql -h host_name_2 -U my_username db_name_2 -c "\copy $table from '/tmp/data.csv' csv header"
done

Line by line in the body of the loop:

1. the echo is just prettyfication: it prints the name of the table and underlines it like a title,
2. the pg_dump command generates some SQL to copy the table schema from the source and performs a few more SQL housekeeping commands (e.g. the -c flag means that the SQL will contain a command to delete the table first before creating it), and this SQL is piped with | to psql pointing at the destination (you can try running just the pg_dump part at the comand line, pg_dump -h host_name_1 --schema-only -t $table db_name_1 -U my_username -c, and see the SQL it generates),
3. the psql on the next line copies a table in the source db into a csv file,
4. the final psql copies from that csv file into a table in the destination db.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

I had the same problem recently and I couldn't find any sufficiently good solution. So I created a small tool to dump a sample of data from the PostgreSQL database: https://github.com/dankeder/pg_dump_sample.

Answer 6

To what it worth, this is the way I did it.

I've used pg_dump normally to dump the whole table. Then, I used head to load only part of it:

head sample_table.sql -n 1000 > sample_table.1000.sql
psql -h localhost -U postgres my-db < sample_table.1000.sql

It does not cut exactly 1000 records. In my case it was 952 (because there are number of lines at the top of the file)