How to create an sql dump file of a Postgres database using psql

Question

I currently have created a database via Supabase, which uses the Postgres database. After doing so, I then downloaded Postgres on my computer, which gave me the psql command line script for use to connect to postgres databases.

I intend to create a local backup of my Supabase database through the psql script. This is after I have connected to the supabase database through psql

From this, i can see that psql successfully connected to the database.

I then tried to use the pg_dump command to attempt to create a dump file of the database. This was the command I used:

pg_dump postgresql://postgres:[Database password]@db.epvzhrganrzfwexmiksg.supabase.co:5432/postgres > dumpfile.sql

However, after I pressed enter, psql did not run the command, it simply moved to a new command line. As such, I am not sure whether I inputted something wrong for the dump command or is it a different psql command I need to use for it.

Discouragement

Nothing wrong. You only need add an extra "-v" in the pg_dump command line to show the output information in your cmd. – Gael

pg_dump is it's own command line command, you can't run it inside psql. Amazing enough this is covered in the docs Postgres client programs. – Arnettearney

Answer 1

As mentioned, you have successfully connected with psql.

• Below are the steps to take plain .sql backup, if you have the OS prompt access.

From OS prompt

$ pg_dump mydb > mydb.sql

From psql prompt

postgres# \! pg_dump mydb > mydb.sql

Post backup done , mydb.sql file can be copied & import via psql to local machine.

Diecious

Answer 2

as mentioned by @adrian-klaver the pg_dump command should be run directly from the terminal, and not from inside the psql shell

run pg_dump -V to check that it's installed. It is usually installed when you install psql https://mcmap.net/q/80868/-correct-way-to-install-psql-without-full-postgres-on-macos

Legerdemain