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 folosind
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
- 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
- ceva nu e bine în
9.6/data, facem unls -Zca să vedem cum a "categorisit" SELinux fișierele nou adăugate, și într-adevăr vedemunconfined_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
yumtoată povestea cu SELinuex să meargă de la sine (așa cum este lamod_passenger), dar nu-i nimic, hai să vedem dacă contextulpostgresqlexistă 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
datarămas de la prima încercare, și vom re-încerca pasulinitdin nou, de data acesta cu succes - vom putea vedea o structură maio bogată îndatade această dată, dar vom vedea că este necesară apelarea luirestoreconî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
- 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.3unix_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-origavând grijă să înlocuiască singularul cu pluralul pentruunix_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
localdin 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_upgradese 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...