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)
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00002a.jpg)
Full backup of a database - Oracle Vs PG
In Oracle you can take a full database backup using a command like below
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_100003.jpg)
In Postgres you can take a full database backup using a command like below
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00003b.jpg)
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)
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00004a.jpg)
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
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00005a.jpg)
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
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00006a.jpg)
In Postgres you can take single table backup using a command like below
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00006b.jpg)
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)
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00007a.jpg)
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
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00008a.jpg)
In Postgres you can use a command like below to backup a particular schema
![](https://klouddb.io/wp-content/uploads/2023/02/oracledatapumpvspgdump_blog_final_00008b.jpg)
You can use -n option
pg_dump -U postgres employee -n <<schemaname>> -f empsingletableexclude.sql
Below is the comparison diagram (for quick reference)
![](https://klouddb.io/wp-content/uploads/2023/02/Picture-l1m.jpg)
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
![](https://klouddb.io/wp-content/uploads/2023/02/Picture-n1.jpg)
In pg_dump you can specify parallelism using -j option . NOTE – This option is only used with directory option (-Fd )
![](https://klouddb.io/wp-content/uploads/2023/02/Pictureo-1.jpg)
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)
![](https://klouddb.io/wp-content/uploads/2023/02/Picture-p1.jpg)
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
![](https://klouddb.io/wp-content/uploads/2023/02/Picture-q1.jpg)
In Postgres you can achieve the same using a command like below
![](https://klouddb.io/wp-content/uploads/2023/02/Picture-r1.jpg)
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)
![](https://klouddb.io/wp-content/uploads/2023/02/Pictures-1.jpg)
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
![](https://klouddb.io/wp-content/uploads/2023/02/Picturet-1.jpg)
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