0

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?

1 Answer 1

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...

2
  • The first command it's the same as mine except for the LIMIT 5, but this has make me notice that psql returns the output into STDOUT when the query has finished. That's why I wasn't seen anything. I don't know how this is going to work for big queries, maybe for big queries the output it's sent on batches, but if it's always sent at the end the system it's going to run out of memory before the copy query ends. Right now I'm running the piped command but the destination table it's empty meanwhile the source DB copy command it's at 100% core usage... I don't know how this it's going to end.
    – Héctor
    Feb 6, 2023 at 16:53
  • It worked but It's needed to keep the SELECT * FROM in the ultimate version because if not nothing it's copied due to being an hypertable (an empty shell that only points to other tables). I will edit your answer if you don't mind.
    – Héctor
    Feb 22, 2023 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.