Powershell pipe psql commands using DB uri

Question

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?

As an aside: if you place | at the end of your lines, you don't need line-continuations (` at the very end of each line) in Windows PowerShell. In PowerShell (Core) 7, you don't need line-continuations either way. — mklement0
– mklement0, Commented Oct 15, 2024 at 22:37
A workaround for this is to set the remote DB pass in $env:PGPASSWORD. Then you can use a connection URI for master DB and connection with params for remote DB — ionutnespus
– ionutnespus, Commented Oct 16, 2024 at 7:42

Pepe N O · Accepted Answer · 2024-10-17 19:32:26Z

1

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.

answered Oct 17, 2024 at 19:32

Pepe N O

2,3841 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ionutnespus Over a year ago

Thank you for your response! Having the pgpass.conf on another location than the executing script introduces another dependency, that I'm trying to avoid. However, if no other suggestion is given, this could be a solution.

Collectives™ on Stack Overflow

Powershell pipe psql commands using DB uri

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related