Обсуждение: Bulk_insert in postgresql like Microsoft SQL server
Dear all,
We need to provide copy rights to our developer's for bulk inserts. But the case is that, for it to happen it needs superuser permission which is very expensive thing a DBA can grant.
Can anybody please suggest if there are such tools or utility through which this can be achieved and with minimal rights.
Thanks and Regards,
Devendra Yadav
You can use copy command for Bulk Insert in PostgreSQL.
On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav <devendra.857@gmail.com> wrote:
Dear all,We need to provide copy rights to our developer's for bulk inserts. But the case is that, for it to happen it needs superuser permission which is very expensive thing a DBA can grant.Can anybody please suggest if there are such tools or utility through which this can be achieved and with minimal rights.Thanks and Regards,Devendra Yadav
Use the \copy command instead of the COPY command
https://www.postgresql.org/docs/11/app-psql.html
On 12/19/18 10:40 AM, Shreeyansh Dba wrote:
Hi Devendra,You can use copy command for Bulk Insert in PostgreSQL.On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav <devendra.857@gmail.com> wrote:Dear all,We need to provide copy rights to our developer's for bulk inserts. But the case is that, for it to happen it needs superuser permission which is very expensive thing a DBA can grant.Can anybody please suggest if there are such tools or utility through which this can be achieved and with minimal rights.Thanks and Regards,Devendra Yadav
Hello Shreeyansh,
You're talking Copy command in psql utility or in pgadmin. In pgadmin it needs superuser rights. Have not tried it from windows cmd, as the application server is on windows.
Thanks,
Devendra Yadav
On Wed, Dec 19, 2018, 11:12 PM Shreeyansh Dba <shreeyansh2014@gmail.com wrote:
Hi Devendra,You can use copy command for Bulk Insert in PostgreSQL.On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav <devendra.857@gmail.com> wrote:Dear all,We need to provide copy rights to our developer's for bulk inserts. But the case is that, for it to happen it needs superuser permission which is very expensive thing a DBA can grant.Can anybody please suggest if there are such tools or utility through which this can be achieved and with minimal rights.Thanks and Regards,Devendra Yadav
On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <devendra.857@gmail.com> wrote: > But the case is that, for it to happen it needs superuser permission which It only requires superuser permission if you ask the server to COPY a file from the local filesystem. Developers and programs should be using client libraries and feeding the data to be copied (in or out) to the server via the connection (which, for the server, is a stdin/stdout stream). psql provides this via \copy - other libraries have their own methods. David J.
Hi David,
Thanks for the information David. I'll check that. We have most of our applications in Java and .net, if you can please highlight on these ?
Thanks,
Devendra Yadav
On Wed, Dec 19, 2018, 11:17 PM David G. Johnston <david.g.johnston@gmail.com wrote:
On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <devendra.857@gmail.com> wrote:
> But the case is that, for it to happen it needs superuser permission which
It only requires superuser permission if you ask the server to COPY a
file from the local filesystem. Developers and programs should be
using client libraries and feeding the data to be copied (in or out)
to the server via the connection (which, for the server, is a
stdin/stdout stream). psql provides this via \copy - other libraries
have their own methods.
David J.
Yes, copy command required superuser privileges. In pgadmin, you can use \copy (use psql console pgadminIII) or Import/Export option for bulk insert.
On Wed, Dec 19, 2018 at 11:16 PM Devendra Yadav <devendra.857@gmail.com> wrote:
Hello Shreeyansh,You're talking Copy command in psql utility or in pgadmin. In pgadmin it needs superuser rights. Have not tried it from windows cmd, as the application server is on windows.Thanks,Devendra YadavOn Wed, Dec 19, 2018, 11:12 PM Shreeyansh Dba <shreeyansh2014@gmail.com wrote:Hi Devendra,You can use copy command for Bulk Insert in PostgreSQL.On Wed, Dec 19, 2018 at 10:59 PM Devendra Yadav <devendra.857@gmail.com> wrote:Dear all,We need to provide copy rights to our developer's for bulk inserts. But the case is that, for it to happen it needs superuser permission which is very expensive thing a DBA can grant.Can anybody please suggest if there are such tools or utility through which this can be achieved and with minimal rights.Thanks and Regards,Devendra Yadav
Devendra Yadav schrieb am 19.12.2018 um 18:58: >> On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <devendra.857@gmail.com <mailto:devendra.857@gmail.com>> wrote: >> > But the case is that, for it to happen it needs superuser permission which >> >> It only requires superuser permission if you ask the server to COPY a >> file from the local filesystem. Developers and programs should be >> using client libraries and feeding the data to be copied (in or out) >> to the server via the connection (which, for the server, is a >> stdin/stdout stream). psql provides this via \copy - other libraries >> have their own methods. > Thanks for the information David. I'll check that. We have most of > our applications in Java and .net, if you can please highlight on > these ? With JDBC you can use the CopyManager API together with a "COPY FROM STDIN" statement. https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html There are some examples on Stackoverflow: https://stackoverflow.com/questions/6958965 https://stackoverflow.com/questions/46988855
Dear All,
Thank you all for your help. I am able to achieve the same.
Thanks and Regards,
Devendra Yadav
On Thu, Dec 20, 2018, 6:56 PM Thomas Kellerer <spam_eater@gmx.net wrote:
Devendra Yadav schrieb am 19.12.2018 um 18:58:
>> On Wed, Dec 19, 2018 at 10:29 AM Devendra Yadav <devendra.857@gmail.com <mailto:devendra.857@gmail.com>> wrote:
>> > But the case is that, for it to happen it needs superuser permission which
>>
>> It only requires superuser permission if you ask the server to COPY a
>> file from the local filesystem. Developers and programs should be
>> using client libraries and feeding the data to be copied (in or out)
>> to the server via the connection (which, for the server, is a
>> stdin/stdout stream). psql provides this via \copy - other libraries
>> have their own methods.
> Thanks for the information David. I'll check that. We have most of
> our applications in Java and .net, if you can please highlight on
> these ?
With JDBC you can use the CopyManager API together with a "COPY FROM STDIN" statement.
https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html
There are some examples on Stackoverflow:
https://stackoverflow.com/questions/6958965
https://stackoverflow.com/questions/46988855