Restoring a Database Download via CLI

Overview

Once you have obtained your DB download, you will need to restore it to use it and access any information contained within. You can use either of the following methods interchangeably:

1. Via pgAdmin (Postgres)
2. Via CLI (Command Line Interface)

This article describes the step-by-step process to restore a database download via the CLI.

Environment

• This article covers all versions of Jive Custom.
• The commands in this article were written using PostgreSQL version 9.2.6
• This guide was written for the Mac OSX release of Postgres/pgAdmin; however, the steps should translate to Windows or Linux.

Process

1. Open a new terminal (on Mac) /command prompt (on Windows) and use the createdb command to create a new database:
   
   

    test.user$ createdb -h localhost -p 1111 -U postgres testdb

   • createdb is the name of the command we are executing.
   • the -h option specifies the host, which should be localhost for default installs.
   • the -p option specifies the port to connect through, which should be 1111 for default installs.
   • the -U option specifies the username you are connecting as, which should be Postgres.
   • testdb is the name chosen for the new database. You should replace this with the name you wish to use.
2. You will be prompted for a password, which is the password of the Postgres user (not your OS user).
   
   
   • This should be the password you created when installing Postgres.
   • Once you enter this password, the database should be created successfully and you should get a new terminal line with no output, so your terminal window should look like this:
     
     

     test.user$ createdb -h localhost -p 1111 -U postgres testdb
     Password:
     test.user$

           
3. Use the pg_restore command to restore the dump file to the newly created database:
   
   

    test.user$ pg_restore -v -h localhost -p 1111 -U postgres -d testdb ~/Desktop/Database\
    Downloads/dumptest.dmp

   • pg_restore is the name of the command we are executing.
   • the -v option indicates verbose mode. This means the program will print information about what it is doing as it is running.  This is optional but can be helpful if you want to troubleshoot issues.
   • the -h option is the host, as in createdb
   • the -p option is the port, as in createdb
   • the -U option is the user, as in createdb
   • the -d option is the database to restore to, which should be the name of the database you created in Step 1 with the createdb command.
   • ~/Desktop/Database\ Downloads/dumptest.dmp is the path to the dump file. Since these commands were executed in the home directory, the full path to the file is included.
4. Again, you will be prompted for the password of the Postgres user:
   
   
   • Once you enter it, the restore will execute.
   • If you did not include the verbose option, when the command finishes, you will get a new terminal line with no output, such as below:
     
     

     test.user$ pg_restore -h localhost -p 1111 -U postgres -d testdb ~/Desktop/Database\ Downloads/dumptest.dmp
     Password:
     test.user$

5. You can now proceed to query your DB via CLI.

Confirmation

• If you did include the verbose option, then you will see extra messaging preceded by pg_restore: while the command is running. A new terminal line is presented once this is done.
• Your database is now restored and ready to query!

Related Articles

• Restoring a Database Download via pgAdmin
• Querying the Database via pgAdmin