Posted By: Anonymous
I’m new to postgresql, and locally, I use pgadmin3. On the remote server, however, I have no such luxury.
I’ve already created the backup of the database and copied it over, but, is there a way to restore a backup from the command line? I only see things related to GUI or to pg_dumps, so, if someone can tell me how to go about this, that’d be terrific!
There are two tools to look at, depending on how you created the dump file.
Your first source of reference should be the man page
pg_dump(1) as that is what creates the dump itself. It says:
Dumps can be output in script or
archive file formats. Script dumps are
plain-text files containing the SQL
commands required to reconstruct
the database to the state it was
in at the time it was saved. To
restore from such a script, feed it to
psql(1). Script files can be used
to reconstruct the database even
on other machines and other
architectures; with some modifications
even on other SQL database products.
The alternative archive file formats
must be used with pg_restore(1) to
rebuild the database. They allow
pg_restore to be selective about what
is restored, or even to reorder the
items prior to being restored. The
archive file formats are designed to
be portable across architectures.
So depends on the way it was dumped out. You can probably figure it out using the excellent
file(1) command – if it mentions ASCII text and/or SQL, it should be restored with
psql otherwise you should probably use
Restoring is pretty easy:
psql -U username -d dbname < filename.sql -- For Postgres versions 9.0 or earlier psql -U username -d dbname -1 -f filename.sql
pg_restore -U username -d dbname -1 filename.dump
Check out their respective manpages – there’s quite a few options that affect how the restore works. You may have to clean out your “live” databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.