1

Thanks in advance for any help on this.

Problem Summary: I'm looking for the most efficient way possible to export a single table from Postgres/Greenplum for a large number of records (100M+) so that it can be imported by Oracle SQL Loader.

Research Background: I know from research thus far that the pg_dump utility is more efficient than Postgres COPY, so I do NOT want to use the COPY command.

Using pg_dump has many pluses, and can:

  1. Can use multiple threads/cores
  2. Can dump a single table of output
  3. but to CSV?

My Main Question: The critical thing I can't figure out yet is how to get pg_dump to export to csv or fixed-width plain text output.

A sidebar question: I can't seem to find a detailed description (other than, 'The pg_dump --format=custom means the data is compressed') of what exactly the "custom" pg_dump format does to the data.

The word "custom" implies that the output should be to a controllable schema, but I haven't been able to locate documentation yet of how this works.

Improve this question
4
  • Have you actually compared performance of the COPY command and pg_dump on a single large table? I wouldn't expect the "use multiple threads/cores" to make a whole lot of difference when dumping a single table to a single file, as the bottleneck is elsewhere.
    – mustaccio
    Oct 12, 2022 at 17:01
  • You can't change the output of pg_dump. It's either a SQL script, or a binary dump ("custom format") only understood by pg_restore. "Custom" just means is proprietary to Postgres. You will have to use COPY (or psql's \copy)
    – a_horse_with_no_name
    Oct 12, 2022 at 19:05
  • You have not done your research properly, it seems. This statement is clearly wrong: "I know from research thus far that the pg_dump utility is more efficient than Postgres COPY, so I do NOT want to use the COPY command." pg_dump uses COPY to dump the data, so that cannot be true.
    – Laurenz Albe
    Oct 13, 2022 at 4:01
  • Thank to @mustaccio and a_horse_with_no_name and Laurenz Able for your input on this.
    – zigmoo
    Oct 13, 2022 at 13:41

2 Answers 2

Reset to default
0

One possibility is to use the python pgdumplib package (https://pypi.org/project/pgdumplib/). It allows you to read a custom format pg_dump file and extract information from it.

In addition, you can combine this with the cx_Oracle or better, the newer oracledb (https://python-oracledb.readthedocs.io/en/latest/index.html) package to write directly into the database instead of writing csv files and using sql*loader ...

A word of caution: pgdumplib reads the entire dump file in memory. For 100m+ rows, that may need a lot of RAM (or not: that depends on the sizeof your rows). You could generate multiple dump files, each with a smaller number of rows - 10 files of 10m rows. The you can even parallelize the process: launch 10 concurrent jobs, each one processing part of the data.

Now of course, if you can connect both to your PostgreSQL database and your Oracle database, then you could just do the transfer directly using one of the numerous connectors available: psycopg2 (https://pypi.org/project/psycopg2/) seems the most popular. That gives you full control over the process, and again you can take full advantage of parallelism by using multiple concurrent threads.

Improve this answer
0

you can use a normal and well documented odbc interface, build up a connection to the postgres server and use cas statement( create table as .. ).

following steps are necessary:

on the oracle host install postgresodbc then create a odbc.ini with your parameters to the postgres server create tnsadmin.ora and listener.ora entries restart listener

After this and if you done all steps correct you should be able to connect to postgres.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.