0

I need to understand the relations between tables in a PostgreSQL database. I will not have the ability to download pgAdmin4 like I am used to working in. So after looking around I found pg_dump.exe built into PostgreSQL. I thought I could just do something like this in the SQL Shell (psql) to get a dump of the database and then have the ability to upload it into another system:

database=# pg_dump database > path/to/save/file.sql;

Based on the docs >> https://www.postgresql.org/docs/9.3/app-pgdump.html

But when I run that I get an error:

ERROR:  syntax error at or near "pg_dump"
LINE 1: pg_dump database > path/to/save/file.sql;
        ^

I saw on this stack overflow question was similar to my issue with the pg_dump error. In the solutions, Adrian says that pg_dump does not work within psql. When I run the code that Adrian suggested, I also get an error. Any thoughts on how I can use psql to get the information that I need of this database?

Note: I am accessing a Linux VM on a Windows machine.

Lemon
  • 19
  • 3
  • 1
    `pg_dump` is a program that you use within the Windows terminal, not within `psql`. If you want to see relationships between tables, use the `\d` command within `psql` – richyen Nov 06 '20 at 21:13
  • 1
    @richyen 1) `pg_dump` works in many terminals, not only with Windows console. 2) It is possible to execute an external program directly from the `psql` using `\!` meta-command: `\! pg_dump` – Abelisto Nov 06 '20 at 21:24
  • Why *are* you trying to use `pg_dump` from within `psql` if you already that's not how it works? – Bergi Nov 06 '20 at 21:53
  • @Bergi I found out that doesn't work after searching my error. Hence the post. – Lemon Nov 06 '20 at 22:04
  • @richyen I thought that `\d` only gave the columns and data types etc, but not how those columns are related to other table columns? – Lemon Nov 06 '20 at 22:08
  • 1
    @Abelisto is correct, though I am strictly making statements in regards to this post. If `\d` will give columns and such. If you want to see foreign key relationships, please use `\d+` – richyen Nov 06 '20 at 22:13
  • thanks @richyen, I was hoping for a less manual way than going though each table. But it will get the job done. – Lemon Nov 09 '20 at 15:34
  • If you are looking for foreign key relationships, then you can find that here: https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys. From your original post, it sounded like you were trying to get `pg_dump` working so you knew the table structure. A caveat--if the tables are defined without foreign key constraints, you will not be able to understand the relationships even with `\d` – richyen Nov 09 '20 at 17:06

0 Answers0