Source db has Postgresql 8.4. I want to copy some tables and records to Ubuntu 14.04 which has psql 9.3.
I need to create a test database, but I only need to copy some tables, with some of the records to the test system. I thought there was a way to dump the table layout as an SQL CREATE statement, then dump the records as INSERT statements. I'd like to dump 1000 records from each table at a time so as not to put a strain on the production server. So my job would run as a cron job at night.
I've Googled a bit on this but can't seem to find anything on this. How would I go about using SQL to do this? Is there another way to get blocks of 1000 records to a brand new test db which will be a subset of our prodution db?
Thank you.
Chuck
Hello Chuck, You should use the pg_dump from 9.3 to transfer the data. You can use the -s flag from pg_dump just to dump the schema, and psql to load it in, and you can do it per-table if you add -t. After that, you can dump all the data for small tables with -a and -t, or you can use the \copy statement from psql to use a select statement to get out particular rows, limiting the range as needed for your data. Something like this: psql -U <user> <database> -c " \copy (SELECT id, data FROM foo WHERE id < 1000) TO 'first_1000.csv' WITH CSV HEADER NULL AS '' " and copy them back in something like this psql -U <user> <database> -c " \copy foo (id, data) FROM 'first_1000.csv' WITH CSV HEADER NULL AS '' " The documentation here will be invaluable, and I recommend reading it carefully for what you are doing. http://www.postgresql.org/docs/9.3/static/app-pgdump.html http://www.postgresql.org/docs/9.3/static/app-psql.html This is very basic, and you'll probably want to script it somehow, and sync the results over to your test server. Hope this helps.=) Regards, Lacey
