...
For more information, see Install Databases for MySQL.
Database
...
Prep
This procedure describes the process for upgrading the PostgreSQL version in use by the
D s item | ||
---|---|---|
|
- Source version: PostgreSQL 9.3
- Upgrade version: PostgreSQL 9.6
- All current databases are co-located with the software on the
.D s node - Some downtime of the databases during the upgrade process is ok.
...
For more information, see https://www.postgresql.org/docs/9.6/pgupgrade.html.
Upgrade PostgreSQL 9.6 to 12.3
The following sections cover upgrading the versions listed below:
- Source version: PostgreSQL 9.6
- Upgrade version: PostgreSQL 12.3
Beginning in Release 7.1 Hot Fix 5, PostgreSQL 12.3 is supported on a limited basis. Important notes:
Info |
---|
NOTE: Upgrade to PostgreSQL 12.3 is supported in this release for CentOS 7 only. |
Info | |
---|---|
NOTE: Upgrade to PostgreSQL 12.3 is supported in Release 7.1 Hot Fix 5 or later only. For more information on acquiring this build, please contact
|
Info |
---|
NOTE: The database version upgrade must be performed after you have upgraded the software. |
PostgreSQL path references
In PostgreSQL 10 and later, directory references to versions of PostgreSQL paths have changed.
PostgreSQL 9.6 example path:
Code Block |
---|
/var/lib/pgsql/9.6/data/pg_hba.conf |
PostgreSQL 12.3 example path:
Code Block |
---|
/var/lib/pgsql/12/data/pg_hba.conf |
For more information, see https://wiki.postgresql.org/wiki/YUM_Installation.
Upgrade for CentOS/RHEL - PostgreSQL 9.6 to 12.3
Info |
---|
NOTE: For this release, PostgreSQL 12.3 is supported for supported versions of CentOS 7 only. See Product Support Matrix. |
Install the Postgres repo for yum. Below, PostgreSQL 12.3 is installed.
For CentOS 7.x:
Code Block language bash wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum -y install pgdg-redhat-repo-latest.noarch.rpm
For CentOS 8.x: Not supported
For Red Hat Enterprise Linux 7.x:
Code Block language bash wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum -y install pgdg-redhat-repo-latest.noarch.rpm
For Red Hat Enterprise Linux 8.x: Not supported
Install the upgraded version. Below, PostgreSQL 12.3 is installed.
Code Block language bash sudo yum install postgresql12-server
Create a new data directory for the new database version:
For CentOS 7.x:
Code Block language bash sudo /usr/pgsql-12/bin/postgresql-12-setup initdb sudo systemctl enable postgresql-12
For RHEL 7.x:
Code Block language bash sudo /usr/pgsql-12/bin/postgresql-12-setup initdb sudo systemctl enable postgresql-12
Use the following commands to verify that the upgrade is possible:
Code Block language bash su postgres cd ~ /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-9.6/bin --new-bindir=/usr/pgsql-12/bin --old-datadir=/var/lib/pgsql/9.6/data/ --new-datadir=/var/lib/pgsql/12/data/ --check
Info NOTE: Please be sure to include the
--check
command line switch at the end of the above command.
If all is well, you should see aClusters are compatible
message.- Stop the old version of the database service as the root user:
CentOS/RHEL:
Code Block language bash sudo service postgresql-9.6 stop
- Run the upgrade as the
postgres
user:CentOS/RHEL:
Code Block language bash su postgres /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-9.6/bin --new-bindir=/usr/pgsql-12/bin --old-datadir=/var/lib/pgsql/9.6/data/ --new-datadir=/var/lib/pgsql/12/data/
Part of the output should include the following. These hashes will be re-indexed in a later step.
Code Block Your installation contains hash indexes. These indexes have different internal formats between your old and new clusters, so they must be reindexed with the REINDEX command. The file reindex_hash.sql when executed by psql by the database superuser will recreate all invalid indexes; until then, none of these indexes will be used. 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
Copy in
pg_hba.conf
:Copy the default version from the upgraded software:
Code Block language bash cp /opt/trifacta/bin/setup-utils/db/pg_hba.conf.SAMPLE /var/lib/pgsql/12/data/pg_hba.conf
Tip Tip: You may find it easier to using a diff tool to compare your current version of this file (
/var/lib/pgsql/9.6/data/pg_hba.conf
) to the new file (/var/lib/psql/12/data/pg_hba.conf
). If there are only a few updates, then you may find it easier to copy your current version into the target directory and then edit it to apply the differences.If you have made modifications to this configuration file:
Open this file:
Code Block /var/lib/pgsql/9.6/data/pg_hba.conf
Apply custom values to this file:
Code Block /var/lib/pgsql/12/data/pg_hba.conf
Start new PostgreSQL:
Code Block language bash systemctl start postgresql-12
Use the following command to reindex the hashes:
Code Block language bash psql -f reindex_hash.sql
Verify that PostgreSQL is up and running:
Code Block language bash systemctl status postgresql-12.service
Upgrade PostgreSQL 9.3 to 9.6
The following sections cover upgrading the versions listed below:
- Source version: PostgreSQL 9.3
- Upgrade version: PostgreSQL 9.6
Upgrade for CentOS/RHEL - PostgreSQL 9.3 to 9.6
- Install the Postgres repo for yum. Below, PostgreSQL 9.6 is installed.
For CentOS 7.x:
Code Block wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm sudo yum -y install pgdg-centos96-9.6-3.noarch.rpm
For CentOS 8.x:
Code Block wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-8-x86_64/pgdg-centos96-9.6-3.noarch.rpm sudo yum -y install pgdg-centos96-9.6-3.noarch.rpm
For Red Hat Enterprise Linux 7.x:
Code Block wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm sudo yum -y install pgdg-redhat96-9.6-3.noarch.rpm
For Red Hat Enterprise Linux 8.x:
Code Block wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-8-x86_64/pgdg-centos96-9.6-3.noarch.rpm sudo yum -y install pgdg-redhat96-9.6-3.noarch.rpm
Install the upgraded version. Below, PostgreSQL 9.6 is installed.
Code Block sudo yum install postgresql96.x86_64 postgresql96-server.x86_64 postgresql96-contrib.x86_64 -y
Create a new data directory for the new database version:
For CentOS 7.x, CentOS 8.x:
Code Block sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
For RHEL 7.x, RHEL 8.x:
Code Block sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
Use the following commands to verify that the upgrade is possible:
Code Block su postgres cd ~ /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/9.6/data/ --check
If all is well, you should see a
Clusters are compatible
message message.- Stop the old version of the database service as the root user:
CentOS/RHEL:
Code Block sudo service postgresql-9.3 stop
- Run the upgrade as the
postgres
user user:CentOS/RHEL:
Code Block su postgres /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/9.6/data/
Excerpt | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Upgrade for Ubuntu 16.04 (Xenial) - PostgreSQL 9.3 to 9.6Steps:
Upgrade for Ubuntu 18.04 (Bionic Beaver) - PostgreSQL 9.3 to 9.6PostgreSQL should not be on version 9.3 if you are using this version of the operating system. |
...
- Update the port number and any other settings in the platform configuration.
D s config method triconf - Replace all instances of the PostgreSQL port number for the earlier version and replace with the desired port number for the upgrade version.
D s pssql port - Make the above changes and save the file.
- Replace all instances of the PostgreSQL port number for the earlier version and replace with the desired port number for the upgrade version.
Transfer settings from the configuration files for your old database version to the new one. Please review the old and new versions of these files:
Info NOTE: It is risky to perform a straight copy of these configuration files. Settings may change. New ones may be introduced. Setting values specific to to the installation may be overwritten in a copy. Please retain a backup of both versions of each file before migrating settings.
Path to PostgreSQL 9.3 file Path to PostgreSQL 9.6 file /var/lib/pgsql/9.3/data/pg_hba.conf
/var/lib/pgsql/9.6/data/pg_hba.conf
/var/lib/pgsql/9.3/data/postgresql.conf
/var/lib/pgsql/9.6/data/postgresql.conf
- Start the service:
CentOS/RHEL:
Code Block sudo service postgresql-9.6 start
Ubuntu:
Code Block sudo service postgresql start
When the service restarts, you can check the cluster status using the following script:
Code Block ./analyze_new_cluster.sh
After you have completed the database installation, you must review the port number of the newly installed database, which may have changed between versions of the database software. That new port number must be applied through the
. For more information, see Change Database Port.D s item item software If all is well, restart the platform. See Start and Stop the Platform in the Install Guide.
...