How to Restore PostgreSQL Database from sql file via command line

By Posted on

This article as specified in the title will display on how to restore PostgreSQL Database using a file which has the extension of sql for an example ‘mydumpfile.sql’. Using a command which is executed in the command line, the database which has already exist and it has been created in the previous step will be then restored using the data located in the sql file mentioned before.

Before going further, it is informed that the PostgreSQL Database Server used in this article for restoring PostgreSQL Database is the 10.5 version as shown below :

[pgsql@hostname ~]$ psql --version
psql (PostgreSQL) 10.5
[pgsql@hostname ~]$

There are a few steps need to be taken in order to restore the PostgreSQL Database from a file with the extension of ‘sql’ using an appropriate command executed in the command line. Those steps are :

  1. Login to the PostgreSQL Database Command Console by executing the command as shown below :
root@hostname:~# psql -Uusername databasename
psql (9.6.9, server 10.5)
WARNING: psql major version 9.6, server major version 10.
Some psql features might not work.
Type "help" for help.
databasename=#

2. Create the database which is going to be used as the database where the content of the dump file or the backup file will be stored. Use the simple command for creating database as shown as follows : 

postgres=# create database mydb;
CREATE DATABASE
postgres=#

3. After creating the database needed, just execute the command for restoring the database from the sql file as shown below : 

[pgsql@hostname ~]$ psql -d mydb -f /home/pgsql/mydumpfile.sql 
SET
SET
SET
SET
SET
 set_config 
------------
(1 row)
SET
SET
SET
CREATE EXTENSION
COMMENT
...
SET
SET
CREATE TABLE
CREATE TABLE
...
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE VIEW
CREATE VIEW
CREATE VIEW
CREATE VIEW
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 3
COPY 49
...
COPY 162275
COPY 393595
...
COPY 56
COPY 661
 setval 
--------
      2
(1 row)
 setval 
--------
      1
(1 row)
 setval 
--------
    667
(1 row)
...
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
[pgsql@hostname ~]$
  1. After successfully restoring the database with the above command executed in the command line, check the content of the database by executing the following command after logging in and connecting in to the PostgreSQL Database :
root@hostname:~# psql -U pgsql mydb
psql (9.6.9, server 10.5)
WARNING: psql major version 9.6, server major version 10.
         Some psql features might not work.
Type "help" for help.
mydb=# \d+
                                                List of relations
 Schema |                           Name                            |   Type   | Owner |    Size    | Description 
--------+-----------------------------------------------------------+----------+-------+------------+-------------
 ... 
 public | users                                                     | table    | pgsql | 16 kB      | 
 public | users_user_id_seq                                         | sequence | pgsql | 8192 bytes | 
(34 rows)
mydb=#

As it can be shown above, the content of the table can be listed by typing the command ‘\d+’ and it displays several tables on it. So, the restore process of the database from a sql file extension using the associated command has already been accomplished.

Leave a Reply