Unable to connect PostgreSQL to remote database using pgAdmin

Question

I installed PostgreSQL on my local server  Ubuntu  with IP 192 168 1 10   Now  I m trying to access the database from my client machine  Ubuntu  with IP 192 168 1 11 with pgAdmin   I know I have to make changes in postgresql conf and pg hba conf to allow the client to connect   Could you please guide me

User · Answer

If you re using PostgreSQL 8 or above  you may need to modify the listen addresses setting in  etc postgresql 8 4 main postgresql conf   Try adding the line   listen addresses       which will tell PostgreSQL to listen for connections on all network interfaces   If not explicitly set  this setting defaults to localhost which means it will only accept connections from the same machine

User · Answer

I didn t have to change my prostgresql conf file but  i did have to do the following based on my psql via command line was connecting and pgAdmin not connecting on RDS with AWS     I did have my RDS set to Publicly Accessible  I made sure my ACL and security groups were wide open and still problem so  I did the following  sudo find   -name   conf then sudo nano   data pg hba conf then added to top of directives in pg hba conf file host all all 0 0 0 0 0 md5 and pgAdmin automatically logged me in    This also worked in pg hba conf file host all all md5 without any IP address and this also worked with my IP address host all all  lt myip gt  32 md5  As a side note  my RDS was in my default VPC   I had an identical RDS instance in my non-default VPC with identical security group  ACL and security group settings to my default VPC and I could not get it to work   Not sure why but  that s for another day

User · Answer

Connecting to PostgreSQL via SSH Tunneling In the event that you don t want to open port 5432 to any traffic  or you don t want to configure PostgreSQL to listen to any remote traffic  you can use SSH Tunneling to make a remote connection to the PostgreSQL instance  Here s how   Open PuTTY  If you already have a session set up to connect to the EC2 instance  load that  but don t connect to it just yet  If you don t have such a session  see this post  Go to Connection  gt  SSH  gt  Tunnels Enter 5433 in the Source Port field  Enter 127 0 0 1 5432 in the Destination field  Click the  quot Add quot  button  Go back to Session  and save your session  then click  quot Open quot  to connect  This opens a terminal window  Once you re connected  you can leave that alone  Open pgAdmin and add a connection  Enter localhost in the Host field and 5433  in the Port field  Specify a Name for the connection  and the username and password  Click OK when you re done

User · Answer

It is actually a 3 step process to connect to a PostgreSQL server remotely through pgAdmin3       Note  I use Ubuntu 11 04 and PostgreSQL 8 4      You have to make PostgreSQL listening for remote incoming TCP connections because the default settings allow to listen only for connections on the loopback interface  To be able to reach the server remotely you have to add the following line into the file  etc postgresql 8 4 main postgresql conf   listen addresses       PostgreSQL by default refuses all connections it receives from any remote address  you have to relax these rules by adding this line to  etc postgresql 8 4 main pg hba conf   host all all  0 0 0 0 0 md5  This is an access control rule that let anybody login in from any address if he can provide a valid password  the md5 keyword   You can use needed network mask instead of 0 0 0 0 0   When you have applied these modifications to your configuration files you need to restart PostgreSQL server  Now it is possible to login to your server remotely  using the username and password

User · Answer

For redhat linux  sudo vi  var lib pgsql9 data postgresql conf    pgsql9 is the folder for the postgres version installed  might be different for others  changed  listen addresses       from listen addresses      localhost    and then  sudo  etc init d postgresql stop sudo  etc init d postgresql start

User · Answer

Check your firewall  When you disable it  then you can connect  If you want can t disable the firewall  add a rule for your remote connection

User · Answer

In linux terminal try this    sudo service postgresql start   to start the server sudo service postgresql stop   to stop thee server sudo service postgresql status   to check server status

[postgresql] Unable to connect PostgreSQL to remote database using pgAdmin

Examples related to postgresql

Examples related to pgadmin