supabase db diff fails with: psycopg2.OperationalError: could not translate host name "localhost" to address: Try again

[vixalien]

Describe the bug I'm trying to do supabase db diff but it fails with psycopg2.OperationalError: could not translate host name "localhost" to address: Try again. I even tried changing the DNS resolver from native to https, but it doesn't work

To Reproduce Steps to reproduce the behavior:

1. Try to do a supabase db diff

Expected behavior Normal diff to get written

Screenshots

Logs:

$ supabase db diff --dns-resolver https
Connecting to local database...
Creating shadow database...
Setting up initial schema...
Applying migration 20220822165112_xyz.sql...
Applying migration 20220822165112_xyo.sql...
Diffing schemas: extensions,public
error diffing schema: error running container: exit 1:                                                                                                 
WARNING: Running pip as root will break packages and permissions. You should install packages reliably by using venv: https://pip.pypa.io/warnings/venv
WARNING: You are using pip version 21.1.1; however, version 23.2.1 is available.                                                                       
You should consider upgrading via the '/usr/local/bin/python -m pip install --upgrade pip' command.                                                    
Traceback (most recent call last):                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/schemainspect/misc.py", line 9, in connection_from_s_or_c                                               
    s_or_c.engine                                                                                                                                      
AttributeError: 'Session' object has no attribute 'engine'                                                                                             

During handling of the above exception, another exception occurred:                                                                                    

