--- 
canonical: 'https://mwop.net/blog/2024-09-20-postgres-misc.html'
title: 'Miscellaneous Postgres Commands'
author: "[Matthew Weier O'Phinney](https://mwop.net)"
created: '2024-09-20T15:22:03-05:00'
updated: '2024-09-20T15:22:03-05:00'
tags:
  - postgres
  - til

---
I switched over from SQLite to Postgres to power my site some months ago, and have found myself having to learn some new usage when interacting with the database.
These are likely very old hat for anybody familiar with Postgres, but I find myself having to remind myself what they are.









### Schema introspection commands

So, here's the list:

| Command | Description |
| --- | --- |
| `\list` or `\l` | List all databases |
| `\c <dbname>`  or `\connect <dbname>` | Connect to a database |
| `\dt` | List all tables in the current database using your search path |
| `\dt *` | List all tables in the current database, ignoring the search path |

### Determine what databases are available

The other thing I've needed to do is to find out what databases are present on the machine; this is useful when I want to blow away a database and recreate it.
This is done via SQL:

```sql
SELECT schema_name
FROM information_schema.schemata
```

Most times, you'll be on `public`.

### Restore from a backup

I have an automated process that backs up the database nightly using `pg_dump`, and gzips the result.

When I want to drop the database, I discovered that you need to (a) determine what database you're in (see previous section), and then (b) delete all tables in it:

```sql
DROP SCHEMA public CASCADE
```

Once done, recreate the database:

```sql
CREATE SCHEMA public
```

From there, you can restore from your backup.
And regarding that, I discovered that in order to restore from a gzipped DB dump, you use `psql`, and not `pg_restore`.