Обсуждение: 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=njobs > > number 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. > >> >> 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^2behavior 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 > > -- > -- 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/bin > > Performing 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