Re: COPY TO returns "ERROR: could not open file for writing: No such file or directory"

On 24 May 2014, at 8:21, David Noel <david.i.noel@gmail.com> wrote:

> COPY (SELECT * FROM page WHERE "PublishDate" between '2014-03-01' and
> '2014-04-01') TO
> '/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy';
>
> gives me:
>
> ERROR:  could not open file
> "/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy" for
> writing: No such file or directory

> FreeBSD. PostgreSQL 9.3.
>
> Has anyone run into this before?

Two possibilities I can think of:

1. You’re not writing that file on the database server.

2. It’s a permission issue. Can you cd to that directory as user pgsql and create a file there?

# su - pgsql
# cd /home/ygg/sql/backup
# touch test

It’s possible that you lack execute permission on the symlink from /home to /usr/home, for example.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.