Running \dt inside a psql shell results in "Did not find any relations"

Questions / Help
,
1

Having troubles running \dt to list all the tables and I try to figure out why that is. Now it might be my newness to everything, but I hope someone here could help me understand or give pointers where to look. I want to be honest, I started not too long ago with Phoenix (and having a blast) along with PostgreSQL. So it might definitely be my inexperience talking here.

Problem
I’m trying to list all the tables and query my data from the shell as I was having migration issues when deploying a new version of my app.

Running the command \dt from within a psql shell (using flyctl postgres connect -a <my-postgres-app-name> gives me a “Did not find any relations”. I can’t remember receiving that one before.

Context
In order to replicate the problem. I deleted the app and database and started fresh with the same result. I successfully deployed a new Phoenix app + setup a new db (per the guide). Everything is working fine and I can store things in the database.

However when I try to list all tables (\dt) from within a psql shell (using flyctl postgres connect -a I receive a “Did not find any relations”.

What I think is the issue
After searching around found that it might be related to search_paths. I tried setting the searchpath myself for user postgres but I’m not sure if this is actually the problem.

Extra info that might be useful:

running \dn gives me:

   List of schemas
  Name  |   Owner
--------+------------
 public | flypgadmin

running '\dt+ * shows me only the pg_catalog schema (I expected my schema to be here):

 List of relations
   Schema   |          Name           | Type  |   Owner    | Persistence | Access method |    Size    | Description
------------+-------------------------+-------+------------+-------------+---------------+------------+-------------
 pg_catalog | pg_aggregate            | table | flypgadmin | permanent   | heap          | 56 kB      |
 pg_catalog | pg_am                   | table | flypgadmin | permanent   | heap          | 40 kB      |
 pg_catalog | pg_amop                 | table | flypgadmin | permanent   | heap          | 88 kB      |
 pg_catalog | pg_amproc               | table | flypgadmin | permanent   | heap          | 72 kB      |
 pg_catalog | pg_attrdef              | table | flypgadmin | permanent   | heap          | 8192 bytes |
 pg_catalog | pg_attribute            | table | flypgadmin | permanent   | heap          | 472 kB     |
 pg_catalog | pg_auth_members         | table | flypgadmin | permanent   | heap          | 40 kB      |
 pg_catalog | pg_authid               | table | flypgadmin | permanent   | heap          | 48 kB      |
 pg_catalog | pg_cast                 | table | flypgadmin | permanent   | heap          | 48 kB      |
 pg_catalog | pg_class                | table | flypgadmin | permanent   | heap          | 136 kB     |

Thank you!

2

Fixed:
Ugh. Found the solution and it’s just very obvious.

flyctl postgres connect -a <my-postgres-app-name> connects to the postgres database as user postgres.
I simply had to list the other databases using \l or \list and then \c <database-name> to connect to it. Running \dt then works and lists all the tables I was missing.

Unless one of the admins deem otherwise, figured I keep the topic alive along with my answer as it might help someone else.

5 Likes
3

Thanks for the solution, although I don’t find it obvious :upside_down_face:
I have 2 databases and one of them shows this behavior, not the other one, hence my confusion.
To connect from the command line, you can use

flyctl postgres connect -a <postgres app name> -d <database name>