I want to copy data from one PG database to another, without moving the data to my local and back to the db -
pg_dump -U postgres source_db_name | psql -U postgres target_db_name
pretty certain this pipe is moving the data across the network, to my machine and back - I am wondering if there is something I can do more akin to this with a shell session:
postgres=> cp -U postgres -S source_db_name -T target_db_name
obviously the above command is ficticious
is there any such way?
If you run that command locally, then it is your local environment that will pg_dump the first database and pipe the result into the target database, so your problem-statement was accurate.
Now, in order to solve that, as you pointed out, you want a server that's different from your local to do the dumping and piping. In order to achieve this you need to delegate the task to the remote server. If you have ssh access to a remote server, then you can do something like this:
ssh [email protected] -t 'pg_dump -U postgres source_db_name | psql -U postgres target_db_name'
Of course, you can achieve this via other applications too, but the main idea is that you will need to tell the server you have chosen as your victim for dumping to do the dumping and the piping into the target database.
