Backup and restore a postgresql DB

Some times I need to backup and restore a postgresql DB,
I used to have these commands on a stick note but its better to have them here :)
with practice I improved my commands to do them better and better :) here are two of the ones I use:

To backup:

pg_dump -h localhost -p 5432 -U postgres -Fc mydb  > mydb_$(date +"%Y_%m_%d").dump

-h is the host where is the name or ip of the server where your database is running
-p is the port of the server
-U is the username
-Fc means Output a custom-format archive suitable for input into pg_restore
mydb is the name of your database
> means where you are sending the output to 
and $(date +"%Y_%m_%d") add the current date to your file

To Restore:

Tip: when I need to restore and 'overwrite' the actual database, I found only two ways to do it:
one is to delete the database and create a new one and restore it after that, but that could not work properly if you dont have the right permissions to do it. So, the second way it's better, you just have to delete the schema 'public' in the mayority of the cases, create it again and restore it.

to do that I do this:
psql -h localhost -p 5432 -d mydb -U postgres or psql -h localhost -p 5432 -U postgres and connect to your database with \connect mydb;
after that:
drop schema public cascade; create schema public;

Now restore:

pg_restore -h localhost -p 5432 -U postgres -d mydb mydb_2004_12_31.dump -W

and that's it. 


Entradas populares