Trying to connect remotely to postgres db with pgAdmin and it failed. This message looks exactly like the postgres db is down.
"could not connect to server: Connection refused ..."
It says ...
“For PostgreSQL servers starting with version 8.0, this is controlled using the "listen_addresses" parameter in the postgresql.conf file. Here, you can enter a list of IP addresses the server should listen on, or simply use '*' to listen on all available IP addresses. For earlier servers (Version 7.3 or 7.4), you'll need to set the "tcpip_socket" parameter to 'true'. “
So, uncomment, add ‘*’ to postgresql.conf then restart the instance
listen_addresses = '*' # what IP address(es) to listen on;
-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl stop -D /var/lib/pgsql/9.3/data
waiting for server to shut down.... done
server stopped
-bash-4.1$ ps -ef|grep postgres
avahi 1892 1 0 2014 ? 00:00:09 avahi-daemon: running postgres 21160 21018 0 11:00 pts/0 00:00:00 grep postgres
-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl start -D /var/lib/pgsql/9.3/data
server starting
-bash-4.1$ < 2015-02-20 11:00:44.354 EST >LOG: redirecting log output to logging collector process
< 2015-02-20 11:00:44.354 EST >HINT: Future log output will appear in directory "pg_log".
Crap .. now something else ..
"Access to database deinied .. " FATAL" no pg_hba.conf entry for host"
"Access to database deinied .. " FATAL" no pg_hba.conf entry for host"
This is an authentication issue. On the server, locate the pg_hba.conf and make the following changes. Allowing all IPs in trust mode.
FROM
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
TO
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 0.0.0.0/0 trust
# IPv6 local connections:
host all all ::1/128 ident
3 ways to reload pg_hba.conf
with root
[root@postgres ~]# service postgresql-9.3 reload
with postgres
/usr/pgsql-9.3/bin/pg_ctl reload -D /var/lib/pgsql/9.3/data
-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl reload -D /var/lib/pgsql/9.3/data
server signaled
within the database
select pg_reload_conf();
-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
Coming from Oracle world, these security frameworks appeared rather awkward to me. Oracle remote access overlord is the orapwd. ODBC and JDBC connectivity accesses are allowed or restricted to the schema level. Never had to setup these restricitions for IPs and security type. If someone had the root password, the Oracle database would have been compromised and pointless to have these security methods of md5, peer, trust and etc.I guess, once getting used to it, this can be a good thing where it put another layer of security. md5 would have been ideal, other method of security except ‘trust’, it can increase the workloads for a postgres dba.
