Trouble connecting on LB Port

This message was edited 6 times. Last update was at 8 Feb 2012 20:48:15

I can connect fine to each of my DB instances (on DB port) but having issues connecting on LB port.

I receive the following each time:

I've looked at "Connecting to a Cloud Database Cluster" (http://www.enterprisedb.com/docs/en/9.0/conncloud/Tutorial_Connecting_to_a_Cloud_Database_Cluster.htm) and the FAQs (http://www.enterprisedb.com/products-services-training/products-overview/postgres-plus-cloud-database-faqs)hoping for an answer but I can't find any.

What am I missing here? Am I supposed to configure pg_pool myself?

This message was edited 1 time. Last update was at 9 Feb 2012 10:51:36

No, you are not required to configure pgpool; it should update its user:password list automatically (stored in /opt/PostgresPlus/CloudDB/data/pgpool_passwd.conf).

Here are a few questions you can answer for me to diagnose the problem:

.) Have you created any users in the database after the initial cluster creation?
.) Is this user, which is being denied connection, the initial superuser, or one of the users created later.
.) What is the 'Engine version' of the cluster, as reported on the 'Details' pane in the UI.

The error message shows that the pgpool service _is_ running, and that it either does not have the password for the user, or it has an old password.

There was a race condition early in our development cycle, where pgpool would start before we could populate the initial user:password combination in its config file. We had fixed that, but probably you have been bitten by it, and if so, then the bug might not have been completely fixed after all.

You can do the following to work around that bug (or use the PM button to send a private message and help me diagnose this for you):

.) Log in into your database host machine using the instructions her:
Follow sections "Downloading your Private Key" and "Using ssh to Connect to a Server in a Cloud Database Cluster" in http://www.enterprisedb.com/docs/en/9.0/conncloud/Tutorial_Connecting_to_a_Cloud_Database_Cluster.htm

.) Save all the pgpool related config files
cp /opt/PostgresPlus/CloudDB/data/pgpool* /tmp/

.) Restart the pgpool service:
/etc/init.d/pgpool-3.0 restart

Now you should be able to connect to the LB port.

.) Have you created any users in the database after the initial cluster creation?

Yes, one, which is the dedicated user for our application.

.) Is this user, which is being denied connection, the initial superuser, or one of the users created later.

It is not the "postgres" superuser but a user created while logged as "postgres". This user owns our database. Here is the initial creation script runs as "postgres". Everything after that is done with our user:

.) What is the 'Engine version' of the cluster, as reported on the 'Details' pane in the UI.

The engine version is: Engine version: PostgreSQL 9.1 32bit, with initially 3 nodes (a 4th appeared magically over night?!).

So, do you recommend running the work around steps you've listed? Or can you look into my cluster (named "sg_test") to diagnose the issue? What information do you need to locate my cluster?

Cheers,
D.

This message was edited 1 time. Last update was at 9 Feb 2012 13:28:59

I have been able to locate your cluster. Please confirm that the public DNS name of the master/primary node of this cluster is <sent to ddossot in a private message>

I could confirm that the pgpool config file has your other user in the config file, so it might be sufficient to just nudge pgpool a little bit. I would like you to perform a reload of a pgpool, instead of a full restart:

If that doesn't make pgpool let your user in, the please try a restart :

If even that fails to resolve the situation, I can coordinate with you privately to diagnose this further.

Restarting pg_pool did the trick.

Therefore the core issue sounds like synchronization issue, as you suggested. That is when users are created on the main DB, pg_pool needs to sync up with the main DB user authentication config.

Thanks again!

I am glad I could help.

Just a correction on my part though. This is not a synchronization bug as I suspected earlier, but a known problem with the version of pgpool we are currently using.

PPCD agent running on the DB machines sends pgpool a 'reload' signal whenever it detects a new user or a change in password, and pgpool is supposed to reload its config files whenever we send that reload signal. But pgpool version 3.0 does not honor that, and hence does not honor connections from new users because it hasn't re-read the updated config file. Here's my report to the pgpool-hackers:

http://www.pgpool.net/pipermail/pgfoundry/pgpool-hackers/2011-September/000996.html

This issue is fixed in version 3.1 of pgpool, which we will be using in next release of PPCD.

Thanks again for following through with the diagnosis in private messages and the help in resolution.

I am having this exact same problem - unable to connect to LB port with a newly created user. I have checked pgpool_passwd.conf and a line for the new user exists. I have tried both reloading and restarting pgpool and I still get the error

The pgpool log contains the following

Is there anything else I should be trying?

I am surprised to see that restarting pgpool didn't fix it for you.

If possible, can you please somehow grant me access to the instance running pgpool. If that isn't possible, please provide the following details in a private message:

.) Output of this query when executed against the primary database, on DBPORT.

SELECT * FROM pg_authid

.) Contents of pgpool_passwd.conf

This message was edited 1 time. Last update was at 5 Mar 2012 14:11:19

Hi Dion,

Thanks for sharing that info in private.

From the output of the query on pg_authid, I don't see any user named "testuser". Is it possible that you created 'testuser' on some other database and not the one hosted here?

Regards,

I removed the testuser however I am still unable to connect to pgpool with any other user except the postgres user so the problem still remains.

As the config stands, you should be able to login as www user. Please let me know if you are unable to login as that user either.

I am still unable to login as any other user except postgres when connecting through pgpool. In any case, I think I may just delete the cluster and start over again.

I have removed a node from the cluster via the 'Scale Down' option in the console and it is now impossible to connect to the cluster through pgpool.

The console just says my node is shutting down however on my AWS console I can clearly see that the instance has been terminated. When an instance is removed from the cluster, is the pgpool.conf automatically updated? I can still see a reference to the node that has been terminated.

Thanks for your help

When a database node becomes unavailable, pgpool marks it so in memory, and does not use that node until it gets an explicit pcp command to mark that node as online. Since this node is not reachable anymore, pgpool won't use it for any future operations.