In the
process of upgrading, we will perform the migration of PostgreSQL 9.6 database and
configuration information to PostgreSQL 10. Migrating is especially useful
because it allows you to reuse configured information from the earlier version
and saves time in getting started with the new version.
Basically, there will be 2
types of upgradation.
1. Downtime
upgradation
- Using pg_upgrade
- pg_dumpall
2. Without
downtime
- logical replication and pg_dumpall
I have mentioned the steps
which I have performed using pg_upgrade during the process.
Steps :
- Pre-requisite
1. Have a full Backup of Database
2. Installation of the new version
3. Initialize the directory
4. Validation (Compatibility of upgrade)
- Actual Upgrade
- Post tasks
- analyze_new_cluster
- delete_old_cluster
1. Perform a Full Backup
-bash-4.1 $ pg_basebackup -p 5432 -U postgres -D /var/lib/pgsql/Base_backup -Fp -Xs -P 232737/232737 kB (100%), 1/1 tablespace Install postgresql12 with packages as follows. -bash-4.1 $
As a
root user, stop the PostgreSQL 9.x instance.
For
example: systemctl stop postgresql-9.6
2. Perform the installation
[root@oracleasm1 postgresql-10.0]# ls -ltr total 672 -rw-r--r-- 1 1107 1107 1212 Oct 3 2017 README -rw-r--r-- 1 1107 1107 1529 Oct 3 2017 Makefile -rw-r--r-- 1 1107 1107 284 Oct 3 2017 HISTORY -rw-r--r-- 1 1107 1107 3638 Oct 3 2017 GNUmakefile.in -rw-r--r-- 1 1107 1107 1192 Oct 3 2017 COPYRIGHT -rw-r--r-- 1 1107 1107 76410 Oct 3 2017 configure.in -rwxr-xr-x 1 1107 1107 495611 Oct 3 2017 configure -rw-r--r-- 1 1107 1107 457 Oct 3 2017 aclocal.m4 drwxrwxrwx 55 1107 1107 4096 Oct 3 2017 contrib drwxrwxrwx 2 1107 1107 4096 Oct 3 2017 config drwxrwxrwx 3 1107 1107 4096 Oct 3 2017 doc -rw-r--r-- 1 1107 1107 71584 Oct 3 2017 INSTALL drwxrwxrwx 16 1107 1107 4096 Oct 3 2017 src [root@oracleasm1 postgresql-10.0]# ./configure [root@oracleasm1 postgresql-10.0]# make [root@oracleasm1 postgresql-10.0]# make Install
We have completed the
installation. Let’s switch to the Postgres user and perform the initdb
operation by specifying the data and log files as follows. If not specified, the default path will be set.
3. Initialize the directory as below
/usr/local/pgsql/bin/initdb
-D /var/lib/pgsql/pg10/data -X /var/lib/pgsql/pg10/log
We specify the path where the data files will be created with the -D parameter, and we specify the path where the log files will be created with the -X parameter.
Or you can initialize using the service as below, make sure to update the variable inside the service.
[root@oracleasm1 init.d]# ./postgresql-10 initdb Initializing database: [ OK ]
We see that the initdb has
performed successfully.
We have done Postgresql10
installation and initialized the data, we can stop the service and start the
upgrade process.
4. Perform the Compatibility-check to validate using -- check argument
As a Postgres user, perform the upgrade using the pg_upgrade command.
I have configured the pg_upgrade script
as follows to perform the upgrade test.
/usr/local/pgsql/bin/pg_upgrade \ --old-datadir=/var/lib/pgsql/9.6/data \ --new-datadir=/var/lib/pgsql/pg10/data \ --old-bindir=/usr/pgsql-9.6/bin \ --new-bindir=/usr/local/pgsql/bin \ --old-options '-c config_file=/var/lib/pgsql/9.6/data/postgresql.conf' \ --new-options '-c config_file=/var/lib/pgsql/pg10/data/postgresql.conf' \ --check Performing Consistency Checks on Old Live Server ------------------------------------------------ Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for invalid "unknown" user columns ok Checking for hash indexes ok 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: loadable_libraries.txt -bash-4.1 $ cat loadable_libraries.txt could not load library "$libdir/postgres_fdw": ERROR: could not access file "$libdir/postgres_fdw": No such file or directory could not load library "$libdir/pg_buffercache": ERROR: could not access file "$libdir/pg_buffercache": No such file or directory could not load library "$libdir/pg_stat_statements": ERROR: could not access file "$libdir/pg_stat_statements": No such file or directory
Resolution: I have installed those 3 extensions manually from the contrib directory to resolve the above conflicts.
Issue 2 : FATAL: lock file "postmaster.pid" already exists
*failure* Consult the last few lines of "pg_upgrade_server.log" for the probable cause of the failure.
-bash-4.1$ cat pg_upgrade_server.log ----------------------------------------------------------------- pg_upgrade run on Sun Sep 13 16:04:31 2020 ----------------------------------------------------------------- command: "/usr/pgsql-9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.6/data" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c config_file=/var/lib/pgsql/9.6/data/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1 pg_ctl: another server might be running; trying to start server anyway waiting for server to start....user=,db=,32739 FATAL: lock file "postmaster.pid" already exists user=,db=,32739HINT: Is another postmaster (PID 22501) running in data directory "/var/lib/pgsql/9.6/data"? stopped waiting
pg_ctl: could not start server Examine the log output.
Resolution: As mentioned before, we have to stop the existing processes to perform the upgrade, else the upgrade checks will fail with the above errors.
-bash-4.1$ /usr/pgsql-9.6/bin/pg_ctl -D $PGDATA stop waiting for server to shut down..... done server stopped
Actual Upgrade
-bash-4.1$ /usr/local/pgsql/bin/pg_upgrade \ --old-datadir=/var/lib/pgsql/9.6/data \ --new-datadir=/var/lib/pgsql/pg10/data \ --old-bindir=/usr/pgsql-9.6/bin \ --new-bindir=/usr/local/pgsql/bin \ --old-options '-c config_file=/var/lib/pgsql/9.6/data/postgresql.conf' \ --new-options '-c config_file=/var/lib/pgsql/pg10/data/postgresql.conf' \
Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for invalid "unknown" user columns ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok 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 ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_clog to new server ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster ok Copying user relation files ok Setting next OID for new cluster ok Sync data directory to disk ok Creating script to analyze new cluster ok Creating script to delete old cluster ok Checking for hash indexes ok 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 -bash-4.1$
Finally, if you want to delete postgresql9.6 directory,
postgresql prepares sh scripts for you as part of upgrade.
Lets execute them.
-bash-4.1$ ./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: "/usr/local/pgsql/bin/vacuumdb" --all --analyze-only vacuumdb: processing database "newdb_dev": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "nexaprd": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "nikhil": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "test_perf": Generating minimal optimizer statistics (1 target) Done
As can be seen from above, the upgrade has been successfully completed.
General Issues encountered
:
Upgrade has been successfully completed. We need to enable the service, but first, we need to change the value of “Environment = PGDATA” parameter in the postgresql-10 service as our own data directory.
[root@oracleasm1 init.d]# service postgresql-10 start An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See (Your System's documentation directory)/postgresql-9.6.15/README.rpm-dist for more information. [root@oracleasm1 init.d]# /usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/pg10/data start [root@oracleasm1 init.d]# service postgresql-10 upgrade
Found this post interesting? Subscribe us 😊😉
Read more
Configure Streaming Replication in PostgreSQL
All about Physical Replication and Log shipping in Postgres
Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping
“Do all the good you can, by all the means you can, in all the
ways you can, in all the places you can, at all the times you can, to all the
people you can, as long as ever you can.” -John Wesley
No comments:
Post a Comment