Обсуждение: pg_upgrade --jobs

The pg_upgrade --jobs option is not passed as an argument when it calls pg_dump. I haven't found anything in docs or
forumsmentioning a reason for not supporting under certain circumstances other than possibly for pre-9.2. The
pg_upgradedocs page states that it allows multiple CPUs to be used for dump and reload of schemas. Some databases I'm
upgradinghave 500,000+ tables and running with a single process is greatly increasing the upgrade time. 

I am also using the --link option.
I have tried "--jobs 20", "--jobs=20", placing this option first and last and many other variations.
I am upgrading 9.2.4 to 9.6.12 on CentOS 6.
Varying hardware but all with 32+ CPU cores.

su - postgres -c "/usr/pgsql-9.6/bin/pg_upgrade --jobs=20 --link \
       --old-bindir=/usr/pgsql-9.2/bin/ \
       --new-bindir=/usr/pgsql-9.6/bin/ \
       --old-datadir=/var/lib/pgsql/9.2/data/ \
       --new-datadir=/var/lib/pgsql/9.6/data/"

I feel like there's a simple reason I've missed but this seems pretty straight forward.
A secondary plan would be to find instructions for doing the same as "pg_upgrade --link" manually so I can run "pg_dump
--jobs20".  
Any assist is appreciated.
Thanks,
S. Cervesa

On 4/6/19 11:44 AM, senor wrote:
> The pg_upgrade --jobs option is not passed as an argument when it calls pg_dump. I haven't found anything in docs or
forumsmentioning a reason for not supporting under certain circumstances other than possibly for pre-9.2. The
pg_upgradedocs page states that it allows multiple CPUs to be used for dump and reload of schemas. Some databases I'm
upgradinghave 500,000+ tables and running with a single process is greatly increasing the upgrade time.
 
> 
> I am also using the --link option.
> I have tried "--jobs 20", "--jobs=20", placing this option first and last and many other variations.
> I am upgrading 9.2.4 to 9.6.12 on CentOS 6.
> Varying hardware but all with 32+ CPU cores.
> 
> su - postgres -c "/usr/pgsql-9.6/bin/pg_upgrade --jobs=20 --link \
>         --old-bindir=/usr/pgsql-9.2/bin/ \
>         --new-bindir=/usr/pgsql-9.6/bin/ \
>         --old-datadir=/var/lib/pgsql/9.2/data/ \
>         --new-datadir=/var/lib/pgsql/9.6/data/"
> 
> I feel like there's a simple reason I've missed but this seems pretty straight forward.

https://www.postgresql.org/docs/9.6/app-pgdump.html

"--jobs=njobs

     Run the dump in parallel by dumping njobs tables simultaneously. 
This option reduces the time of the dump but it also increases the load 
on the database server. You can only use this option with the directory
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
output format because this is the only output format where multiple
^^^^^^^^^^^^^
processes can write their data at the same time."


> A secondary plan would be to find instructions for doing the same as "pg_upgrade --link" manually so I can run
"pg_dump--jobs 20".
 
> Any assist is appreciated.
> Thanks,
> S. Cervesa
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Thank you for responding. I did see that note and should have included that as part of my question.

Since pg_upgrade is in control of how it is calling pg_dump, is there a reason pg_upgrade cannot use the directory
outputformat when calling pg_dump? Is the schema-only operation incompatible? 

________________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Saturday, April 6, 2019 1:52 PM
To: senor; pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade --jobs

On 4/6/19 11:44 AM, senor wrote:
> The pg_upgrade --jobs option is not passed as an argument when it calls pg_dump. I haven't found anything in docs or
forumsmentioning a reason for not supporting under certain circumstances other than possibly for pre-9.2. The
pg_upgradedocs page states that it allows multiple CPUs to be used for dump and reload of schemas. Some databases I'm
upgradinghave 500,000+ tables and running with a single process is greatly increasing the upgrade time. 
>
> I am also using the --link option.
> I have tried "--jobs 20", "--jobs=20", placing this option first and last and many other variations.
> I am upgrading 9.2.4 to 9.6.12 on CentOS 6.
> Varying hardware but all with 32+ CPU cores.
>
> su - postgres -c "/usr/pgsql-9.6/bin/pg_upgrade --jobs=20 --link \
>         --old-bindir=/usr/pgsql-9.2/bin/ \
>         --new-bindir=/usr/pgsql-9.6/bin/ \
>         --old-datadir=/var/lib/pgsql/9.2/data/ \
>         --new-datadir=/var/lib/pgsql/9.6/data/"
>
> I feel like there's a simple reason I've missed but this seems pretty straight forward.

