PostgreSQL - Load Sample Database

In this tutorial, we will learn how can we load a sample database in PostgreSQL. There are two methods you can import a sample database, either using a command line client or GUI software. So, let’s get started!

Prerequisites

After installation of any DBMS on the local system, it is very important to have a sample database so that you can try things out on the already available data instead of creating everything.

A sample database already consists of a number of tables having lots of data with relationships between them. Therefore, it will be very easy to perform complex operations such as joins, inner queries, and similar stuff.

To perform the loading sample database in your PostgreSQL system, first, make sure you have set the password for your database user and enabled the md5 authentication method. If you don’t know how to enable the md5 login method, you can refer to this tutorial(link to fatal:peer authentication failed tutorial).

Second, to load the sample database using the GUI, we recommend you have a pgAdmin as an administration tool. It is the most popular and highly suggested GUI tool for PostgreSQL. However, you are free to use anyone. Follow our tutorial on how to install pgAdmin.

This tutorial is performed on Ubuntu. However, almost all steps are very similar for other Operating systems such as UNIX-like or Windows.

Now let’s get started with the procedure.

Download the Sample Database

The first thing you will need is the database file. Below you can find two database files. The first is the pagila.tar file which contains .dat files. It is a sample database based on MySQL’s sakila database. Second is the plain world.sql file which contains countries and cities information. However, it is compressed in zip format. Therefore, you need to unzip and extract it first.

Note that, when database backup is taken in the plain SQL or text format, we cannot import it back using the pg_restore program or a GUI tool. It must be imported using the psql because plain SQL or text files are designed to feed psql.

Load Sample Database Using pgAdmin

We can import a database file using pgAdmin only when the file is in the correct archive format. Plain SQL files can not be imported using pgAdmin.

Now follow the steps given below to import the sample database using the pgAdmin tool.

Open the pgAdmin tool and connect to the server.
Now, click on your server name and then you will see the Databases option, click on it.
You will see the available databases list under this option.
Now, right-click on the Databases option and click create > database as shown in the image below.

Create Database Using Gui — Create a Database Using Gui

Now enter the database name, ‘pagila’ in our case. And click save. This will create an empty database. Now we will need to import the data.
You can see the pagila database is created under the Databases option. Right-click on the pagila > restore as shown in the image below.

Now enter the details of the database that you want to restore such as file type, file path, and the database user/role as shown below. Click Restore.

It will take a few seconds to load all the data. On the bottom right side, you can see the process status. Notifications will appear for the process start and for the process completion.
Now you can see the tables of the pagila database under the schemas > tables option.
That’s it. We have successfully imported the sample database into our PostgreSQL.

Load Sample Database using CLI

For beginners, loading/importing the database using psql CLI may seem difficult. However, it’s not. We will simplify the process as easier as possible.

Using the CLI, we can import any type of file, let it be a compressed file (tar, zip) or a plain SQL/text file. To import the plain text format files, we need to use the psql utility. Whereas, to import the other format files such as tar, we need to use the pg_restore utility which comes with the PostgreSQL setup by default.

Here we will load the database using both methods. First, we will import the pagila.tar using the pg_restore program and then we will import the world.sql file using the psql program.

Load database using pg_restore

Follow the steps given below to import the pagila.tar file using the pg_restore program.

First, log in to the PostgreSQL client.
Now create a database named ‘pagila’ using the command “CREATE DATABASE pagila;” and exit from the client using \q command.

To import the pagila.tar file, use the following syntax.

pg_restore -u <database_user> -d <database_name> <archive_filepath>Code language: HTML, XML (xml)

In our case, the command will be –

pg_restore -U postgres -d pagila /home/tej/Desktop/pagila.tar

After entering the command, you will be asked to enter the database user password.
Now it will take a few seconds to load the data into your database.

Now to check if the database is loaded successfully, login to the PostgreSQL client. Now enter the command “\c pagila” to connect to the pagila database and enter the command \dt to display all the tables.

Our database is imported successfully.

Note that, if you try to import the SQL file using the pg_restore program, you will see an error that the file is not in valid archive format. So, you should use the psql program to import the plain-text dump files.

Let’s now see how can we import the plain SQL dump file using the psql program now.

Load database using psql

Follow the steps given below to import the world.sql file using the psql program. But first, do not forget to unzip the given file above after downloading it.

First, log in to the PostgreSQL client and create a database “world” using the command “CREATE DATABASE pagila;” and exit from the client using \q command.

To import the SQL file, the following syntax is used –

psql -h hostname -U username -d databasename -f /path/to/filename

In our case, the command will be –

psql -h localhost -U postgres -d world -f /home/tej/Desktop/world.sql

Enter the password of your user and wait for a few seconds.

You can see the operations performed while loading the database.
Now to cross-check if the data is imported successfully, perform the same steps given above for the previous method.

As you can see above, all the tables are imported. This means the database is imported successfully.

Conclusion

In this tutorial, we have seen the procedure to import a sample database with two different methods; using CLI and using GUI. We also saw two different cases while importing the database files of different formats. I hope you will not face any problems while installing any type of backup file now.

Reference

Here is the StackOverflow thread on importing SQL dump file which contains multiple solutions. You can check them out. serverfault by StackExchange thread on pg_restore errors.