Postgres for Oracle DBAs – impdp Vs Pg_restore

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)

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)

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)

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)

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)

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)

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)

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

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

Share this article: