How To: Working With Database Dumps

Version 5

     

    Summary

     

    This document is designed to be a simple how-to for hosted customers who wish to obtain and query a dump of their databases directly.  It will guide you through the full process of requesting, downloading and then restoring your Postgres database (DB) using either the PG Admin Graphical User Interface (GUI) or via the Command Line Interface (CLI).  There is also a section explaining the general method for querying the restored database using Structured Query Language (SQL) using both the GUI and CLI.  This guide assumes that you have already downloaded and installed PostgreSQL: The world's most advanced open source database on your local machine with the default settings.  The information in this document is provided to help empower customers to work with their database dumps, however Jive Software does not support issues installing or using PostgreSQL.

     

     

    Versions

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

     

    Details

     

    Obtaining a Database Dump

    Any Hosted Jive Custom or JiveX Custom customer may request a one-time dump of their Application, EAE or Analytics (if applicable) databases from any Production instance (UAT and Cloud are not supported) from our hosting team.  Customers can also request regular database dumps for their Production instances on a weekly or daily basis.  The dumps will then be made available for download via the Jive Cloud Admin (JCA) tool.

     

    Gaining Access to JCA

    In order to access any DB dumps that will be made available to you, you'll need to have access to the Jive Cloud Admin tool, located at https://cloud.jivesoftware.com/admin.  To request access, you'll need to file a new support case with the title "Please Grant JCA Access" and a list of the URLs of each site you need JCA access to, as JCA access is granted on a per user, per site basis.

     

    Requesting Database Dumps

    Once you have JCA access, and you've verified that there aren't any dumps available via the "Downloads" tab (see below), you can request either a one-time dump or regular dumps (production only).  To request a download, you'll need to file a new support case, with a title similar to "Please provide one-time DB dump" or "Please enable regular DB dumps", including the URL(s) for the site(s) you wish to obtain dumps from, and the list of which databases you would like dumped (Application, Analytics, or EAE).

     

    Downloading the Database Dump

    To download the dump, you'll want to follow the steps listed in the Working with Databases section of the Jive Cloud Admin documentation.

     

    Restoring a Database Download

    Now that you have your DB download, you'll need to "restore" it in order to use it and access any information contained within.  You can use either of the following methods interchangeably, i.e. just because you use PG Admin to restore a database, doesn't mean you couldn't later use the CLI to do so.

     

    Via PG Admin

    1. Open PG Admin.  You'll notice that your local server is listed, but has a red X --> over it.  This is normal, and simply indicates that the server is currently not connected:
      OpenPGAdmin.png
    2. Double click on the server to connect to it.  You'll be prompted for a password.  This is the password you created when you initially installed Postgres.
      Password.png
    3. Once you've connected to the server, you'll see several elements.  To do this, right click on "Databases" and choose "New Database":
      NewDatabase.png
    4. In the pop-up dialog, enter the name of the new database in the name field, then click "Ok".  You can name it whatever you'd like.  The other fields are not required for a basic install, and are outside the purview of this guide.
      testdb.png

      Once you've created the database, the Databases list will open, and you'll see all of the databases currently available on this server, including the one you just created.  If you are running a fresh install you'll only see postgres (the default database) and the database you've just created. The databases will also be designated with an indicating that they're not currently connected.  This is also normal, and you'll only need to click on them once to re-connect.

      DatabasesList.png

    5. Right Click on the database you've just created, and select "Restore".  Note that if you haven't already clicked on your database, once you right click the is removed.
      RightClickRestore.png
    6. In the pop-up dialog window, leave the Format field set to "Custom or tar" (the dumps provided to you are in "custom" format) and enter the path to the dump file you downloaded in the "filename" field, or use the ellipsis button (...) to browse to the file using the Finder (OSX) / Windows Explorer (Windows), then click "Restore".
      FileName.png
      Note: If browsing to the file using the ellipsis (...) button, you may need to choose "All files" under "Enable" to be able to select your .dmp file.
      AllFiles.png
    7. Before you start the Restore, switch over to the "Restore Options #1" tab and check the "Owner" and "Privilege" boxes under "Don't save". These typically produce a conflict when performing the restore and is not needed for running queries.
      Screen Shot 2015-07-22 at 9.57.31 PM.png
    8. After Clicking "Restore" you'll see the "Messages" window, which will list the command and steps taken by the restore process.
      RestoreProcess.png

      Once you see "Process returned exit code 0." the restore has completed and you can click "Done".

      ExitCode.png
    9. Your database is now successfully restored and ready to use!

     

    Via CLI

    1. Open a new terminal/cmd window and use the createdb command to create a new database.

      nicholas.lawrence$ createdb -h localhost -p 5432 -U postgres testdb

            
      • createdb is the name of the command we're executing
      • -h option specifies the host, which should be localhost for default installs
      • -p option specifies the port to connect through, which should be 5432 for default installs
      • -U option specifies the username you're connecting as, which should be postgres
      • testdb is the name I've chose for the new database.  You should replace this with the name you wish to use.
    2. You'll 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:

      nicholas.lawrence$ createdb -h localhost -p 5432 -U postgres testdb

      Password:

      nicholas.lawrence$

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

      nicholas.lawrence$ pg_restore -v -h localhost -p 5432 -U postgres -d testdb ~/Desktop/Database\ Downloads/dumptest.dmp

            
      • pg_restore is the name of the command we're executing
      • -v option indicates "verbose mode".  This means the program will print information about what it's doing as it's running.  This is optional, but can be helpful if you want to troubleshoot issues.
      • -h option is the host, as in createdb
      • -p option is the port, as in createdb
      • -U option is the user, as in createdb
      • -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.  Because I'm running these commands in my home directory, I'm including the full path to the file.
    4. Again, you'll be prompted for the password of the postgres user.  Once you enter it, the restore will execute.  If you didn't include the verbose option, when the command finishes you'll get a new terminal line with no output, such as below:

      nicholas.lawrence$ pg_restore -h localhost -p 5432 -U postgres -d testdb ~/Desktop/Database\ Downloads/dumptest.dmp

      Password:

      nicholas.lawrence$

            
      If you did include the verbose option, then you'll see extra messaging preceded by "pg_restore:" while the command is running.  You'll know that it's done, when a new terminal line is presented.

    5. Your database is now restored and ready to query!

     

    Querying the Database

     

    This section is intended to show you how to execute SQL statements against your newly restored database, and not intended to be a guide on how to construct your own SQL statements.  There is a good introduction to SQL in the PostgreSQL: Documentation: 9.2: The SQL Language, as well as numerous books, courses and tutorials available from other sources, both free and available for purchase.  Please note that writing, executing, and debugging custom SQL statements is not supported by Jive Software.

     

    Via PG Admin

     

    1. Left Click on your database name and then click the image of the magnifying glass over the yellow silo with "SQL" in the center.
      DBQuery.png

      Alternatively, you could select "Tools" > Query Tool from the menu bar or use the hotkey Cmd-E (Ctrl-E on Windows?). 

      ToolsQuery.png
      This will load the query tool and connect it to your database.
      QueryTool.png
      If the database listed is incorrect, or you need to connect the query tool to a new database, then click the arrow next to the database name and select "<new connection>".
      New Connection.png
      Then select the database name from the "database" drop down list and hit "Ok".  For basic installs, there should be no need to change the server or username.
      DBList.png
    2. Once you're connected to the proper database, you can enter your queries directly into the main pane of the SQL editor.  In order to execute the query, you'll need to hit the green arrow (execute query button), as shown below. The results will be displayed in tabular format in the lower half of the screen under the "Data Output" tab.
      ExecuteQuery.png

     

    Via CLI

    1. Use the psql command to connect to your database.

      nicholas.lawrence$ psql -h localhost -p 5432 -U postgres testdb

        
      • psql is the name of the command we're running
      • -h option is the name of the host, which should be localhost for default installs
      • -p option is the name of the port, which should be localhost for default installs
      • -U option is the name of the user to connect as, which should be postgres
      • testdb is the name of the database to connect to.  Replace this with the name of the database you restored your dump to.
    2. You can now execute SQL statements directly in the command line!

     

    Related Information

     

    Important Notes

     

    • This guide assumes a default installation of postgres to localhost on port 5432.  If you installed to a different host location or are using a different port, you'll need to make the appropriate substitutions.
    • This guide explicitly specifies connection information (hostname, port) to avoid any potential confusion or errors resulting in ambiguous environment settings.  You may not need to explicitly define this information based on your configuration.
    • This guide recommends creating a new, empty database to restore to as the restore process attempts to recreate each of the tables, and will encounter an error if those tables already exist.  You could, if you chose, replace an existing database by using the pg_restore command and using the -c option to perform a "clean" restore, which will drop any existing tables before recreating them.
    • As with any program, there is certainly more than one way to restore or query your database.  This guide is not intended to be all-inclusive, but rather shows the the quickest, straight forward method to get users with little to no knowledge of postgres (or any DBMS for that matter), up and running
    • This guide and the contents herein are not supported. If you have any issues restoring your database or running postgres locally, you'll need to refer to the PostgreSQL: Documentation for your version of postgres.  You may also find assistance in the Jive Developers space on the community.  Any support cases you file related to local installations of postgres (excepting those requesting DB dumps or JCA access) will be closed as unsupported.

    jive-ask-a-question.png?a=145945776139