Move timescaledb hypertable from a postgresql server to another one

Question

I want to move a timescaledb hypertable that's on the terabytes order. I wanted to do something like the following:

pg_dump -t "schema1"."table1" -h host1_ip -U username1 dbname1 | psql -h host2_ip -U username2 dbname2;

But this doesn't seem to work with an hypertable and only copies the table structure. Timescaledb docs recommend:

psql -d <SOURCE_DB_NAME> -c "\COPY (SELECT * FROM <TABLE_NAME>) TO <FILENAME>.csv DELIMITER ',' CSV"

and then:

psql -d <DATABASE_NAME> -c "\COPY <TABLE_NAME> FROM <FILENAME>.csv CSV"

But this will create a huge file that will need to be written on disk, then sent through the network, to be writted again on disk and then copied into the new DB. I was trying to pipe directly the copy to to the copy from to avoid the unnecessary writting to disk but I'm not being able to.

I'm trying the following:

psql -U user -h source_server -d source_db -c "COPY (SELECT * FROM source_table) TO STDOUT DELIMITER ',' CSV;"

to pipe then into psql copy from, but it isn't sending anything to the standard output. What I'm doing wrong?

Answer 1

First, let's make sure that you are getting any data from the source:

psql -U user -h source_server -d source_db -c "COPY (SELECT * FROM <<source>> 
 LIMIT 5) TO STDOUT WITH CSV HEADER;"

If that works, you can just skip the SELECT:

psql -U user -h source_server -d source_db -c "COPY (SELECT * FROM <<source>>) TO STDOUT WITH CSV HEADER;" | psql -d <DATABASE_NAME> -c "COPY <TABLE_NAME> FROM STDIN WITH CSV HEADER;" 

Note that there are a couple of syntax changes in the destination also...