Hướng dẫn php check postgresql connection

Việc kết nối tới PostgreSQL Database để lấy dữ liệu là rất dễ dàng. Với PHP có 2 cách để kết nối như sau:

Nội dung chính

• 1. Sử dụng hàm pg_connect[] của PHP
• Kích hoạt Extension pgsql của PHP
• Kết nối tới PostgreSQL Database sử dụng hàm pg_connect[]
• 2. Sử dụng class PDO của PHP
• Kết nối tới PostgreSQL Database sử dụng hàm PDO

1. Sử dụng hàm pg_connect[] của PHP

Kích hoạt Extension pgsql của PHP

Để sử dụng hàm được hàm pg_connect[] của PHP chúng ta cần kích hoạt 1 extension của PHP là: pgsql

Sửa file php.ini và mở comment của dòng sau:

– Trên Windows:

extension=php_pgsql.dll

– Trên Linux:

extension=pgsql.so

Sau đó restart lại php-fpm hoặc apache để cập nhật config.

Kiểm tra php đã có kích hoạt module pgsql chưa? Chúng ta sử dụng command sau:

php -m | grep pgsql

Nếu kết quả như sau tức là module pgsql của PHP đã hoạt động.

Kết nối tới PostgreSQL Database sử dụng hàm pg_connect[]

Tham khảo đoạn code sau:

 gutostraube at gmail dot com ¶
12 years ago

It's possible connect to a PostgreSQL database via Unix socket using the pg_connect[] function by the following two ways:
1] Using the socket path:


2] Omitting the host name/path:


Note: in this case [omitting the host value], the default socket path will be used.


bgalloway at citycarshare dot org ¶
14 years ago

Beware about writing something like 


It will return a boolean.  This will appear to be fine if you don't use the return value as a db connection handle, but will fail if you do.

  

Instead, use:


which actually returns a handle.

 phpnet at benjamin dot schulz dot
name ¶
18 years ago

if you need to open a new connection handle [i.e. for multiple pg_send_query[]] use PGSQL_CONNECT_FORCE_NEW as second parameter to pg_connect[]
 kayotix at yahoo dot com ¶
21 years ago

Little note that is buried in the install somewhere.  In Php 3, PostgreSQL support was activated by adding --with-postgresql=[DIR] to the options passed to ./configure.  With Php 4.0.2 [on Linux] the parameter was --with-pgsql.  The only place I found this was in the installing PHP on Unix section of the manual.
 leace at post dot cz ¶
22 years ago

If you use PostgreSQL users for authenticating into your pg database rather than using your own authentication, always specify host directive in pg_connect and edit pg_hba.conf to authenticate from this host accordingly. Otherwise, PHP will connect as 'local' using UNIX domain sockets, which is set in pg_hba.conf to 'trust' by default [so you can connect using psql on console without specifying password] and everyone can connect to db _without password_ .

floriparob at gmail dot com ¶
6 years ago

Using the "service" parameter as the connection string -- we found that the following functions:-
putenv["PGSERVICEFILE=/path/to/your/service/file/pg_service.conf"];
$connect_string = ["service=testdb"];
try {
   $pgconn_handle = pg_connect[$connect_string];
   . . . . . etc.
Note:-
1] the environment variable has to point to the path AND file name.
2] the file has to be readable by Apache.
See:-
//www.postgresql.org/docs/9.6/static/libpq-pgservice.html
for how to create your pg_service.conf

 xzilla at users dot sourceforge dot net ¶
18 years ago

regarding the note from  matias at nospam dot projectcast dot com
on 12-Feb-2002 01:16, you do not need a user in the database with the same name a your web user with ANY version of postgresql.  The only time that would be a requirement ifs if you set your postgresql server to only allow IDENT based authentication  [which IIRC is the default on Red Hat systems, which might be what lead to the confusion].  For more info on the various authentication methods allowed by postgresql, check out //www.postgresql.org/docs/7.4/static/client-authentication.html
 Cybertinus ¶
18 years ago

If you use pg_connect['host=localhost port=5432 user=my_username password=my_password dbname=my_dbname'] and you get the following error:
"Warning: pg_connect[]: Unable to connect to PostgreSQL server: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 5432?"
then you should try to leave the host= and port= parts out of the connection string. This sounds strange, but this is an "option" of Postgre. If you have not activated the TCP/IP port in postgresql.conf then postgresql doesn't accept any incoming requests from an TCP/IP port. If you use host= in your connection string you are going to connect to Postgre via TCP/IP, so that's not going to work. If you leave the host= part out of your connection string you connect to Postgre via the Unix domain sockets, which is faster and more secure, but you can't connect with the database via any other PC as the localhost.
 Anonymous ¶
7 years ago

If you get the following warning :
"Warning: pg_connect[]: Unable to connect to PostgreSQL server: could not translate host name "server.your.trying.to.connect.to" to address:"
and the server you are trying to connect to is fine and the connecting itself should be working fine,
it might be the case that the postgres extension for PHP might be confused about something.
Try to restart your Apache to reinitialize the extension.
 derry at siliconriver.com dot
au ¶
19 years ago

pg_connect seems to support SSL connections, on systems where Postgres has been compiled with ssl, i'm assuming this is since psql uses libpq to connect.
pg_connect can successfully connect, and use the "requiressl" argument.
 Sohel Taslim ¶
15 years ago


I got the same problem but I have to solve that in different way.
In my postgresql.conf file the following was commented.
So, I active that under Connection Settings-
# - Connection Settings –
tcpip_socket = true

 rolf at sir-wum dot de ¶
20 years ago

pg_connect[] won't work with the authentication method 'crypt' in the pg_hba.conf. Took me an hour to figure that out till I remeberd some other issues with windows missing the crypt[] call.
 borovik -at-
gmail ¶
15 years ago

"If you use pg_connect['host=localhost port=5432 user=my_username password=my_password dbname=my_dbname'] and you get the following error:
"Warning: pg_connect[]: Unable to connect to PostgreSQL server: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 5432?"
"
I solved this error just by setting listen_addresses = '*' in the postgresql.conf file. This error occurs probably despite of a name resolution to localhost, given in the "host" parameter. So you can set the host in the pg_connect[] function.
 xourge ¶
15 years ago

remember that when you use a blank password there will be an error because of:
password= dbname= [...]
to fix this problem use '' in your $options variable
example:
$options = " host='localhost' port='5432' user='postgres' password='' dbname='test' ";
pg_connect[$options];
*** careful: I used double ' after password=, not "


 dreamsoundaudio at gmail dot com ¶
11 years ago

Ubuntu/Debian users, specifically server versions: If you used Tasksel to build PostgreSQL, and you're banging your head against the wall with the "Fatal error: Call to undefined function pg_connect[]" error, check that php5-pgsql is installed. 
Tasksel apparently doesn't install it.


Anonymous ¶
4 years ago