Traceback (most recent call last):                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect                                            
    return fn()                                                                                                                                        
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 301, in connect                                                          
    return _ConnectionFairy._checkout(self)                                                                                                            
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout                                                        
    fairy = _ConnectionRecord.checkout(pool)                                                                                                           
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 419, in checkout                                                         
    rec = pool._do_get()                                                                                                                               
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get                                                          
    self._dec_overflow()                                                                                                                               
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__                                                   
    compat.raise_(                                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_                                                         
    raise exception                                                                                                                                    
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get                                                          
    return self._create_connection()                                                                                                                   
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection                                               
    return _ConnectionRecord(self)                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 362, in __init__                                                         
    self.__connect()                                                                                                                                   
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 605, in __connect                                                        
    pool.logger.debug("Error on connect(): %s", e)                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__                                                   
    compat.raise_(                                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_                                                         
    raise exception                                                                                                                                    
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 599, in __connect                                                        
    connection = pool._invoke_creator(self)                                                                                                            
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 578, in connect                                                      
    return dialect.connect(*cargs, **cparams)                                                                                                          
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect                                                     
    return self.dbapi.connect(*cargs, **cparams)                                                                                                       
  File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect                                                             
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)                                                                             
psycopg2.OperationalError: could not translate host name "localhost" to address: Try again                                                             

The above exception was the direct cause of the following exception:                                                                                   

Traceback (most recent call last):                                                                                                                     
  File "/usr/local/bin/migra", line 8, in <module>                                                                                                     
    sys.exit(do_command())                                                                                                                             
  File "/usr/local/lib/python3.9/site-packages/migra/command.py", line 121, in do_command                                                              
    status = run(args)                                                                                                                                 
  File "/usr/local/lib/python3.9/site-packages/migra/command.py", line 86, in run                                                                      
    m = Migration(                                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/migra/migra.py", line 31, in __init__                                                                   
    self.changes.i_from = get_inspector(                                                                                                               
  File "/usr/local/lib/python3.9/site-packages/schemainspect/get.py", line 14, in get_inspector                                                        
    c = connection_from_s_or_c(x)                                                                                                                      
  File "/usr/local/lib/python3.9/site-packages/schemainspect/misc.py", line 14, in connection_from_s_or_c                                              
    return s_or_c.connection()                                                                                                                         
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1516, in connection                                                    
    return self._connection_for_bind(                                                                                                                  
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1526, in _connection_for_bind                                          
    return self._transaction._connection_for_bind(                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind                                           
    conn = bind.connect()                                                                                                                              
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3166, in connect                                                       
    return self._connection_cls(self, close_with_result=close_with_result)                                                                             
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__                                                        
    else engine.raw_connection()                                                                                                                       
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3245, in raw_connection                                                
    return self._wrap_pool_connect(self.pool.connect, _connection)                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3215, in _wrap_pool_connect                                            
    Connection._handle_dbapi_exception_noconnection(                                                                                                   
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2068, in _handle_dbapi_exception_noconnection                          
    util.raise_(                                                                                                                                       
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_                                                         
    raise exception                                                                                                                                    
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect                                            
    return fn()                                                                                                                                        
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 301, in connect                                                          
    return _ConnectionFairy._checkout(self)                                                                                                            
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout                                                        
    fairy = _ConnectionRecord.checkout(pool)                                                                                                           
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 419, in checkout                                                         
    rec = pool._do_get()                                                                                                                               
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get                                                          
    self._dec_overflow()                                                                                                                               
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__                                                   
    compat.raise_(                                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_                                                         
    raise exception                                                                                                                                    
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 142, in _do_get                                                          
    return self._create_connection()                                                                                                                   
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection                                               
    return _ConnectionRecord(self)                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 362, in __init__                                                         
    self.__connect()                                                                                                                                   
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 605, in __connect                                                        
    pool.logger.debug("Error on connect(): %s", e)                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__                                                   
    compat.raise_(                                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_                                                         
    raise exception                                                                                                                                    
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 599, in __connect                                                        
    connection = pool._invoke_creator(self)                                                                                                            
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 578, in connect                                                      
    return dialect.connect(*cargs, **cparams)                                                                                                          
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect                                                     
    return self.dbapi.connect(*cargs, **cparams)                                                                                                       
  File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect                                                             
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)                                                                             
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "localhost" to address: Try again                           

(Background on this error at: http://sqlalche.me/e/14/e3q8)                                                                                            

Try rerunning the command with --debug to troubleshoot the error.

Desktop (please complete the following information):

• OS: arch rolling
• Browser (if applicable: N/A
• Version of CLI: v1.100.1
• Version of supabase-js (if applicable): N/A
• Version of Node.js (if applicable): v20.5.1

Additional context Add any other context about the problem here.

[sweatybridge]

This could be due to localhost resolving to ipv6 address on your machine. Could you give the beta version a try?

npx supabase@beta db diff

[ArnaudScheffler]

I think the real issue is that the meta container started during the diff use localhost as target db. In this case, localhost will resolve to the meta container itself and not the real host. Using host.docker.internal in supabase db diff --db-url postgresql://supabase_admin:PASSWORD@host.docker.internal:54328/postgres doesn't work because the first connexion to the db fail.

I think one solution would be to to replace localhost by host.docker.internal in env used by the meta container.

[sweatybridge]

We use docker's host network mode to allow localhost in migra container to resolve to the host machine's ip. @ArnaudScheffler are you able to reproduce this issue using beta version?

[ArnaudScheffler]

Yes, I have the same issue with the beta version :

npx supabase@beta db diff --db-url postgresql://supabase_admin:PASSWORD@localhost:54328/postgres               1 ↵ arnaud@MBP-de-Arnaud-001
Connecting to remote database...
Creating shadow database...
Setting up initial schema...
Applying migration 20230717125841_init_pipeline.sql...
Applying migration 20230717125842_init_tera.sql...
Applying migration 20230717125843_init_colas.sql...
Applying migration 20230717125844_migration_to_deploy.sql...
Diffing schemas: extensions,private,public
error diffing schema: error running container: exit 1:                                                                                                 
WARNING: Running pip as root will break packages and permissions. You should install packages reliably by using venv: https://pip.pypa.io/warnings/venv
WARNING: You are using pip version 21.1.1; however, version 23.3.1 is available.                                                                       
You should consider upgrading via the '/usr/local/bin/python -m pip install --upgrade pip' command.                                                    
Traceback (most recent call last):                                                                                                                     
  File "/usr/local/lib/python3.9/site-packages/schemainspect/misc.py", line 9, in connection_from_s_or_c                                               
    s_or_c.engine                                                                                                                                      
AttributeError: 'Session' object has no attribute 'engine'

...

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused                                           
    Is the server running on host "localhost" (::1) and accepting                                                                                      
    TCP/IP connections on port 54328?                                                                                                                  
could not connect to server: Connection refused                                                                                                        
    Is the server running on host "localhost" (127.0.0.1) and accepting                                                                                
    TCP/IP connections on port 54328?                                                                                                                  

(Background on this error at: http://sqlalche.me/e/14/e3q8)                                                                                                

I still belive there is something wrong with localhost inside the meta container. Take a look at the following tests :

docker run -it --rm --network host public.ecr.aws/supabase/postgres:14.1.0.89 bash

root@docker-desktop:/# psql -h localhost -p 54328 -U postgres
psql: error: connection to server at "localhost" (::1), port 54328 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 54328 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
root@docker-desktop:/# 
exit

Localhost doesn't resolve in container but it's ok outside :

psql -h localhost -p 54328 -U postgres
psql (14.8 (Homebrew), server 15.3)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
Type "help" for help.

postgres=> 

[sweatybridge]

Hmm my best guess is some differences between OS. As mentioned in their docs

The host networking driver only works on Linux hosts, and is not supported on Docker Desktop for Mac, Docker Desktop for Windows, or Docker EE for Windows Server.

I personally use an intel mac, and the command works

qiao@Qiaos-MBP cli % docker run -it --rm --network host public.ecr.aws/supabase/postgres:15.1.0.103 bash
root@docker-desktop:/# psql -h localhost -p 54322 -U postgres
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg20.04+1), server 15.1 (Ubuntu 15.1-1.pgdg20.04+1))
Type "help" for help.

postgres=> \q
root@docker-desktop:/# exit

Are you perhaps on windows?

[ArnaudScheffler]

Oh okay, it seems that host network can be tricky 😞. I use a Mac with Apple silicon (macOS 13.4.1) and docker desktop (4.9.1). I just updated docker desktop (4.24.2) but nothing changed.

[kamilogorek]

@ArnaudScheffler give https://orbstack.dev/ a try, it unifies network issues between different OSs.

btw. Docker Desktop do not support ipv6 (https://docs.docker.com/desktop/troubleshoot/known-issues/) and your logs state that it indeed still tries to connect to ipv6 address.

[vixalien]

I have Linux as well

Le lun. 23 oct. 2023, 15:44, Kamil Ogórek @.***> a écrit :

@ArnaudScheffler https://github.com/ArnaudScheffler give https://orbstack.dev/ a try, it unifies network issues between different OSs.

— Reply to this email directly, view it on GitHub https://github.com/supabase/cli/issues/1574#issuecomment-1775234274, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJB5FCNWE5ZEYSASO6ANXFTYAZYFPAVCNFSM6AAAAAA552MSKWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONZVGIZTIMRXGQ . You are receiving this because you authored the thread.Message ID: @.***>

[ArnaudScheffler]

Thanks for the discovery @kamilogorek !

I've also tried 127.0.0.1, my Mac IPv4 address and it's DNS name :

root@docker-desktop:/# psql -h 192.168.1.89 -p 54328
psql: error: connection to server at "192.168.1.89", port 54328 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
root@docker-desktop:/# psql -h MBP-de-Arnaud-001 -p 54328
psql: error: connection to server at "MBP-de-Arnaud-001" (192.168.1.89), port 54328 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

I found a workaround for my specific use case : my target db is in fact in a k8s cluster. I initially used kubectl proxy to establish a connection but I found another way to expose the db so it doesn't use localhost anymore.

[kamilogorek]

Closing the issue, as it seems like the original issue has been partially resolved or there is a working solution. Please do not hesitate to ping me if it is still relevant, and I will happily reopen it.

[vixalien]

Hi. I'm sorry, but I didn't get the solution.

[sweatybridge]

@vixalien are you using docker desktop or just docker engine? https://docs.docker.com/desktop/install/archlinux/

I suspect host network requires docker desktop to work.

[vixalien]

I use docker engine. however, I don't think it's the one causing the issue because otherwise supabase used to work correctly.

Le mar. 24 oct. 2023, 04:33, Han Qiao @.***> a écrit :

@vixalien https://github.com/vixalien are you using docker desktop or just docker engine? https://docs.docker.com/desktop/install/archlinux/

I suspect host network requires docker desktop to work.

— Reply to this email directly, view it on GitHub https://github.com/supabase/cli/issues/1574#issuecomment-1776404520, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJB5FCJVSSGQCUEMR5RG5NTYA4SI3AVCNFSM6AAAAAA552MSKWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONZWGQYDINJSGA . You are receiving this because you were mentioned.Message ID: @.***>

[bricesuazo]

This could be due to localhost resolving to ipv6 address on your machine. Could you give the beta version a try?

npx supabase@beta db diff

This works for me. I'm using Windows.

[iamchubko]

Updated cli today from 1.115.4 where everything worked to 1.123.4 and received this error, using Apple silicon.

Update: but now I see that 1.115.4 was released on November 27th and OP is using 1.100.1 with issue created in October so the cause might be different

[sweatybridge]

This should have been resolved by https://github.com/supabase/cli/pull/1852

[ivan-robert]

Hello I am getting exactly the same error word by word. It worked fine before but it just started doing this. Could it be due to the latest supabase updates ? (ipv6 migration for instance)

[sweatybridge]

@ivan-robert could you create a new issue please? The original error concerns local diff only. It's completely unrelated to IPv6 migration.

[ivan-robert]

I can and will if necessary, but my usecase is exactly the same. I am only trying to run supabase diff --linked to pull the remote in a local instance

edit: I am currently writing the issue

[sweatybridge]

I am only trying to run supabase diff --linked to pull the remote in a local instance

Yes a new issue would be good because this ticket is about diffing without --linked flag.

As a blind shot though, you can try npx supabase@beta db diff --linked.

[ivan-robert]

Tried the command but got the same error... Here is the issue : #1910

[PhilippS93]

same error here on windows using npx supabase@beta db pull --linked