Re: Cannot connect to one specific postgres database

Melvin Davidson wrote:
> Yes, you are correct, but you can have multiple _clusters_ with
> different ports,
> so there would a separate postmaster for each cluster on a
> server/machine with
> a different port.
>

Yes, you can, but the way you've worded your response was that you have
one postmaster process per _database_, which isn't correct. A cluster
is a group of databases, not one, therefore your response to the OP was
misleading.
> Keep in mind the port for the database was not specified in the original
> description
> so it s entirely possible the unaccessible database was due to the
> postmaster for that
> port being down.
>

The OP said they can access other databases on the same server OK, plus
PgAdmin wouldn't crash - they'd get a "the server is not listening..."
message.
> BTW, we do have several servers/systems/machines running multiple
> postmasters on multiple ports
> so even of my "understanding" of postgresql is different than yours,
> I must be doing something right. :)
>

If you have legitimate reasons for doing so, fine, but I was responding
to the way you worded your question, which was you were saying you have
one process per port per database, which is by no means ideal.

Regards

Andy.
> -----Original Message-----
> From: Andy Shellam (Mailing Lists)
> [mailto:andy(dot)shellam-lists(at)mailnetwork(dot)co(dot)uk]
> Sent: Monday, January 22, 2007 2:35 PM
> To: Melvin Davidson
> Cc: Matt Busby; pgadmin-support(at)postgresql(dot)org
> Subject: Re: [pgadmin-support] Cannot connect to one specific postgres
> database on a server
>
> Melvin,
>
> Your understanding of how to set up a PostgreSQL server is wrong.
> One server process (it was called the postmaster up to 8.1, since 8.2
> it's now called the Server process) can run any number of databases, you
> don't need multiple postmasters for multiple databases or your server is
> going to die quickly when you start adding more and more databases, not
> to mention the admin overhead of managing all the ports, and handling
> backups.
>
> Using PgAdmin you connect to a particular server, connecting to the
> "postgres" database by default, then once you're connected to a
> PostgreSQL server, you choose which database/s to connect to.
>
> Melvin Davidson wrote:
>
>> When you say "The site that is using the database is also running
>> fine", Do you mean users are accessing that database? If so, that
>> check the port # you are using to connect.
>>
> This should be 5432 unless specifically changed during the build.
>
>> You might also try adding a new server connection to that database in
>> pgadmin.
>>
> If the OP can connect to one database, he should be able to connect to
> another on the same server.
>
>> postmaster is the postgresql process that is needed to access the
>> database.
>> Telnet to the server and do
>> ps -ef | grep postmaster
>> You should see something like
>> admin 2986 1 0 Jan12 ? 00:00:00
>> /home/pgsql/postgresql-8.0.6/bin/postmaster
>>
> There are various processes for PostgreSQL, such as the auto-vacuum,
> stats-gatherer and background writer processes.
>
>> You will have a process for each database on a separate port.
>>
> No - if yours is set up this way, your server is going to be
> unnecessarily overloaded. One PostgreSQL postmaster/server process can
> run any number of databases on one port. It's only advisable to run more
> than one PostgreSQL server process on different ports if you want to run
> two different versions of PostgreSQL, or want to dump one set of
> databases to a different server (popular during a major version
> upgrade.)
>
>> If all postmaster(s) are running, then perhaps someone or
>> something(gremlins) :) has modified the pg_hba.conf file in the
>> $PGDATA directory?
>>
> If the OP can connect to one database on the same server, there's no
> reason (aside from data corruption) why he shouldn't be able to connect
> to the required one, especially if nothing has changed - he'd get an "no
> pg_hba.conf entry for <such and such> if the pg_hba.conf file was
> mis-configured.
>
> I'd advise the OP to either turn on debug logging in PgAdmin and
> re-create the crash, or dump the database from the server, restore as a
> different database on the same machine and try to connect to it again.
> Matt, if you follow this method, it won't take the database down as long
> as you restore the dump to a different database name. If it works, I'd
> then advise to dump the original database again, drop it and re-create
> it. That will take it down for as long as the dump takes place.
>
> Hope this helps!
>
> Andy.
>
>> ----------------------------------------------------------------------
>> --
>> *From:* Matt Busby [mailto:busby(at)condodomain(dot)com]
>> *Sent:* Monday, January 22, 2007 12:10 PM
>> *To:* Melvin Davidson
>> *Cc:* pgadmin-support(at)postgresql(dot)org
>> *Subject:* RE: [pgadmin-support] Cannot connect to one specific
>> postgres database on a server
>>
>> I can telnet to the database and query the database.... The site that
>>
> is
>
>> using the database is also running fine
>>
>> Not sure what postmaster is.... How can I check if its running? I did
>>
> a
>
>> google search for it, but not sure how to check it on my postgres db
>> server
>>
>> I don't get any errors using telnet ... I can access the db every
>>
> other
>
>> way it seems, just using postgres
>>
>> If I restore/rebuild the db, will that cause the database to go down
>> at all? I will try anything to get pgadmin working with the db... it
>> makes my life SOOO much easier!
>>
>> Thanks very much for taking the time to help me with this issue! Much
>> appreciated!
>>
>> Matt
>>
>>
>
> !DSPAM:37,45b52fd0118219813838351!
>
>
>

--
Andy Shellam
NetServe Support Team

the Mail Network
"an alternative in a standardised world"

p: +44 (0) 121 288 0832/0839
m: +44 (0) 7818 000834