Paul Labis: This is a personal blog where everyone can read my experiences, ideas and thoughts about programming, latest gadgets and other information technology related topics, lifestyle and many other stuff I would like to share to the public.

How to Backup and 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.