inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in
*********************************************************************************************
Upgrade PostgreSQL 16 to PostgreSQL 17 Effortlessly with pg_upgrade in Ubuntu 24.04 LTS
*********************************************************************************************
YouTube Video Link:
PostgreSQL server:
Server IP: 192.168.224.134
------Install PostgreSQL 16 on Ubuntu 24.04--------
Step 1 — Installing PostgreSQL
*************************************
To install PostgreSQL, first refresh your server’s local package index:
$ sudo apt update && sudo apt upgrade -y
Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:
$ sudo apt install postgresql postgresql-contrib -y
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 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.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>';
ALTER USER postgres PASSWORD 'admin@123';
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 16 to PostgreSQL 17 Effortlessly with pg_upgrade-------------
Step 1: Welcoming PostgreSQL 17
*******************************
Start by installing the shining star of our show, PostgreSQL 17. Let the magic unfold!
1.1 Add the PostgreSQL APT Repository
To get PostgreSQL 17, we need to add the official PostgreSQL APT repository.
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
Next, add the PostgreSQL repository:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Step 2: Install PostgreSQL 17
After adding the repository, update the package list and install PostgreSQL 17.
sudo apt update
sudo apt -y install postgresql postgresql-client postgresql-contrib
Step 3: Basic PostgreSQL Configuration
3.1 Switch to PostgreSQL User
Switch to the default PostgreSQL user and access the PostgreSQL shell.
sudo -i -u postgres
psql
3.2 Change Password for the postgres User
It’s important to set a password for the postgres superuser account.
ALTER USER postgres PASSWORD 'admin@123';
Exit the psql shell:
\q
exit
Step 4: 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
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
17 main 5433 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
Step 5: Preparing for the Upgrade
*********************************
Stop the PostgreSQL 17 cluster to prepare for the upgrade.
root@dept:~# sudo pg_dropcluster 17 main --stop
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
Step 6: Initiating the Upgrade Process
***************************************
Start the upgrade process by executing the following command:
sudo pg_upgradecluster 16 main
Output:
...................
...................
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 16 main
Ver Cluster Port Status Owner Data directory Log file
16 main 5433 down postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-16-main.log
Ver Cluster Port Status Owner Data directory Log file
17 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-17-main.log
root@dept:~#
Monitor the process, and upon success, confidently remove the older version.
root@dept:~# sudo pg_dropcluster 16 main
Step 7: Final Touch — Removing the Old Package
**********************************************
Clean up the remnants of PostgreSQL 16 by purging the old package.
root@dept:~# sudo apt purge postgresql-16 postgresql-client-16
Step 8: 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
17 main 5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-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:
--------------------------------------------------------------
PostgreSQL 17.0 (Ubuntu 17.0-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
(1 row)
postgres=# \c mydatabase
mydatabase=# SELECT * FROM users;
Output:
id | name | age
----+--------+-----
1 | Chirag | 30
2 | Sanju | 25
(2 rows)
mydatabase=# \q
For any doubts and query, please write on YouTube video comments section.
Note : Flow the Process shown in video.
Subscribe and like for more videos:
https://www.youtube.com/@chiragstutorial
Don't forget to, Follow, Like, Share &, Comment
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.
