Thread

1. PostgreSQL on Webmin

   Kostadin Solakov <kostadin.solakov@gmail.com> — 2009-03-15T22:24:57Z

   Hi there!
   
    
   
   I just installed Webmin on my dedicated server and I really had hard time
   making all configuration that were needed to migrate my site. Anyway, now
   everything is working except for one thing. I can't connect to the database
   through PHP. Remote connection is working fine, but local doesn't. 
   
   The connection string looks like this:
   
    
   
   pg_connect('host=localhost port=5432 user=user password=pass dbname=db')
   
    
   
   I made the necessary changes in postgresql.conf
   
   listen_addresses = '*'
   
   port=5432
   
    
   
   And I added this in pg_nba.conf: 
   
    
   
   local   all         all                      trust
   
   local   all         user                 ident sameuser
   
   local   db        user                 password
   
    
   
   But still I cannot connect using php. The connection file is the same as it
   was on my previous host (shared one) so it's supposed to be working.
   
   I created the same  database and user.
   
    
   
   Any idea?
   
    
   
   Thanks in advance!
   
    
   
    
   
   Kostadin Solakov
   
    
   
   GSM +389 75 470 503
   
   Email: kostadin.solakov@gmail.com
   
   Skype: kostadin.solakov
   
   MSN: solakov@hotmail.com
   
   LinkedIn: http://www.linkedin.com/kostadinsolakov
   
    
   
    
   
   

2. Re: PostgreSQL on Webmin

   John R Pierce <pierce@hogranch.com> — 2009-03-16T01:25:29Z

   Kostadin Solakov wrote:
   >
   > Hi there!
   >
   > I just installed Webmin on my dedicated server and I really had hard 
   > time making all configuration that were needed to migrate my site. 
   > Anyway, now everything is working except for one thing. I can’t 
   > connect to the database through PHP. Remote connection is working 
   > fine, but local doesn’t.
   >
   > The connection string looks like this:
   >
   > pg_connect('host=localhost port=5432 user=user password=pass dbname=db')
   >
   > I made the necessary changes in postgresql.conf
   >
   > listen_addresses = '*'
   >
   > port=5432
   >
   > And I added this in pg_nba.conf:
   >
   > local all all trust
   >
   > local all user ident sameuser
   >
   > local db user password
   >
   > But still I cannot connect using php. The connection file is the same 
   > as it was on my previous host (shared one) so it’s supposed to be working.
   >
   > I created the same database and user.
   >
   
   
   the first local all all takes precedence as that matches any local 
   (domain socket) connections. as is, you're saying any process running 
   your server can connect to any database as any user with no 
   authorization required.
   
   however, none of those lines affect localhost IP connections, those 
   instead would match a `host .... 127.0.0.1/32 ....`
   
   
   
   I most typically use the following...
   
   local all all ident sameuser # allow local domain connections to 
   authenticate only as themselves
   host all all 127.0.0.1/32 md5 # allow localhost IP connections to 
   authenticate with passwords only
   
   and sometimes...
   
   host all all my.ip.sub.net/24 md5 # allow any user on my IP subnet to 
   authneticate with passwords
   
   
   
   
   

