duangaoe9401 2017-10-10 21:03
浏览 95

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 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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥20 关于#r语言#rda分析作图的问题,请各位专家解答!
  • ¥50 找回 股票行情系统的资源问题
  • ¥15 gwas 分析-plink 检查人口分层出现下面问题
  • ¥15 关于#matlab#的问题:需要 MATLAB 运行程序,对比四个多址通信方式(TDMA,FDMA,CDMA,OFDMA)的抗噪声性能
  • ¥15 有没有复现过PFENet的,如何解决?
  • ¥15 vue3使用swiper轮播图循环失败
  • ¥15 FDMA,OFDMA,TDMA 和 CDMA 系统容量对比仿真
  • ¥15 图神经网络的入侵检测
  • ¥15 工业数据采集技术+存储架构推荐
  • ¥15 GPT-SOVITS如何训练少数民族语言支持合成语音