PostgreSQL Books and how to manually close connections to Postgres

pgosse · May 1, 2003

Hi all. I'm wondering how I can go about closing postgresql connections manually via the command line, or if this is something which should generally not be approached, given that I'm using persistent connections.

Here's the reason for my question:

I've got four applications which hit different databases on the same server, and last night I wanted to drop one of those databases but I was unable to do so as I kept getting an error message which told me there was still a user connected the database.

Now, the database I was trying to drop is used by an application which is in the development stage, which means I am the ONLY person using it.

So when I ran the following command:

ps auxww | grep ^postgres

I got a list of all active postgresql connections, and sure enough there was one connection to the database I wanted to drop, and it was over a day old.

I'm using persistent connections, so I guess I'm looking for two things here:

1) how long persistent connections are kept in the connection pool after they are idle, and

2) how I might go about manually terminating said connection so I may drop the database again if need be?

Also, I've recently been informed that I'm the only person who uses PostgreSQL where I work, and my server administrator is asking that I start to do more of the PostgreSQL administration myself. I have absolutely no problem with this, and am quite excited about the whole process, but I'm going to have to seriously bump up my knowledge of *nix and PostgreSQL.

Can any of the hardened PostgreSQL gurus on this list (leland123, rycamor, I'm looking at you ;o) recommend some good *nix and PostgreSQL books which I could use as learning tools?

Thanks very much in advance,
Pablo

Leland123 · May 1, 2003

Hi pgosse,

congratulations on your promotion. There are a couple of online books that might be of some help to you. You can find them under the FAQ regarding "Where can I find information about postgreSQL". Below are the links:

http://www.commandprompt.com/ppbook/index.lxp

If you have access to the Red hat Linux 8.0 desktop, you can type "pgaccess" from the command window to bring up the default postgres GUI Administrative tool. It has a new tab for users. If you click on it, it will display all connection to postgres, and allow you to disconnect any orphaned persistent connections. As far as I know, by default postgres will never timeout on a connection, even when a connection is not persistent. It is the programmer's responsibility to make sure a connection is closed when done.

Having said that, there is surly a setting, probably in postgresql.conf that would allow an entry of a time, like 5 or 10 minutes, after which a connection would automatically disconnect.

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox

newbiepg · May 2, 2003

In postgresql.conf

you need to set these two parameters for timeout

authentication_timeout = 60
deadlock_timeout = 1000

these will not however close the persistent connections,
they will force a timeout when there is a problem i.e. authentication or error

I would suggest that you avoid persistent connections whenever possible as it puts a lot of load on the server.
When you use connections to postgresql using jsp or php , the connection automatically close when the user leaves the page(unless you keep a persistent connection)

Commandprompt books are good as leland suggested
I had bought Practical postgresl from them
this is the online link

http://www.commandprompt.com/ppbook/

The book is fairly good except for two points
They dont have a good index and
they are silent about programming in languages other than plpgsql
To check out programming in C for postgresql
I use Professional Linux programming from Wrox publications.

You can also check the techdocs link

http://techdocs.postgresql.org/

pgosse · May 2, 2003

Thanks for all your information folks. Greatly appreciated.

Newbiepg, your comments about persistent connections causes me to raise a couple of questions. Which I've made in another post. Greatly appreciated if you could take a look.

Thanks much in advance,
Pablo

rycamor · May 3, 2003

Hi Pablo,

I personally haven't been much impressed with any of the PostgreSQL books out there. None of them really digs too deeply, and they are almost all seriously lacking because they describe PostgreSQL pre-7.3+, where major new functionality changes came, and more are coming with 7.4.

That having been said, my two favorites are PostgreSQL Developer's Handbook by Gerschwinde and Shoenig (see here for a bargain price:

http://www.bookpool.com/.x/z7ep9nnz8n/sm/0672322609)

and PostgreSQL Essential Reference --

http://www.bookpool.com/.x/m8kxa44271/sm/0735711216

(although I would wait for a new edition to cover version 7.4+)

There are several new PostgreSQL books comging out, though, if you browse

http://www.bookpool.com/.x/m8kxa47wh8/ss/1?qs=PostgreSQL&Go.x=0&Go.y=0&Go=Go

. I would recommend you look for the newest comprehensive one.

Really, one of your best ways to really learn about PostgreSQL is to subscribe to a couple of the mail lists at

http://archives.postgresql.org/,

especially the pgsql-general and pgsql-sql ones. I know it's not a very organized way to learn, but just read through a few threads in the lists every night and you will learn a LOT, because the lead developers often take the time to answer questions (see that coming from Oracle or Sybase ;-))

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

PostgreSQL Books and how to manually close connections to Postgres

pgosse

Programmer

Leland123

Programmer

newbiepg

Programmer

pgosse

Programmer

rycamor

Programmer

Similar threads

Part and Inventory Search

Sponsor