cum fac upgrade la o bază de date postgresql

Jan. 16, 2019, noon linux centos selinux


Salut și LMA 2019 din nou!

Pentru că ieri am făcut un upgrade la un server mongodb, astăzi a mai rămas doar de adus la zi o instanță postgresql 9.2.x. Acum nu va fi chiar "la zi", dar 9.2 expirase de un an și o lună, iar 9.6 este una din versiunile de postgres "supported".

Am hotărât să fac primul pas la 9.6, întrucât vreau să migrez datele și în pași mici este mai ușor. Am respectat instrucțiunile din documentația oficială pentru upgrade-ul unui PostgreSQL Cluster, dar am vrut să văd ce experiențe au avut ceilalți așa că am căutat căteva articole și am ales un articol publicat pe Medium, despre cum faci un upgrade postgresql pe CentOS:

  • adaugăm noul repo cu versiunea PostgreSQL dorită; următoarea comandă va crea un fișier repo nou /etc/yum.repos.d/pgdg-96-redhat.repo:
[root@database ~]$ yum install https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
  • acum putem instala postgresql 9.6 folosing yum (avantajul este că dacă versiunea precedentă era "venită" odată cu instalarea distribuției standard centos, atunci 9.6 va fi instalată într-un director separat, având astfel posibilitatea să rulăm alternativ ambele versiuni)

    [root@database ~]$ yum install postgresql96-server postgresql96-contrib
    
  • acum mai trebuie să inițializăm data-directory

[root@database ~]$ /usr/pgsql-9.6/bin/postgresql96-setup initdb
  • și în sfârșit, să facem o probă a comenzii pg_upgrade, "unelta de migrare" care vine cu PostgreSQL (facem în prealabil un "switch user postgres", nu merge ca root):
[root@database yum.repos.d]$ su - postgres
Last login: Wed Jan 16 11:20:26 EET 2019 on pts/0
-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/9.6/data/ --check # check only, won't change any data
check for "/var/lib/pgsql/9.6/data/base" failed: No such file or directory
Failure, exiting
  • hmmm, ceva nu e bine în 9.6/data, facem un ls -Z ca să vedem cum a "categorisit" SELinux fișierele nou adăugate, și într-adevăr vedem unconfined_r:
-bash-4.2$ ls -Z 9.6/
drwx------. postgres postgres system_u:object_r:postgresql_db_t:s0 backups
drwx------. postgres postgres system_u:object_r:postgresql_db_t:s0 data
-rw-------. postgres postgres unconfined_u:object_r:postgresql_log_t:s0 initdb.log
-bash-4.2$ ls -Z 9.6/data/
drwx------. postgres postgres unconfined_u:object_r:postgresql_db_t:s0 log
-bash-4.2$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
-bash-4.2$ exit
logout
  • mă așteptam ca folosind yum toată povestea cu SELinuex să meargă de la sine (așa cum este la mod_passenger), dar nu-i nimic, hai să vedem dacă contextul postgresql există in SEL - și există:
[root@database pgsql]$ semanage fcontext -l | grep postgr
/usr/bin/(se)?postgres                             regular file       system_u:object_r:postgresql_exec_t:s0
/etc/postgrey(/.*)?                                all files          system_u:object_r:postgrey_etc_t:s0
/var/lib/pgsql(/.*)?                               all files          system_u:object_r:postgresql_db_t:s0
/etc/postgresql(/.*)?                              all files          system_u:object_r:postgresql_etc_t:s0
  • întrucât totul pare în regulă, să facem un restore context (câteva informații referitor la asta într-un articol mai vechi despre SELinux publicat pe acest blog), observând schimbările de context unconfined_u:object_r:postgresql_db_t:s0->system_u:object_r:postgresql_db_t:s0 :
[root@database pgsql]$ restorecon -RvF $PWD/9.6
restorecon reset /var/lib/pgsql/9.6/data context unconfined_u:object_r:postgresql_db_t:s0->system_u:object_r:postgresql_db_t:s0
restorecon reset /var/lib/pgsql/9.6/data/pg_xlog context unconfined_u:object_r:postgresql_db_t:s0->system_u:object_r:postgresql_db_t:s0
restorecon reset /var/lib/pgsql/9.6/data/pg_xlog/archive_status context unconfined_u:object_r:postgresql_db_t:s0->system_u:object_r:postgresql_db_t:s0
  • acum vom șterge directorul data rămas de la prima încercare, și vom re-încerca pasul init din nou, de data acesta cu succes - vom putea vedea o structură maio bogată în data de această dată, dar vom vedea că este necesară apelarea lui restorecon încă odată:
