inchirags@gmail.com Chirag’s PostgreSQL DBA Tutorial https://www.chirags.in
*********************************************************************************************
Upgrade PostgreSQL 14 to PostgreSQL 16 Step by Step Process in Ubuntu 22.04 LTS
***********************************************************************************************
------Install PostgreSQL on Ubuntu 22.04--------
Step 1 — Installing PostgreSQL
*************************************
To install PostgreSQL, first refresh your server’s local package index:
$ sudo apt update
Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:
$ sudo apt install postgresql postgresql-contrib
Step 2 — Using PostgreSQL Roles and Databases
The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a few ways to utilize this account to access Postgres. One way is to switch over to the postgres account on your server by running the following command:
$ sudo -i -u postgres
Then you can access the Postgres prompt by running:
$ psql
To exit out of the PostgreSQL prompt, run the following:
$ \q
Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:
$ sudo -u postgres psql
# check version of postgresql
$ SELECT version();
Output:
----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
postgres=# CREATE DATABASE mydatabase;
postgres=# \c mydatabase
mydatabase=# CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);
mydatabase=# INSERT INTO users (name, age) VALUES ('Chirag', 30);
INSERT INTO users (name, age) VALUES ('Sanju', 25);
mydatabase=# SELECT * FROM users;
Output:
id | name | age
----+--------+-----
1 | Chirag | 30
2 | Sanju | 25
(2 rows)
mydatabase=# \q
Step 3 - To change the PostgreSQL user's password, follow these steps:
**********************************************************************
log in into the psql console:
root@dept:~# sudo -u postgres psql
Then in the psql console, change the password and quit:
postgres=# \password postgres
Enter new password: <new-password>
postgres=# \q
Or using a query:
ALTER USER postgres PASSWORD '<new-password>';
Or in one line
sudo -u postgres psql -c "ALTER USER postgres PASSWORD '<new-password>';"
Note:
If that does not work, reconfigure authentication by editing /etc/postgresql/<version>/main/pg_hba.conf (the path will differ) and change:
local all all peer # change this to md5
to
local all all md5 # like this
Then restart the server:
root@dept:~# sudo service postgresql restart
-----Now Upgrade PostgreSQL 14 to PostgreSQL 16 Effortlessly with pg_upgrade-------------
Step 1: Welcoming PostgreSQL 16
*******************************
Start by installing the shining star of our show, PostgreSQL 16. Let the magic unfold!
# Create the file repository configuration: Add the PostgreSQL 16 repository
root@dept:~# sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:
root@dept:~# curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
# Update the package lists:
root@dept:~# sudo apt-get update
# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-16' or similar instead of 'postgresql':
root@dept:~# sudo apt-get -y install postgresql-16
Step 2: Checking Your Database Clusters
*****************************************
Verify that your server now proudly hosts both PostgreSQL 14 and PostgreSQL 16 using the pg_lsclusters command.
root@dept:~# sudo pg_lsclusters
Output:
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
16 main 5433 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
Step 3: Preparing for the Upgrade
*********************************
Stop the PostgreSQL 16 cluster to prepare for the upgrade.
root@dept:~# sudo pg_dropcluster 16 main --stop
root@dept:~# sudo pg_lsclusters
Output:
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
Step 4: Initiating the Upgrade Process
***************************************
Start the upgrade process by executing the following command:
sudo pg_upgradecluster 14 main
Output:
...................
...................
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 14 main
Ver Cluster Port Status Owner Data directory Log file
14 main 5433 down postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
root@dept:~#
Monitor the process, and upon success, confidently remove the older version.
root@dept:~# sudo pg_dropcluster 14 main
Step 5: Final Touch — Removing the Old Package
**********************************************
Clean up the remnants of PostgreSQL 14 by purging the old package.
root@dept:~# sudo apt purge postgresql-14 postgresql-client-14
Step 6: Verification — Ensuring a Successful Upgrade
****************************************************
Double-check your current clusters to confirm the successful upgrade.
root@dept:~# sudo pg_lsclusters
Output:
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
Now you can login and test the previous database and tables in database.
$ sudo -u postgres psql
# check version of postgresql
$ SELECT version();
Output:
postgres=# \c mydatabase
mydatabase=# SELECT * FROM users;
Output:
id | name | age
----+--------+-----
1 | Chirag | 30
2 | Sanju | 25
(2 rows)
mydatabase=# \q
Note : Flow the Process shown in video.
Subscribe and like for more videos:
https://www.youtube.com/channel/UCLdaO4-i_gxQMi87JwjKOwA
Don't forget to, Follow, Like, Share &, Comment
Tutorial Link :
https://www.chirags.in/tutorials/PostgreSQL_Database
Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
____________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
