Hi everyone,
I am actually new to PostgreSQL so pardon me for my question if it’s so simple. Is there a way to restore postgreSQL backup using a command line? The backup of the database has already been created and scp’d it already but I can only see files which are related to guis or pg_dumps. Can someone help me how to go along?
How can I restore postgreSQL backup file?
Hello Andree,Â
There two ways to do this:
1) You can use the pgAdminIII interface. Just open the pgAdminIII interface from program files and then postgres 9.1 (depending on the version of your postgres database). Then type in your credentials. Select the database you want to use for restore. Or if you don't have a database yet you can create one. Right click on the database of your choice and then select the "Restore Option". A dialog box will open and will prompt you for the file name. Browse for the file name and then click restore. Your file should be a valid sql dump file. An exact copy of the snapshot of your backup will be created. Done!
2) You can use the shell sql(psql) or the postgresql commandline interface. Just go to program files and locate your postgres folder and then click on it. Select "Shell sql(psql)" and it will open up. It will prompt you for the server which you will have to enter the ip address if you are connecting to a remote server or just leave it like that if the server resides in your computer and hit enter. Then you wlll be prompted for a database to use and you type the database you want to use or you can leave the default postgres database and hit enter. You will then be prompted for the port, just leave the default port number and hit enter. You will then be asked for a username, type the username that you normally use to connect to your server and hit enter. Then you will be prompted for a password, enter your password and hit enter. It will then connect to the database you specified and then await your command.Â
When it opens up now type this command: "i e:mydbbackup.sql" and hit enter. What this command does it that it invokes the postgres input command, telling it to input from a directory called "e" and the file called "mydbbackup.sql", but it wlll depend with your directory and your file name. Your database will be restored.Â
How can I restore postgreSQL backup file?
Dear Jaclyn,
Resorting depends on how you have created the dump file. There are 2 types of dump file. Your main source is the man page pg_dump(1) because it creates the dump file. There are 2 types of dump file such script file format or archive file formats. So important how it has been created.
You can find it out by file (1) command – if the result exhibit ASCII text and/or SQL, it can be restored with psql otherwise you have to use pg_restore. Restoring is simple:
"psql -U <username> -d <dbname> -1 -f <filename>.sql"
and
"pg_restore -U <username> -d <dbname> -1 -f <filename>.dump"
Thank you.
