Thread: background jobs
Hello all, I am using a laptop and some of my .sql files with insert statements (300K insert statements) take very long time. I user database=> \i /home/user/../../../insert.sql this takes a long time. and if i shutdown my laptop, the server connection is lost . I am using Putty to connect to my server. Could any one help to setup a background job where I can start a job and forget until the next morning to see that the job is finished. Should it be something like the following: myserver$ psql -d mydb -d node2|\i /insert.sql &> this command is WRONG. Can any one help me with a correct one. Thank you K ________________________________________________________________________ Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/
On Sat, Mar 05, 2005 at 09:28:57PM -0800, Kumar S wrote: > I am using a laptop and some of my .sql files with > insert statements (300K insert statements) take very > long time. How long is "very long"? Have you seen the "Performance Tips" chapter in the documentation, in particular the "Populating a Database" section? http://www.postgresql.org/docs/8.0/interactive/populate.html Are you using transactions? Wrapping a lot of INSERTs in a transaction should be significantly faster than doing each INSERT as its own transaction. Have you considered using COPY instead of INSERT? COPY should be significantly faster than INSERT for bulk loads. > Could any one help to setup a background job where I > can start a job and forget until the next morning to > see that the job is finished. > > Should it be something like the following: > myserver$ psql -d mydb -d node2|\i /insert.sql &> See the psql documentation and your shell's documentation on how to run a background job and redirect its output; you might also need to use "nohup". For example, the following might work in Bourne-like shells (bash, sh, ksh, etc.): nohup psql -d mydb -f insert.sql > insert.log 2>&1 & -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi , thank you very much for your helpful comments. I cannot use COPY because I am using a many SELECT statements in INSERT statements. also one more clarification: is the syntax correct for a transaction: BEGIN TRANSACTION; INSERT ......... INSERT....... INSERT....... 100 k LINES in case if want to rollback: # ROLLBACK; Thank you. --- Michael Fuhr <mike@fuhr.org> wrote: > On Sat, Mar 05, 2005 at 09:28:57PM -0800, Kumar S > wrote: > > > I am using a laptop and some of my .sql files > with > > insert statements (300K insert statements) take > very > > long time. > > How long is "very long"? Have you seen the > "Performance Tips" chapter > in the documentation, in particular the "Populating > a Database" section? > > http://www.postgresql.org/docs/8.0/interactive/populate.html > > Are you using transactions? Wrapping a lot of > INSERTs in a transaction > should be significantly faster than doing each > INSERT as its own > transaction. > > Have you considered using COPY instead of INSERT? > COPY should be > significantly faster than INSERT for bulk loads. > > > Could any one help to setup a background job where > I > > can start a job and forget until the next morning > to > > see that the job is finished. > > > > Should it be something like the following: > > myserver$ psql -d mydb -d node2|\i /insert.sql &> > > See the psql documentation and your shell's > documentation on how to run > a background job and redirect its output; you might > also need to use > "nohup". For example, the following might work in > Bourne-like shells > (bash, sh, ksh, etc.): > > nohup psql -d mydb -f insert.sql > insert.log 2>&1 & > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Did you want to give us a sample of what you are trying to do? Example table structures, your INSERT statement, and an EXPLAIN on one of them? If you are using selects, it could be as simple as indexing some columns to make this faster.... Sean ----- Original Message ----- From: "Kumar S" <ps_postgres@yahoo.com> To: "Michael Fuhr" <mike@fuhr.org> Cc: <pgsql-novice@postgresql.org> Sent: Sunday, March 06, 2005 11:05 AM Subject: Re: [NOVICE] background jobs > Hi , > > thank you very much for your helpful comments. > > I cannot use COPY because I am using a many SELECT > statements in INSERT statements. > > also one more clarification: > > is the syntax correct for a transaction: > > > BEGIN TRANSACTION; > INSERT ......... > INSERT....... > INSERT....... > 100 k LINES > > in case if want to rollback: > > # ROLLBACK; > > > Thank you. > > > > > > --- Michael Fuhr <mike@fuhr.org> wrote: >> On Sat, Mar 05, 2005 at 09:28:57PM -0800, Kumar S >> wrote: >> >> > I am using a laptop and some of my .sql files >> with >> > insert statements (300K insert statements) take >> very >> > long time. >> >> How long is "very long"? Have you seen the >> "Performance Tips" chapter >> in the documentation, in particular the "Populating >> a Database" section? >> >> > http://www.postgresql.org/docs/8.0/interactive/populate.html >> >> Are you using transactions? Wrapping a lot of >> INSERTs in a transaction >> should be significantly faster than doing each >> INSERT as its own >> transaction. >> >> Have you considered using COPY instead of INSERT? >> COPY should be >> significantly faster than INSERT for bulk loads. >> >> > Could any one help to setup a background job where >> I >> > can start a job and forget until the next morning >> to >> > see that the job is finished. >> > >> > Should it be something like the following: >> > myserver$ psql -d mydb -d node2|\i /insert.sql &> >> >> See the psql documentation and your shell's >> documentation on how to run >> a background job and redirect its output; you might >> also need to use >> "nohup". For example, the following might work in >> Bourne-like shells >> (bash, sh, ksh, etc.): >> >> nohup psql -d mydb -f insert.sql > insert.log 2>&1 & >> >> -- >> Michael Fuhr >> http://www.fuhr.org/~mfuhr/ >> > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
What I understand is that that you want to insert to the database, moreover leaving the program running without attention. I recomend that you use the at command. I use it for inserting during the night, even turning off my laptop and the process still run even if the parent -- the putty session -- dies. bash$at -m now /~/script >ctrl + d --aply bash$ you can turn off your laptop and the script run as a background job openning a different bash session "internally" Hope can help you Eduardo Kumar S wrote: >Hello all, > I am using a laptop and some of my .sql files with >insert statements (300K insert statements) take very >long time. > >I user > >database=> \i /home/user/../../../insert.sql > >this takes a long time. and if i shutdown my laptop, >the server connection is lost . I am using Putty to >connect to my server. > >Could any one help to setup a background job where I >can start a job and forget until the next morning to >see that the job is finished. > >Should it be something like the following: >myserver$ psql -d mydb -d node2|\i /insert.sql &> > >this command is WRONG. Can any one help me with a >correct one. > >Thank you >K > >________________________________________________________________________ >Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/ > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > > -- If I have seen further it is by standing on the shoulders of the giants Isaac Newton