-bash-4.2$ exit
logout
[root@database 9.6]$ pwd
/var/lib/pgsql/9.6
[root@database 9.6]$ rm -r data/
rm: descend into directory ‘data/’? y
rm: remove directory ‘data/log’? y
rm: remove directory ‘data/’? y
[root@database 9.6]$
[root@database 9.6]$ /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
[root@database 9.6]$ ls -Z
drwx------. postgres postgres system_u:object_r:postgresql_db_t:s0 backups
drwx------. postgres postgres unconfined_u:object_r:postgresql_db_t:s0 data
-rw-------. postgres postgres system_u:object_r:postgresql_log_t:s0 initdb.log
[root@database 9.6]$ restorecon -RvF $PWD/data
# ... puncte puncte puncte nu am mai ionclus output-ul, insă:
[root@database 9.6]# ls -Z
drwx------. postgres postgres system_u:object_r:postgresql_db_t:s0 backups
drwx------. postgres postgres system_u:object_r:postgresql_db_t:s0 data
-rw-------. postgres postgres system_u:object_r:postgresql_log_t:s0 initdb.log
[root@database 9.6]# ls -Z data/
drwx------. postgres postgres system_u:object_r:postgresql_db_t:s0 base
  • iar acum să facem testul final, verificănd valabilitatea ambelor instanțe postgres în așteptarea upgrade-ului mult dorit
root@database 9.6]# su - postgres
Last login: Wed Jan 16 12:11:35 EET 2019 on pts/0
-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/9.6/data/ --check # check only, won't change any data

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      ok
Checking for invalid "line" user columns                    ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*
  • big problem urmează la upgrade-ul propriuzis întrucât postgres 9.2 folosește unix_socket_directory, iar de la 9.3 unix_socket_directories, că deh la open-source trebuie să ne mai și suflecăm mânecile (nota bene voi fi foarte la obiect în cele ce urmează, pentru detalii vezi de exemplu aici stack exchange):
[root@database yum.repos.d]# su - postgres
Last login: Wed Jan 16 14:29:26 EET 2019 on pts/0
-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/9.6/data/

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting
-bash-4.2$ exit
logout
[root@database yum.repos.d]# service postgresql stop
Redirecting to /bin/systemctl stop postgresql.service
[root@database yum.repos.d]# su - postgres
Last login: Wed Jan 16 14:30:15 EET 2019 on pts/0
-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/9.6/data/
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/lib/pgsql/.s.PGSQL.50432"?


could not connect to old postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start
Failure, exiting
-bash-4.2$ postgres --describe-config | grep -o 'unix_socket_director\w*'
unix_socket_directories
  • în concluzie facem ce ne-a spus omul acela pe StackExchange, și anume facem un back-up la pg_ctl, apoi îi facem un "wrapper" în shell script care când va fi apelat o să cheme de fapt pg_ctl-orig având grijă să înlocuiască singularul cu pluralul pentru unix_socket_directory:
root@database yum.repos.d]$ mv /usr/bin/pg_ctl{,-orig}
[root@database yum.repos.d]$ ls -ltr /usr/bin/pg_ctl*
-rwxr-xr-x. 1 root root 37520 Aug 23 18:03 /usr/bin/pg_ctl-orig
[root@database yum.repos.d]$ echo '#!/bin/bash' > /usr/bin/pg_ctl
[root@database yum.repos.d]$ echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> /usr/bin/pg_ctl
[root@database yum.repos.d]$ chmod +x /usr/bin/pg_ctl
[root@database yum.repos.d]$ su - postgres
Last login: Wed Jan 16 14:31:32 EET 2019 on pts/0
-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/9.6/data/
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

connection to database failed: fe_sendauth: no password supplied

could not connect to old postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start
Failure, exiting
  • fuuuuuu*k, vă scutesc de detalii, dar întrucât avem security activat la vechiul cluster, trebuie să modificăm temporar accesul local din md5 în trusted, în fișierul /var/lib/pgsql/data/pg_hba.conf:
# local   all             all                                     md5
local   all             all                                     trusted
  • într-un final cu voia Domnului, pg_upgrade se hotărăște să își facă treaba:
-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/9.6/data/ -U postgres
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 reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      ok
Checking for invalid "line" user columns                    ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Setting minmxid counter in new cluster                      ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
  • nu uitați să reveniți la setările precedente gen:
