Importing and Exporting PostgreSQL file


👤 Diwas Poudel    🕒 08 May 2019    📁 FIX

In this article, I will describe how to import and export a Postgre SQL database file. We can import and export database file easily.

For Exporting PostgreSQL file

Keeping the backup of the database is essential and is a good data disaster recovery strategy. So, lets back up our database:

Method 1 :

Step 1:

In the Start/taskbar search field, type pgadmin and then press Enter key to open PostgreSQL Admin Panel.

Step 2:

Then in the admin panel

2.a )Expand Server tab
2.b ) Expand PostgreSQL Server tab
2.c) Right-click on the database you want to back up. My case: PathwayDb
2. d) Click on Backup

Then a backup dialogue box will open

select required postgresql database

Step 3:
On backup database windows, Select location where you want to save database file, Select the format to plain ( if you want to backup both schema and data

backup postgresql

.If you want just either schema or data then goto dump option1 and select the required type.

Select Schema Only: If you want to create a database with no data, especially when creating a fresh database.


Select Both (Schema + Data): If you want to both data and schema.


Step 4)
Goto Objects tab if you want to keep the only the required object.
Here, I don't want to backup valuetable so I unchecked it.

backup sql postgre

Step 5:
Click on backup.

postgre

Check your backup file is created or not.

Method 2:

Coming Soon

For Importing PostgreSQL file

Suppose I want to import abc.sql file located in my J: drive. So follows these below step in order.

Method 1)

1)First, create a database name in pgadmin with the same name as your SQL file.

I have SQL file: abc.sql and I will create database name as abc 

   1.1) Open pgadmin panel

   1.2) Right-click on Database then select New Database

select new database in pgadminIII posgresql   1.3)  Give the name of the database as of your SQL file. My case: abc

abc file

 

Notice: Just for convenience I have kept SQL file name and database name same.

2) Then go to your path where you have installed Postgres.

postgre sql installation location

3) Then go inside the bin folder 

bin folder of postgresql

4)Then type cmd in the address bar and press enter (Notice: that you must be inside bin folder)

inside bin folder of postgre

5)Now, you must be inside the bin folder in the command line. Double-check it. Then type this below command

General Syntax:

psql -U username -h host -f location-of-sql-file databasename-that-we-have-created-in-pgadmin

Q)How to Find Hostname and Username in pgadmin?

Click on PostgreSQL file and go to Properties tab on the right panel, there you will see username and hostname 

username and hostname in pgadmin

6)Then press enter and it will ask you to enter your pgadmin password. Just enter the password and press enter.

enter postgresql password for pgadmin panel

7)Then your database with schema and data get created in pgadmin panel. As shown below

We can clearly see that the table that was in my abc.sql is successfully imported to abc database.

In this way, we have successfully import or restoring the PostgreSQL SQL file.

Method 2 Coming Soon