DSC_0322 Zach Doty Cover Photo for SQL Database Creation, Restoration, Deletion

Creating, Restoring and Deleting SQL Databases with pgAdmin 4

Howdy! Let’s continue the journey into learning Structured Query Language (or SQL). Previously, I shared my notes on the absolute beginning point for starting SQL, and also getting started with PostgreSQl and pgAdmin.
Now, we venture into some basics of database management via SQL:
• Creating a database with pgAdmin
• Restoring a database with SQL commands
• Deleting a database

Creating a new SQL Database in pgAdmin via Graphical User Interface

So, let’s retrace our steps on creating a new database. After you’ve successfully started pgAdmin and accessed your databases, right click “Databases” and go to “Create” > “Database”. Then choose your desired name and save.

2017-01-16-001-pgAdmin4-PostgreSQL-Create-Additional-Database

Give it a second, and your brand spankin’ new database should be created! (Alternate method, you could select the “postgres” database in the file tree and select the option to create a new, arbitrary SQL query. In the Query window, type:

CREATE DATABASE dvdtwo;

2017-01-16-002-pgAdmin4-PostgreSQL-Create-Additional-Database-Query-Tool

If you run and refresh you should see the output of the code’s successful run, with no results, and a new database in the file tree. Joy!

2017-01-16-003-pgAdmin4-PostgreSQL-Create-Additional-Database-Query-Tool-Success

 

Deleting (Dropping) Databases in pgAdmin

Let’s build some character and delete the database we just created! Fortunately, deleting (also known as dropping a table is super simple in pgAdmin / PostgreSQL. Below, in the interface, we right click the name of the newly created database and click “Delete/Drop”, and click Okay. Obviously, be sure you don’t do this accidentally.

2017-01-16-004-pgAdmin4-PostgreSQL-Delete-Drop-Database-User-Interface

 

Gracefully Restoring a Database

In the second SQL article, we previously restored a database. We’ll revisit that process in the pgAdmin interface again here. Practice, practice, practice! Let’s restore the dvdtwo database created earlier in this article for good practice. Below, we’ll right click the “dvdtwo” tree header and select the “Restore” option.

2017-01-16-005-pgAdmin4-PostgreSQL-Restoring-Database-2-User-Interface

In the resulting dialogue box, I attempted to use the interface’s file browser. However, I was met with some bugs/error messages. Thus, I copied the file path from Windows Explorer, pasted it into the file path name field and began the restore job. It took a few moments to run, then I refreshed the database and saw tables populated under dvdtwo > Schemas > public > tables. Hooray!

Wrap-Up

Thanks for tuning in! Next article, I’ll share my learnings for restoring a database, but with only the table schema. So to be more specific, I’ll restore the framing and architecture of the sample database, but without the data. This seems a bit like copying and duplicating sheets in Excel, but with blanks for data, so you can populate at your discretion. See you soon.

 

PS- If you’re just arriving at this article, here’s my running list of articles that detail how to learn SQL.

1 reply

Trackbacks & Pingbacks

  1. […] Welcome back to our shared journey of learning SQL. Last time, we learned about creating, deleting and completely restoring SQL databases in pgAdmin. Today, we’ll learn about how to restore a database, but only its table […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply