Postgres for Oracle DBAs – Datapump Vs Pg_dump

Table of Contents

What is included in this blog post ?

This is Part 4 of the “Postgres for Oracle DBAs” series where we compare logical backups – Data pump Vs pg_dump . We covered 1) Default tablespaces 2) Executing sql scripts 3) Shutdown modes 4) Startup modes 5) alert log vs postgres log in part 1 ,2 and 3

Oracle Vs Postgres - Logical backups

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 backup scenarios

Creating directory in Oracle - What is equivalent in Postgres ?

In Oracle before you can start using expdp , you need to create a directory(as shown in below diagram) . But in Postgres this is not needed and you can dump to any location (as long as postgres user has permission to that directory)

Full backup of a database - Oracle Vs PG

In Oracle you can take a full database backup using a command like below

In Postgres you can take a full database backup using a command like below

pg_dump -d employee -U postgres -p 5432 -f emp_backup.sql

-d option is used to give the dbname , in this example dbname is employee
-U is the username
-p is the port (default is 5432)
-f is the output file for backup

Below is the comparison diagram (for quick reference)

Pg_dump backup formats

There are different backup formats for pg_dump . By default it dumps in plain text but you can specify custom format according to your needs

Backing up a list of tables - Oracle Vs PG

In Oracle you can take a table backup using a command like below . In below example we are taking a backup of EMPLOYEES , JOBS tables

In Postgres you can take single table backup using a command like below

If you need to take backup for multiple tables you can use a command like below

pg_dump -U postgres -t emptest -t dept -d employee -f empsingletabletest.sql

In above example we are taking backup of dept and emptest tables in employee database . You can also use wildcards to specify a pattern to backup

Below is the comparison diagram (for quick reference)

Backing up a Schema - Oracle Vs PG

In Oracle you can take a schema backup using a command like below . In below example we are backing up HR schema

In Postgres you can use a command like below to backup a particular schema

You can use -n option
pg_dump -U postgres employee -n <<schemaname>> -f empsingletableexclude.sql

Below is the comparison diagram (for quick reference)

Backup parallelism - Oracle Vs PG

In Oracle you can take backup with parallelism using parallel option . In below example you can see that we are using parallel=4 and tablebkp1_%U.dmp in the dumpfile

In pg_dump you can specify parallelism using -j option . NOTE – This option is only used with directory option (-Fd )

pg_dump -j 8 -Fd -f /tmp/bkpemp -d employee

-j option is equivalent to parallel in Oracle

-d database name here is employee

-Fd takes backup in directory format

Here is the comparison diagram (for quick reference)

DDL only backup (METADATA) - Oracle Vs PG

In Oracle you can take DDL only backup using metadata_only option . In below example we gave CONTENT=metadata_only option to take backup without data

In Postgres you can achieve the same using a command like below

You can use –schema-only option
pg_dump -U postgres -t emptest employee –schema-only -f empsingletabletest.sql


Here is the comparison diagram (for quick reference)

Checking the progress of logical backup - Oracle Vs PG

In Oracle you can attach to the running datapump job and check status of the job , stop job etc.. You can also check the status using dba_datapump_jobs and in Postgres you do not have an equivalent feature

Oracle gives ton of options to check and manage an existing job

Logical backup - Oracle Vs PG Comparison

  • Postgres pg_dump has multiple options like -Ft,-Fd,-Fc ,-Fp (Plain , directory , compression , plain text)
  • Oracle has metadata_only option to get DDL only backup and in Postgres the equivalent is – schema-only
  • In Oracle you need to create a directory before using expdp and that step is not needed in Postgres
  • In Postgres you can specify parallelism using -j option and the equivalent in Oracle is –parallel
  • You can attach to a running datapump job in Oracle and manage the job but in Postgres that is not possible

Conclusion

In part 4 of the series “Postgres for Oracle DBAs” we compared logical backups – expdp vs pg_dump

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 Post :