How To Backup & Restore PostgreSQL Tables

PostgreSQL is an open source database released under the PostgreSQL license. This license allows for the free use and distribution of the PostgreSQL software for any reason. PostgreSQL fully supports the SQL standard and allows for the backup and restoration of database files through a feature called an SQL dump. This means you can export and import database data at any time from within the database system using the PostgreSQL utility "pg_dump."

Instructions

Back Up the PostgreSQL Table

1. Open the Terminal or Command window on the system. Although the process varies between systems, Windows 7 users, for example, can open the "Start" menu, type "cmd" and press "Enter."

2. Navigate to the PostgreSQL "bin" directory on the command line. This directory is located inside the folder where you installed PostgreSQL. You can change directories on most operating systems using the "cd" command, such as "cd C:\postgresql\bin".

3. Run the pg_dump application with the proper username. Type "pg_dump -U <database> > output.sql". Replace "<database>" with the name of the database to which you would like to connect.

4. Enter the database password when prompted, then press "Enter." A new file will be created called "output.sql," which contains a complete backup of the database.

Restore the Database

1. Open a new Terminal window or Command Prompt if you have not already done so.

2. Navigate to the PostgreSQL "bin" directory.

3. Load the exported dump file in the "psql" application. Type "psql -U <dbname> < output.sql". Replace "<dbname>" with the name of the database to which you would like to import the SQL backup.

4. Enter the password when prompted, then press "Enter."

References

• Open Source Initiative: The PostgreSQL License (TPL)
• PostgreSQL: Backup and Restore