2017-10-10 21:03
浏览 81

Golang Postgres如何将表转储到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!


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 {
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doulu2011
    doulu2011 2017-10-11 18:29

    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:     "",   //database address
      defer db.Close()
      //open output file
      out, err := os.Create("/tmp/bar.json")
      if err != nil {
      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 {
    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@"
      //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 {
      ", stdoutStderr)


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

    点赞 评论