DEV Community

Chuks
Chuks

Posted on

How To Restore A PostgreSQL Database Using Command Line

Introduction
PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS).

Here, in this article, you will learn how to restore a database backup file on postgresql. When working with a new database file, it is more convenient to use the command line to restore the database, especially if the file is large so as to reduce query execution time.

Let's proceed.

  1. Locating the database file
    Find the location of the ".sql" file on your computer and change the directory in command line to the folder containing the database. The folder holding my database like "france", "world" and "stote" is located in "databases" folder which is located in "mydatabases" folder which is located in "Downloads" folder. To change the directory you type "cd" followed by the file path then press Enter to switch to the new directory.
    Image description
    Image description

  2. Running the postgresql application in command line
    After switching to the directory containing the database file, the next step is to run the postgersql executable file "psql.exe" in command line so that the database will be inserted through postgresql. To do this:
    (a) Copy the file path that contains the "psql.exe" file
    (b) Paste it in the command line wrapped up in quotes, this is to let the command line know that it should be run together.

Image description
Image description

  1. Create the respective databases in PostgreSQL
    The next step is to create a database, this is the database that will house the backup file and this can be done using pgadmin.
    Right click on PostgreSQL 14, you will see "create". Click on "create" and you will see "database", then click on it to create and name your database. Then click "save". Do this to create your
    Image description respective databases
    Image description

  2. Upload the backup database file into the created database
    Lastly we want to upload the ".sql" file into the database that was created with pgadmin.
    To do this, we use the same way we wrote the command line action in step 2 only that here you have to specify the user and the database. To specify the user we write "-U username" and to specify the database, we use "-d database name".
    Here our user is "postgres" and let's say the database name is "Store" or "world" or "france", created in the previous step. Once the user and the database have been indicated, you type "<" followed by the file name for example "store.sql" or "world.sql" or "france.sql" then press Enter to upload the database. You will the be prompted to input your user password and after the password has been correctly inputted, the backup file will be uploaded into the database. So for example I will write "-U postgres" for the user and "-d World" for the database
    Image description

Top comments (0)