PostgreSQL: Export database to .sql file

Question

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.

Answer 1

You can use cmd: May require you to change to postgres account to do prompt

Linux

sudo -i -u postgres

Export Database cmd

pg_dump --host=127.0.0.1 --username=account databaseName --file=/tmp/databaseName.sql

Answer 2

For example, you can export the schema and data of apple database of the user(role) john to backup.sql as shown below. *backup.sql is created if it doesn't exist and you can also use -Fp explained in my answer of how to export and archive a database with -Fc and -Ft and my answer explains how to export and import all databases and my answer explains how to import backup.sql into orange database and the doc explains how to export and import databases:

pg_dump -U john apple > backup.sql

Or:

pg_dump -U john -d apple > backup.sql

Or:

pg_dump --username=john --dbname=apple > backup.sql

Or:

pg_dump --username john --dbname apple > backup.sql

Or:

pg_dump -U john -f backup.sql apple

Or:

pg_dump -U john -d apple -f backup.sql

Or:

pg_dump --username=john --file=backup.sql apple

Or:

pg_dump --username john --file backup.sql apple

Or:

pg_dump --username=john --dbname=apple --file=backup.sql

Or:

pg_dump --username john --dbname apple --file backup.sql

Then, you need to input a password after running the command above:

Password:

In addition, you can export the schema and data of apple database of the user(role) john to backup.sql without a password prompt by setting a password(e.g., banana) to PGPASSWORD as shown below:

PGPASSWORD=banana pg_dump -U john apple > backup.sql

And, you can export only the schema of apple database of the user(role) john to backup.sql with -s or --schema-only as shown below:

pg_dump -U john -s apple > backup.sql

Or:

pg_dump -U john --schema-only apple > backup.sql

Or, you can export only the data of the specific tables person and animal of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below. *Specifying multiple tables are available in one command and my answer explains how to export only data more:

pg_dump -U john -a --column-inserts -t person -t animal apple > backup.sql

Then, you need to input a password after running the command above:

Password:

Answer 3

For example, you can export and archive the schema and data of apple database of the user(role) john to backup.sql with e.g. -Fc, -Ft, --format=c and --format t as shown below. *backup.sql is created if it doesn't exist and my answer explains how to export schema and data and my answer explains how to import archive backup.sql into orange database and the doc explains how to export and import databases:

pg_dump -U john -Fc apple > backup.sql

Or:

pg_dump -U john -Ft -d apple > backup.sql

Or:

pg_dump --username=john --format=c --dbname=apple > backup.sql

Or:

pg_dump --username john --format t --dbname apple > backup.sql

• c means custom which outputs a custom-format archive suitable for input into pg_restore. *pg_restore must be used to import the file.

• t means tar which outputs a tar-format archive suitable for input into pg_restore. *pg_restore must be used to import the file.

• d means directory which outputs a directory-format archive suitable for input into pg_restore. *pg_restore must be used to import the file but it doesn't work properly giving error.

• p means plain which outputs a plain-text SQL script file (the default). *psql must be used to import the file.

*pg_restore is to import archive files created with c, t and d while psql is to import non-archive files created with p (default) and the doc explains it more:

Then, you need to input a password after running the command above:

Password:

In addition, you can export and archive the schema and data of apple database of the user(role) john to backup.sql without a password prompt by setting a password(e.g., banana) to PGPASSWORD as shown below:

PGPASSWORD=banana pg_dump -U john -Fc apple > backup.sql

And, you can export and archive only the schema of apple database of the user(role) john to backup.sql as shown below:

pg_dump -U john -s -Fc apple > backup.sql

Or:

pg_dump -U john --schema-only -Fc apple > backup.sql

Or, you can export and archive only the data of the specific tables apple database of the user(role) john to backup.sql only INSERT statement which has column names as shown below:

pg_dump -U john -Fc -a --column-inserts -t person -t animal apple > backup.sql

Then, you need to input a password after running the command above:

Password:

Answer 4

If you are on windows open cmd and run the folloing command, edit the details like user and database

pg_dump -U postgres -h localhost -d statepos > C:\Users\ston\Desktop\statepos.sql

Answer 5

Go to your command line and run

pg_dump -U userName -h localhost -d databaseName > ~/Desktop/cmsdump.sql

Answer 6

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

Documentation: https://www.postgresql.org/docs/current/app-pgdump.html

Answer 7

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 ...