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