So, I have this script:
Code:
#!/bin/sh

DBUSER="postgres"

printf "Backing up the database to the local drive...\n\n"

psql -U "$DBUSER" dump_user <<EOF
SELECT backup_clean();
EOF

printf "\n\nThe database has been backed up to the local drive.\n\n"
When I run it, this is what happens:
Code:
$ ./run_postgre_backup.sh
Backing up the database to the local drive...

ERROR:  could not open file "/local/output/2015-10-02__14:47:22.641609-04.csv" for writing: Permission denied
CONTEXT:  SQL statement "COPY logs TO '/local/output/2015-10-02__14:47:22.641609-04.csv' DELIMITER ',' CSV HEADER;"
PL/pgSQL function "backup_clean" line 11 at EXECUTE statement


The database has been backed up to the local drive.
I checked /local/output and its permissions are set to 0777. I restarted the PostgreSQL DB service.

This is the function in SQL:
Code:
CREATE OR REPLACE FUNCTION backup_clean() RETURNS void AS $$
DECLARE
  file_name text := '';
BEGIN
  -- generate a unique file name that's time-stamped.
  file_name := '/local/output/' || current_date || '__' || current_time || '.csv';

  -- lock the table logs so that this method has exclusive access to it.
  LOCK TABLE logs IN ACCESS EXCLUSIVE MODE;

  -- make the actual backup of my file.
  EXECUTE 'COPY logs TO ' || quote_literal(file_name) || ' DELIMITER ' || quote_literal(',') || ' CSV HEADER;';

  -- empty the entire logs table.
  DELETE FROM logs;
END
$$ LANGUAGE plpgsql;
Why can't I write to my local hard-drive? I even switched to the user postgres, went to that specific location and was able to create a simple text file!!!! This doesn't make any sense...