Pivotal Knowledge Base

Follow

How to use gptransfer

Problem

How do I use gptransfer to transfer data from one database to another database.

Example

Here is a simple example of how this works (both the database, port 4300 and port 5181, runs on the same host here) , for more options available for gptransfer utility , check the help page using the command "gptransfer --help"

So my current configuration for this test is

  • My Destination host: mdw
  • My Destination Port: 5181
  • My source host: mdw
  • My Source port: 4300

In the below example I want to copy from source host:port - mdw:4300, all of the contents on database test2 (Only table:test11 currently available) and just one table public.t1 under the database test to the destination database mdw:5181.

So the gptransfer command would be.

gpadmin:Fullrack@mdw $ gptransfer -t test.public.t1 -d test2 --dest-host=mdw --truncate --dest-port=5181 --source-port=4300 --source-map-file=/data/home/gpadmin/host-map
20141004:08:55:07:032466 gptransfer:mdw:gpadmin-[INFO]:-Starting gptransfer with args: -t test.public.t1 -d test2 --dest-host=mdw --truncate --dest-port=5181 --source-port=4300 --source-map-file=/data/home/gpadmin/host-map
20141004:08:55:07:032466 gptransfer:mdw:gpadmin-[INFO]:-Retrieving configuration of source Greenplum Database...
20141004:08:55:08:032466 gptransfer:mdw:gpadmin-[INFO]:-Retrieving configuration of destination Greenplum Database...
20141004:08:55:08:032466 gptransfer:mdw:gpadmin-[INFO]:-Retrieving list of source tables...
20141004:08:55:09:032466 gptransfer:mdw:gpadmin-[INFO]:-Checking for gptransfer schemas...
20141004:08:55:11:032466 gptransfer:mdw:gpadmin-[INFO]:-Retrieving list of destination tables...
20141004:08:55:11:032466 gptransfer:mdw:gpadmin-[INFO]:-Reading source host map file...
20141004:08:55:11:032466 gptransfer:mdw:gpadmin-[INFO]:-Building list of source tables to transfer...
20141004:08:55:12:032466 gptransfer:mdw:gpadmin-[INFO]:-gptransfer will use "fast" mode for transfer.
20141004:08:55:12:032466 gptransfer:mdw:gpadmin-[INFO]:-Validating options...
20141004:08:55:12:032466 gptransfer:mdw:gpadmin-[INFO]:-Validating source host map...
20141004:08:55:12:032466 gptransfer:mdw:gpadmin-[INFO]:-Validating transfer table set...
20141004:08:55:12:032466 gptransfer:mdw:gpadmin-[INFO]:-Using batch size of 2
20141004:08:55:12:032466 gptransfer:mdw:gpadmin-[INFO]:-Using sub-batch size of 12
20141004:08:55:12:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating work directory...
20141004:08:55:13:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating database test...
20141004:08:55:45:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating schema public in database test...
20141004:08:55:46:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating schema public in database test2...
20141004:08:55:47:032466 gptransfer:mdw:gpadmin-[INFO]:-Starting transer of test2.public.test11 to test2.public.test11...
20141004:08:55:47:032466 gptransfer:mdw:gpadmin-[INFO]:-Starting transer of test.public.t1 to test.public.t1...
20141004:08:55:48:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating target table test2.public.test11...
20141004:08:55:48:032466 gptransfer:mdw:gpadmin-[INFO]:-Retrieving schema for table test2.public.test11...
20141004:08:55:48:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating target table test.public.t1...
20141004:08:55:48:032466 gptransfer:mdw:gpadmin-[INFO]:-Retrieving schema for table test.public.t1...
20141004:08:55:49:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating FIFO pipes for source table test2.public.test11...
20141004:08:55:49:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating FIFO pipes for source table test.public.t1...
20141004:08:55:50:032466 gptransfer:mdw:gpadmin-[INFO]:-Starting gpfdist for readable external table for table test2.public.test11...
20141004:08:55:50:032466 gptransfer:mdw:gpadmin-[INFO]:-Starting gpfdist for readable external table for table test.public.t1...
20141004:08:55:51:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating source writable external table for source table test2.public.test11...
20141004:08:55:51:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating source writable external table for source table test.public.t1...
20141004:08:55:51:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating external table for destination table test2.public.test11...
20141004:08:55:51:032466 gptransfer:mdw:gpadmin-[INFO]:-Transfering data test2.public.test11 -> test2.public.test11...
20141004:08:55:51:032466 gptransfer:mdw:gpadmin-[INFO]:-Creating external table for destination table test.public.t1...
20141004:08:55:51:032466 gptransfer:mdw:gpadmin-[INFO]:-Transfering data test.public.t1 -> test.public.t1...
20141004:08:55:56:032466 gptransfer:mdw:gpadmin-[INFO]:-Closing named pipes for table test2.public.test11...
20141004:08:55:56:032466 gptransfer:mdw:gpadmin-[INFO]:-Closing named pipes for table test.public.t1...
20141004:08:55:57:032466 gptransfer:mdw:gpadmin-[INFO]:-Dropping external table for destination table test.public.t1...
20141004:08:55:57:032466 gptransfer:mdw:gpadmin-[INFO]:-Dropping writable external table for source table test.public.t1...
20141004:08:55:57:032466 gptransfer:mdw:gpadmin-[INFO]:-Stopping gpfdist for source table test.public.t1...
20141004:08:55:58:032466 gptransfer:mdw:gpadmin-[INFO]:-Removing FIFO pipes for source table test.public.t1...
20141004:08:56:01:032466 gptransfer:mdw:gpadmin-[INFO]:-Dropping external table for destination table test2.public.test11...
20141004:08:56:01:032466 gptransfer:mdw:gpadmin-[INFO]:-Dropping writable external table for source table test2.public.test11...
20141004:08:56:02:032466 gptransfer:mdw:gpadmin-[INFO]:-Stopping gpfdist for source table test2.public.test11...
20141004:08:56:03:032466 gptransfer:mdw:gpadmin-[INFO]:-Removing FIFO pipes for source table test2.public.test11...
20141004:08:56:03:032466 gptransfer:mdw:gpadmin-[INFO]:-Finished transfering table test.public.t1
20141004:08:56:08:032466 gptransfer:mdw:gpadmin-[INFO]:-Finished transfering table test2.public.test11
20141004:08:56:08:032466 gptransfer:mdw:gpadmin-[INFO]:-Removing work directories...
20141004:08:56:13:032466 gptransfer:mdw:gpadmin-[INFO]:-Finished.

where "--source-map-file=/data/home/gpadmin/host-map" is based on my segment configuration on source host , so the content would look like.

[root@mdw ~]# cat /data/home/gpadmin/host-map
sdw10,172.28.8.10
sdw8,172.28.8.8

Verifying the destination for the content.

gpadmin=# \c test2
You are now connected to database "test2" as user "gpadmin".
test2=# \d
              List of relations
 Schema |  Name  | Type  |  Owner  | Storage
--------+--------+-------+---------+---------
 public | test11 | table | gpadmin | heap
(1 row)

test2=# \c test
You are now connected to database "test" as user "gpadmin".
test=# \d
             List of relations
 Schema | Name | Type  |  Owner  | Storage
--------+------+-------+---------+---------
 public | t1   | table | gpadmin | heap
(1 row)

Please check here for an alternative method to transfer data between database (if the data set is small).

The same command can be altered to transfer data between database within the same host, for example if i need transfer everything from source database to destination database on the same GPDB cluster, the command would look like (destination and source host / port is the same).

gptransfer -d <source-database> --dest-database=<destination database> --dest-host=mdw --truncate --dest-port=5432 --source-port=5432 --source-map-file=/data/home/gpadmin/host-map

Related articles

Comments

Powered by Zendesk