~$ sudo su - postgres\n~# pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n11 main 5432 online postgres \/var\/lib\/postgresql\/11\/main \/var\/log\/postgresql\/postgresql-11-main.log\n13 main 5433 online postgres \/var\/lib\/postgresql\/13\/main \/var\/log\/postgresql\/postgresql-13-main.log<\/code><\/pre>\n\n\n\nAlso, lets double check each instance to make sure where our data truly resides.<\/p>\n\n\n\n
~$ sudo su - postgres\n~# psql --cluster 11\/main\npsql (13.15 (Debian 13.15-0+deb11u1), server 11.22 (Debian 11.22-0+deb10u2))\nType \"help\" for help.\n\npostgres=# \\l\n List of databases\n Name | Owner | Encoding | Collate | Ctype | Access privileges \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +\n | | | | | postgres=CTc\/postgres\n template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +\n | | | | | postgres=CTc\/postgres\n(3 rows)\n\npostgres=# \\q\n~# psql --cluster 13\/main\npsql (13.15 (Debian 13.15-0+deb11u1))\nType \"help\" for help.\n\npostgres=# \\l\n List of databases\n Name | Owner | Encoding | Collate | Ctype | Access privileges \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +\n | | | | | postgres=CTc\/postgres\n template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +\n | | | | | postgres=CTc\/postgres\n(3 rows)\n\npostgres=# \\q<\/code><\/pre>\n\n\n\nDrop the default databases found in the newer PostgreSQL versions.<\/p>\n\n\n\n
~$ sudo su - postgres\n~# pg_dropcluster --stop 13 main<\/code><\/pre>\n\n\n\nUpgrade 11 to 13<\/p>\n\n\n\n
# pg_upgradecluster 11 main<\/code><\/pre>\n\n\n\n<\/span>This will show a bunch of output about the process of upgrading the DB.<\/span><\/h2>\n\n\n\nWarning: the cluster will not be running as a systemd service. Consider using systemctl:\n sudo systemctl start postgresql@13-main\n\nSuccess. Please check that the upgraded cluster works. If it does,\nyou can remove the old cluster with\n pg_dropcluster 11 main\n\nVer Cluster Port Status Owner Data directory Log file\n11 main 5433 down postgres \/var\/lib\/postgresql\/11\/main \/var\/log\/postgresql\/postgresql-11-main.log\nVer Cluster Port Status Owner Data directory Log file\n13 main 5432 online postgres \/var\/lib\/postgresql\/13\/main \/var\/log\/postgresql\/postgresql-13-main.log<\/code><\/pre>\n\n\n\n<\/span>Lets see what is running now.<\/span><\/h2>\n\n\n\n$ pg_lsclusters \nVer Cluster Port Status Owner Data directory Log file\n11 main 5433 down postgres \/var\/lib\/postgresql\/11\/main \/var\/log\/postgresql\/postgresql-11-main.log\n13 main 5432 online postgres \/var\/lib\/postgresql\/13\/main \/var\/log\/postgresql\/postgresql-13-main.log<\/code><\/pre>\n\n\n\n<\/span>Remove all old versions of PostgreSQL.<\/span><\/h2>\n\n\n\n$ sudo su - postgres\n$ pg_dropcluster 11 main\n$ pg_lsclusters \nVer Cluster Port Status Owner Data directory Log file\n13 main 5432 online postgres \/var\/lib\/postgresql\/13\/main \/var\/log\/postgresql\/postgresql-13-main.log\n$ sudo apt remove postgresql-11 postgresql-client-11<\/code><\/pre>\n\n\n\nCurrently PostgreSQL is running as the postgres user, we need to stop the PostgreSQL daemon and start it with the normal system calls.<\/p>\n\n\n\n
~$ sudo su - postgres\n~$ pg_ctlcluster 13 main stop\n~$ \nlogout\n~$ sudo systemctl daemon-reload\n~$ sudo systemctl stop postgresql@11-main\n~$ sudo systemctl stop postgresql@13-main\n~$ sudo systemctl start postgresql@13-main\n~$ sudo pg_lsclusters \nVer Cluster Port Status Owner Data directory Log file\n13 main 5432 online postgres \/var\/lib\/postgresql\/13\/main \/var\/log\/postgresql\/postgresql-13-main.log\n<\/code><\/pre>\n\n\n\nYour upgrade is now complete!<\/p>\n","protected":false},"excerpt":{"rendered":"
The newer version of Debian 11 comes with PostgreSQL 13. I have the original PostgreSQL 11 running with all my data. Now I’m going to walk through the process of upgrading from PostgreSQL 11 to 13. Be aware this process will work when upgrading from PostgreSQL 9.6 to 11 or 13. Lets see what’s going on. Run pg_lsclusters to see […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-254","post","type-post","status-publish","format-standard","hentry","category-misc"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/posts\/254","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/comments?post=254"}],"version-history":[{"count":6,"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions"}],"predecessor-version":[{"id":264,"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions\/264"}],"wp:attachment":[{"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/media?parent=254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/categories?post=254"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cmsws.com\/blog\/wp-json\/wp\/v2\/tags?post=254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}