{"id":9701,"date":"2017-01-19T09:29:09","date_gmt":"2017-01-19T08:29:09","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/"},"modified":"2017-01-19T09:29:09","modified_gmt":"2017-01-19T08:29:09","slug":"from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/","title":{"rendered":"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime"},"content":{"rendered":"

As you might know PostgreSQL 9.1 is out of support<\/a> since last September. But, no surprise, there are still plenty of PostgreSQL 9.1 instances out there which need to be upgraded to a supported release. At one of our customers we exactly have this situation: The production environment is running on PostgreSQL 9.1.8 and needs to be upgraded to a recent release (9.5.5 in this case). To make life a little bit more fun there are more constraints: This is a PostgreSQL streaming replication configuration, so there is a standby database involved. Allowed downtime is 30 minutes and there is space pressure on the current systems and the current systems need to be reused. In this post we’ll look at how you can do the upgrade with minimal downtime (without using logical replication).<\/p>\n

<\/p>\n

First, lets build the test environment. We need two systems, one for the master instance (192.168.22.32) and one for the standby (192.168.22.32). On both of these system we’ll need PostgreSQL 9.1.8 installed, so:<\/p>\n

\npostgres@debian6pg:~$ wget https:\/\/ftp.postgresql.org\/pub\/source\/v9.1.8\/postgresql-9.1.8.tar.bz2\npostgres@debian6pg:~$ tar -axf postgresql-9.1.8.tar.bz2\npostgres@debian6pg:~$ cd postgresql-9.1.8\/\npostgres@debian6pg:~\/postgresql-9.1.8$ PGHOME=\/u01\/app\/postgres\/product\/91\/db_8\npostgres@debian6pg:~\/postgresql-9.1.8$ SEGSIZE=2\npostgres@debian6pg:~\/postgresql-9.1.8$ BLOCKSIZE=8\npostgres@debian6pg:~\/postgresql-9.1.8$ WALSEGSIZE=16\npostgres@debian6pg:~\/postgresql-9.1.8$ .\/configure --prefix=${PGHOME} \n                                                   --exec-prefix=${PGHOME} \n                                                   --bindir=${PGHOME}\/bin \n                                                   --libdir=${PGHOME}\/lib \n                                                   --sysconfdir=${PGHOME}\/etc \n                                                   --includedir=${PGHOME}\/include \n                                                   --datarootdir=${PGHOME}\/share \n                                                   --datadir=${PGHOME}\/share \n                                                   --with-pgport=5432 \n                                                   --with-perl \n                                                   --with-python \n                                                   --with-tcl \n                                                   --with-openssl \n                                                   --with-pam \n                                                   --with-ldap \n                                                   --with-libxml \n                                                   --with-libxslt \n                                                   --with-segsize=${SEGSIZE} \n                                                   --with-blocksize=${BLOCKSIZE} \n                                                   --with-wal-segsize=${WALSEGSIZE}\npostgres@debian6pg:~\/postgresql-9.1.8$ make world\npostgres@debian6pg:~\/postgresql-9.1.8$ make install\npostgres@debian6pg:~\/postgresql-9.1.8$ cd contrib\npostgres@debian6pg:~\/postgresql-9.1.8\/contrib$ make install\npostgres@debian6pg:~\/postgresql-9.1.8\/contrib$ cd ..\/..\npostgres@debian6pg:~$ rm -rf postgresql-9.1.8*\n<\/pre>\n
Once this is available on both nodes we can initialize our master instance:<\/p>\n
\npostgres@debian6pg:~$ \/u01\/app\/postgres\/product\/91\/db_8\/bin\/initdb -D \/u02\/pgdata\/testmig -X \/u03\/pgdata\/testmig\nThe files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale en_US.UTF-8.\nThe default database encoding has accordingly been set to UTF8.\nThe default text search configuration will be set to \"english\".\n\ncreating directory \/u02\/pgdata\/testmig ... ok\ncreating directory \/u03\/pgdata\/testmig ... ok\ncreating subdirectories ... ok\nselecting default max_connections ... 100\nselecting default shared_buffers ... 24MB\ncreating configuration files ... ok\ncreating template1 database in \/u02\/pgdata\/testmig\/base\/1 ... ok\ninitializing pg_authid ... ok\ninitializing dependencies ... ok\ncreating system views ... ok\nloading system objects' descriptions ... ok\ncreating collations ... ok\ncreating conversions ... ok\ncreating dictionaries ... ok\nsetting privileges on built-in objects ... ok\ncreating information schema ... ok\nloading PL\/pgSQL server-side language ... ok\nvacuuming database template1 ... ok\ncopying template1 to template0 ... ok\ncopying template1 to postgres ... ok\n\nWARNING: enabling \"trust\" authentication for local connections\nYou can change this by editing pg_hba.conf or using the -A option the\nnext time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    \/u01\/app\/postgres\/product\/91\/db_8\/bin\/postgres -D \/u02\/pgdata\/testmig\nor\n    \/u01\/app\/postgres\/product\/91\/db_8\/bin\/pg_ctl -D \/u02\/pgdata\/testmig -l logfile start\n<\/pre>\n
(I am assuming that password less ssh authentication is already setup between the nodes for the following). Setup authentication:<\/p>\n
\npostgres@debian6pg:\/u03$ echo \"host    replication     postgres       192.168.22.32\/32        trust\" >> \/u02\/pgdata\/testmig\/pg_hba.conf\npostgres@debian6pg:\/u03$ echo \"host    replication     postgres       192.168.22.33\/32        trust\" >> \/u02\/pgdata\/testmig\/pg_hba.conf\n<\/pre>\n
Adjust the parameters:<\/p>\n
\npostgres@debian6pg:\/u03$ sed -i 's\/#wal_level = minimal\/wal_level = hot_standby\/g' \/u02\/pgdata\/testmig\/postgresql.conf\npostgres@debian6pg:\/u03$ sed -i 's\/#max_wal_senders = 0\/max_wal_senders = 10\/g' \/u02\/pgdata\/testmig\/postgresql.conf\npostgres@debian6pg:\/u03$ sed -i 's\/#wal_keep_segments = 0\/wal_keep_segments = 100\/g' \/u02\/pgdata\/testmig\/postgresql.conf\npostgres@debian6pg:\/u03$ sed -i \"s\/#listen_addresses = 'localhost'\/listen_addresses = '*'\/g\" \/u02\/pgdata\/testmig\/postgresql.conf\npostgres@debian6pg:\/u03$ sed -i 's\/#logging_collector = off\/#logging_collector = on\/g' \/u02\/pgdata\/testmig\/postgresql.conf    \npostgres@debian6pg:\/u03$ mkdir \/u02\/pgdata\/testmig\/pg_log    \n<\/pre>\n
Start and stop the instance:<\/p>\n
\npostgres@debian6pg:\/u03$ export PATH=\/u01\/app\/postgres\/product\/91\/db_8\/bin\/:$PATH\npostgres@debian6pg:\/u03$ pg_ctl -D \/u02\/pgdata\/testmig\/ start -l \/u02\/pgdata\/testmig\/pg_log\/log.log\npostgres@debian6pg:\/u03$ pg_ctl -D \/u02\/pgdata\/testmig\/ stop\n<\/pre>\n
Ready to setup the standby:<\/p>\n
\npostgres@debian6pg:\/u03$ cd \/u02    \npostgres@debian6pg:\/u02$ rsync -r pgdata\/ 192.168.22.33:\/u02\/pgdata\npostgres@debian6pg:~$ cd \/u03\npostgres@debian6pg:\/u03$ rsync -r pgdata\/ 192.168.22.33:\/u03\/pgdata\npostgres@debian6pg:\/u03$ ssh 192.168.22.33 \"ln -s \/u03\/pgdata\/testmig\/ \/u02\/pgdata\/testmig\/pg_xlog\"\npostgres@debian6pg:\/u03$ scp \/u02\/pgdata\/testmig\/pg_hba.conf 192.168.22.33:\/u02\/pgdata\/testmig\/pg_hba.conf\npostgres@debian6pg:\/u03$ ssh 192.168.22.33 \"echo \"standby_mode = on\" >> \/u02\/pgdata\/testmig\/recovery.conf\"\npostgres@debian6pg:\/u03$ ssh 192.168.22.33 \"echo \"primary_conninfo = 'host=192.168.22.32 port=5432 user=postgres'\" >> \/u02\/pgdata\/testmig\/recovery.conf\"\npostgres@debian6pg:\/u03$ ssh 192.168.22.33 \"echo \"trigger_file = '\/u02\/pgdata\/testmig\/up_slave'\" >> \/u02\/pgdata\/testmig\/recovery.conf\"\npostgres@debian6pg:\/u03$ ssh 192.168.22.33 \"sed -i 's\/#hot_standby = off\/hot_standby = on\/g' \/u02\/pgdata\/testmig\/postgresql.conf\"\npostgres@debian6pg:\/u03$ ssh 192.168.22.33 \"sed -i 's\/#logging_collector = off\/#logging_collector = on\/g' \/u02\/pgdata\/testmig\/postgresql.conf\"\npostgres@debian6pg:\/u03$ ssh 192.168.22.33 \"mkdir -p \/u02\/pgdata\/testmig\/pg_log\"\n<\/pre>\n
Start the master:<\/p>\n
\npostgres@debian6pg:\/u03$ pg_ctl -D \/u02\/pgdata\/testmig\/ start -l \/u02\/pgdata\/testmig\/pg_log\/log.log\n<\/pre>\n
Start the standby:<\/p>\n
\n# standby side\npostgres@debian6pg:\/u03$ export PATH=\/u01\/app\/postgres\/product\/91\/db_8\/bin\/:$PATH\npostgres@debian6pg:\/u03$ pg_ctl -D \/u02\/pgdata\/testmig\/ start -l \/u02\/pgdata\/testmig\/pg_log\/log.log\n<\/pre>\n
… and if everything went fine we should see this in the log of the standby instance:
\n
\nLOG:  database system was shut down at 2017-01-18 07:28:02 CET
\nLOG:  entering standby mode
\nLOG:  consistent recovery state reached at 0\/16BCBB0
\nLOG:  database system is ready to accept read only connections
\nLOG:  record with zero length at 0\/16BCBB0
\nLOG:  streaming replication successfully connected to primary
\n<\/code><\/p>\n
A quick check on the standby to confirm that it is operating in recovery mode:<\/p>\n
\npostgres@debian6pg:~$ psql\npsql (9.1.8)\nType \"help\" for help.\n\npostgres=# select pg_is_in_recovery();\n pg_is_in_recovery \n-------------------\n t\n(1 row)\n<\/pre>\n
In the case we had at the customer there was the adminpack<\/a> extension installed in the postgres database and the pg_trgm<\/a> and pg_buffercache<\/a> extension in the application database, so lets do the same here on the master (this will get replicated to the standby automatically):<\/p>\n
\npostgres@debian6pg:\/u03$ psql\npsql (9.1.8)\nType \"help\" for help.\n\npostgres=# create extension adminpack;\nCREATE EXTENSION\npostgres=# create database testmig;\nCREATE DATABASE\npostgres=# c testmig\nYou are now connected to database \"testmig\" as user \"postgres\".\ntestmig=# create extension pg_trgm;\nCREATE EXTENSION\ntestmig=# create extension pg_buffercache;\nCREATE EXTENSION\ntestmig=# \n<\/pre>\n
Quickly confirm that it is there on the standby:<\/p>\n
\npostgres=# dx\n                        List of installed extensions\n   Name    | Version |   Schema   |               Description               \n-----------+---------+------------+-----------------------------------------\n adminpack | 1.0     | pg_catalog | administrative functions for PostgreSQL\n plpgsql   | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(2 rows)\n\npostgres=# c testmig\nYou are now connected to database \"testmig\" as user \"postgres\".\ntestmig=# dx\n                                       List of installed extensions\n      Name      | Version |   Schema   |                            Description                            \n----------------+---------+------------+-------------------------------------------------------------------\n pg_buffercache | 1.0     | public     | examine the shared buffer cache\n pg_trgm        | 1.0     | public     | text similarity measurement and index searching based on trigrams\n plpgsql        | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(3 rows)\n<\/pre>\n
Finally, some sample data generated with pgbench<\/a>:<\/p>\n
\npostgres@debian6pg:\/u03$ pgbench -i testmig -s 10\n<\/pre>\n
Should be there on the standby as well:<\/p>\n
\ntestmig=# select count(*) from pgbench_accounts;\n count  \n--------\n1000000\n(1 row)\ntestmig=# \n<\/pre>\n
This is, more or less, the situation to start from. How can we upgrade this to PostgreSQL 9.5.5 with minimal downtime and without using logical replication? Obviously we’ll need to get PostgreSQL 9.5.5 installed on both systems before we can do anything further, so:<\/p>\n
\npostgres@debian6pg:~$ wget https:\/\/ftp.postgresql.org\/pub\/source\/v9.5.5\/postgresql-9.5.5.tar.bz2\npostgres@debian6pg:~$ tar -axf postgresql-9.5.5.tar.bz2 \npostgres@debian6pg:~$ cd postgresql-9.5.5\/\npostgres@debian6pg:~\/postgresql-9.5.5$ PGHOME=\/u01\/app\/postgres\/product\/95\/db_5\npostgres@debian6pg:~\/postgresql-9.5.5$ PGHOME=\/u01\/app\/postgres\/product\/95\/db_5\npostgres@debian6pg:~\/postgresql-9.5.5$ SEGSIZE=2\npostgres@debian6pg:~\/postgresql-9.5.5$ BLOCKSIZE=8\npostgres@debian6pg:~\/postgresql-9.5.5$ WALSEGSIZE=16\npostgres@debian6pg:~\/postgresql-9.5.5$ .\/configure --prefix=${PGHOME} \n                                                   --exec-prefix=${PGHOME} \n                                                   --bindir=${PGHOME}\/bin \n                                                   --libdir=${PGHOME}\/lib \n                                                   --sysconfdir=${PGHOME}\/etc \n                                                   --includedir=${PGHOME}\/include \n                                                   --datarootdir=${PGHOME}\/share \n                                                   --datadir=${PGHOME}\/share \n                                                   --with-pgport=5432 \n                                                   --with-perl \n                                                   --with-python \n                                                   --with-tcl \n                                                   --with-openssl \n                                                   --with-pam \n                                                   --with-ldap \n                                                   --with-libxml \n                                                   --with-libxslt \n                                                   --with-segsize=${SEGSIZE} \n                                                   --with-blocksize=${BLOCKSIZE} \n                                                   --with-wal-segsize=${WALSEGSIZE}\npostgres@debian6pg:~\/postgresql-9.5.5$ make world\npostgres@debian6pg:~\/postgresql-9.5.5$ make install\npostgres@debian6pg:~\/postgresql-9.5.5$ cd contrib\npostgres@debian6pg:~\/postgresql-9.5.5\/contrib$ make install\npostgres@debian6pg:~\/postgresql-9.5.5\/contrib$ cd ..\/..\npostgres@debian6pg:~$ rm -rf postgresql-9.5.5*\n<\/pre>\n
Then we need a new cluster initialized with the new version of PostgreSQL on the master:<\/p>\n
\npostgres@debian6pg:~$ \/u01\/app\/postgres\/product\/95\/db_5\/bin\/initdb -D \/u02\/pgdata\/testmig95\/ -X \/u03\/pgdata\/testmig95\/ \nThe files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale \"en_US.UTF-8\".\nThe default database encoding has accordingly been set to \"UTF8\".\nThe default text search configuration will be set to \"english\".\n\nData page checksums are disabled.\n\ncreating directory \/u02\/pgdata\/testmig95 ... ok\ncreating directory \/u03\/pgdata\/testmig95 ... ok\ncreating subdirectories ... ok\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting dynamic shared memory implementation ... posix\ncreating configuration files ... ok\ncreating template1 database in \/u02\/pgdata\/testmig95\/base\/1 ... ok\ninitializing pg_authid ... ok\ninitializing dependencies ... ok\ncreating system views ... ok\nloading system objects' descriptions ... ok\ncreating collations ... ok\ncreating conversions ... ok\ncreating dictionaries ... ok\nsetting privileges on built-in objects ... ok\ncreating information schema ... ok\nloading PL\/pgSQL server-side language ... ok\nvacuuming database template1 ... ok\ncopying template1 to template0 ... ok\ncopying template1 to postgres ... ok\nsyncing data to disk ... ok\n\nWARNING: enabling \"trust\" authentication for local connections\nYou can change this by editing pg_hba.conf or using the option -A, or\n--auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    \/u01\/app\/postgres\/product\/95\/db_5\/bin\/pg_ctl -D \/u02\/pgdata\/testmig95\/ -l logfile start\n<\/pre>\n
Shutdown the master and record the latest checkpoint location (This is where your downtime starts):<\/p>\n
\npostgres@debian6pg:\/u02$ pg_ctl -D \/u02\/pgdata\/testmig stop -m fast\nLOG:  received fast shutdown request\nLOG:  aborting any active transactions\nLOG:  autovacuum launcher shutting down\nLOG:  shutting down\nwaiting for server to shut down....LOG:  database system is shut down\n done\nserver stopped\n\npostgres@debian6pg:\/u02\/pgdata\/testmig$ pg_controldata  | grep \"Latest checkpoint location\"\nLatest checkpoint location:           0\/C619840\n<\/pre>\n
Shutdown the slave and compare the last checkpoint:<\/p>\n
\n# slave side\npostgres@debian6pg:\/u02\/pgdata\/testmig$ pg_ctl -D \/u02\/pgdata\/testmig\/ stop -m fast\nwaiting for server to shut down.... done\nserver stopped\n\npostgres@debian6pg:\/u02\/pgdata\/testmig$ pg_controldata  | grep \"Latest checkpoint location\"\nLatest checkpoint location:           0\/C619840\n<\/pre>\n
As both checkpoint locations match we are sure that the standby applied all changes and there is not difference in data.<\/p>\n
Save your configuration files:<\/p>\n
\npostgres@debian6pg:\/u02$ cp \/u02\/pgdata\/testmig\/postgresql.conf \/var\/tmp \npostgres@debian6pg:\/u02$ cp \/u02\/pgdata\/testmig\/pg_hba.conf \/var\/tmp\npostgres@debian6pg:\/u02$ cp \/u02\/pgdata\/testmig\/postgresql.conf \/var\/tmp \npostgres@debian6pg:\/u02$ cp \/u02\/pgdata\/testmig\/pg_hba.conf \/var\/tmp\n<\/pre>\n
Run pg_upgrade<\/a> on the master with link (-k) mode (if you have many cores on your box you can use the “-j” option to parallelize pg_upgrade):<\/p>\n
\npostgres@debian6pg:\/u02$ export PGDATAOLD=\/u02\/pgdata\/testmig\/\npostgres@debian6pg:\/u02$ export PGDATANEW=\/u02\/pgdata\/testmig95\/\npostgres@debian6pg:\/u02$ export PGBINOLD=\/u01\/app\/postgres\/product\/91\/db_8\/bin\/\npostgres@debian6pg:\/u02$ export PGBINNEW=\/u01\/app\/postgres\/product\/95\/db_5\/bin\/\n\npostgres@debian6pg:\/u02$ \/u01\/app\/postgres\/product\/95\/db_5\/bin\/pg_upgrade -k \n<\/pre>\n
(Usually you’d do a “-c” check run before doing the real upgrade). When using link mode the files get hard-linked instead of copied which is much faster and saves disk space. The downside is that you can not revert to the old cluster in case anything goes wrong. When it goes fine, it looks like this:<\/p>\n
\nPerforming Consistency Checks\n-----------------------------\nChecking cluster versions                                   ok\nChecking database user is the install user                  ok\nChecking database connection settings                       ok\nChecking for prepared transactions                          ok\nChecking for reg* system OID user data types                ok\nChecking for contrib\/isn with bigint-passing mismatch       ok\nChecking for invalid \"line\" user columns                    ok\nCreating dump of global objects                             ok\nCreating dump of database schemas\n                                                            ok\nChecking for presence of required libraries                 ok\nChecking database user is the install user                  ok\nChecking for prepared transactions                          ok\n\nIf pg_upgrade fails after this point, you must re-initdb the\nnew cluster before continuing.\n\nPerforming Upgrade\n------------------\nAnalyzing all rows in the new cluster                       ok\nFreezing all rows on the new cluster                        ok\nDeleting files from new pg_clog                             ok\nCopying old pg_clog to new server                           ok\nSetting next transaction ID and epoch for new cluster       ok\nDeleting files from new pg_multixact\/offsets                ok\nSetting oldest multixact ID on new cluster                  ok\nResetting WAL archives                                      ok\nSetting frozenxid and minmxid counters in new cluster       ok\nRestoring global objects in the new cluster                 ok\nRestoring database schemas in the new cluster\n                                                            ok\nSetting minmxid counter in new cluster                      ok\nAdding \".old\" suffix to old global\/pg_control               ok\n\nIf you want to start the old cluster, you will need to remove\nthe \".old\" suffix from \/u02\/pgdata\/testmig\/global\/pg_control.old.\nBecause \"link\" mode was used, the old cluster cannot be safely\nstarted once the new cluster has been started.\n\nLinking user relation files\n                                                            ok\nSetting next OID for new cluster                            ok\nSync data directory to disk                                 ok\nCreating script to analyze new cluster                      ok\nCreating script to delete old cluster                       ok\n\nUpgrade Complete\n----------------\nOptimizer statistics are not transferred by pg_upgrade so,\nonce you start the new server, consider running:\n    .\/analyze_new_cluster.sh\n\nRunning this script will delete the old cluster's data files:\n    .\/delete_old_cluster.sh\n<\/code><\/pre>\n
Restore the configuration files:<\/p>\n
\npostgres@debian6pg:\/u02$ mkdir -p \/u02\/pgdata\/testmig95\/pg_log\npostgres@debian6pg:\/u02$ cp \/var\/tmp\/postgresql.conf \/u02\/pgdata\/testmig95\/postgresql.conf  \npostgres@debian6pg:\/u02$ cp \/var\/tmp\/pg_hba.conf \/u02\/pgdata\/testmig95\/pg_hba.conf \n<\/pre>\n
Start and stop the upgraded instance and check that everything is fine in the log file:<\/p>\n
\npostgres@debian6pg:\/u02$ \/u01\/app\/postgres\/product\/95\/db_5\/bin\/pg_ctl -D \/u02\/pgdata\/testmig95\/ -l \/u02\/pgdata\/testmig95\/pg_log\/log.log start    \npostgres@debian6pg:\/u02$ \/u01\/app\/postgres\/product\/95\/db_5\/bin\/pg_ctl -D \/u02\/pgdata\/testmig95\/ stop   \n<\/pre>\n
You could already keep your cluster running now and your downtime is completed when you plan to re-build the standby. When you want to do the standby now then: save the configuration files:<\/p>\n
\n# standby side\npostgres@debian6pg:\/u03$ cp \/u02\/pgdata\/testmig\/postgresql.conf \/var\/tmp\npostgres@debian6pg:\/u03$ cp \/u02\/pgdata\/testmig\/pg_hba.conf \/var\/tmp\npostgres@debian6pg:\/u03$ cp \/u02\/pgdata\/testmig\/recovery.conf \/var\/tmp\n<\/pre>\n
Sync the directories from the master to the standby (this will be very fast because it will create hard links on the standby server instead of copying the user files):<\/p>\n
\npostgres@debian6pg:\/u03$ cd \/u02\/pgdata   \npostgres@debian6pg:\/u02$ rsync --archive --delete --hard-links --size-only testmig testmig95 192.168.22.33:\/u02\/pgdata\npostgres@debian6pg:\/u02$ cd \/u03\npostgres@debian6pg:\/u03$ rsync -r pgdata\/testmig95 192.168.22.33:\/u03\/pgdata\/testmig95\n<\/pre>\n
Restore the configuration files on the standby:<\/p>\n
\npostgres@debian6pg:\/u03$ cp \/var\/tmp\/postgresql.conf \/u02\/pgdata\/testmig95\/postgresql.conf \npostgres@debian6pg:\/u03$ cp \/var\/tmp\/pg_hba.conf \/u02\/pgdata\/testmig95\/pg_hba.conf\npostgres@debian6pg:\/u03$ cp \/var\/tmp\/recovery.conf \/u02\/pgdata\/testmig95\/recovery.conf\n<\/pre>\n
Start the master:<\/p>\n
\npostgres@debian6pg:\/u03$ export PATH=\/u01\/app\/postgres\/product\/95\/db_5\/bin:$PATH\npostgres@debian6pg:\/u03$ pg_ctl -D \/u02\/pgdata\/testmig95\/ start -l \/u02\/pgdata\/testmig95\/pg_log\/log.log\n<\/pre>\n
Start the standby:<\/p>\n
\npostgres@debian6pg:\/u03$ export PATH=\/u01\/app\/postgres\/product\/95\/db_5\/bin:$PATH\npostgres@debian6pg:\/u03$ pg_ctl -D \/u02\/pgdata\/testmig95\/ start -l \/u02\/pgdata\/testmig95\/pg_log\/log.log\n<\/pre>\n
Check the standby’s logfile:<\/p>\n
\nLOG:  database system was shut down at 2017-01-19 07:51:24 GMT\nLOG:  creating missing WAL directory \"pg_xlog\/archive_status\"\nLOG:  entering standby mode\nLOG:  started streaming WAL from primary at 0\/E000000 on timeline 1\nLOG:  consistent recovery state reached at 0\/E024D38\nLOG:  redo starts at 0\/E024D38\nLOG:  database system is ready to accept read only connections\n<\/code><\/pre>\n
Do some checks to see that everything is there on the standby:<\/p>\n
\npostgres@debian6pg:~$ psql\npsql (9.5.5)\nType \"help\" for help.\n\npostgres=# select pg_is_in_recovery();\n pg_is_in_recovery \n-------------------\n t\n(1 row)\n\npostgres=# dx\n                        List of installed extensions\n   Name    | Version |   Schema   |               Description               \n-----------+---------+------------+-----------------------------------------\n adminpack | 1.0     | pg_catalog | administrative functions for PostgreSQL\n plpgsql   | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(2 rows)\n\npostgres=# c testmig\nYou are now connected to database \"testmig\" as user \"postgres\".\ntestmig=# dx\n                                       List of installed extensions\n      Name      | Version |   Schema   |                            Description                            \n----------------+---------+------------+-------------------------------------------------------------------\n pg_buffercache | 1.0     | public     | examine the shared buffer cache\n pg_trgm        | 1.0     | public     | text similarity measurement and index searching based on trigrams\n plpgsql        | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(3 rows)\n\ntestmig=# d\n              List of relations\n Schema |       Name       | Type  |  Owner   \n--------+------------------+-------+----------\n public | pg_buffercache   | view  | postgres\n public | pgbench_accounts | table | postgres\n public | pgbench_branches | table | postgres\n public | pgbench_history  | table | postgres\n public | pgbench_tellers  | table | postgres\n(5 rows)\n\ntestmig=# select count(*) from pgbench_accounts;\n  count  \n---------\n 1000000\n(1 row)\n<\/pre>\n
Run the analyze_new_cluster.sh on the master:<\/p>\n
\npostgres@debian6pg:~$ .\/analyze_new_cluster.sh\nThis script will generate minimal optimizer statistics rapidly\nso your system is usable, and then gather statistics twice more\nwith increasing accuracy.  When it is done, your system will\nhave the default level of optimizer statistics.\n\nIf you have used ALTER TABLE to modify the statistics target for\nany tables, you might want to remove them and restore them after\nrunning this script because they will delay fast statistics generation.\n\nIf you would like default statistics as quickly as possible, cancel\nthis script and run:\n    \"\/u01\/app\/postgres\/product\/95\/db_5\/bin\/vacuumdb\" --all --analyze-only\n\nvacuumdb: processing database \"postgres\": Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database \"template1\": Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database \"testmig\": Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database \"postgres\": Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database \"template1\": Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database \"testmig\": Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database \"postgres\": Generating default (full) optimizer statistics\nvacuumdb: processing database \"template1\": Generating default (full) optimizer statistics\nvacuumdb: processing database \"testmig\": Generating default (full) optimizer statistics\n<\/pre>\n
Now you can delete the old cluster on the master:<\/p>\n
\npostgres@debian6pg:~$ postgres@debian6pg:~$ .\/delete_old_cluster.sh\n<\/pre>\n
Then either copy the script to the standby or delete the old standby the manual way:<\/p>\n
\npostgres@debian6pg:~$ rm -rf \/u02\/pgdata\/testmig\npostgres@debian6pg:~$ rm -rf \/u03\/pgdata\/testmig\n<\/pre>\n
Done. Hope this helps …<\/p>\n","protected":false},"excerpt":{"rendered":"
As you might know PostgreSQL 9.1 is out of support since last September. But, no surprise, there are still plenty of PostgreSQL 9.1 instances out there which need to be upgraded to a supported release. At one of our customers we exactly have this situation: The production environment is running on PostgreSQL 9.1.8 and needs […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-9701","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nFrom PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime\" \/>\n<meta property=\"og:description\" content=\"As you might know PostgreSQL 9.1 is out of support since last September. But, no surprise, there are still plenty of PostgreSQL 9.1 instances out there which need to be upgraded to a supported release. At one of our customers we exactly have this situation: The production environment is running on PostgreSQL 9.1.8 and needs […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-01-19T08:29:09+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime\",\"datePublished\":\"2017-01-19T08:29:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/\"},\"wordCount\":668,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/\",\"name\":\"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-01-19T08:29:09+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/","og_locale":"en_US","og_type":"article","og_title":"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime","og_description":"As you might know PostgreSQL 9.1 is out of support since last September. But, no surprise, there are still plenty of PostgreSQL 9.1 instances out there which need to be upgraded to a supported release. At one of our customers we exactly have this situation: The production environment is running on PostgreSQL 9.1.8 and needs […]","og_url":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/","og_site_name":"dbi Blog","article_published_time":"2017-01-19T08:29:09+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime","datePublished":"2017-01-19T08:29:09+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/"},"wordCount":668,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/","url":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/","name":"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-01-19T08:29:09+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/from-postgresql-9-1-8-to-postgresql-9-5-5-including-a-standby-database-with-minimal-downtime\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"From PostgreSQL 9.1.8 to PostgreSQL 9.5.5 including a standby database with minimal downtime"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9701","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9701"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9701\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9701"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}