Backup and restore PostgreSQL databases on Ubuntu


I want to make a backup of my postgres database to restore it later to another postgres database.

asked 2 years, 3 months ago
Reputation: 1

1 Answer


Back Up a PostgreSQL Database Using pg_dump

PostgreSQL includes a utility called pg_dump that can be used to dump database information into a file for backup purposes.

The pg_dump utility is run from the Linux command line. The basic syntax of the command is: pg_dump name_of_database > name_of_backup_file

The command must be run by a user with privileges to read all of the database information, so it is run as the superuser most of the time.

For a real-world example, we can log into the postgresuser user and execute the command on the default database, called postgresdb.

$ sudo su - postgresuser
$ pg_dump  postgresdb > postgresdb_backup

How to Restore Data Dumps from pg_dump with PostgreSQL

To restore a backup created by pg_dump, you can redirect the file into psql standard input, but you need an empty database created prior to restore.
psql empty_database < backup_file

Note: This redirection operation does not create the database in question. This must be done in a separate step prior to running the command.

$ psql postgresnewdb < postgresdb_backup

answered 2 years, 3 months ago
Reputation: 1

Your Answer

Nothing to preview

Post Answer

Asked:  2 years, 3 months ago
Viewed:  620 times
Active:  2 years, 3 months ago