https://www.postgresql.org/docs/9.6/app-pgdump.html

"--jobs=njobs

     Run the dump in parallel by dumping njobs tables simultaneously.
This option reduces the time of the dump but it also increases the load
on the database server. You can only use this option with the directory
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
output format because this is the only output format where multiple
^^^^^^^^^^^^^
processes can write their data at the same time."


> A secondary plan would be to find instructions for doing the same as "pg_upgrade --link" manually so I can run
"pg_dump--jobs 20". 
> Any assist is appreciated.
> Thanks,
> S. Cervesa
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

senor <frio_cervesa@hotmail.com> writes:
> Since pg_upgrade is in control of how it is calling pg_dump, is there a reason pg_upgrade cannot use the directory
outputformat when calling pg_dump? Is the schema-only operation incompatible? 

Well, there's no point in it.  pg_dump can only parallelize data dumping,
and there's none to be done in the --schema-only case that pg_upgrade
uses.

Also, since pg_upgrade *does* use parallelism across multiple pg_dump
calls (if you've got multiple databases in the cluster), it'd be a bit
problematic to have another layer of parallelism below that, if it did
indeed do anything.  You don't want "--jobs=10" to suddenly turn into
100 sessions.

            regards, tom lane

Thanks Tom. I suppose "pg_dump can only parallelize data dumping" answers my original question as "expected behavior"
butI would like to understand the reason better. 

My knowledge of Postgres and other DBMSs is at casual admin level with the occasional deep dive on specific errors or
analysis.I'm not averse to getting into the code. Before my OP I searched for reasons that the schema-only option would
preventpg_dump from being able to run multiple jobs and didn't find anything that I understood to confirm either way. 

Is the limitation simply the state of development to date or is there something about dumping the schemas that
conflictswith paralleling? I'm willing to do some studying if provided links to relevant articles. 

The --link option to pg_upgrade would be so much more useful if it weren't still bound to serially dumping the schemas
ofhalf a million tables. As already mentioned, if there is an alternate process that mimics pg_upgrade but allows for
paralleling,I'm open to that. 

Thanks all

________________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Saturday, April 6, 2019 3:02 PM
To: senor
Cc: pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade --jobs

senor <frio_cervesa@hotmail.com> writes:
> Since pg_upgrade is in control of how it is calling pg_dump, is there a reason pg_upgrade cannot use the directory
outputformat when calling pg_dump? Is the schema-only operation incompatible? 

Well, there's no point in it.  pg_dump can only parallelize data dumping,
and there's none to be done in the --schema-only case that pg_upgrade
uses.

Also, since pg_upgrade *does* use parallelism across multiple pg_dump
calls (if you've got multiple databases in the cluster), it'd be a bit
problematic to have another layer of parallelism below that, if it did
indeed do anything.  You don't want "--jobs=10" to suddenly turn into
100 sessions.

                        regards, tom lane

senor <frio_cervesa@hotmail.com> writes:
> Is the limitation simply the state of development to date or is there
> something about dumping the schemas that conflicts with paralleling?

At minimum, it'd take a complete redesign of pg_dump's output format,
and I'm not even very sure what such a redesign would look like.  All
the schema information goes into a single file that has to be written
serially.  Trying to make it be one file per table definition wouldn't
really fix much: somewhere there has to be a "table of contents", plus
where are you going to put the dependency info that shows what ordering
is required for restore?

> The --link option to pg_upgrade would be so much more useful if it
> weren't still bound to serially dumping the schemas of half a million
> tables.

To be perfectly blunt, if you've got a database with half a million
tables, You're Doing It Wrong.

            regards, tom lane

senor <frio_cervesa@hotmail.com> writes:

The --link option to pg_upgrade would be so much more useful if it
weren't still bound to serially dumping the schemas of half a million
tables.

To be perfectly blunt, if you've got a database with half a million
tables, You're Doing It Wrong.

Thanks Tom for the explanation. I assumed it was my ignorance of how the schema was handled that was making this look
likea problem that had already been solved and I was missing something. 

I fully expected the "You're Doing It Wrong" part. That is out of my control but not beyond my influence.

I suspect I know the answer to this but have to ask. Using a simplified example where there are 100K sets of 4 tables,
eachrepresenting the output of a single job, are there any shortcuts to upgrading that would circumvent exporting the
entireschema? I'm sure a different DB design would be better but that's not what I'm working with. 

Thanks

________________________________________
From: Ron <ronljohnsonjr@gmail.com>
Sent: Saturday, April 6, 2019 4:57 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade --jobs

On 4/6/19 6:50 PM, Tom Lane wrote:

senor <frio_cervesa@hotmail.com><mailto:frio_cervesa@hotmail.com> writes:


[snip]

The --link option to pg_upgrade would be so much more useful if it
weren't still bound to serially dumping the schemas of half a million
tables.



To be perfectly blunt, if you've got a database with half a million
tables, You're Doing It Wrong.

Heavy (really heavy) partitioning?

--
Angular momentum makes the world go 'round.

On 4/6/19 5:47 PM, senor wrote:
> Thanks Tom for the explanation. I assumed it was my ignorance of how the schema was handled that was making this look
likea problem that had already been solved and I was missing something.
 
> 
> I fully expected the "You're Doing It Wrong" part. That is out of my control but not beyond my influence.
> 
> I suspect I know the answer to this but have to ask. Using a simplified example where there are 100K sets of 4
tables,each representing the output of a single job, are there any shortcuts to upgrading that would circumvent
exportingthe entire schema? I'm sure a different DB design would be better but that's not what I'm working with.
 

An answer is going to depend on more information:

1) What is the time frame for moving from one version to another?
Both the setup and the actual downtime.

2) There are 500,000+ tables, but what is the amount of data involved?

