Version upgrade guide
This appendix provides a reference guide for upgrading FUJITSU Enterprise Postgres 11 Advanced Edition on IBM LinuxONE to FUJITSU Enterprise Postgres 12 Advanced Edition. We show the steps to use the pg_upgrade command to perform a version upgrade.
This appendix covers the following topics:
Overview of pg_upgrade for major version upgrades
The pg_upgrade (formerly pg_migrator) utility was introduced to simplify the major version upgrade process. Major version upgrades can get complex because of the various new features and associated system tables changes. The pg_upgrade utility performs version upgrades without needing to dump and restore data between the existing database version to the new database version because dump and restore are handled internally by pg_upgrade. Figure A-1 shows a high-level internal flow of pg_upgrade.
Figure A-1 The pg_upgrade high-level process flow
Using pg_upgrade
In this section, we show how to use pg_upgrade to update FUJITSU Enterprise Postgres 11 to FUJITSU Enterprise Postgres 12 on a SUSE Linux Enterprise Server 12 SP4 operating system (OS). We perform the following steps on virtual machine (VM) RDBKPGS1 on logical partition (LPAR) ARIES32 on IBM LinuxONE Machine A:
1. Set up FUJITSU Enterprise Postgres 11.
2. Populate the sample application database that is represented by the pgbench database.
3. Set up FUJITSU Enterprise Postgres 12.
4. Perform the upgrade by using the pg_upgrade utility.
5. Drop FUJITSU Enterprise Postgres 11.
|
Note: The prerequisites for the steps that are shown in this chapter is that FUJITSU Enterprise Postgres 11 and 12 are already installed.
|
Setting up FUJITSU Enterprise Postgres 11
In this section, we prepare the system to upgrade FUJITSU Enterprise Postgres 11 Advanced Edition on IBM LinuxONE to FUJITSU Enterprise Postgres 12 Advanced Edition by completing the following steps:
1. Configure the environment variables on the server, as shown in Example A-1.
Example: A-1 Configuring the environment variables
# su - fsepuser
$ PATH=/opt/fsepv11server64/bin:$PATH ; export PATH
$ MANPATH=/opt/fsepv11server64/share/man:$MANPATH ; export MANPATH
$ LD_LIBRARY_PATH=/opt/fsepv11server64/lib:$LD_LIBRARY_PATH ; export LD_LIBRARY_PATH
2. Create an instance as shown in Example A-2. Specify the transaction log storage destination and the locale setting option as required.
Example: A-2 Creating an instance
$ initdb -D /database/inst1 --waldir=/transaction/inst1 --lc-collate="C" --lc-ctype="C" --encoding=UTF8
The files belonging to this database system will be owned by user "fsepuser".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: C
CTYPE: C
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC: en_US.UTF-8
TIME: en_US.UTF-8
The default text search configuration will be set to "english". (15541)
Data page checksums are disabled. (18153)
fixing permissions on existing directory /database/inst1 ... ok (15516)
fixing permissions on existing directory /transaction/inst1 ... ok (15516)
creating subdirectories ... ok (15516)
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... (19842)America/New_York
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok (15516)
running bootstrap script ... ok (15516)
performing post-bootstrap initialization ... ok (15516)
syncing data to disk ... ok (15516)
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /database/inst1 -l logfile start
Example: A-3 Configuration file
$ vim /database/inst1/postgresql.conf
listen_addresses = '*'
port = 27500
logging_collector = on
backup_destination = '/backup/inst1'
archive_mode = on
archive_command = '/opt/fsepv12server64/bin/pgx_walcopy.cmd "%p" "/backup/inst1/archived_wal/%f"'
4. Set up pg_hba.conf, which is the client authentication configuration file. Change xx.xx.xx.0 in Example A-4 to the IP address range of your environment.
Example: A-4 Configuration file
$ vim /database/inst1/pg_hba.conf
host all all xxx.xx.xx.0/24 trust
5. Start the FUJITSU Enterprise Postgres 11 database instance, as shown in Example A-5.
Example: A-5 Starting the database instance
$ pg_ctl start -D /database/inst1
waiting for server to start....2021-01-06 01:39:15.579 EST [24177] LOG: listening on IPv4 address "0.0.0.0", port 27500
2021-01-06 01:39:15.579 EST [24177] LOG: listening on IPv6 address "::", port 27500
2021-01-06 01:39:15.580 EST [24177] LOG: listening on Unix socket "/tmp/.s.PGSQL.27500"
2021-01-06 01:39:15.585 EST [24177] LOG: redirecting log output to logging collector process
2021-01-06 01:39:15.585 EST [24177] HINT: Future log output will appear in directory "log".
done
server started
6. Verify the connectivity by using the command that is shown in Example A-6.
Example: A-6 Verifying the connectivity
$ psql -h xxx.xx.xx.162 -p 27500 -d postgres -U fsepuser
psql (11.6)
Type "help" for help.
postgres=#
We have now set up the FUJITSU Enterprise Postgres 11 database instance.
Preparing FUJITSU Enterprise Postgres 11 for an upgrade
In this section, we prepare a FUJITSU Enterprise Postgres 11 installation for upgrading to a FUJITSU Enterprise Postgres 12 installation by completing the following steps:
Example: A-7 Initializing the sample pgbench database
$ pgbench -i postgres
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping (11929)
NOTICE: table "pgbench_branches" does not exist, skipping (11929)
NOTICE: table "pgbench_history" does not exist, skipping (11929)
NOTICE: table "pgbench_tellers" does not exist, skipping (11929)
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
2. Back up the FUJITSU Enterprise Postgres 11 database instance by using the following command:
$ pg_dumpall > fep11_postgres.sql
3. Drop the extension pg_stat_statements. The command along with the results is shown in Example A-8.
Example: A-8 Dropping the extension
$ psql -h xxx.xx.xx.162 -p 27500 -d postgres -U fsepuser -c "DROP EXTENSION pg_stat_statements"
DROP EXTENSION
|
Note: If you have set up the following extensions, you must drop them from all databases other than the template0 database:
•pg_stat_statements
•oracle_compatible
•pg_dbms_stats
•pg_hint_plan
|
4. Stop the database instance (Example A-9).
Example: A-9 Stopping the instance
$ pg_ctl stop -D /database/inst1
waiting for server to shut down.... done
server stopped
5. Move the instance, Write-Ahead-Log (WAL), and backup directories by using the commands that are shown in Example A-10.
Example: A-10 Moving the instance
$ mv /database/inst1/ /database/inst1.fep11
$ mv /transaction/inst1/ /transaction/inst1.fep11
$ mv /backup/inst1/ /backup/inst11.fep11
6. Update the symbolic links by using the following command:
$ ln -nfs /transaction/inst1.fep11 /database/inst1.fep11/pg_wal
Example: A-11 Updating the configuration file
$ vim /database/inst1/postgresql.conf
port = 27500
backup_destination = '/backup/inst1.fep11'
archive_command = '/opt/fsepv12server64/bin/pgx_walcopy.cmd "%p" "/backup/inst1.fep11/archived_wal/%f"'
Setting up FUJITSU Enterprise Postgres 12
In this section, we provide the steps to set up FUJITSU Enterprise Postgres 12:
1. Prepare the directories, as shown in Example A-12.
Example: A-12 Preparing directories
# mkdir -p /database/inst1
# chown -R fsepuser:fsepuser /database
# chmod 700 /database/inst1
# mkdir -p /transaction/inst1
# chown -R fsepuser:fsepuser /transaction
# chmod 700 /transaction/inst1
# mkdir -p /backup/inst1
# chown -R fsepuser:fsepuser /backup
# chmod 700 /backup/inst1
2. Configure the environment variables, as shown in Example A-13.
Example: A-13 Configuring environment variables
# su - fsepuser
$ PATH=/opt/fsepv12server64/bin:$PATH ; export PATH
$ MANPATH=/opt/fsepv12server64/share/man:$MANPATH ; export MANPATH
$ LD_LIBRARY_PATH=/opt/fsepv12server64/lib:$LD_LIBRARY_PATH ; export LD_LIBRARY_PATH
3. Create the database instance, as shown in Example A-14.
Example: A-14 Creating the database instance
$ initdb -D /database/inst1 --waldir=/transaction/inst1 --lc-collate="C" --lc-ctype="C" --encoding=UTF8
The files belonging to this database system will be owned by user "fsepuser".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: C
CTYPE: C
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC: en_US.UTF-8
TIME: en_US.UTF-8
The default text search configuration will be set to "english". (15541)
Data page checksums are disabled. (18153)
fixing permissions on existing directory /database/inst1 ... ok (15516)
fixing permissions on existing directory /transaction/inst1 ... ok (15516)
creating subdirectories ... ok (15516)
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... (19842)America/New_York
creating configuration files ... ok (15516)
running bootstrap script ... ok (15516)
performing post-bootstrap initialization ... ok (15516)
syncing data to disk ... ok (15516)
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /database/inst1 -l logfile start
Example: A-15 Modifying the configuration file
$ vim /database/inst1/postgresql.conf
listen_addresses = '*'
port = 27500
logging_collector = on
backup_destination = '/backup/inst1'
archive_mode = on
archive_command = '/opt/fsepv12server64/bin/pgx_walcopy.cmd "%p" "/backup/inst1/archived_wal/%f"'
5. Set up pg_hba.conf, which is the client authentication configuration file, for connectivity. Update the xx.xx.xx.0 with the IP address range of your environment. Also, use authentication that is more appropriate than the trust authentication that is used in Example A-16.
Example: A-16 Client authentication configuration edit
$ vim /database/inst1/pg_hba.conf
host all all xxx.xx.xx.0/24 trust
6. Start the database instance, as shown in Example A-17.
Example: A-17 Starting the database instance
$ pg_ctl start -D /database/inst1
waiting for server to start....2021-01-06 02:36:12.413 EST [25135] LOG: starting PostgreSQL 12.1 on s390x-ibm-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 64-bit
2021-01-06 02:36:12.413 EST [25135] LOG: listening on IPv4 address "0.0.0.0", port 27500
2021-01-06 02:36:12.413 EST [25135] LOG: listening on IPv6 address "::", port 27500
2021-01-06 02:36:12.414 EST [25135] LOG: listening on Unix socket "/tmp/.s.PGSQL.27500"
2021-01-06 02:36:12.421 EST [25135] LOG: redirecting log output to logging collector process
2021-01-06 02:36:12.421 EST [25135] HINT: Future log output will appear in directory "log".
done
server started
7. Verify the connectivity to the FUJITSU Enterprise Postgres 12 database instance, as shown in Example A-18.
Example: A-18 Connectivity verification
$ psql -h xxx.xx.xx.162 -p 27500 -d postgres -U fsepuser
psql (12.1)
Type "help" for help.
postgres=#
8. Stop the database instance, as shown in Example A-19.
Example: A-19 Stopping the database instance
$ pg_ctl stop -D /database/inst1
waiting for server to shut down.... done
server stopped
Performing the upgrade
In this section, we list the steps that are required to perform the upgrade by using pg_upgrade:
1. Both the instances of FUJITSU Enterprise Postgres 11 and 12 should now be in a shutdown state. Check the compatibility of the databases instances for an upgrade, as show in Example A-20.
Example: A-20 Checking compatibility
$ pg_upgrade --old-datadir "/database/inst1.fep11" --new-datadir "/database/inst1" --old-bindir "/opt/fsepv11server64/bin" --new-bindir "/opt/fsepv12server64/bin" --old-port=27500 --new-port=27500 --user=fsepuser --check
Performing Consistency Checks
-----------------------------
Checking cluster versions (19587)ok (19491)
Checking database user is the install user (19587)ok (19491)
Checking database connection settings (19587)ok (19491)
Checking for prepared transactions (19587)ok (19491)
Checking for reg* data types in user tables (19587)ok (19491)
Checking for contrib/isn with bigint-passing mismatch (19587)ok (19491)
Checking for tables WITH OIDS (19587)ok (19491)
Checking for invalid "sql_identifier" user columns (19587)ok (19491)
Checking for presence of required libraries (19587)ok (19491)
Checking database user is the install user (19587)ok (19491)
Checking for prepared transactions (19587)ok (19491)
*Clusters are compatible*
The two database instances of FUJITSU Enterprise Postgres 11 and 12 are compatible for an upgrade.
Example: A-21 Performing the upgrade
$ pg_upgrade --old-datadir "/database/inst1.fep11" --new-datadir "/database/inst1" --old-bindir "/opt/fsepv11server64/bin" --new-bindir "/opt/fsepv12server64/bin" --old-port=27500 --new-port=27500 --user=fsepuser
Performing Consistency Checks
-----------------------------
Checking cluster versions (19587)ok (19491)
Checking database user is the install user (19587)ok (19491)
Checking database connection settings (19587)ok (19491)
Checking for prepared transactions (19587)ok (19491)
Checking for reg* data types in user tables (19587)ok (19491)
Checking for contrib/isn with bigint-passing mismatch (19587)ok (19491)
Checking for tables WITH OIDS (19587)ok (19491)
Checking for invalid "sql_identifier" user columns (19587)ok (19491)
Creating dump of global objects (19587)ok (19491)
Creating dump of database schemas
(19587)ok (19491)
Checking for presence of required libraries (19587)ok (19491)
Checking database user is the install user (19587)ok (19491)
Checking for prepared transactions (19587)ok (19491)
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster (19587)ok (19491)
Freezing all rows in the new cluster (19587)ok (19491)
Deleting files from new pg_xact (19587)ok (19491)
Copying old pg_xact to new server (19587)ok (19491)
Setting next transaction ID and epoch for new cluster (19587)ok (19491)
Deleting files from new pg_multixact/offsets (19587)ok (19491)
Copying old pg_multixact/offsets to new server (19587)ok (19491)
Deleting files from new pg_multixact/members (19587)ok (19491)
Copying old pg_multixact/members to new server (19587)ok (19491)
Setting next multixact ID and offset for new cluster (19587)ok (19491)
Resetting WAL archives (19587)ok (19491)
Setting frozenxid and minmxid counters in new cluster (19587)ok (19491)
Restoring global objects in the new cluster (19587)ok (19491)
Restoring database schemas in the new cluster
(19587)ok (19491)
Copying user relation files (19567)
(19587)ok (19491)
Setting next OID for new cluster (19587)ok (19491)
Sync data directory to disk (19587)ok (19491)
Creating script to analyze new cluster (19587)ok (19491)
Creating script to delete old cluster (19587)ok (19491)
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
The upgrade is marked as complete.
3. Start the database instance, as shown in Example A-22.
Example: A-22 Starting the database
$ pg_ctl start -D /database/inst1
waiting for server to start....2021-01-06 02:52:15.941 EST [25844] LOG: starting PostgreSQL 12.1 on s390x-ibm-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 64-bit
2021-01-06 02:52:15.941 EST [25844] LOG: listening on IPv4 address "0.0.0.0", port 27500
2021-01-06 02:52:15.941 EST [25844] LOG: listening on IPv6 address "::", port 27500
2021-01-06 02:52:15.943 EST [25844] LOG: listening on Unix socket "/tmp/.s.PGSQL.27500"
2021-01-06 02:52:15.949 EST [25844] LOG: redirecting log output to logging collector process
2021-01-06 02:52:15.949 EST [25844] HINT: Future log output will appear in directory "log".
done
server started
4. Check that the pgbench tables that were in FUJITSU Enterprise Postgres 11 before the upgrade to ensure that they are now available in FUJITSU Enterprise Postgres 12 after the upgrade, as shown in Example A-23.
Example: A-23 Upgrading the verification
$ psql -h xxx.xx.xx.162 -p 27500 -d postgres -U fsepuser
psql (12.1)
Type "help" for help.
postgres=# d
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | fsepuser
public | pgbench_branches | table | fsepuser
public | pgbench_history | table | fsepuser
public | pgbench_tellers | table | fsepuser
(Four rows)
5. Re-create the pg_stat_statements extension that was dropped before the upgrade process (Example A-24).
Example: A-24 Re-creating the extension
$ psql -h xxx.xx.xx.162 -p 27500 -d postgres -U fsepuser -c "CREATE EXTENSION pg_stat_statements"
CREATE EXTENSION
6. Analyze the upgraded database instance by using the analyze_new_cluster.sh script to generate minimal statistics and then gather the statistics, as shown in Example A-25.
Example: A-25 Analyzing the instance
$ ./analyze_new_cluster.sh
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.
If you would like default statistics as quickly as possible, cancel
this script and run:
"/opt/fsepv12server64/bin/vacuumdb" -U fsepuser --all --analyze-only
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
Done
Dropping FUJITSU Enterprise Postgres 11
Example A-26 shows the commands that are used to clear the obsolete FUJITSU Enterprise Postgres 11 directories.
Example: A-26 Deleting directories
$ rm -rf /database/inst1.fep11/
$ rm -rf /transaction/inst1.fep11/
$ rm -rf /backup/inst1.fep11/
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.