Using Go to copy from one postgres db to another

Question

I'd like to use Go to copy postgres data from database A, view A into Database B, table B. This will be done for several dozen tables and I'd like to take advantage of the concurrency offered by Go.

I can do this using psql commandline that looks something like:

psql -h hostServer -p 5432 -U dbUser -d dbName \
    -c "\copy (Select * From myView) TO STDOUT" \
    | psql -h destinationHost -p 5432 -U dbUser -d destinationDB \
    -c "\copy destinationTable FROM STDIN"

Or in Python by running the above command or by using os.pipe and psycpog2's copy_from/to commands. Unfortunately, neither of these options seem to play too well with concurrency. This is why I'm looking to do the same thing in go.

So my question - how can I run the above command line command in GO? Or pipe data to/from different postgres databases? I'v been experimenting with the following code and haven't had success. Any tips would be appreciated.

import "github.com/go-pg/pg"

//connect to source and destination db's (successful connection confirmed)

r, w := io.Pipe()

println("start")

_, err := destDB.CopyFrom(r, "COPY destinationTable FROM STDIN")
if err != nil {
    println(err)
}

_, err = srcDB.CopyTo(w, "COPY (SELECT * FROM sourceView) TO STDOUT")
if err != nil {
    println(err)
}

srcDB.Close()
destDB.Close()

It might be faster to use postgres instead of an external language, eg pg_connect or pg_fdw. — Jasen
– Jasen, Commented Feb 22, 2020 at 2:55
Above your CopyFrom and CopyTo must be run concurrently i don't know how go makes threads but suspect that your code isn't doing that. — Jasen
– Jasen, Commented Feb 22, 2020 at 2:58
It would be faster to use postgres. Unfortunately, I can't (with the current setup) select data from one database into another. Hence the need for an external language. Further, the end goal is to move 30+ tables and would like to do so concurrently. — Zach
– Zach, Commented Feb 22, 2020 at 3:02
I'd run several psql commands concurrently (one for a table) in a bash script. Or would look into existing tools for replication: severalnines.com/database-blog/… — Krzysztof Madejski
– Krzysztof Madejski, Commented Feb 22, 2020 at 20:22

Shang Jian Ding · Accepted Answer · 2020-02-22 05:04:40Z

2

The reader and writer should be in different goroutines and the write side of the pipe should be closed when done to prevent the read side from hanging. See the following for a starting point:

    r, w := io.Pipe()

    writer := make(chan error)
    go func() {
        defer w.Close()
        _, err := src.CopyTo(w, `COPY (SELECT * FROM sourceView) TO STDOUT`)
        writer <- err
    }()

    reader := make(chan error)
    go func() {
        _, err := dest.CopyFrom(r, "COPY destinationTable FROM STDIN")
        reader <- err
    }()

    errWriter := <-writer
    if errWriter != nil {
        fmt.Printf("Writer (CopyTo) error: %v", errWriter)
    }

    errReader := <-reader
    if errReader != nil {
        fmt.Printf("Reader (CopyFrom) error: %v", errReader)
    }

    if errWriter == nil && errReader == nil {
        fmt.Println("All done - no errors")
    }

answered Feb 22, 2020 at 5:04

Shang Jian Ding

2,15617 silver badges25 bronze badges

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

1 Comment

Zach Over a year ago

Thanks for the quick response. This was exactly what I needed.

Collectives™ on Stack Overflow

Using Go to copy from one postgres db to another

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