Howdy! 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 schema.

Specifically, we’ll restore the table names and preferences for types of data within those tables. However, the actual data itself won’t be ported in.

Think of it as taking your house or apartment, and recreating, but you don’t move in with the furniture. (Maybe a sibling instead? 🙂 ) According to the material I’m working through, this method of database restoration is very common and is something we should have down pat.

Database Table Schema-Only Restoration, Method 1

An easy method to start this is to right click on the Databases header near the top of the file tree, and to select “Create” > “Database”. With a fresh new database, we’ll have flexibility to do some more management on the “front” side of things. (Knowing I may be abusing terminology a bit, but makes sense to me while writing this.)

For this example, I’ve finished the new database creation by naming (“OnlySchema”) and saving a new database. Below, you can see the new database in the file tree. Also, if we click through the tree as such, “Schemas” > “public” > “Tables”, we’ll notice there are no tables! (Compare that path exploration to the dvdrental database tables, where you can find 15 tables.)

Anyway, let’s get on with restoring table schema only!

1. Right click on “OnlySchema” and select “Restore”
2. Select the “Custom or tar” option for the Format field
3. Select the file via the dialogue, or paste in your file path
   • Up to this point, you should notice we’ve taken the exact same steps for a full database restore. However, #4 is where things are slightly different. Pay attention!
4. As shown below, click “Restore Options”, and activate the radio button for “Only schema” to yes
5. Click “Restore” and refresh!

After the refresh, we should see something like below, where if we select the tables option and view “Properties”, we’ll see 15 empty named tables.

Method 2: Schema-Only Restoration onto a Database with existing tables, data

What if we’re working with a database that contains existing tables and data? And suppose we need to restore only the schema? Perhaps an error was committed in formatting / management and must be corrected.

Fortunately, this method of restoration is extremely similar to Method 1. For this scenario, there’s one added step toward the end:

1. Right click on “OnlySchema” and select “Restore”
2. Select the “Custom or tar” option for the Format field
3. Select the file via the dialogue, or paste in your file path
4. Click “Restore Options”, and activate the radio button for “Only schema” to yes
   • Here’s our jump off point, the next step is what differs from Method 1.
5. Scroll down the dialogue box slightly. You should see a field and radio button for “Clean before restore”. Activate the radio button to “Yes”
6. Click “Restore” and refresh!

After running the restore job and refreshing, we can verify the Schema Only and data clean prior to restoration occurred correctly. Below, we can click through the file tree down from “dvdrental” > “Schemas” > “public” > “Tables” and check the table properties to see that there are zero (0) rows of data.

Wrap Up

I do hope these articles are serving a useful introduction to navigating pgAdmin, and getting familar with the foundations of PostgreSQL and databases. In the forthcoming articles, we’ll start learning about basic SQL syntax. To keep track of all the shared SQL posts and learnings, visit the SQL Education page for a list of articles to date. Cheers!

Zach