3) Are all the tables active?

4) How are the tables distributed across databases in the cluster and 
schemas in each database?


> 
> Thanks
> 
> ________________________________________
> From: Ron <ronljohnsonjr@gmail.com>
> Sent: Saturday, April 6, 2019 4:57 PM
> To: pgsql-general@lists.postgresql.org
> Subject: Re: pg_upgrade --jobs
> 
> On 4/6/19 6:50 PM, Tom Lane wrote:
> 
> senor <frio_cervesa@hotmail.com><mailto:frio_cervesa@hotmail.com> writes:
> 
> 
> [snip]
> 
> The --link option to pg_upgrade would be so much more useful if it
> weren't still bound to serially dumping the schemas of half a million
> tables.
> 
> 
> 
> To be perfectly blunt, if you've got a database with half a million
> tables, You're Doing It Wrong.
> 
> Heavy (really heavy) partitioning?
> 
> --
> Angular momentum makes the world go 'round.
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Thank you Adrian. I'm not sure if I can provide as much as you'd need for a definite answer but I'll give you what I
have.

The original scheduled downtime for one installation was 24 hours. By 21 hours it had not completed the pg_dump
schema-onlyso it was returned to operation. 
The amount of data per table is widely varied. Some daily tables are 100-200GB and thousands of reports tables with
statsare much smaller. I'm not connected to check now but I'd guess 1GB max. We chose to use the --link option partly
becausesome servers do not have the disk space to copy. The time necessary to copy 1-2TB was also going to be an issue. 
The vast majority of activity is on current day inserts and stats reports of that data. All previous days and existing
reportsare read only. 
As is all too common, the DB usage grew with no redesign so it is a single database on a single machine with a single
schema.
I get the impression there may be an option of getting the schema dump while in service but possibly not in this
scenario.Plan B is to drop a lot of tables and deal with imports later. 

I appreciate the help.

________________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Sunday, April 7, 2019 8:19 AM
To: senor; pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade --jobs

