Postgresql – Differences between .dump and .sql in pg_dump PostgreSQL

formatpg-dumppg-restorepostgresql

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…

Best Answer

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.