Dump all data to json files

Question

I'm working on a script that has to dump app data from a schema to json files. Here is what I have thus far:

export SCHEMA="citydb"
export DB="kv_db"
export PGPASSWORD=root
psql -U postgres -Atc "select tablename from pg_catalog.pg_tables where schemaname='$SCHEMA'" $DB |

  while read TBL; do
    name=${TBL::-1} # for some reason a whitespace character is appended to the end of each name
    echo -e "Table name: $name";
    touch $name".json"
    psql -U postgres \o $name".json"
    psql -U postgres -c "COPY (SELECT row_to_json(t) FROM $SCHEMA.$name as t) TO 'c:/users/me/desktop/$name.json'" $DB > $name.json
  done

I'm pretty much building up on information about how to export to csv's since I didn't find any information about dumping all the data to json.

I'm getting an error that says:

psql: warning: extra command-line argument "plant_cover.json" ignored
psql: error: FATAL:  database "o" does not exist
ERROR:  could not open file "c:/users/me/desktop/plant_cover.json" for writing: Permission denied
HINT:  COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.

Any ideas?

Dump all data to json files

0 Answers0