sp_get_postgres_connection error

[harryahlas]

I am at chapter 5.3 in the book and ran into an error when running this code:

con <- sp_get_postgres_connection( host = "localhost", port = 5439, user = "postgres", password = "postgres", dbname = "postgres", seconds_to_test = 30, connection_tab = TRUE )

Error:
Error in sp_get_postgres_connection(host = "localhost", port = 5439, user = "postgres", :
Database is not ready - reason: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5439?
could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5439?

I am on Windows 10. Postgresql was installed on my machine prior to installing docker. I was able to install docker successfully and run sp_make_simple_pg("cattle"). I stopped postgres and tried restarting but still got this error. Please advise, thank you.

[znmeb]

Have you re-installed sqlpetr? There was an update to move the port to 5439.

[znmeb]

I'm going to look at modifying the container start functions so they wait till PostgreSQL is running.

[harryahlas]

I tried reinstalling sqlpetr and got the same error. Also please note when I run sp_docker_containers_tibble() I get the following error. Not sure if it helps.

Warning in if (grepl("\n", x)) { :
the condition has length > 1 and only the first element will be used

Warning in if (grepl("\n", path)) return(path) :
the condition has length > 1 and only the first element will be used

Warning in if (is_url(path)) { :
the condition has length > 1 and only the first element will be used

Warning in if (file.exists(path)) return(normalizePath(path, "/", mustWork = FALSE)) :
the condition has length > 1 and only the first element will be used

Warning in if (!is_absolute_path(path)) paste0(" in current working directory ('", :
the condition has length > 1 and only the first element will be used

Error: 'CONTAINER ID|IMAGE|COMMAND|CREATED AT|CREATED|PORTS|STATUS|SIZE|NAMES|LABELS|MOUNTS|NETWORKS13647dc5ecdd|postgres:10|"docker-entrypoint.s…"|2019-04-10 19:07:47 -0700 PDT|17 seconds ago|0.0.0.0:5432->5432/tcp|Up 14 seconds|63B (virtual 230MB)|cattle||f60b63bd32d12b…|bridge' does not exist in current working directory ('C:/Users/Anyone/Documents').

[znmeb]

@harryahlas What does docker ps -a return at a PowerShell command line?

[harryahlas]

docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS
NAMES
13647dc5ecdd postgres:10 "docker-entrypoint.s…" 2 hours ago Up 2 hours 0.0.0
.0:5432->5432/tcp cattle
6ab48607001d hello-world "/hello" 28 hours ago Exited (0) 28 hours ago
quirky_antonelli

[znmeb]

cattle should be on port 5439, not port 5432. It should look like this after the sp_make_simple_pg("cattle") :

$ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS                              NAMES
9c59e27692a0        postgres:10         "docker-entrypoint.s…"   About a minute ago   Up About a minute   5432/tcp, 0.0.0.0:5439->5439/tcp   cattle
[znmeb@AlgoCompSynth sql-pet]$

Try this in the R console:

remotes::install_github("smithjd/sqlpetr", ref = "wait-for-postgresql", force = TRUE)

Then try the code again.

[harryahlas]

After running the code above I now get this error, before I even get to the sp_get_postgres_connection error:
>sp_make_simple_pg("cattle")

Error in sp_get_postgres_connection(user = "postgres", password = postgres_password, :
Database is not ready - reason: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5439?
could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5439?

[znmeb]

@harryahlas I'll have to test this on Windows tomorrow - I can't reproduce it on Linux.

[znmeb]

I can't reproduce it on Windows either. Here's my test procedure:

1. Install the latest sqlpetr.
2. Clone the sql-pet repository - git clone https://github.com/smithjd/sql-pet.git. If you already have it, check out the master branch and do git pull.
3. Remove any existing PostgreSQL containers. If you have a PostgreSQL container from work that you can't recreate / reinstall, stop. You won't be able to proceed unless you remove it.
4. Open the sql-pet project in RStudio and source the file build-book.R. This takes a fair amount of time; there are two images that need to get pulled / built and there are a number of chapters.

We have code to remove containers we create, but for safety reasons we don't remove any containers we didn't create. I can add a test to the package for an existing container and offer to remove it.

[harryahlas]

On #3 above, do you mean remove any existing PostgreSQL containers from Docker?

I am brand new to Docker. I installed it per the instructions so I did not have any existing PostgreSQL containers. I did have PostgreSQL already installed on my machine, but I stopped it prior to running Docker.

[znmeb]

If you stopped the host PostgreSQL and have no other PostgreSQL containers everything should work.