Unable to Connect to Database Error

[drewpost]

Running this:
docker run --env-file /home/pi/TeslaMateAgile/variables.env mattjeanes/teslamateagile

With an variables.env file containing this:

ConnectionStrings__TeslaMate=Server=127.0.0.1;Port=5432;Database=teslamate;User Id=teslamate;Password=teslamate;
TeslaMate__UpdateIntervalSeconds=300
TeslaMate__GeofenceId=1 
TeslaMate__Phases=1
Octopus__RegionCode=J

Returns this:

info: TeslaMateAgile.PriceService[0]
      Price service is starting
info: Microsoft.Hosting.Lifetime[0]
      Application started. Press Ctrl+C to shut down.
info: Microsoft.Hosting.Lifetime[0]
      Hosting environment: Production
info: Microsoft.Hosting.Lifetime[0]
      Content root path: /app/
info: TeslaMateAgile.PriceHelper[0]
      Updating prices
fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database 'teslamate' on server 'tcp://127.0.0.1:5432'.
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'TeslaMateAgile.Data.TeslaMate.TeslaMateDbContext'.
      System.InvalidOperationException: An exception has been raised that is likely due to a transient failure.
       ---> Npgsql.NpgsqlException (0x80004005): Exception while connecting
       ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (111): Connection refused 127.0.0.1:5432
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source)
         at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
         at System.Net.Sockets.Socket.<>c.<ConnectAsync>b__274_0(IAsyncResult iar)
      --- End of stack trace from previous location where exception was thrown ---
         at Npgsql.TaskExtensions.WithCancellation(Task task, CancellationToken cancellationToken)
         at Npgsql.TaskExtensions.WithTimeout(Task task, NpgsqlTimeout timeout)
         at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
         at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
         at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
         at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         --- End of inner exception stack trace ---
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
System.InvalidOperationException: An exception has been raised that is likely due to a transient failure.
 ---> Npgsql.NpgsqlException (0x80004005): Exception while connecting
 ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (111): Connection refused 127.0.0.1:5432
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source)
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.Socket.<>c.<ConnectAsync>b__274_0(IAsyncResult iar)
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.TaskExtensions.WithCancellation(Task task, CancellationToken cancellationToken)
   at Npgsql.TaskExtensions.WithTimeout(Task task, NpgsqlTimeout timeout)
   at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
fail: TeslaMateAgile.PriceService[0]
      Failed to update prices
System.InvalidOperationException: An exception has been raised that is likely due to a transient failure.
 ---> Npgsql.NpgsqlException (0x80004005): Exception while connecting
 ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (111): Connection refused 127.0.0.1:5432
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source)
   at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
   at System.Net.Sockets.Socket.<>c.<ConnectAsync>b__274_0(IAsyncResult iar)
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.TaskExtensions.WithCancellation(Task task, CancellationToken cancellationToken)
   at Npgsql.TaskExtensions.WithTimeout(Task task, NpgsqlTimeout timeout)
   at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at TeslaMateAgile.PriceHelper.Update() in /src/TeslaMateAgile/PriceHelper.cs:line 45
   at TeslaMateAgile.PriceService.DoWork() in /src/TeslaMateAgile/PriceService.cs:line 58
info: TeslaMateAgile.PriceService[0]
      Waiting 300 seconds until next update

I've confirmed that PostGres is listening on the correct port:

pi@raspberrypi:~/TeslaMateAgile $ sudo netstat -plunt |grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      636/postgres        
tcp6       0      0 ::1:5432                :::*                    LISTEN      636/postgres  

I'm running this on a RaspberryPi 4 running Raspian 10 Buster - all of the latest updates applied as of the submission of this issue.

Any ideas?

[MattJeanes]

Hi Drew, I've just responded to your posts on the TMC forum but I'll summarize it here for others who may run into this issue.

The connection string contains Server, Database, User Id and Password, these correspond to the DATABASE_HOST, DATABASE_NAME DATABASE_USER and DATABASE_PASS env variables respectively set in TeslaMate / Grafana and must match in order for this to work.

Using the default TeslaMate docker setup from their documentation your connection string would be:
ConnectionStrings__TeslaMate=Server=database;Port=5432;Database=teslamate;User Id=teslamate;Password=secret;

Would it be useful to support the same env variables as TeslaMate / Grafana as an alternate configuration? I think perhaps that would make it clearer for people to know what to put in them

[drewpost]

Thanks for the quick reply. I've updated it to:

ConnectionStrings__TeslaMate=Server=database;Port=5432;Database=teslamate;User Id=teslamate;Password=secret;
TeslaMate__UpdateIntervalSeconds=300
TeslaMate__GeofenceId=1 
TeslaMate__Phases=1
Octopus__RegionCode=J

But it's still giving the same error. I checked the Docker compose file and the connection string values match those used by TeslaMate itself so I know they are correct as that's still working.

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database 'teslamate' on server 'tcp://database:5432'.
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'TeslaMateAgile.Data.TeslaMate.TeslaMateDbContext'.
      System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000005, 0xFFFDFFFF): Name or service not known
         at System.Net.Dns.InternalGetHostByName(String hostName)
         at System.Net.Dns.ResolveCallback(Object context)
      --- End of stack trace from previous location where exception was thrown ---

[MattJeanes]

Ah just done some reading on the docker default network mode and looks like hostname lookup only works if you put teslamate inside the docker-compose.yml with the other services. You could try that, or you can lookup the ip address using sudo docker ps to find the container name of the database then sudo docker inspect --format '{{ .NetworkSettings.IPAddress }}' container_name_here and then put that in there.

Let me know how it goes! I'm currently running TeslaMateAgile myself on top of Kubernetes so the networking abstraction is a little different to the normal Docker one that I imagine most TeslaMate users are doing and I would like to improve the documentation for this

[drewpost]

OK - I added it to the docker-compose file for Teslamate and that did the trick! Thanks. I'll document this and submit a pull request to help others out. Quick one - does this work historically or is it only point in time forward?

[MattJeanes]

Perfect, yes if you could document about adding it to the TeslaMate docker-compose in particular that would be very useful to others I think, and yes it does work historically too, it calculates the cost for any charges within your geofence with no cost set (null value). Might also be worth documenting that too as I've had a few questions of that nature 🤣

[MattJeanes]

I went ahead and updated the app to support the same env variables as TeslaMate itself and added instructions on how to add it to docker-compose so don't worry about that documentation