Search code examples
postgresqlsshconsolepgadmin

Console pgAdmin-like software


I like the pgAdmin III GUI software, but the GUI uses more bandwidth than SSH console.

psql is not interactive, without menus, tables list, etc.

Does exist some interactive text-mode tool to connect to PostgreSQL ?


Solution

  • There is no text-mode window-and-menu curses/ncurses style text mode interface for PostgreSQL; no ncurses equivalent to PgAdmin-III.

    I strongly recommend learning psql and getting comfortable with it. You could use PgAdmin-III remotely as detailed below, but in the long run you'll be massively more productive if you learn psql.

    Use PgAdmin-III via ssh tunnels or direct connection

    You can always connect with PgAdmin-III via an ssh tunnel or remote TCP/IP connection. That way you aren't transmitting all the GUI data over the network, just the PostgreSQL protocol data.

    For ssh, do something like:

    ssh -L 15432:localhost:5432 remote_host
    

    then while the ssh session is open, connect to localhost port 15432 to make a connection to the remote DB.

    This will work even if the remote DB is only listening on 127.0.0.1. It'll also work if you ssh into a bastion host then connect to the DB server from there; just change localhost in the -L argument to the IP/hostname of the Pg server. For more information see the ssh manual, particularly LocalForward for IP forwarding, ProxyCommand for custom multilayer tunnels, and the -D option for dynamic SOCKS proxying.

    It's even possible to use an ssh tunnel to connect to a server that's only listening for unix socket connections, by running socat to proxy between the unix socket and TCP.

    None of this will work when you're connecting to a Windows host, but rdp2tcp can be used to tunnel TCP over RDP connections for similar effect. See this question.

    Use psql

    psql is pretty interactive. Though it isn't a GUI windowing interface, it's hardly just a scripting tool. It provides lots of visibility into the system with the \d commands, lots of info via \h, tab completion, paging, \e break-out command editing, and lots of other interactive features.

    Use \? for psql help, and \h SQL_COMMAND for syntax of a particular SQL command, eg \h INSERT.