Backup and restore a postgresql database using commandline

Normally I use phppgadmin Export/Import feature for this task, but when the database in question contains tables with large number of rows, phpgadmin simply fails to do the task, during that time we can rely on this method. Even otherwise this method is more faster than the phppgadmin method. So we'll be using the pg_dump command to take the backup of a postgresql database and the pg_restore commandrestore it to another postgresql database:

Creating the Dump

Take the dump of a database in postgresql:

$ pg_dump -h localhost -U username -W -F t database_name > database_dump_file.tar

Explanation for the commanline options used:

• -h localhost - the database hostname, we've used localhost ( Specifies the host name of the machine on which the server is running. )

• -U username - the database username ( Connects to the database as user username )

• -W - force pg_dump to prompt for a password before connecting to the database.

• -F t - Specify the format of the output ( We've used the t option to specify tar archive file suitable for input into `pg_restore)

• database_name - name of the database, of which we're taking backup

• database_dump_file.tar -name of the output file

For more details into the command-line options for `pgdump`, please refer:
pg_dump manpage_

Restoring the dump

$ pg_restore -h localhost -U username -W -F t -d new_database_name database_dump_file.tar

Explanation for the commanline options used:

• -h , -U, -W, -F options are the same as in pg_dump, as explained previously.

• -d new_database_name - name of the database to which we're restoring the backup ( here we're restoring to new_database_name database)

• database_dump_file.tar -name of the input file

For more details into the command-line options for `pgrestore`, please refer:
pg_restore manpage_