【转】在两个PostgreSQL数据库服务器之间使用copy命令迁移数据

https://www.cybertec-postgresql.com/en/copy-in-postgresql-moving-data-between-servers/

The COPY command in PostgreSQL is a simple means to copy data between a file and a table. COPY can either copy the content of a table to or from a table. Traditionally data was copied between PostgreSQL and a file. However, recently a pretty cool feature was added to PostgreSQL: It is now possible to send data directly to the UNIX pipe.

COPY … TO PROGRAM: Sending data to the pipe

The ability to send data directly to the UNIX pipe (or Linux command line) can be pretty useful. You might want to compress your data or change the format on the fly. The beauty of the UNIX shell is that it allows you all kinds of trickery.

If you want to send data to an external program – here is how it works:

1 2 3

test=# COPY (SELECT * FROM pg_available_extensions) TO PROGRAM 'gzip -c > /tmp/file.txt.gz'; COPY 43

In this case the output of the query is sent to gzip, which compresses the data coming from PostgreSQL and stores the output in a file. As you can see this is pretty easy and really straight forward.

Copying data between PostgreSQL and other machines

However, in some cases users might desire to store data on some other machine. Note that the program is executed on the database server and not on the client. It is also important to note that only superusers can run COPY … TO PROGRAM. Otherwise people would face tremendous security problems, which is not desirable at all.

Once in a while people might not want to store the data exported from the database on the server but send the result to some other host. In this case SSH comes to the rescue. SSH offers an easy way to move data.

Here is an example:

1	echo "Lots of data" | ssh user@some.example.com 'cat > /directory/big.txt'

In this case “Lots of data” will be copied over SSH and stored in /directory/big.txt.

The beauty is that we can apply the same technique to PostgreSQL:

1 2 3

test=# COPY (SELECT * FROM pg_available_extensions) TO PROGRAM 'ssh user@some.example.com ''cat > /tmp/result.txt'' '; COPY 43

To make this work in real life you have to make sure that SSH keys are in place and ready to use. Otherwise the system will prompt for a password, which is of course not desirable at all. Also keep in mind that the SSH command is executed as “postgres” user (in case your OS user is called “postgres” too).