Select Page

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

  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


NOTE
: PostgreSQL 8.3.7 has been used and installed on Ubuntu 9.04 with this how-to

Last Updated On April 07, 2018

Leave a reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.