Move table from server1 to server2

Question

I have two Postgresql servers (Windows), and I am trying to transfer a table from server1 to server2. This table is around 200 MB size as it contains binary data. I want to put the table into a usb stick and then move it to the second server. (assume the two servers are not connected by a LAN). What is the simplest way to do that? Can you describe the way with command.

Answer 1

The easiest way would probably be to use pg_dump.

I haven't used it on Windows so I don't know the actual path to it, but it should be in the Postgres\bin directory and you need to execute it in a shell window (like PowerShell or CMD).

Assuming you have console access to each server, and that the table already exists in the second database:

pg_dump -a -b -Fc -t <tablename> <databasename> > <path to dump file>

Then when you have moved it to the new server.

pg_restore -a -Fc -d <databasename> <path to dump file>

If you don't have direct access to each server, then you need to add the connection parameters to each command:

-h <server> -U <username>

Quick description of the parameters:

• -a : dumps only the data and not the schema definition. This should be removed if the table is not already in place on the new server
• -b : dumps blobs. You mentioned there are binary data in the table, if they are stored as large objects, this parameter needs to be included, otherwise you can skip it.
• -Fc : The format to dump the data as. c stands for Postgres custom format, which is better suited for moving binary data. You could change it to d to use a directory format since you're using 9.2, but I prefer the custom format still. d however is useful when dumping large databases since it stores each table in one file within the specified directory.
• -t : Specifies that you want to dump a table and not the entire database.
• -d : the database that you want to restore to (this parameter can be used in pg_dump as well, but not needed if specified as above)

There is a possibility that you need to add the -t parameter to the restore as well, but as far as I remember, it should not be necessary since you only have that table in the dump (however, if you had several tables in the dump, for instance if it is a complete dump of the database, this can be used to only restore parts of the database).