mv /var/lib/pgsql/data/pg_hba.conf.bak /var/lib/pgsql/data/pg_hba.conf
mv /usr/bin/pg_ctl-orig /usr/bin/pg_ctl
  • acum ne pregatim sa pornim noua instanta si sa verificam daca totul este in regula inainte de a dezactiva versiunea veche:
-bash-4.2$ systemctl list-unit-files | grep postgres
postgresql-9.6.service                        disabled
postgresql.service                            enabled

[root@database yum.repos.d]$ systemctl enable postgresql-9.6.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.
  • cu toate acestea nu putem inca decola, intrucat procesele sunt unconfined,
-bash-4.2$ ps -defZ | grep postgres
system_u:system_r:unconfined_service_t:s0 postgres 21425 1  0 15:54 ?  00:00:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
system_u:system_r:unconfined_service_t:s0 postgres 21426 21425  0 15:54 ? 00:00:00 postgres: logger process
system_u:system_r:unconfined_service_t:s0 postgres 21428 21425  0 15:54 ? 00:00:00 postgres: checkpointer process
system_u:system_r:unconfined_service_t:s0 postgres 21429 21425  0 15:54 ? 00:00:00 postgres: writer process
system_u:system_r:unconfined_service_t:s0 postgres 21430 21425  0 15:54 ? 00:00:00 postgres: wal writer process
system_u:system_r:unconfined_service_t:s0 postgres 21431 21425  0 15:54 ? 00:00:00 postgres: autovacuum launcher process
system_u:system_r:unconfined_service_t:s0 postgres 21432 21425  0 15:54 ? 00:00:00 postgres: stats collector process
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 root 22096 22038  0 16:02 pts/0 00:00:00 su - postgres
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 postgres 22097 22096  0 16:02 pts/0 00:00:00 -bash
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 postgres 22552 22097  0 16:07 pts/0 00:00:00 ps -defZ
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 postgres 22553 22097  0 16:07 pts/0 00:00:00 grep --color=auto postgres

-bash-4.2$ ls -Z /usr/bin/postmaster
lrwxrwxrwx. root root system_u:object_r:bin_t:s0       /usr/bin/postmaster -> postgres
-bash-4.2$ ls -Z /usr/bin/postgres
-rwxr-xr-x. root root system_u:object_r:postgresql_exec_t:s0 /usr/bin/postgres

-bash-4.2$ ls -Z /usr/pgsql-9.6/bin/postmaster
lrwxrwxrwx. root root system_u:object_r:bin_t:s0       /usr/pgsql-9.6/bin/postmaster -> postgres
-bash-4.2$ ls -Z /usr/pgsql-9.6/bin/postgres
-rwxr-xr-x. root root system_u:object_r:bin_t:s0       /usr/pgsql-9.6/bin/postgres

[root@database pgsql-9.6]$ restorecon -RvF $PWD/bin

[root@database pgsql-9.6]$ ps -defZ | grep postg
system_u:system_r:init_t:s0     postgres 23390     1  0 16:24 ?        00:00:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
system_u:system_r:init_t:s0     postgres 23391 23390  0 16:24 ?        00:00:00 postgres: logger process
system_u:system_r:init_t:s0     postgres 23393 23390  0 16:24 ?        00:00:00 postgres: checkpointer process
system_u:system_r:init_t:s0     postgres 23394 23390  0 16:24 ?        00:00:00 postgres: writer process
system_u:system_r:init_t:s0     postgres 23395 23390  0 16:24 ?        00:00:00 postgres: wal writer process
system_u:system_r:init_t:s0     postgres 23396 23390  0 16:24 ?        00:00:00 postgres: autovacuum launcher process
system_u:system_r:init_t:s0     postgres 23397 23390  0 16:24 ?        00:00:00 postgres: stats collector process
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 root 23413 22038  0 16:24 pts/0 00:00:00 grep --color=auto postg
  • pentru indepartarea vechiului install:
rpm -qa | grep postgres
yum whatprovides /usr/bin/psql
yum remove 

A durat un pic, scrierea articolului și ar trebui îmbunătățit finalul. Dar mai întâi o să fac o pauză de lucru, urmând să revin dacă am observații importante legate de migrarea datelor sau upgrade, iar dacă uit eu, să nu uitați voi să faceți un backup pentru orice eventualitate.

Dacă (nu) vă convine ceva, lăsați un comentariu mai jos...