Certain options in pg_upgrade don't seem to work.

pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: DBA <ecountdba@ecounterp.co.kr>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Certain options in pg_upgrade don't seem to work.
Date: Wed, 14 Aug 2024 10:41:50 +0900
Message-ID: <20240814014150.15B2710705F@wsmtpa1.ecounterp.com> (raw)

<p>Hello, I'm inquiring because --j option&nbsp;don't seem to be working properly in pg_upgrade.</p><p><br></p><p><b>1. A description of what you are trying to achieve and what results you expect :&nbsp;</b></p><p><br></p><p>We took to much time to upgrade postgresql with pg_upgrade.</p><p><br></p><p>So we have tested with --j option in pg_upgrade by below command&nbsp;</p><p><br></p><p><b>time PGPASSWORD='1q2w3e4r' nohup /usr/lib/postgresql/16/bin/pg_upgrade -b /usr/lib/postgresql/15/bin/ -B /usr/lib/postgresql/16/bin/ -d /data/PG15 -D /data/PG16 -k -j 16 -U ecount_own &gt; /data/tmp4/upgrade.log &amp;</b></p><p><br></p><p>It took 520 minutes to upgrade and we used -j option to make pg_upgrade use multiple CPU processor but it didn't use them. (Only use single processor)&nbsp;</p><p>(It took the same amount of time as when tested without --j option.)</p><p><br></p><p>Is it normal not to use multiple cpu cores even with the --j option?</p><p><br></p><p>The docs says that you use multi-processors for restoring.</p><p><br></p><p><b>note.)</b> We have about 80 partition tables and each partition table has 9000 children tables.</p><p><br></p><p>&nbsp; &nbsp; &nbsp; &nbsp;I think it could be affected by the number of partition tables.</p><p><br></p><p><b>2. PostgreSQL version number you are running:</b></p><p><br></p><p>PostgreSQL 15.3 (Ubuntu 15.3-1.pgdg22.04+~20230706.1614.g25624c5) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, 64-bit</p><p><br></p><p><b style="">3. How you installed PostgreSQL:</b></p><p><br></p><p>We have downloaded debs on https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-16/</p><p><br><... by</p><p><br></p><p>apt install ./*.deb</p><p><br></p><p><b>4. Changes made to the settings in the postgresql.conf file:</b></p><p><b><br></b></p><table border="0" cellpadding="0" cellspacing="0" width="735" style="width: 551pt;">
 <colgroup><col width="259" style="mso-width-source:userset;mso-width-alt:9472;width:194pt">
 <col width="359" style="mso-width-source:userset;mso-width-alt:13129;width:269pt">
 <col width="117" style="mso-width-source:userset;mso-width-alt:4278;width:88pt">
 </colgroup><tbody><tr height="18" style="height:13.5pt">
  <td height="18" width="259" style="height:13.5pt;width:194pt">name</td>
  <td width="359" style="width: 269pt; text-align: left;">current_setting</td>
  <td width="117" style="width:88pt">source</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">archive_command</td>
  <td style="text-align: left;">cp /data/PG15/pg_wal/%f /archive_data/%f</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">archive_mode</td>
  <td style="text-align: left;">on</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">auto_explain.log_analyze</td>
  <td style="text-align: left;">on</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">auto_explain.log_buffers</td>
  <td style="text-align: left;">on</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_analyze_scale_factor</td>
  <td style="text-align: left;"><span></span><span></span>0</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_analyze_threshold</td>
  <td style="text-align: left;">100000000</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_freeze_max_age</td>
  <td style="text-align: left;">500000000</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_max_workers</td>
  <td style="text-align: left;">3</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_naptime</td>
  <td style="text-align: left;">1min</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_vacuum_cost_delay</td>
  <td style="text-align: left;">50ms</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_vacuum_insert_scale_factor</td>
  <td style="text-align: left;">0</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_vacuum_insert_threshold</td>
  <td style="text-align: left;">30000</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_vacuum_scale_factor</td>
  <td style="text-align: left;">0</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">autovacuum_vacuum_threshold</td>
  <td style="text-align: left;">5000</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">checkpoint_warning</td>
  <td style="text-align: left;">1h</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">client_encoding</td>
  <td style="text-align: left;">UTF8</td>
  <td>client</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">DateStyle</td>
  <td style="text-align: left;">ISO, MDY</td>
  <td>client</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">deadlock_timeout</td>
  <td style="text-align: left;">100ms</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">default_text_search_config</td>
  <td style="text-align: left;">pg_catalog.english</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">dynamic_shared_memory_type</td>
  <td style="text-align: left;">posix</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">effective_cache_size</td>
  <td style="text-align: left;">48GB</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">effective_io_concurrency</td>
  <td style="text-align: left;">600</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">enable_mergejoin</td>
  <td style="text-align: left;">off</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">enable_seqscan</td>
  <td style="text-align: left;">off</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">extra_float_digits</td>
  <td style="text-align: left;">3</td>
  <td>session</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">jit</td>
  <td style="text-align: left;">off</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">lc_messages</td>
  <td style="text-align: left;">en_US.UTF-8</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">lc_monetary</td>
  <td style="text-align: left;">en_US.UTF-8</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">lc_numeric</td>
  <td style="text-align: left;">en_US.UTF-8</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">lc_time</td>
  <td style="text-align: left;">en_US.UTF-8</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">listen_addresses</td>
  <td style="text-align: left;">*</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_autovacuum_min_duration</td>
  <td style="text-align: left;">10ms</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_checkpoints</td>
  <td style="text-align: left;">on</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_destination</td>
  <td style="text-align: left;">stderr, csvlog</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_directory</td>
  <td style="text-align: left;">/data/log</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_filename</td>
  <td style="text-align: left;">postgresql-%a.log</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_line_prefix</td>
  <td style="text-align: left;">%m [%p][%u][%h]</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_lock_waits</td>
  <td style="text-align: left;">on</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_min_duration_statement</td>
  <td style="text-align: left;">1500ms</td>
  <td>user</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_min_messages</td>
  <td style="text-align: left;">warning</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_rotation_age</td>
  <td style="text-align: left;">1d</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_rotation_size</td>
  <td style="text-align: left;">0</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_timezone</td>
  <td style="text-align: left;">Asia/Seoul</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">log_truncate_on_rotation</td>
  <td style="text-align: left;">on</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">logging_collector</td>
  <td style="text-align: left;">on</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">maintenance_work_mem</td>
  <td style="text-align: left;">400MB</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">max_connections</td>
  <td style="text-align: left;">1000</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">max_locks_per_transaction</td>
  <td style="text-align: left;">2000</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">max_parallel_workers</td>
  <td style="text-align: left;">0</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">max_parallel_workers_per_gather</td>
  <td style="text-align: left;">0</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">max_wal_size</td>
  <td style="text-align: left;">2GB</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">max_worker_processes</td>
  <td style="text-align: left;">50</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">min_wal_size</td>
  <td style="text-align: left;">1GB</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">password_encryption</td>
  <td style="text-align: left;">md5</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">plan_cache_mode</td>
  <td style="text-align: left;">force_custom_plan</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">port</td>
  <td style="text-align: left;">45432</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">restore_command</td>
  <td style="text-align: left;">mv -f /archive_data/%f /data/PG15/pg_wal/%f</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">shared_buffers</td>
  <td style="text-align: left;">70GB</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">shared_preload_libraries</td>
  <td style="text-align: left;">pg_hint_plan, pg_cron, pg_stat_statements, auto_explain</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">superuser_reserved_connections</td>
  <td style="text-align: left;">6</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">tcp_keepalives_idle</td>
  <td style="text-align: left;">30</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">tcp_keepalives_interval</td>
  <td style="text-align: left;">1</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">TimeZone</td>
  <td style="text-align: left;">Asia/Seoul</td>
  <td>client</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">vacuum_cost_limit</td>
  <td style="text-align: left;">1000</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">wal_buffers</td>
  <td style="text-align: left;">1GB</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">wal_compression</td>
  <td style="text-align: left;">zstd</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">wal_sender_timeout</td>
  <td style="text-align: left;">10min</td>
  <td>configuration file</td>
 </tr>
 <tr height="18" style="height:13.5pt">
  <td height="18" style="height:13.5pt">work_mem</td>
  <td style="text-align: left;">200MB</td>
  <td>configuration file</td>
 </tr></tbody></table><p><br></p><p><b>5. Operating system and version:</b></p><p>Distributor ID: Ubuntu</p><p>Description:&nbsp; &nbsp; Ubuntu 22.04.2 LTS</p><p>Release:&nbsp; &nbsp; &nbsp; &nbsp; 22.04</p><p>Codename:&nbsp; &nbsp; &nbsp; &nbsp;jammy</p><p><br></p><p><b>6. What program you're using to connect to PostgreSQL:</b></p><p><br></p><p>Just do on Linux kernel</p><p><br></p><p><br></p><p><br></p><table id="ecma_tracking"><tr><td><img src='https://wmail.ecount.com/ec5/api/app.webmail/action/ReadReceiptAction:6d736769643d323032343038313431...;

view thread (2+ messages)  latest in thread

Message-ID: <20240814014150.15B2710705F@wsmtpa1.ecounterp.com>
Permalink:  ../20240814014150.15B2710705F@wsmtpa1.ecounterp.com/
Also on:    postgresql.org/message-id/20240814014150.15B2710705F@wsmtpa1.ecounterp.com

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: ecountdba@ecounterp.co.kr, pgsql-bugs@lists.postgresql.org
  Subject: Re: Certain options in pg_upgrade don't seem to work.
  In-Reply-To: <20240814014150.15B2710705F@wsmtpa1.ecounterp.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox