Differences between .dump and .sql in pg_dump PostgreSQL

Question

I'm trying to take a backup of schema using pg_dump like,

$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.dump

It create the backup, But I can't restore it using pg_restore. It give error like this,

pg_restore: [archiver] input file does not appear to be a valid archive

Then I try to take the backup with .sql extension like,

$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.sql

But It also gives the same error.

pg_restore: [archiver] input file does not appear to be a valid archive

After that I try to take a backup by specifying the format like, -Fc in both .dump and .sql extensions. Then I try to restore it. It restored successfully.

So now my doubt is,

What is the differences between the extensions of .dump and .sql in pg_dump in PostgreSQL?

Because both needs to specify the format.

Thanks in advanced...

Answer 1

The file extension means nothing. At all. It's just a part of a file name.

If you want a custom-format dump for use with pg_restore use -Fc as an argument to pg_dump.

pg_dump defaults to generating SQL-format dumps for use with psql.

See the manual for more details.

Answer 2

Try this

see your actual names in the Cluster column

pg_lsclusters

restore a DB backup dump with

pg_restore -h HOST -p PORT -U SUPERUSERNAME -C -d postgres DBtoRESTORE.dump