Powershell pipe psql commands using DB uri

I'm trying to move records from one database to another database using a powershell script with psql, in a single call. The select query result is copied to stdout, which is used in the second pipe command as an input for the insert query. The databases are on different locations. The source and destination tables have the same schema.

I tried locally to pipe the commands using psql with connection params and it worked, because there was no need to change the DB connection password.

Here is the the working block:

$env:PGPASSWORD = 'local_pass';    
# execute 2 queries in sequence 
$res = & $psqlPath -U 'user' -d 'localMasterDB' -h $'local' `
               -c "\copy (SELECT * FROM target_table WHERE id IN ( $idsToMove)) TO stdout" `
       | & $psqlPath -U 'postgres' -d 'localReplicaDB' -h 'local' `
               -c 'COPY target_table FROM stdin' 

Because I need different DB connections, I must define 2 different DB passwords.

This can be done by defining the DB connection with connection URIs (https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS).

This is the result:

$res =  "\copy (SELECT * FROM target_table WHERE id IN ( $idsToMove)) TO stdout" `
         | & $psqlPath "postgresql://user:local_pass@master_db:5432/target_table" `
         | 'COPY target_table FROM stdin' `  # <== this is where the second psql command should start
         | & $psqlPath -U 'postgres' -d 'remote_db'

Is there a way to pipe 2 psql commands that use db connection uris?

Is there another similar way to achieve moving records with psql?

You can store passwords for multiple hosts using pgpass.conf file, as:

mkdir $env:APPDATA\postgresql\
 
edit $env:APPDATA\postgresql\pgpass.conf

then add lines of the following format:

hostname:port:database:username:password

after that, when executing psql, those passwords will be identified by the combination -h 'hostname' -U 'username' -d 'database' and applied. Therefore your first command would work without problem, making use of this, like

& $psqlPath -U 'user' -d 'localMasterDB' -h $local `
-c "\copy (SELECT * FROM target_table WHERE id IN ( $idsToMove)) TO stdout" | 
& $psqlPath -U 'postgres' -d $remote_db -h $remote_host `
-c 'COPY target_table FROM stdin'

displaying the output

COPY N

For further reference the official docs on pgpass.