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

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)

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)

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)

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)

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)

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)

• 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

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