PostgreSQL - Restoring a database

Recent days I was following Brent Ozar's site on postgresql - smart postgres.

In his articles and classes he use copy of stackoverflow database (postgres version). There fore I wanted to restore it on my test postgres server.

Here is how I did it.

Download the Dump

First I downloaded the stackoverflow data dump (which was created by Brent) using links in his site. See this page for links to data dump torrent and instructions on restoring and configuring it. 

I choose small version of the data dump (which expand to 6GB, but torrent is about 1GB).

Create a Database

In this scenario, I have used DBeaver to help me with database. In DBeaver, created a new database connection.

See above screenshot for settings I have used. I have kept most settings default, but made sure to tick "Show all databases" tick box, this allows me to see all database in addition to the one you specified in the connection.

Once connection is created, select the database node and right click on it. Select "Create New Database" menu item.

Create database dialog appear and enter the name "stackoverflow" in the database name box. Keep all other settings default and press ok.

Your new database will appear under the database node:

Restore

Right click on the newly created database and select Tools > Restore

This will popup the restore dialog. In restore dialog, browse to the downloaded data dump (.sql) file and make sure to "Discard object owners" tick box.

This will make sure some errors are by passed. Due to the way dump was created there are some mis match of owners. This is explained in Brent's page, but he has advice to ignore them, by ticking above box those errors a skipped,

Then press on "Start" button.

Confirm your request:

Progress will appear on the dialog box and depending on the power of your machine it will take about 2-10 minutes to restore.

Once finished, press cancel on the dialog.

Now you will be able to see stack overflow tables on the database: