1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

### START ###
### login server as minion

# import repository key
sudo apt install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null

# check pgdg.list
echo "deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg-snapshot main 16" |sudo tee -a /etc/apt/sources.list.d/pgdg.list

# install psql-16
sudo apt update
sudo apt install postgresql-16 -y

# make new database folder
sudo mkdir /database_16
sudo chown -R postgres:postgres /database_16

### cp psql configurations

# stop psql
sudo service postgresql stop

### remove old systemd files and create a new one
sudo systemctl disable postgresql@15
sudo systemctl stop postgresql@15
sudo systemctl enable postgresql@16-main
sudo systemctl start postgresql@16-main

### sudo to postgres

sudo su - postgres

# update /usr/lib/postgresql/15/bin to 16
sed -i 's/15\/bin/16\/bin/g' ~/.profile
# apply .profile
source ~/.profile

#check if using new pg command now
# should return /usr/lib/postgresql/16/bin/pg_ctl
which pg_ctl

### init db
initdb -D /database_16

### cp psql configurations
cp -R /etc/postgresql/15/main/{conf.d,postgresql.conf,pg_hba.conf} /etc/postgresql/16/main/

### update postgresql configurations
sed -i 's/\/database/\/database_16/g' /etc/postgresql/16/main/postgresql.conf
sed -i 's/15\/main/16\/main/g' /etc/postgresql/16/main/postgresql.conf
sed -i 's/15-main/16-main/g' /etc/postgresql/16/main/postgresql.conf

### restart postgresql to apply configuration as minion
exit
sudo systemctl restart postgresql@16-main

### check if postgresql listen on 5432
### should return
#tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      278716/postgres
#tcp6       0      0 :::5432                 :::*                    LISTEN      278716/postgres

sudo netstat -antlp |grep 5432

### create extensions on template1 as postgres
sudo su - postgres
psql -d template1

### now u should see something like this
# psql (16beta1 (Ubuntu 16~beta1-2.pgdg22.04+~20230605.2256.g3f1aaaa))

CREATE EXTENSION if not exists pg_trgm;
CREATE EXTENSION if not exists cube;
CREATE EXTENSION if not exists earthdistance;
CREATE EXTENSION if not exists tablefunc;
CREATE EXTENSION if not exists intarray;
CREATE EXTENSION if not exists pg_stat_statements;

### back to terminal to run pg_upgrade
### now stop postgresql service first
### some error messages if u did not stop postgresql
#There seems to be a postmaster servicing the new cluster.
#Please shutdown that postmaster and try again.
#Failure, exiting
###

### stop postgresql service as minion
exit
sudo systemctl stop postgresql@16-main

### back to postgres
sudo su - postgres
### check before upgrading
pg_upgrade -d /database -D /database_16 -b /usr/lib/postgresql/15/bin -B /usr/lib/postgresql/16/bin --check

###Performing Consistency Checks
###-----------------------------
###Checking cluster versions                                   ok
###Checking database user is the install user                  ok
###Checking database connection settings                       ok
###Checking for prepared transactions                          ok
###Checking for system-defined composite types in user tables  ok
###Checking for reg* data types in user tables                 ok
###Checking for contrib/isn with bigint-passing mismatch       ok
###Checking for incompatible "aclitem" data type in user tablesok
###Checking for presence of required libraries                 ok
###Checking database user is the install user                  ok
###Checking for prepared transactions                          ok
###Checking for new cluster tablespace directories             ok

###*Clusters are compatible*
### make sure u see the messages above

### now run pg_upgrade , this will take some time
pg_upgrade -d /database -D /database_16 -b /usr/lib/postgresql/15/bin -B /usr/lib/postgresql/16/bin

### success messages
###Upgrade Complete
###----------------
###Optimizer statistics are not transferred by pg_upgrade.
###Once you start the new server, consider running:
###    /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages
###Running this script will delete the old cluster's data files:
###    ./delete_old_cluster.sh

### back to minion , start postgresql service
exit
sudo systemctl start postgresql@16-main

### check if postgresql running
sudo netstat -antlp|grep 5432
### tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      279322/postgres
### tcp6       0      0 :::5432                 :::*                    LISTEN      279322/postgres

### check if some_db_here DB i sready to serve
sudo su - some_db_here
/usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages

psql -d some_db_here

select * from center;
### check the output

### END ###