CODEX

PostgreSQL and Their Dumps in Windows 10

Scott Fiene
Feb 28 · 5 min read
Peaceful

About a week ago I decided I wanted do more with SQL than what I was using at work. I came across this a book from Packt Publishing called The Applied SQL Data Analytics Workshop that I decided to purchase. It seems to have everything that I was wanting in a tutorial.

At work we use Microsoft SQL Server and SQL Server Management Studio, which there is nothing wrong with in my honest opinion. However, this book utilizes PostgreSQL and pgAdmin4 so there is a little bit of a learning curve right in the beginning.

Challenge accepted!

Turns out that PostgreSQL is not that different from what I am used to. As I get further into this book though it became clear that I needed to create the database that the book uses to teach the course. Come to find out, I needed to restore a database from a dump file. Huh?!

What is a Dump Files

I have never actually looked up what the definition of a dump file is. I have just always assumed that it is a file that holds a bunch of information as text. Well today is the day that I looked up what a dump file is. Turns out I was not that far off.

They are called dump files because you a program is essentially taking it raw data and dumping it into a file with next to no formatting. There are other forms of dump files, but I will be dealing with a text dump file

Creating a Dump File in PostgreSQL in 2 Steps

To create a this dump file, I assume that you have a PostgreSQL database already handy. I will be using the one I had to create for this tutorial. It is called sqlda2 has the following tables. You can think of this database as an vehicle dealership that only sells electric scooters and electric cars. Now is a good time to mention that this is all made up data. These tables and all of the data contained within will be dumped into a text file.

Database Table Tree

Now on to creating a dump file. Here is how:

Step 1:

Open command prompt and navigate to the version of PostgreSQL you are using’s bin folder. I am using version 13 so this is what mine looks like:

C:\Users\username>cd C:\Program Files\PostgreSQL\13\bin
C:\Program Files\PostgreSQL\13\bin>

Step 2:

Run the following command replacing USERNAME with the your username, the DBNAME with the database you want to dump, and the BACKUP_FILE_NAME_AND_PATH with the place and name you want it to be.

pg_dump.exe -U USERNAME -d DBNAME -f BACKUP_FILE_NAME_AND_PATH

C:\Program Files\PostgreSQL\13\bin>pg_dump.exe -U postgres -d sqlda2 -f c:\users\username\desktop\test.dump
Password:

Once you press enter you will be prompted for your password. Input password and press enter. Congratulations you just created a dump file and backed up your database!

Restoring a PostgreSQL Database from Dump File in 4 Steps

So something has happened to your database and you need to restore it. It is pretty miraculous that you created a backup dump file of it not too long ago. You follow these 3 steps and you will be back up and running in no time.

Step 1:

Open pgAdmin4, click the servers dropdown, then click the PostgreSQL <version #> dropdown, and the right click databases. Hover over create and then click on database.

Database Creation in PostgreSQL

Step 2:

Give the database a name and make sure the owner is correct and click save.

Name the Database

Step 3:

Open command prompt and navigate to the version of PostgreSQL you are using’s bin folder. I am using version 13 so this is what mine looks like.

C:\Users\username>cd C:\Program Files\PostgreSQL\13\bin 
C:\Program Files\PostgreSQL\13\bin>

Step 4:

Run the following command replacing USERNAME with the your username, the DBNAME with the database you want to restore, and the BACKUP_FILE_NAME_AND_PATH with the place and name it is stored.

psql.exe -U USERNAME -d DBNAME -f BACKUP_FILE_NAME_AND_PATH

C:\Program Files\PostgreSQL\13\bin>psql.exe -U postgres -d test -f c:\users\username\desktop\test.dump
Password:

Once you press enter you will be prompted for your password. Input password and press enter. You will get something similar to this as it is being ran.

Verify Restoration of Database

Now that the restore has been run, you should be able to open pgAdmin 4 and find your restored database. I am able to verify that the restore was successful.

You can run some quick SQL code to see that it was indeed successful.

select *
from customers
where title is not null
and phone is not null
order by last_name
limit 10;
select c.customer_id
, c.first_name
, c.last_name
, p.product_id
, p.model
, p.year
, p.product_type
, s.sales_amount
, s.sales_transaction_date
from sales s
left outer join customers c on s.customer_id = c.customer_id
left outer join products p on s.product_id = p.product_id
order by s.sales_transaction_date desc;
select c.customer_id
, c.first_name
, c.last_name
, p.product_id
, p.model
, p.year
, p.product_type
, s.sales_amount
, s.sales_transaction_date
from sales s
left outer join customers c on s.customer_id = c.customer_id
left outer join products p on s.product_id = p.product_id
order by s.sales_transaction_date desc

Thank you for reading and I hope you enjoyed!

CodeX

Everything connected with Tech & Code

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store