Export postgres table to csv error

Question

I am trying to export all my tables of postrgres into individual csv files for that I am using the following function

CREATE OR REPLACE FUNCTION db_to_csv(path text)
  RETURNS void AS
$BODY$
declare
  tables RECORD;
  statement TEXT;
begin
  FOR tables IN 
    SELECT (table_schema || '.' || table_name) AS schema_table
    FROM information_schema.tables t INNER JOIN information_schema.schemata s 
    ON s.schema_name = t.table_schema 
    WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')
    ORDER BY schema_table
  LOOP
    statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
    EXECUTE statement;
  END LOOP;
  return;  
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION db_to_csv(text)
  OWNER TO postgres;

but when I am calling this function I am getting could not open file "/home/user/Documents/public.tablename.csv" for writing: Permission denied

I have tried copying individual table using

COPY activities TO '/home/user/Documents/foldername/conversions/tablename.csv' DELIMITER ',' CSV HEADER;

It gives me the following error

ERROR:  could not open file "/home/user/Documents/foldername/conversions/tablename.csv" for writing: Permission denied


********** Error **********

 ERROR:  could not open file "/home/user/Documents/foldername/conversions/tablename.csv" for writing: Permission denied
SQL state: 42501

Any suggestions how to fix this.

COPY will be run by the PostgreSQL backend (user "postgres"). That user needs to be able to write that file. wiki.postgresql.org/wiki/COPY — pozs
– pozs, Commented May 23, 2014 at 7:25
I have tried that under console only. Was giving me permission denied error. Changed the ownership to 777 now working fine J. — Arihant Godha
– Arihant Godha, Commented May 23, 2014 at 8:18

Alexandros · Accepted Answer · 2014-05-23 07:02:30Z

7

Make a folder on which every user has access. Then run the COPY command on a file there. COPY works only on directories where postgres user has access

sudo mkdir /media/export
sudo chmod 777 /media/export

COPY activities TO '/media/export/activities.csv' DELIMITER ',' CSV HEADER;

answered May 23, 2014 at 7:02

Alexandros

2,2004 gold badges27 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raj Over a year ago

this did not work fro me. however this worked -> masterywithdata.com/blog/copy-permission-denied

Nauman Shah · Accepted Answer · 2017-11-09 10:48:34Z

2

I was facing the same issue and I followed the second answer

Make a folder on which every user has access. Then run the COPY command on a file there. COPY works only on directories where postgres user has access

This didn't work for me.

So, I performed copy to /tmp/somename.csv and then copied the file to my actual required location for usage.

\copy query TO '/tmp/somename.csv' with csv;

answered Nov 9, 2017 at 10:48

Nauman Shah

3522 silver badges13 bronze badges

Comments

Anoop Saxena · Accepted Answer · 2017-02-28 16:45:51Z

1

Not working after given permission.

Now I tried to export the same location where greenplum data available i.e greenplum/data, now permission denied problem get resolved.

COPY Table_Name TO '/home/greenplum/data/table_data_export.csv';

answered Feb 28, 2017 at 16:45

Anoop Saxena

414 bronze badges

Collectives™ on Stack Overflow

Export postgres table to csv error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related