On 4/6/19 5:47 PM, senor wrote:
> Thanks Tom for the explanation. I assumed it was my ignorance of how the schema was handled that was making this look
likea problem that had already been solved and I was missing something. 
>
> I fully expected the "You're Doing It Wrong" part. That is out of my control but not beyond my influence.
>
> I suspect I know the answer to this but have to ask. Using a simplified example where there are 100K sets of 4
tables,each representing the output of a single job, are there any shortcuts to upgrading that would circumvent
exportingthe entire schema? I'm sure a different DB design would be better but that's not what I'm working with. 

An answer is going to depend on more information:

1) What is the time frame for moving from one version to another?
Both the setup and the actual downtime.

2) There are 500,000+ tables, but what is the amount of data involved?

3) Are all the tables active?

4) How are the tables distributed across databases in the cluster and
schemas in each database?


>
> Thanks
>
> ________________________________________
> From: Ron <ronljohnsonjr@gmail.com>
> Sent: Saturday, April 6, 2019 4:57 PM
> To: pgsql-general@lists.postgresql.org
> Subject: Re: pg_upgrade --jobs
>
> On 4/6/19 6:50 PM, Tom Lane wrote:
>
> senor <frio_cervesa@hotmail.com><mailto:frio_cervesa@hotmail.com> writes:
>
>
> [snip]
>
> The --link option to pg_upgrade would be so much more useful if it
> weren't still bound to serially dumping the schemas of half a million
> tables.
>
>
>
> To be perfectly blunt, if you've got a database with half a million
> tables, You're Doing It Wrong.
>
> Heavy (really heavy) partitioning?
>
> --
> Angular momentum makes the world go 'round.
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

I just noticed I missed Sherrylyn's post.
I did some reading about Slony and believe it is would be useful if I had the time to dig in. As pointed out, it's not
anout-of-the box solution. It is included on the TODO list though. For now I can only dream of the 86 second down time. 

Thanks

________________________________________
From: Sherrylyn Branchaw <sbranchaw@gmail.com>
Sent: Sunday, April 7, 2019 6:43 AM
To: senor
Cc: pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade --jobs

are there any shortcuts to upgrading that would circumvent exporting the entire schema?

By "shortcuts," do you mean you want to minimize the time and energy you put into the upgrade, or that you want to
minimizedatabase downtime? If you mean downtime, I was able to upgrade a customer-facing database with ~350,000 tables
fromPostgres 9.0 to 9.6 last year with only 86 seconds of downtime, using Slony, but I had to make many custom
modificationsto Slony and test thoroughly beforehand, and it was not for the faint of heart, the pressed for time, or
theinexperienced. There may be better ways (and if so, I would be curious to learn about them), but Slony was the tool
withwhich I was most familiar at the time. 

This method does, of course, require exporting the entire schema, but because our only constraint was to minimize
customerdowntime, and the database was online while the schema was being exported, we didn't care how long it took.
Yourconstraints may be different. 

For those reading: we do know that 350,000 tables is Doing It Wrong, and we're getting rid of them, but we decided
beingon an EOLed version of Postgres was worse and should be fixed first. 

Sherrylyn

On 4/7/19 12:05 PM, senor wrote:
> Thank you Adrian. I'm not sure if I can provide as much as you'd need for a definite answer but I'll give you what I
have.
> 
> The original scheduled downtime for one installation was 24 hours. By 21 hours it had not completed the pg_dump
schema-onlyso it was returned to operation.
 

So this is more then one cluster?

I am assuming the below was repeated at different sites?

> The amount of data per table is widely varied. Some daily tables are 100-200GB and thousands of reports tables with
statsare much smaller. I'm not connected to check now but I'd guess 1GB max. We chose to use the --link option partly
becausesome servers do not have the disk space to copy. The time necessary to copy 1-2TB was also going to be an
issue.
> The vast majority of activity is on current day inserts and stats reports of that data. All previous days and
existingreports are read only.
 
> As is all too common, the DB usage grew with no redesign so it is a single database on a single machine with a single
schema.
> I get the impression there may be an option of getting the schema dump while in service but possibly not in this
scenario.Plan B is to drop a lot of tables and deal with imports later.
 

I take the above to mean that a lot of the tables are cruft, correct?

> 
> I appreciate the help.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com