Re: How to create tables and copy records in blocks?

On 02/11/2015 03:55, Chuck Roberts wrote:
>
> 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