Upgrade PostgreSQL 14 to PostgreSQL 16 Step by Step Process in Ubuntu 22.04 LTS
Step 1 — Installing PostgreSQL
*********
To install PostgreSQL, first refresh your server’s local package index:
$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
Step 2 —
$ sudo -i -u postgres
Then you can access the Postgres prompt by running:
# check version of postgresql
$ SELECT version();
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;
mydatabase=# \q
Step 3 - To change the PostgreSQL user's password, follow these steps:
log in into the psql console:
root sudo -u postgres psql
postgres=# \password postgres
Enter new password: new-password
postgres=# \q
Or using a query:
ALTER USER postgres PASSWORD 'admin
nano /etc/postgresql/14/main/pg_hba.conf
local all all md5 # like this
Then restart the server:
root 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 sudo sh -c 'echo "deb $(lsb_release -cs)-pgdg main" /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:
root curl -fsSL | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
# Update the package lists:
root 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 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 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 sudo pg_dropcluster 16 main --stop
root 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
Monitor the process, and upon success, confidently remove the older version.
root sudo pg_dropcluster 14 main
Step 5: Final Touch — Removing the Old Package
**********************************************
root sudo apt purge postgresql-14 postgresql-client-14
Step 6: Verification — Ensuring a Successful Upgrade
****************************************************
root 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
$ 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)
Note : Flow the Process shown in video.
Subscribe and like for more videos:
Don't forget to, Follow, Like, Share &, Comment
Tutorial Link :
Watch video Upgrade PostgreSQL 14 to PostgreSQL 16 Step by Step Process in Ubuntu 22.04 LTS online without registration, duration 21 minute 46 second in high hd quality. This video was added by user Chirags Tutorial 03 September 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 44 once and liked it 2 people.