CODEWITHSUNDEEP
postgresqlgo
Chemdream
Chemdream

Reputation: 627

Golang Postgres How To Dump Tables Into JSON

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)
    }

Upvotes: 1

Views: 4629

Answers (1)

putu
putu

Reputation: 6444

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:[email protected]/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\n", stdoutStderr)

Note

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

Upvotes: 4

Related Questions