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.

Nothing wrong. You only need add an extra "-v" in the pg_dump command line to show the output information in your cmd. — SeanH
– SeanH, Commented May 15, 2022 at 11:01
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. — Adrian Klaver
– Adrian Klaver, Commented May 15, 2022 at 15:59

Siddheshwar Soni · Accepted Answer · 2022-07-15 06:53:32Z

11

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.

answered Jul 15, 2022 at 6:53

Siddheshwar Soni

4804 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Awalias · Accepted Answer · 2022-05-20 07:20:50Z

0

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://stackoverflow.com/a/49689589/1267728

answered May 20, 2022 at 7:20

Awalias

2,1677 gold badges31 silver badges51 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related