3. Re: PostgreSQL on Webmin

   Kostadin Solakov <kostadin.solakov@gmail.com> — 2009-03-16T19:05:52Z

   Thanks John, I tried that and it works but with some changes I made.
   After the changes you suggested I was still getting the same message:
   Warning: pg_pconnect() [function.pg-pconnect]: Unable to connect to
   PostgreSQL server: could not connect to server: Permission denied Is the
   server running on host "localhost" and accepting TCP/IP connections on port
   5432?
   
   Anyway I managed to connect to localhost, but after I made changes to
   pg_connect and I removed the host name
   FROM
   
      1. pg_pconnect("host=localhost dbname=mydb user=myuser
   password=mypassword");
   
   TO
   
      1. pg_pconnect("dbname=mydb user=myuser password=mypassword");
   
   
   
   Now it works, but VERY SLOWER than before.
   One of the scripts I'm running on the previous host took below 2 sec for
   1000 entries and now it takes 1 sec for 100 entries.
   
   working pg_hba.conf looks like this:
   
      1. # IPv4 local connections:
      2.  local   all         all                               trust
      3.  local   all         my_user                          ident sameuser
      4.  local my_db my_user password
      5.  host my_db my_user 0.0.0.0/0 password
      6. # IPv6 local connections:
      7.  host    all         all         127.0.0.1/32          ident sameuser
      8.  host    all         all         ::1/128               ident sameuser
   
   
   -----Original Message-----
   From: John R Pierce [mailto:pierce@hogranch.com] 
   Sent: Monday, March 16, 2009 2:25 AM
   To: Kostadin Solakov
   Cc: pgsql-general@postgresql.org
   Subject: Re: [GENERAL] PostgreSQL on Webmin
   
   Kostadin Solakov wrote:
   >
   > Hi there!
   >
   > I just installed Webmin on my dedicated server and I really had hard 
   > time making all configuration that were needed to migrate my site. 
   > Anyway, now everything is working except for one thing. I can't 
   > connect to the database through PHP. Remote connection is working 
   > fine, but local doesn't.
   >
   > The connection string looks like this:
   >
   > pg_connect('host=localhost port=5432 user=user password=pass dbname=db')
   >
   > I made the necessary changes in postgresql.conf
   >
   > listen_addresses = '*'
   >
   > port=5432
   >
   > And I added this in pg_nba.conf:
   >
   > local all all trust
   >
   > local all user ident sameuser
   >
   > local db user password
   >
   > But still I cannot connect using php. The connection file is the same 
   > as it was on my previous host (shared one) so it's supposed to be working.
   >
   > I created the same database and user.
   >
   
   
   the first local all all takes precedence as that matches any local 
   (domain socket) connections. as is, you're saying any process running 
   your server can connect to any database as any user with no 
   authorization required.
   
   however, none of those lines affect localhost IP connections, those 
   instead would match a `host .... 127.0.0.1/32 ....`
   
   
   
   I most typically use the following...
   
   local all all ident sameuser # allow local domain connections to 
   authenticate only as themselves
   host all all 127.0.0.1/32 md5 # allow localhost IP connections to 
   authenticate with passwords only
   
   and sometimes...
   
   host all all my.ip.sub.net/24 md5 # allow any user on my IP subnet to 
   authneticate with passwords
   
   
   
   
   

4. Re: PostgreSQL on Webmin

   John R Pierce <pierce@hogranch.com> — 2009-03-16T20:43:15Z

   Kostadin Solakov wrote:
   > Anyway I managed to connect to localhost, but after I made changes to
   > pg_connect and I removed the host name
   > FROM
   >
   >    1. pg_pconnect("host=localhost dbname=mydb user=myuser
   > password=mypassword");
   >
   > TO
   >
   >    1. pg_pconnect("dbname=mydb user=myuser password=mypassword");
   >
   >
   > Now it works, but VERY SLOWER than before.
   > One of the scripts I'm running on the previous host took below 2 sec for
   > 1000 entries and now it takes 1 sec for 100 entries.
   >   
   
   thats odd, as a domain socket should be slightly -faster- than a tcp/ip 
   socket.   now, since you say 'previous host' I could wonder if other 
   configuration items are impacting this, such as buffer sizes in 
   postgresql.conf, relative speed of disk controllers, etc.   or perhaps 
   this new database hasn't been analyzed since it was populated, or its 
   indexes need rebuilding, or something similar...
   
   
   > working pg_hba.conf looks like this:
   >
   >    1. # IPv4 local connections:
   >    2.  local   all         all                               trust
   >    3.  local   all         my_user                          ident sameuser
   >    4.  local my_db my_user password
   >   
   line 2 masks lines 3,4 as it accepts any connection to any database over 
   'local' (unix domain socket), so it would never bother to try the others.
   
   
   >    5.  host my_db my_user 0.0.0.0/0 password
   >   
   
   that line would allow anyone anywhere (assuming listen_address = '*' in 
   postgresql.conf, and no firewalls intervene) to connect as myuser to 
   mydb with a password
   
   
   
   
   
   

5. Re: PostgreSQL on Webmin

   Kostadin Solakov <kostadin.solakov@gmail.com> — 2009-03-16T21:55:00Z

   John R Pierce wrote:
   
   thats odd, as a domain socket should be slightly -faster- than a tcp/ip 
   socket.   now, since you say 'previous host' I could wonder if other 
   configuration items are impacting this, such as buffer sizes in 
   postgresql.conf, relative speed of disk controllers, etc.   or perhaps 
   this new database hasn't been analyzed since it was populated, or its 
   indexes need rebuilding, or something similar...
   
   	I think that is the issue. Previous server was old machine and the
   settings in postgresql.conf were the default ones.
   	The new machine is Xeon quad with 8GB ram and I already made some
   changes in postgresql.conf, but still no result.
   	I followed the instructions on
   http://www.powerpostgresql.com/PerfList/ and made the following changes:
   	
   		shared_buffers = 50000
   		work_mem = 512000
   		checkpoint_segments = 32
   		effective_cache_size = 200000
   		
   	Also I ran REINDEX on the DB and VACUUM VERBOSE ANALYZE on each
   table, but still the results are the same as before.
   	What are the settings you recommend for this server?
   	The application that is going to use it has a very demanding back
   end, it parses very large XML files (20+) and saves the data in the DB.
   
   	Thanks, Kosta
   	
   
   
   

6. Re: PostgreSQL on Webmin

   Robert Treat <xzilla@users.sourceforge.net> — 2009-03-17T04:57:46Z

   On Monday 16 March 2009 17:55:00 Kostadin Solakov wrote:
   > John R Pierce wrote:
   >
   > thats odd, as a domain socket should be slightly -faster- than a tcp/ip
   > socket.   now, since you say 'previous host' I could wonder if other
   > configuration items are impacting this, such as buffer sizes in
   > postgresql.conf, relative speed of disk controllers, etc.   or perhaps
   > this new database hasn't been analyzed since it was populated, or its
   > indexes need rebuilding, or something similar...
   >
   > 	I think that is the issue. Previous server was old machine and the
   > settings in postgresql.conf were the default ones.
   > 	The new machine is Xeon quad with 8GB ram and I already made some
   > changes in postgresql.conf, but still no result.
   > 	I followed the instructions on
   > http://www.powerpostgresql.com/PerfList/ and made the following changes:
   >
   > 		shared_buffers = 50000
   > 		work_mem = 512000
   > 		checkpoint_segments = 32
   > 		effective_cache_size = 200000
   >
   > 	Also I ran REINDEX on the DB and VACUUM VERBOSE ANALYZE on each
   > table, but still the results are the same as before.
   > 	What are the settings you recommend for this server?
   > 	The application that is going to use it has a very demanding back
   > end, it parses very large XML files (20+) and saves the data in the DB.
   >
   
   Unless you're actually running 8.2, that information is a bit out of date. 
   There's a better write up at 
   http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
   
   Once you go through that and restart, if it's still slow, can you paste 
   explain analyze from the two different servers?
   
   -- 
   Robert Treat
   Conjecture: http://www.xzilla.net
   Consulting: http://www.omniti.com