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 :
In the Start/taskbar search field, type pgadmin and then press Enter key to open PostgreSQL Admin Panel.
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
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
.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.
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.
Click on backup.
Check your backup file is created or not.
For Importing PostgreSQL file
Suppose I want to import abc.sql file located in my J: drive. So follows these below step in order.
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
1.3) Give the name of the database as of your SQL file. My case: abc
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.
3) Then go inside the bin folder
4)Then type cmd in the address bar and press enter (Notice: that you must be inside bin folder)
5)Now, you must be inside the bin folder in the command line. Double-check it. Then type this below command
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
6)Then press enter and it will ask you to enter your pgadmin password. Just enter the password and press enter.
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