Friday, July 10, 2015

Backup or Restore PostgreSQL Database

There is an easy way to create backup file and restore your current PostgreSQL database using the Linux terminal. I am not sure though if this is the easiest way.

As of the moment I am writing this article, I used the following command to backup and restore my database. 

Using pg_dump and psql will make your life easier. Below are the terminal command I use to backup and restore my database.
  • Backup : pg_dump -U {user_name} {database_name} -f {backup_file_name}
  • Restore : psql -U {user_name} -d {database_name} -f {backup_file_name}
If you are getting an error:
psql: FATAL: Ident authentication failed for user "username"...
You then need to locate file pg_hba.conf and add or change if already existing to look something like below:

local all all trust
host all trust
By the way, to locate file on your Ubuntu... Just run command "locate pg_hba.conf" on your terminal.
After which, restart PostgreSQL database by running "sudo /etc/init.d/postgresql_version restart". Hopefully the error should already be fixed.

There are lots of articles online that you can read to backup and restore you database. I hope this article helps you in one way or another.

I give all credits to for doing a good job on explaining how you can use pg_dump and psql to backup your PostgreSQL database. 

No comments:

Post a Comment