CODEWITHSUNDEEP
postgresqlgoogle-compute-engine
Aaron
Aaron

Reputation: 3419

How to Debug postgres connection issues on google cloud compute VM

When trying to connect to postgres using psql thus: sql -d DATABASE_NAME -U postgres -h PUBLIC_VM_IP

I get the following error: psql: error: could not connect to server: Operation timed out

My configuration is

  1. Google cloudcompute vm instance e2-micro (2 vCPUs, 1 GB memory).
  2. The container image is set to gcr.io/google/postgresql12:latest
  3. I have two env vars set up in the VM via the console: a) POSTGRES_DB=MY_DATABASE_NAME and POSTGRES_PASSWORD=MY_PASSWORD
  4. I have network tags set to allow_postgres.
  5. I have a firewall rule named allow_posgres with the following properties:

If I ssh into the instance and do docker ps I see: gcr.io/stackdriver-agents/stackdriver-logging-agent:1.8.4 as the only listed running container, which doesn't seem right. I should see the postgres container running too right?

If so, how do I change my configuration in the console to ensure that container is running and how can I further debug the connection issue?

Upvotes: 0

Views: 1279

Answers (1)

Wojtek_B
Wojtek_B

Reputation: 4443

When I tried running the image you specified I was always getting en error (seen after I logged in to this VM):

  #########################[ Error ]#########################
  #  The startup agent encountered errors. Your container   #
  #  was not started. To inspect the agent's logs use       #
  #  'sudo journalctl -u konlet-startup' command.           #
  ###########################################################

The logs displayed by journalctl also gave no indication what's wrong.

So - I went to marketplace and looked for postgresql 12. After I clicked the Show Pull Command button I got a link to this image: marketplace.gcr.io/google/postgresql12:latest.

I created a new VM (default settings) and used above image url to deploy a container. I assigned a proper network tag to allow connections on 5432 port and clicked create button and created a firewall rule just as you described in your question.

After I logged in to a new VM I ran docker ps and seen that the container is running:

wb@pg1 ~ $ docker ps
CONTAINER ID   IMAGE                                           COMMAND                  CREATED          STATUS          PORTS     NAMES
37b651f9c383   marketplace.gcr.io/google/postgresql12:latest   "docker-entrypoint.s…"   28 seconds ago   Up 19 seconds             klt-pg1-maok

After that I tried connecting from the outside:

wb@cloudshell:~$ psql -h xx.xxx.xxx.xxx -U postgres
psql (13.3 (Debian 13.3-1.pgdg100+1), server 12.6 (Debian 12.6-1.pgdg90+1))
Type "help" for help.

postgres=#

I was able to list all databases:

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

As you can see I got in with no issues so you may try doing the same. As you can see I didn't configure any variables nor database password since it was for testing purposes.

Use the link I found in the Marketplace and it should work.

Additionally

Upvotes: 1

Related Questions