RE: [PoC] pg_upgrade: allow to upgrade publisher node

Dear Amit,

> > I updated the patch to allow parallel executions. Workers are launched per slots,
> > each one connects to the new node via psql and executes
> pg_create_logical_replication_slot().
> >
> 
> Will it be beneficial for slots? Invoking a separate process each time
> could be more costlier than slot creation. The other thing is during
> slot creation, the snapbuild waits for parallel transactions to finish
> so that can also hurt the patch. I think we can test it by having 50,
> 100, or 500 slots on the old cluster and see if doing parallel
> execution for the creation of those on the new cluster has any benefit
> over serial execution.

Indeed. I have tested based on the comment and found that serial execution was
faster. PSA graphs and tables. The x-axis shows the number of upgraded slots,
y-axis shows the execution time. The parallelism of pg_upgrade (-j) was also
varied during the test.

I've planned to revert the change in upcoming versions.

# compared source code

For parallel execution case, the v21 patch set was used.
For serial execution case, logics in create_logical_replication_slots() are changed,
which is basically same as v20 (I can share if needed).

Moreover, in both cases, debug logs for measuring time were added.

# method

PSA the script. Some given number of slots are created and then pg_upgrade was executed.

# consideration

* In any conditions, the serial execution was faster than parallel. Maybe the
  launching process was more costly than I expected.
* Another reason I thougth was that in case of serial execution, the connection
  to new node was established only once. Parallel case, however, workers must
  establish connections every time. IIUC this requires long duration.
* (very trivial) Number of workers were not affected in serial execution. This
  means the coding seems right.

> > * Added checks for output plugin libraries. pg_upgrade ensures that plugins
> >   referred by old slots were installed to the new executable directory.
> >
> 
> I think this is a good idea but did you test it with out-of-core
> plugins, if so, can you please share the results? Also, let's update
> this information in docs as well.

I have not used other plugins, but forcibly renamed the shared object file.
I would test by plugins like wal2json[1] if more cases are needed.

1. created logical replication slots on old node
  SELECT * FROM pg_create_logical_replication_slot('test', 'test_decoding')
2. stopped the old nde
3. forcibly renamed the so file. I used following script:
  sudo mv /path/to/test_decoding.so /path/to//test\"_decoding.so
4. executed pg_upgrade and failed. Outputs what I got were:

```
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    data_N3/pg_upgrade_output.d/20230817T100926.979/loadable_libraries.txt
Failure, exiting
```

And contents of loadable_libraries.txt were below:

```
could not load library "test_decoding": ERROR:  could not access file "test_decoding": No such file or directory
In database: postgres
```

[1]: https://github.com/eulerto/wal2json

Best Regards,
Hayato Kuroda
FUJITSU LIMITED