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