Samstag, 1. Juni 2019

Migration of a Postgresql database in OpenShift

I'm working with an OpenShift 3.11 HA cluster where I deployed a Red Hat Single Sign On Application for authorization using a template of the catalouge. This template create a single Pod for the SSO and another one for a Postgresql 9.5 database to get the data persistent.
After collecting some experience with this application I decided to deploy a new one which should be redundant and each Pod should be schedule on a different node. So I would like to migrate the data from the Postgresql database of the first application.

I found following workaround which works for me. The way to it I describe at the bottom of this article ;-)

Ok, let's start:
Let's say we have the following Pods:
old Postgresql Pod: sso-postgresql
new Postgresql Pod: sso-new-postgresql
name of the database: root
user of the database: user1

First we have to create a custom dump from the Postgresql database which hosts the data:

oc rsh sso-postgresql

sh-4.2$ mkdir tmp
sh-4.2$ cd tmp
sh-4.2$ pg_dump -Fc root > db.dump
sh-4.2$ exit
oc rsync sso-postgresql:/opet/app-root/src/tmp </local/path>

Then we switch to the actual project in OpenShift and sync the Postgresql dump into the new Postgresql Pod. By the way, this Pod has to be created with the same database name and credentials like the ole one

oc project sso-new
oc rsync <local/path> sso-new-postgresql:/var/lib/pgsql/data/

The output of this command will look like this:

sending incremental file list

./

db.dump



sent 306,145 bytes received 131 bytes 204,184.00 bytes/sec

total size is 305,961 speedup is 1.00

rsync: failed to set permissions on "/var/lib/pgsql/data/.": Operation not permitted (1)

rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1178) [sender=3.1.2]

error: exit status 23

The error has no meaning for us, so we can go further

Now you have to pause all Pods with a connection to the new database

oc resume pause dc <Pods>

For the next steps we log in the new Postgresql Pod

oc rsh sso-new-postgresql

sh-4.2$ cd /var/lib/pgsql/data
Check that the db.dump is there

Now we enter the workaround cause a normal restore didn't worked for me:


sh-4.2$ psql --list

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+------------+------------+-----------------------

postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |

root | user1 | UTF8 | en_US.utf8 | en_US.utf8 |

template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +

| | | | | postgres=CTc/postgres

(4 rows)

Delete the database root
sh-4.2$ dropdb -U user1 root

Then create a new one 

sh-4.2$ createdb -O user1 root

Check if this new database is empty:

sh-4.2$ psql root_entw

psql (9.5.14)

Type "help" for help.



root_entw=# \dt

No relations found.

root_entw=# \q

Then make the restore in this new database:

sh-4.2$ pg_restore -d root_entw -U userv1c --clean --if-exists --create db_c.dump

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 3309; 1262 16385 DATABASE root_entw userv1c

pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop the currently open database

Command was: DROP DATABASE IF EXISTS root_entw;



pg_restore: [archiver (db)] could not execute query: ERROR: permission denied to create database

Command was: CREATE DATABASE root_entw WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';







pg_restore: [archiver (db)] Error from TOC entry 3312; 0 0 COMMENT EXTENSION plpgsql

pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql

Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';







pg_restore: WARNING: no privileges could be revoked for "public"

pg_restore: WARNING: no privileges could be revoked for "public"

pg_restore: WARNING: no privileges were granted for "public"

pg_restore: WARNING: no privileges were granted for "public"

WARNING: errors ignored on restore: 3


Ignore the errors ;-)

Check the database for the tables

sh-4.2$ psql root_entw

psql (9.5.14)

Type "help" for help.



root_entw=# \dt

List of relations

Schema | Name | Type | Owner

--------+-------------------------------+-------+---------

public | admin_event_entity | table | userv1c

public | associated_policy | table | userv1c

public | authentication_execution | table | userv1c

public | authentication_flow | table | userv1c

public | authenticator_config | table | userv1c

public | authenticator_config_entry | table | userv1c

public | broker_link | table | userv1c

public | client | table | userv1c

public | client_attributes | table | userv1c

public | client_auth_flow_bindings | table | userv1c

public | client_default_roles | table | userv1c

public | client_initial_access | table | userv1c

public | client_node_registrations | table | userv1c

public | client_scope | table | userv1c

public | client_scope_attributes | table | userv1c

public | client_scope_client | table | userv1c

public | client_scope_role_mapping | table | userv1c

public | client_session | table | userv1c

public | client_session_auth_status | table | userv1c

public | client_session_note | table | userv1c


root_entw=# \q



Exit the Pod

sh-4.2$ exit

Resume the related Pods

After the Pods are started, check if the data is available for the Pods 

That's it!!!

-----------------------------------------------------------------------------------------------------------
So... what was the way to here??? (a long and winding road ;-))

First I tried to make a snapshot of the pvc of the postgresql Pod. But unfortunatly the plugin I'm using doesn't support pvc snapshots

Then I tried to make a full and a partial dump of the old Postgresql database and restore the dump in the new one. I tried

pg_dump root | gzip > /opt/app-root/src/tmp /sso-postgresql.gz
pg_dumpall

and in the new Postgresql Pod

psql root < /var/lib/pgsql/data/sso-postgreql

That produce only a lot of errors, but no new data in my database

Then I found a tip that pg_restore works better with a custom dump format. So I created a cutom dum and tried the direct restore in the new Pod (see documentation of Postgresql https://www.postgresql.org/docs/9.5/app-pgrestore.html)
But again... only errors and no expected data

After many experiments (and learned a lot how to act with Postgresql) suddenly it works... I tried to figure out what I've done in my experiments ans recogniszed, that I once deleted the first database. I deleted it to make a pg_restore expecting the restore would also create the database new. But this didn't happened. So I created the database new. But I forgotten bevore deleting the database to notice which tables are inside (shit). And so I tried again the restore, but I only say the errors thinking the restore was not succesful. But after leaving the pod and check the data in the application -surprise- the data was there.

I was using this workaround many times, and every time it was a success.

If someone outside there has a better idea to realize such a migration, please tell me... I'm more an OpenShift specialist than a Postgresql master... 









Keine Kommentare:

Kommentar veröffentlichen