0 like 0 dislike
234 views
asked by (2k points)  
Is it possible to back up a database with only a fraction of the data (500 Rows from each table) ?

Please log in or register to answer this question.

2 Answers

1 like 0 dislike
answered by  
edited by

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:

  •     the echo is just prettyfication: it prints the name of the table and underlines it like a title,
  •     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),
  •     the psql on the next line copies a table in the source db into a csv file,
  •     the final psql copies from that csv file into a table in the destination db.
0 like 0 dislike
answered by (2k points)  

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;

 

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
0 answers
2 like 0 dislike
1 answer
0 like 0 dislike
0 answers
0 like 0 dislike
0 answers
...