How Can We Help?
How to create a backup of a PostgreSQL database to a SQL file
How to create a backup of a PostgreSQL database to a sql file? This sql file then can be used to restore the database on a later stage.
Follow the steps below in order to create a backup
- Open a Terminal Window if not yet in command line
- Login as user postgres. eg. sudo su postgres
- Type in the following command
pg_dump db_name -CdiOv > /tmp/db_name_backup.sql- pg_dump = Utility to backup a PostgreSQL db.
- -C = Begin the output with a command to create the database itself and reconnect to the created database.
- -d = Dump the data as INSERT commands
- -i = Ignores version mismatch between the old database server and the new database server.
- -O = No owner, the database does not belong to any specific owner that was specified in the database
- -v = Verbose, will output a detailed report of pg_dump if ran in command line.
- > /tmp/db_name_backup.sql = dump the output of the command into a file called db_name_backup.sql (Here you may choose any location on your drive where you want the backup to be stored)
- Now you have a backup of your pqsl database in sql file format
NOTE: PostgreSQL 8.3.7 has been used and installed on Ubuntu 9.04 with this how-to
