Thread: COPY command cannot access file...
I have an application that will only ever run locally on a development box. The application writes a text file to the hard drive, and then issues the "COPY" sql command to import records in that text file. The application ran fine on one box, but then when trying to run it on another, I keep getting error messages, something such as... COPY command, running in backend with effective uid 501, could not open file... 501 is the postgres user. I understand why this is happening. The database server, does not seem to be able to access the file and from what I understand changing permissions of the file will not help. What I need to know is how can I give the database server permission to read such files? I have a number of scripts written this way that import millions of records. I read somewhere I should use the 'psql \copy' command instead. But then the manual page for the psql command reccomends the SQL copy command for large numbers of records, and I would hate to have to rewrite so many scripts to change it. With that, how can I grant the database server what it needs to read these files. Thanks in advance. Alan
Hi alan, The problem is most likely to be in the permission for the directory. Check if you have executable permission for all the directories before the actual file for the 'postgres' user. HTH. Regards, HK. On Thu, 29 Jan 2004, Alan T. Miller wrote: > I have an application that will only ever run locally on a development box. > The application writes a text file to the hard drive, and then issues the > "COPY" sql command to import records in that text file. The application ran > fine on one box, but then when trying to run it on another, I keep getting > error messages, something such as... > > COPY command, running in backend with effective uid 501, could not open > file... > > 501 is the postgres user. > > I understand why this is happening. The database server, does not seem to be > able to access the file and from what I understand changing permissions of > the file will not help. What I need to know is how can I give the database > server permission to read such files? I have a number of scripts written > this way that import millions of records. I read somewhere I should use the > 'psql \copy' command instead. But then the manual page for the psql command > reccomends the SQL copy command for large numbers of records, and I would > hate to have to rewrite so many scripts to change it. With that, how can I > grant the database server what it needs to read these files. > > Thanks in advance. > Alan > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Thu, Jan 29, 2004 at 05:17:23 -0700, "Alan T. Miller" <amiller@hollywood101.com> wrote: > I understand why this is happening. The database server, does not seem to be > able to access the file and from what I understand changing permissions of > the file will not help. What I need to know is how can I give the database > server permission to read such files? I have a number of scripts written > this way that import millions of records. I read somewhere I should use the > 'psql \copy' command instead. But then the manual page for the psql command > reccomends the SQL copy command for large numbers of records, and I would > hate to have to rewrite so many scripts to change it. With that, how can I > grant the database server what it needs to read these files. Normally you want to have the copy command read from standard input and then the application can read from the file and write to the connection.