Output File from PSQL Command

She had an immense curiosity about life, and was constantly staring and wondering.

~ Henry James in The Portrait of a Lady.

On a bright sunny day, while you’re on vacation on a remote island, you get an emergency call from your office regarding a failure on the production database, and you don’t have your workstation with you. The only access you can get, due to slow internet speed, is SSH (Secure Shell). You try to access the remote production database using a rented computer and an SSH client, and the tools inside the server only consist of a psql command. How can you export and back up your SQL schema layouts?

I have been in exactly this situation, staring at a terminal on someone else’s computer while production burned in the background. The good news: the situation is not as hopeless as it feels, and you do not need a fancy GUI to get out of it.

Don’t panic and don’t worry, we got you covered: the psql saves your day if you’re just going to be exporting SQL schema layouts.

🛠️ The Rescue Command

First and foremost, fire up and execute the psql command. Inside the psql environment execute the commands below:

\o dump.txt
SELECT 'postgresql' AS dbms,t.table_catalog,t.table_schema,t.table_name,c.column_name,c.ordinal_position,c.data_type,c.character_maximum_length,n.constraint_type,k2.table_schema,k2.table_name,k2.column_name
  FROM information_schema.tables t
  NATURAL LEFT JOIN information_schema.columns c 
  LEFT JOIN(information_schema.key_column_usage k 
    NATURAL JOIN information_schema.table_constraints n
    NATURAL LEFT JOIN information_schema.referential_constraints r) 
  ON c.table_catalog=k.table_catalog AND c.table_schema=k.table_schema AND c.table_name=k.table_name AND c.column_name=k.column_name
  LEFT JOIN information_schema.key_column_usage k2 
  ON k.position_in_unique_constraint=k2.ordinal_position AND r.unique_constraint_catalog=k2.constraint_catalog AND r.unique_constraint_schema=k2.constraint_schema AND r.unique_constraint_name=k2.constraint_name 
  WHERE t.TABLE_TYPE='BASE TABLE' AND t.table_schema NOT IN('information_schema','pg_catalog');
\O

This command will create a dump file containing all the table structure with each constraint available. This is usually needed when you need to analyze an ER (Entity Relation) diagram. You could, for example, paste the output of this command into Lucid Chart to analyze entity relationships, which is exactly how I reconstructed the database design before attempting any fix.

A breakdown of this is: the SELECT command will query all table structure and schema, including every column, its data type, and every constraint linking tables together. The information_schema queries are Postgres’s own catalog views, so this is not a magic trick; it is reading the database’s documentation of itself.

Switches Legend:
\o => outputs to file
\O => turns off the output to file feature

The output file will be generated in the same directory where you run the psql command. From there, you can cat the file over your SSH session, scroll through it, and rebuild the schema if needed.

🧠 Final Thoughts

So guys, when on vacation, what are the things you can do on a slow internet and a rented computer? Quite a lot, as it turns out, as long as you know a few built in commands. The \o switch is a tiny feature that turns your SSH terminal into a database rescue kit, and it works on any machine that has psql, which is every Postgres server by definition.

Bookmark this one for the next production emergency, and remember: slow internet favors people who know their command line. Hope you enjoyed this article!