pg_dump of database with numerous objects
I have always used pg_basebackup to backup my database and I have never
had any issues.
I am now needing to upgrade to a new version of PostgreSQL and I am
running into problems when pg_upgrade calls pg_dump. pg_dump stalled at:
"pg_dump: saving database definition" for 24 hours before I killed the
process.
My pg_class table contains 9,000,000 entries and I have 9004 schema.
I was able to get output from pg_dump if I used the -n option to dump
schema with wildcards. I was able to use -n 'data???x' where x was a
digit from 0 to 9. This way I was able to execute 10 concurrent pg_dump
processes and dump the database in 30 minutes. I then dumped the public
schema and used pg_dumpall to dump the globals.
Can anyone tell me if there is something else I need to do to manually
dump the database? What I did do seems to have restored correctly on the
upgraded server, but if I want to make sure that I haven't missed
anything that will creep up on me.
On 5/31/20 8:05 AM, tony@exquisiteimages.com wrote:
I have always used pg_basebackup to backup my database and I have never
had any issues.I am now needing to upgrade to a new version of PostgreSQL and I am
running into problems when pg_upgrade calls pg_dump. pg_dump stalled at:
"pg_dump: saving database definition" for 24 hours before I killed the
process.
Where you using the jobs option?:
https://www.postgresql.org/docs/12/pgupgrade.html
-j njobs
--jobs=njobs
number of simultaneous processes or threads to use
My pg_class table contains 9,000,000 entries and I have 9004 schema.
I was able to get output from pg_dump if I used the -n option to dump
schema with wildcards. I was able to use -n 'data???x' where x was a
digit from 0 to 9. This way I was able to execute 10 concurrent pg_dump
processes and dump the database in 30 minutes. I then dumped the public
schema and used pg_dumpall to dump the globals.Can anyone tell me if there is something else I need to do to manually
dump the database? What I did do seems to have restored correctly on the
upgraded server, but if I want to make sure that I haven't missed
anything that will creep up on me.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2020-05-31 11:24, Adrian Klaver wrote:
On 5/31/20 8:05 AM, tony@exquisiteimages.com wrote:
I have always used pg_basebackup to backup my database and I have
never had any issues.I am now needing to upgrade to a new version of PostgreSQL and I am
running into problems when pg_upgrade calls pg_dump. pg_dump stalled
at: "pg_dump: saving database definition" for 24 hours before I killed
the process.Where you using the jobs option?:
https://www.postgresql.org/docs/12/pgupgrade.html
-j njobs
--jobs=njobsnumber of simultaneous processes or threads to use
Yes, I did try with this option. It did dump the postgres database at
the same time as my main database, but my database hung in the same
place.
Show quoted text
My pg_class table contains 9,000,000 entries and I have 9004 schema.
I was able to get output from pg_dump if I used the -n option to dump
schema with wildcards. I was able to use -n 'data???x' where x was a
digit from 0 to 9. This way I was able to execute 10 concurrent
pg_dump processes and dump the database in 30 minutes. I then dumped
the public schema and used pg_dumpall to dump the globals.Can anyone tell me if there is something else I need to do to manually
dump the database? What I did do seems to have restored correctly on
the upgraded server, but if I want to make sure that I haven't missed
anything that will creep up on me.
On May 31, 2020, at 08:05, tony@exquisiteimages.com wrote:
My pg_class table contains 9,000,000 entries and I have 9004 schema.
Which version of pg_dump are you running? Older versions (don't have the precise major version in front of me) have N^2 behavior on the number of database objects being dumped.
--
-- Christophe Pettus
xof@thebuild.com
On 2020-05-31 13:08, Christophe Pettus wrote:
On May 31, 2020, at 08:05, tony@exquisiteimages.com wrote:
My pg_class table contains 9,000,000 entries and I have 9004 schema.
Which version of pg_dump are you running? Older versions (don't have
the precise major version in front of me) have N^2 behavior on the
number of database objects being dumped.
I am upgrading from 9.3
Show quoted text
--
-- Christophe Pettus
xof@thebuild.com
On May 31, 2020, at 13:10, tony@exquisiteimages.com wrote:
On 2020-05-31 13:08, Christophe Pettus wrote:
On May 31, 2020, at 08:05, tony@exquisiteimages.com wrote:
My pg_class table contains 9,000,000 entries and I have 9004 schema.Which version of pg_dump are you running? Older versions (don't have
the precise major version in front of me) have N^2 behavior on the
number of database objects being dumped.I am upgrading from 9.3
To which version? You might try the dump with the version of pg_dump corresponding to the PostgreSQL version you are upgrading *to* (which is recommended practice, anyway) to see if that improves matters.
--
-- Christophe Pettus
xof@thebuild.com
On 5/31/20 1:13 PM, Christophe Pettus wrote:
On May 31, 2020, at 13:10, tony@exquisiteimages.com wrote:
On 2020-05-31 13:08, Christophe Pettus wrote:
On May 31, 2020, at 08:05, tony@exquisiteimages.com wrote:
My pg_class table contains 9,000,000 entries and I have 9004 schema.Which version of pg_dump are you running? Older versions (don't have
the precise major version in front of me) have N^2 behavior on the
number of database objects being dumped.I am upgrading from 9.3
To which version? You might try the dump with the version of pg_dump corresponding to the PostgreSQL version you are upgrading *to* (which is recommended practice, anyway) to see if that improves matters.
Just a reminder that the OP's original issue was with using pg_upgrade.
--
-- Christophe Pettus
xof@thebuild.com
--
Adrian Klaver
adrian.klaver@aklaver.com
On May 31, 2020, at 13:37, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Just a reminder that the OP's original issue was with using pg_upgrade.
True, although IIRC pg_ugprade uses pg_dump under the hood to do the schema migration.
--
-- Christophe Pettus
xof@thebuild.com
On 5/31/20 1:38 PM, Christophe Pettus wrote:
On May 31, 2020, at 13:37, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Just a reminder that the OP's original issue was with using pg_upgrade.
True, although IIRC pg_ugprade uses pg_dump under the hood to do the schema migration.
Again true, but pg_upgrade will not work older to newer:
/usr/local/pgsql11/bin/pg_upgrade --check -U postgres -d
/usr/local/pgsql11/data -D /usr/local/pgsql12_up/data -b
/usr/local/pgsql11/bin -B /usr/local/pgsql12/bin
Performing Consistency Checks
-----------------------------
Checking cluster versions
This utility can only upgrade to PostgreSQL version 11.
Failure, exiting
--
-- Christophe Pettus
xof@thebuild.com
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2020-May-31, tony@exquisiteimages.com wrote:
I am now needing to upgrade to a new version of PostgreSQL and I am running
into problems when pg_upgrade calls pg_dump. pg_dump stalled at: "pg_dump:
saving database definition" for 24 hours before I killed the process.My pg_class table contains 9,000,000 entries and I have 9004 schema.
We've made a number of performance improvements to pg_dump so that it
can dump databases that are "large" in several different dimensions, but
evidently from your report it is not yet good enough when it comes to
dumping millions of tables in thousands of schemas. It will probably
take some profiling of pg_dump to figure out where the bottleneck is,
and some careful optimization work in order to make it faster. Not a
weekend job, I'm afraid :-(
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Jun 3, 2020 at 04:10:55PM -0400, Alvaro Herrera wrote:
On 2020-May-31, tony@exquisiteimages.com wrote:
I am now needing to upgrade to a new version of PostgreSQL and I am running
into problems when pg_upgrade calls pg_dump. pg_dump stalled at: "pg_dump:
saving database definition" for 24 hours before I killed the process.My pg_class table contains 9,000,000 entries and I have 9004 schema.
We've made a number of performance improvements to pg_dump so that it
can dump databases that are "large" in several different dimensions, but
evidently from your report it is not yet good enough when it comes to
dumping millions of tables in thousands of schemas. It will probably
take some profiling of pg_dump to figure out where the bottleneck is,
and some careful optimization work in order to make it faster. Not a
weekend job, I'm afraid :-(
FYI, we never actually found what version of pg_dump was being used,
since pg_upgrade uses the pg_dump version in the newer cluster. We only
know the user is coming _from_ 9.3.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Sun, May 31, 2020 at 02:02:08PM -0700, Adrian Klaver wrote:
On 5/31/20 1:38 PM, Christophe Pettus wrote:
On May 31, 2020, at 13:37, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Just a reminder that the OP's original issue was with using pg_upgrade.
True, although IIRC pg_ugprade uses pg_dump under the hood to do the schema migration.
Again true, but pg_upgrade will not work older to newer:
/usr/local/pgsql11/bin/pg_upgrade --check -U postgres -d
/usr/local/pgsql11/data -D /usr/local/pgsql12_up/data -b
/usr/local/pgsql11/bin -B /usr/local/pgsql12/binPerforming Consistency Checks
-----------------------------
Checking cluster versions
This utility can only upgrade to PostgreSQL version 11.
Failure, exiting
This is saying you can only use pg_upgrade 11.X to upgrade _to_ Postgres
11.X. If you want to upgrade to 12, you have to use pg_upgrade from 12.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee