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?
ROW_TO_JSON
doesn't actually produce correct JSON for a table - see here...