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 :
Смотрите видео Upgrade PostgreSQL 14 to PostgreSQL 16 Step by Step Process in Ubuntu 22.04 LTS онлайн без регистрации, длительностью 21 минут 46 секунд в хорошем hd качестве. Это видео добавил пользователь Chirags Tutorial 03 Сентябрь 2024, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 4 раз и оно понравилось людям.