2

I created an EC2 instance and then installed postgresql (version 11) and timescaledb in it. My security group looks as follows:

enter image description here

As of now, I have enabled this instance for all the IP addresses.

My /etc/postgresql/11/main/pg_hba.conf looks like this:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# 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            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5 #ipv4 range
host    all             all             ::0/0                   md5 #ipv6 range
host    all             all             all                     md5 #all ip

and the /etc/postgresql/11/main/postgresql.conf looks like this:

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

Then, I try to connect to the database via Pgadmin4 using the Public DNS(IPv4):ec2-xx-xxx-xxx-xxx.eu-central-1.compute.amazonaws.com

enter image description here

After I enter the username and password, it throws the following error:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "ec2-xx-xxx-xxx-xxx.eu-central-1.compute.amazonaws.com" (xx.xxx.xxx.xxx) and accepting
TCP/IP connections on port 5432?

I had a look at the following post and followed all the instructions, but to no avail:

  1. connect-to-remote-postgresql-server-on-amazon-ec2

Can anyone please help me out with this?

Edit 1

When I do:

/etc/init.d/postgresql restart

I get:

[....] Restarting postgresql (via systemctl): postgresql.service==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to restart 'postgresql.service'.
Authenticating as: Ubuntu (ubuntu)
Password: 

I don't know what password to enter here so that the postgresql can get restarted

3
  • Did you restart the database after changing postgresql.conf? If so, did it actually start? If you ssh to the machine, can you connect with psql? Commented Apr 20, 2020 at 23:01
  • @jjanes Hey, I edited my question with the restart part. Could you please have a look at it? Commented Apr 21, 2020 at 7:18
  • You were right, @jjanes. The problem was in restarting the db. I did: sudo /etc/init.d/postgresql restart and it works Commented Apr 21, 2020 at 8:24

1 Answer 1

2

As pointed out by @jjanes in the comments, I had to restart the postgresql inorder to access it.

Doing

/etc/init.d/postgresql restart

was throwing an error (as shown in the question) that's why I had to use

sudo /etc/init.d/postgresql restart

in order to grant the root access.

Once the db was restarted, I tried connecting to the ec2 instance again via pgadmin4, and this time it worked.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.