Table of Contents
What is included in this blog post ?
This is Part 5 of the “Postgres for Oracle DBAs” series where we compare logical backup restores – Data pump restore Vs pg_dump restore(pg_restore). In Part 4 we covered backup scenarios – Please read part 4 before reading this blog . We covered 1) Default tablespaces 2) Executing sql scripts 3) Shutdown modes 4) Startup modes 5) alert log vs postgres log 6) Pg_dump Vs Datapump backups in part 1 ,2,3 and 4
NOTE – In this post we are only covering the restore part (impdp comparison ), In Part 4 we already covered expdp comparison part
Oracle Vs Postgres - Logical backup restores
In Oracle people generally use datapump (expdp,impdp) and the equivalent in Postgres for logical backups is pg_dump and pg_dumpall . In this blog post we will cover common restore scenarios – impdp vs pg_restore
Creating directory in Oracle - What is equivalent in Postgres ?
In Oracle before you can start using impdp , you need to create a directory(as shown in below diagram) . But in Postgres this is not needed and you can restore from a location (as long as postgres user has permission to that directory)
![](https://klouddb.io/wp-content/uploads/2023/03/Picture-1.jpg)
Full restore of a database - Oracle Vs PG
In Oracle you can restore a database using command like below (Provided you took a full logical backup with expdp before)
impdp full=Y DIRECTORY=data_pump_dir DUMPFILE=fullbkp.dmp LOGFILE=fullbkp_f.log
In Postgres you typically use pg_restore command to restore from logical backup . You can also use the psql method if it is in plain text mode . Please see part 4 of this series to learn more about different logical backup formats
pg_restore -d employee emp.pgdmp
In above example we gave the database as employee with -d option. emp.pgdmp is the backup file name
Below is the comparison diagram (for quick reference)
![](https://klouddb.io/wp-content/uploads/2023/03/Picture-2.jpg)
Restore a dropped table - Oracle Vs PG
In Oracle you can restore a particular table using below command
impdp DIRECTORY=data_pump_dir DUMPFILE=fullbkp.dmp TABLES=JOBS LOGFILE=tabrestor_f.log
In above example we are restoring only JOBS table
In Postgres you can restore a particular table using below command
pg_restore -d employee –table=emptest empsingle.pgdmp
In above example we are only restoring emptest table and not the entire database. Important gotcha – When you restore a table using pg_restore it does not restore its indexes
Below is the comparison diagram (for quick reference)
![](https://klouddb.io/wp-content/uploads/2023/03/Image-8.jpeg)
Restore a set of tables - Oracle Vs PG
In Oracle you can restore a set of tables using below command. In below example we are restoring two tables JOBS and EMPLOYEES
impdp DIRECTORY=data_pump_dir DUMPFILE=fullbkp.dmp TABLES=JOBS,EMPLOYEES LOGFILE=tabrestor_f.log
In Postgres you can restore a set of tables using below command . In below example we are restoring emptest and department tables from full backup
pg_restore -d postgres –table=emptest –table=department postgressingle.pgdmp
Below is the comparison diagram (for quick reference)
![](https://klouddb.io/wp-content/uploads/2023/03/Picture-4.jpg)
Restore data only or schema only
In below example we are restoring JOBS table with only data. We are using the flag CONTENT=DATA_ONLY here
impdp hr CONTENT=DATA_ONLY DIRECTORY=data_pump_dir DUMPFILE=fullbkp.dmp TABLES=JOBS NOLOGFILE=YES
In Postgres you can achieve the same with below command
pg_restore -d employee –table=emptest –data-only empsingle.pgdmp
Note – For only schema (inverse of data only ) you would use –schema-only option. In Oracle the equivalent is CONTENT=metadata_only
Below is the comparison diagram (for quick reference)
![](https://klouddb.io/wp-content/uploads/2023/03/Picture-5.jpg)
Restore by replacing existing data
In Oracle you can use the “ table_exists_action=replace “ to replace existing data
impdp directory=data_pump_dir dumpfile=fullbkp.dmp table_exists_action=replace TABLES=JOBS NOLOGFILE=YES
In Postgres If you use the –clean option of pg_restore to replace existing data
pg_restore -d postgres –table=emptest –clean postgressingle.pgdmp
Below is the comparison diagram (for quick reference)
![](https://klouddb.io/wp-content/uploads/2023/03/Picture-6.jpg)
REMAPPING in Oracle - What is equivalent in Postgres ?
In Oracle you can use remap_schema in impdp to restore into a different schema . Exact equivalent is not available in PG but you can use some workarounds
Parallel restore - Oracle Vs PG
In Oracle you can take backup with parallelism using parallel option , you can use parallel=4 (4 is just an example here) and tablebkp1_%U.dmp in the dumpfile . Restore parallelism can be achieved with a command like below
impdp schemas=HR directory=TEST_DIR parallel=4 dumpfile=tablebkp1_%U.dmp logfile=impdpparatest.log
In Postgres you can perform parallel restore using a command like below
pg_restore -U <username> -d postgres -Fd -j <NUM> -C <dump directory>
-j is similar to parallel option in Oracle
-Fd is directory format (Please check part 4 to know more about different backup formats in pg_dump)
Below is the comparison diagram (for quick reference)
![](https://klouddb.io/wp-content/uploads/2023/03/Picture-7.jpg)
Checking the progress of restore - Oracle Vs PG
In Oracle you can attach to a datapump job or use the monitoring view dba_datapump_jobs and in Postgres you do not have an equivalent feature . You can use verbose mode to get info but it is nowhere close to the job attachment and status checking feature in Oracle
![](https://klouddb.io/wp-content/uploads/2023/03/Picture-8.jpg)
Plain text format restore - You dont need pg_restore
In Pg_dump you can take plain text backup . When you restore from a plain text backup, you need to use psql (something like psql < emp_backup.sql) . Pg_restore is only applicable for custom , tar and directory formats
Logical restore - Oracle Vs PG Comparison
- You can use pg_restore for logical dump restores . For plain text backups you will use the psql method as discussed in previous section
- In Pg_restore -j option can be used for parallelism(similar to impdp -parallel option)
- One important gotcha with pg_restore table restore is that it wont restore indices by default
- You dont need to create a directory before using pg_dump and pg_restore. In Oracle you need to create a directory before using expdp/impdp
- If you use the –clean option of pg_restore, you can replace existing data
Conclusion
In part 5 of the series “Postgres for Oracle DBAs” we compared logical backup restores – impdp vs pg_restore
Also check our performance articles –Pg_fincore and pg_buffercache to troubleshoot performance issues , Pgbouncer multiple instances(How we increased our TPS using multiple instances) , Postgres and temporary files , Pgpool and performance tuning, How we improved Lambda performance by 130x etc..
Want to learn Postgres ? Register for webinar (link on homepage)
Looking for Postgres support ? Please reach us at support@klouddb.io