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!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

PostgreSQL Books and how to manually close connections to Postgres

Status
Not open for further replies.

pgosse

Programmer
Sep 26, 2001
42
CA
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
 
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:



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)
Nothing Runs Like the Fox
 
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
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

 
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
 
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: and PostgreSQL Essential Reference -- (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 . 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 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 --
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top