Server Administration & Management

How to Backup & Restore PostgreSQL Database

pg_dump is an effective tool for PostgreSQL through which we can backup postgres database and to restore PostgreSQL database, command psql is enough.

1) Backup postgres database, By ssh to server and execute following command to take backup of database.

# pg_dump -U Db_user Db_name -f database_name.sql

2) Executing above command will prompt you for database password, once password is authenticated database will be backup into file database_name.sql

Note: replace Db_User and Db_name with your actual database user and database name respectively.

3) Restore postgres database

# psql -U Db_user -d Db_name -f mydb.sql

November 16, 2011 Posted by | PgSQL | , | Leave a comment