How to create a backup of a postgres database to a sql file?

Question: How to create a backup of a postgres database to a sql file? This sql file then can be used to restore the database on a later stage.

Answer:

Follow the steps below in order to create a backup

1. Open a Terminal Window if not yet in command line
2. Login as user postgres. eg. sudo su postgres 
3. 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)
4. Now you have a backup of your pqsl database in sql file format

• Add New Search
• Comments

Write comment

Your Contact Details:

Name:

Email: do not notifynotify

Comment:

Title:

UBBCode:         -color-aquablackbluefuchsiagraygreenlimemaroonnavyolivepurpleredsilvertealwhiteyellow -size-tinysmallmediumlargehuge

Message:

Security

Please input the anti-spam code that you can read in the image.