collation

gistfile1.txt

https://jumpcloud.com/blog/how-to-upgrade-ubuntu-20-04-to-ubuntu-22-04
https://askubuntu.com/questions/1098480/attempting-to-upgrade-from-ubuntu-server-16-04-to-18
https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/13273
https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/
https://github.com/ardentperf/glibc-unicode-sorting


2.27 > 2.31 
index corruption

postgres=# SELECT pg_encoding_to_char(encoding),*
FROM pg_database WHERE datname = current_database();
-[ RECORD 1 ]-------+------------
pg_encoding_to_char | UTF8
datname             | postgres
datdba              | 10
encoding            | 6
datcollate          | en_US.UTF-8
datctype            | en_US.UTF-8
datistemplate       | f
datallowconn        | t
datconnlimit        | -1
datlastsysoid       | 13017
datfrozenxid        | 548
datminmxid          | 1
dattablespace       | 1663
datacl              |

postgres=# \q
postgres@ubuntu:~$ psql
psql (12.22 (Ubuntu 12.22-0ubuntu0.20.04.1), server 10.23 (Ubuntu 10.23-0ubuntu0.18.04.2))
Type "help" for help.

postgres=# select bt_index_check('testcoll_pkey'::regclass);
ERROR:  item order invariant violated for index "testcoll_pkey"
DETAIL:  Lower index tid=(1,1) (points to heap tid=(0,2)) higher index tid=(1,2) (points to heap tid=(0,1)) page lsn=0/16588A0.
postgres=#



----------------
   59  sudo apt-get install debootstrap
   60  sudo apt-get install schroot
   61  sudo vim /etc/schroot/schroot.conf
   62  lsb_release -a
   63  sudo mkdir /opt/chroot
   64  sudo chown -R postgres:postgres /opt/chroot
   65  cat /etc/debian_version
   66  wget https://ftp.debian.org/debian/pool/main/d/debootstrap/debootstrap_1.0.140_all.deb
   67  sudo dpkg -i debootstrap_1.0.140_all.deb
   68  sudo debootstrap --arch amd64 stable /opt/chroot/ http://deb.debian.org/debian/
   69  sudo chroot /opt/chroot/
   70  sudo chroot /opt/chroot/
   71  sudo chroot /opt/chroot/
   72  mount /opt/pg15 /opt/chroot/opt/pg15
   73  sudo mount /opt/pg15 /opt/chroot/opt/pg15
   74  mount
   75  cp -rf /opt/pg15/* /opt/chroot/opt/pg15/
   76  sudo cp -rf /opt/pg15/* /opt/chroot/opt/pg15/
   77  sudo chown -R postgres:postgres /opt/chroot/opt/pg15
   78  sudo chroot /opt/chroot/
   79  ls -lrt
   80  sudo cp -rf db1 /opt/chroot/var/lib/postgresql/
   81  sudo chown -R postgres:postgres /opt/chroot/var/lib/postgresql/db1
   82  sudo chroot /opt/chroot/
   83  export PATH=/opt/pg15/bin:$PATH
   84  pg_ctl -D db1 stop
   85  sudo chroot /opt/chroot/
   86  ls -rlt
   87  rm -
   88  ls -lrt
   89  sudo mkdir /opt/bionic
   90  sudo debootstrap --arch amd64 bionic http://deb.debian.org/debian/
   91  sudo debootstrap --arch amd64 sid http://deb.debian.org/debian/
   92  ls /opt
   93  sudo debootstrap --arch amd64 bionic /opt/bionic/ http://deb.debian.org/debian/
   94  sudo debootstrap --arch amd64 focal /opt/bionic/ http://deb.debian.org/debian/
   95  sudo debootstrap --arch amd64 jammy /opt/bionic/ http://deb.debian.org/debian/
   96  sudo debootstrap --arch amd64 buster /opt/bionic/ http://deb.debian.org/debian/
   97  history
   98  cp -rf /opt/pg15 /opt/bionic/opt/
   99  sudo cp -rf /opt/pg15 /opt/bionic/opt/
  100  chroot /opt/bionic
  101  sudo chroot /opt/bionic
  102  sudo cp -rf db1 /opt/bionic/var/lib/postgresql/
  103  sudo chroot /opt/bionic



root@ubuntu:/# history
    1  apt-get install postgres
    2  apt-get install postgresql
    3  apt-get remove postgresql\*
    4  apt install vi
    5  vi /etc/default/locale
    6  apt-get install locale
    7  apt-get install localgen
    8  apt-get install local-gen
    9  locale
   10  locale-gen en_US.UTF-8
   11  vi /etc/locale.gen
   12  locale -a
   13  locale-gen en_US.UTF-8
   14  locale -a
   15  apt-get install postgresql
   16  chown -R postgres:postgres /opt/pg15
   17  su - postgres
   18  chown -R postgres:postgres /var/lib/postgresql
   19  mount -t proc /proc proc/
   20  mount --rbind /sys sys/
   21  mount --rbind /dev dev/
   22  su - postgres
   23  mount -t tmpfs tmp /dev/shm
   24  su - postgres
   25  history


su - postgres
    1  apt-get install postgres
    2  apt-get install postgresql
    3  apt-get remove postgresql\*
    4  apt install vi
    5  vi /etc/default/locale
    6  apt-get install locale
    7  apt-get install localgen
    8  apt-get install local-gen
    9  locale
   10  locale-gen en_US.UTF-8
   11  vi /etc/locale.gen
   12  locale -a
   13  locale-gen en_US.UTF-8
   14  locale -a
   15  apt-get install postgresql
   16  export PATH=/opt/pg15/bin:$PATH
   17  ldd --version
   18  pg_ctl -D db1 -l db1.log start
   19  cat db1.log
   20  export PATH=/opt/pg15/bin:$PATH
   21  pg_ctl -D db1 -l db1.log start
   22  cat db1.log
   23  psql
   24  psql -h /var/run/postgresql/
   25  ps aux | grep postgres
   26  grep soc db1/postgresql.conf
   27  psql -h /tmp
   28  psql -h /tmp -p 5433
   29  psql -h /tmp -p 5433
   30  history


postgres@ubuntu:~$ psql -h /tmp -p 5433
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.27, but the operating system provides version 2.28.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.0)
Type "help" for help.

postgres=# create table foo as select * from testcoll;
SELECT 3
postgres=# drop table testcoll;
DROP TABLE
postgres=# alter database postgres refresh collation version;
NOTICE:  changing version from 2.27 to 2.28
ALTER DATABASE