Postgresql Database export to .sql file

I want to export my database as a .sql file. Can someone help me? The solutions I have found don't work. A detailed description please. On Windows 7. Thanks


Solution 1:

pg_dump defaults to plain SQL export. both data and structure.

open command prompt and run pg_dump -U username -h localhost databasename >> sqlfile.sql

Above command is preferable as most of the times there will be an error which will be something like - ...FATAL: Peer authentication failed for user ...

Solution 2:

In windows, first, make sure the path is added in environment variables PATH

C:\Program Files\PostgreSQL\12\bin

After a successful path adding restart cmd and type command

pg_dump -U username -p portnumber -d dbname -W -f location

this command will export both schema and data

for only schema use -s in place of -W and for only data use -a.

replace each variable like username, portnumber, dbname and location according to your situation everything is case sensitive, make sure you insert everything correctly, and to import

psql -h hostname -p port_number -U username -f your_file.sql databasename

make sure your db is created or creation query is present in .sql file