Oracle expdp vs PostgreSQL pg_dump: A Practical Comparison of Logical Backups

both are methods of taking logical backups in these 2 databases.

Oracle Data Pump: expdp

1. Prepare the OS directory
bash
sudo mkdir -p /backup && sudo chown oracle:oinstall /backup
2. Connect with SQL*Plus and list containers
sql
$ sqlplus / as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
4 ORCLPDB2 READ WRITE NO
3. Create sample data in orclpdb1
sql
SQL> alter session set container = orclpdb1;
Session altered.
SQL> create user scott identified by tiger quota unlimited on users;
User created.
SQL> grant create session, create table to scott;
Grant succeeded.
SQL> create table scott.employees (id number primary key, name varchar2(50), dept varchar2(20));
Table created.
SQL> insert into scott.employees values (1,'Anna','IT');
SQL> insert into scott.employees values (2,'Erik','HR');
SQL> insert into scott.employees values (3,'Lena','Finance');
SQL> commit;
SQL> select * from scott.employees;
ID NAME DEPT
---------- -------------------------------------------------- --------------------
1 Anna IT
2 Erik HR
3 Lena Finance
4. Create the Oracle directory object (inside the PDB)
sql
SQL> create or replace directory backup_dir as '/backup';
Directory created.
SQL> grant read, write on directory backup_dir to scott;
Grant succeeded.
SQL> select directory_name, directory_path from dba_directories where directory_name='BACKUP_DIR';
DIRECTORY_NAME DIRECTORY_PATH
---------------- ---------------
BACKUP_DIR /backup
The directory object must be created inside the PDB, because Data Pump runs against a PDB and a CDB$ROOT directory is not visible there.
5. Run expdp against orclpdb1
expdp connects through the listener using the PDB service name, not / as sysdba (which lands in CDB$ROOT):
bash
expdp system@//localhost:1521/orclpdb1 \
schemas=scott \
directory=backup_dir \
dumpfile=orclpdb1_scott.dmp \
logfile=orclpdb1_scott_exp.log
For a full PDB export instead of one schema, replace schemas=scott with full=y.
Expected output (trimmed):
Export: Release 19.0.0.0.0 - Production
Connected to: Oracle Database 19c Enterprise Edition - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@//localhost:1521/orclpdb1 schemas=scott directory=backup_dir dumpfile=orclpdb1_scott.dmp logfile=orclpdb1_scott_exp.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMPLOYEES" 5.5 KB 3 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/orclpdb1_scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed

Postgres : pg_dump

postgres=# CREATE DATABASE pgdump_test;
CREATE DATABASE
postgres=# \c pgdump_test
psql (16.15 (Ubuntu 16.15-0ubuntu0.24.04.1), server 15.16 (Ubuntu 15.16-1.pgdg24.04+1))
You are now connected to database "pgdump_test" as user "postgres".
pgdump_test=# SELECT current_database();
current_database
------------------
pgdump_test
(1 row)
pgdump_test=# CREATE TABLE employees (
employee_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_name varchar(100) NOT NULL,
department varchar(50),
salary numeric(10,2),
joining_date date
);
CREATE TABLE
pgdump_test=# CREATE TABLE departments (
department_id integer PRIMARY KEY,
department_name varchar(50) NOT NULL,
location varchar(50)
);
CREATE TABLE
pgdump_test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | departments | table | postgres
public | employees | table | postgres
(2 rows)
pgdump_test=# INSERT INTO employees
(employee_name, department, salary, joining_date)
VALUES
('John Smith', 'Database', 75000, '2022-01-10'),
('Mary Jones', 'Database', 72000, '2022-03-15'),
('David Miller', 'Linux', 68000, '2021-06-20'),
('Sarah Brown', 'Cloud', 82000, '2020-11-05'),
('Robert Wilson','Cloud', 79000, '2023-02-12'),
('Lisa Taylor', 'Backup', 70000, '2022-08-17'),
('James Davis', 'Linux', 71000, '2021-12-01'),
('Emma White', 'Database', 76000, '2023-04-18'),
('Michael Clark','Backup', 69000, '2020-07-25'),
('Sophia Lewis', 'Cloud', 85000, '2024-01-08');
INSERT 0 10
pgdump_test=# INSERT INTO departments
VALUES
(10, 'Database', 'Stockholm'),
(20, 'Linux', 'London'),
(30, 'Cloud', 'Frankfurt'),
(40, 'Backup', 'Amsterdam');
INSERT 0 4
pgdump_test=# select * from employees;
employee_id | employee_name | department | salary | joining_date
-------------+---------------+------------+----------+--------------
1 | John Smith | Database | 75000.00 | 2022-01-10
2 | Mary Jones | Database | 72000.00 | 2022-03-15
3 | David Miller | Linux | 68000.00 | 2021-06-20
4 | Sarah Brown | Cloud | 82000.00 | 2020-11-05
5 | Robert Wilson | Cloud | 79000.00 | 2023-02-12
6 | Lisa Taylor | Backup | 70000.00 | 2022-08-17
7 | James Davis | Linux | 71000.00 | 2021-12-01
8 | Emma White | Database | 76000.00 | 2023-04-18
9 | Michael Clark | Backup | 69000.00 | 2020-07-25
10 | Sophia Lewis | Cloud | 85000.00 | 2024-01-08
(10 rows)
pgdump_test=# select * from departments;
department_id | department_name | location
---------------+-----------------+-----------
10 | Database | Stockholm
20 | Linux | London
30 | Cloud | Frankfurt
40 | Backup | Amsterdam
(4 rows)
pgdump_test=# \q

Leave a Reply

Designed with WordPress