Friday, May 6, 2016

PostgreSQL database backup and restore

I needed to backup a DB then reset a VM and then put the backup on it.
This is what I did ( I think )

To dump it to a file of sql commands:

pg_dump -U [user-name] [source db name] -f [name of file to dump it to]

One nice thing is that it is human readable.

To reinstall it:

  1. I had to drop the existing DB with the same name ( I used pgAdmin III to do so)
  2. I recreated an empty DB with the same name, and since I needed extensions, I added the 2 extensions I needed.
  3. Then I loaded it in ( which could have been to a different DB name, but in my case I used the same one ):
pgsl -U [user-name] -d [db name to restore to] -f [name of the dump file]

Also note that the commands are found in PostgreSQL's bin directory.

No comments:

Post a Comment