Golang Postgres如何将表转储到JSON

Question

I'm trying to run this command for many tables (one table at a time) in Go 1.9:

COPY (select row_to_json(foo) FROM (SELECT * FROM bar) foo ) TO '/tmp/bar.json';

Is this even possible? It seems with lib/pg, it is not. With go-pg, I keep running out of memory because it buffers it all into memory first.

Doing this from the command prompt works fine. I'd rather use Go's PG libs than have it run it at command prompt.

In short, I'm trying to dump entire tables into JSON in their own files.

Has anyone done this successfully?

Thank you!

EDIT:

Since lb/pg didn't support this at all, I'm using pg-go. Here is the code:

var buf bytes.Buffer
    _, err := db.CopyTo(&buf, "COPY (select row_to_json(foo) FROM (SELECT * FROM bar) r ) TO '/tmp/bar.json'")
    if err != nil {
        panic(err)
    }

Answer 1

There are two approaches to export table(s) into json file using COPY.

1. Using go-pg. To avoid out of memory issue, instead of writing the result to buffer, the result should be written directly to a file. The snippets will be:

   //open database connection first
   db := pg.Connect(&pg.Options{
       User:     "username",
       Password: "password",
       Database: "database",
       Addr:     "192.168.1.2:5432",   //database address
   })
   defer db.Close()
   
   //open output file
   out, err := os.Create("/tmp/bar.json")
   if err != nil {
       panic(err)
   }
   defer out.Close()
   
   //execute opy
   copy := `COPY (SELECT row_to_json(foo) FROM (SELECT * FROM bar) foo ) TO STDOUT`
   _, err = db.CopyTo(out, copy)
   if err != nil {
       panic(err)
   }
   

2. Using psql and exec package. Basically, this approach execute a psql command in the form of: psql -c query1 -c query2 ... args. Please note, this approach requires that psql is already installed. The snippets:

   queries := []string{
       `SET client_encoding='UTF8'`,
       `\COPY (SELECT row_to_json(foo) FROM (SELECT * FROM bar) foo ) TO '/tmp/bar.json'`,
   }
   dsn := "postgresql://username:password@192.168.1.2/database"
   
   //construct arguments
   args := []string{}
   for _, q := range queries {
       args = append(args, "-c", q)
   }
   args = append(args, dsn)
   
   //Execute psql command
   cmd := exec.Command("psql", args...)
   stdoutStderr, err := cmd.CombinedOutput()
   if err != nil {
       panic(err)
   }
   fmt.Printf("%s
   ", stdoutStderr)
   

Note

Adjust the value of connection parameters (username, password, host/ip address, etc) as needed. For the detail, please refer to the documentation.