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 only takes a minute to sign up.
Sign up to join this community
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??
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:
echo is just prettyfication: it prints the name of the table and underlines it like a title,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),psql on the next line copies a table in the source db into a csv file,psql copies from that csv file into a table in the destination db.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:
ON DELETE CASCADE - you can achieve this by a well targeted sed command, for exampleI 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.
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;
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
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
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.
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)
