< Niklaus.20180123-postgres10 | Index | Niklaus.20180117-oddb-ruby-250 >>

20180122-postgres10

Summary

• Migrating yus on thinpower to postgres-10
• Fix warnings when running migel
• Keep in Mind

Commits

Index

• Migrating yus on thinpower to postgres-10
• Fix warnings when running migel

Migrating yus on thinpower to postgres-10

Saved the /etc/init.d/postgresql-10.1 as Attach:postgresql_10_1.txt

The following changes were done to enable a test instance using postgresql 10. Add the following lines in /etc/yus/yus.yml

db_name: ';dbname=yus;host=localhost;port=5433'
cleaner_interval: 300
server_url: drbssl://localhost:19997

Creating a dump on thinpower like

export PATH=/usr/local/pg-10_1/bin:$PATH
time sudo -u postgres /usr/local/pg-10_1/bin/pg_dumpall --host localhost --file /var/backup/thinpower/db/postgresql/8.3.dumpall
/usr/local/pg-10_1/bin/pg_dumpall: symbol lookup error: /usr/local/pg-10_1/bin/pg_dumpall: undefined symbol: PQconninfoParse
# thried
pg_dumpall --version
pg_dumpall (PostgreSQL) 10.1
thinpower ywesee # time pg_dumpall --host localhost --file /var/backup/thinpower/db/postgresql/8.3.dumpall
pg_dumpall: symbol lookup error: pg_dumpall: undefined symbol: PQconninfoParse
# Fixed with
chown postgres /var/backup/thinpower/db/postgresql
# now it works
su postgres
export PATH=/usr/local/pg-10_1/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH
pg_dumpall --version
pg_dumpall (PostgreSQL) 10.1
time sudo -u postgres pg_dumpall --host localhost --clean --if-exists  | gzip > /var/backup/thinpower/db/postgresql/8.3.dumpall.gz

Had to adapt /etc/init.d/postgresql-10.1 a little to display correctly the postgresql version used. Also update LD_LIBRARAY_PATH/PAT there.

To load I will use something like

sudo svc -d /service/*
sudo /etc/init.d/postgresql-8.4 stop
sudo mkdir  /opt/data/postgres-10.1
sudo chown -R postgres:postgres /opt/data/postgres-10.1
scp  ywesee@thinpower.ywesee.com:/var/backup/thinpower/db/postgresql/8.3.dumpall.gz /var/backup/thinpower/db_dumps/8.3.dumpall.gz
su postgres
export PATH=/usr/local/pg-10_1/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH
psql --version
initdb -D /opt/data/postgres-10.1/
exit
sudo /etc/init.d/postgresql-10.1 start
sudo su postgres
export PATH=/usr/local/pg-10_1/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH
zcat /var/backup/thinpower/db_dumps/8.3.dumpall.gz | psql -d postgres

I had to fix the gen_postgres_101 to avoid an error when calling initdb. Added

• use ../configure --prefix=${INSTALL_DIR} instead of passing --prefix to make install
• Added a timestamp value to the created branch name, to be able to call the script several times

I think it is a good idea to use a different port number for the new postgres server. In this case it will be easy to migrate on database after the other to. By default postgres uses port 5432. I will use 5433 for the PostgresSQL 10.1 installation. Changing port and uncommenting the line port = 5432 in postgresql.conf. Must restart to take effect.

To make oddb.org start again I had to change etc/db_connection.rb to include the line ODBA.storage.dbi = ODBA::ConnectionPool.new('DBI:Pg:dbname=oddb.org.ruby193;host=localhost;port=5433', 'postgres', '')

Similar fixes must go into the files

• /var/www/bbmb/lib/bbmb/persistence/odba.rb: ODBA.storage.dbi = ODBA::ConnectionPool.new("DBI:Pg:#{@config.db_name}",
• /var/www//migel/lib/migel/persistence/odba.rb: ODBA.storage.dbi = ODBA::ConnectionPool.new("DBI:Pg:#{@config.db_name}", @config.db_user, @config.db_auth)
• /var/www//xmlconv/lib/xmlconv/util/application.rb: ODBA.storage.dbi = ODBA::ConnectionPool.new("DBI:Pg:#{XmlConv::CONFIG.db_name}",
• /var/www/ydim/lib/ydim/server_config.rb: 'db_driver_url' => 'DBI:Pg:ydim',

For migel I added a line db_name: ';dbname=migel;host=localhost;port=5433' in /var/www/migel/etc/migel.yml

For ydim I added line db_driver_url: 'DBI:Pg:dbname=ydim;host=localhost;port=5433' in /etc/ydim/ydimd.yml

Added the following two lines to /etc/yus/yus.yml

db_name: ';dbname=yus;host=localhost;port=5433'
cleaner_interval: 300

As we want migrate one service after another we need to minize the downtime and ensure a consistent state I plan to proceed like this for each service:

• Before the first servic
• mkdir /home/ywesee/migration
  • Start the postgresql 10.1 server
  • Load the dump into postgres 10 to get the related users rights zcat /var/backup/thinpower/db/postgresql/8.3.dumpall.gz| /usr/local/pg-10_1/bin/psql -d postgres --port 5433 -U postgres
• for each database (eg.yus)
  • drop the postgresql 10 database: /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost # executing drop database yus;
  • Stop the service (e.g. sudo svc -d /service/yus
  • Change the configuration to use the new postgres port 5433
  • Dump the concerned 8.3 database /usr/local/pg-10_1/bin/pg_dump -U postgres -p 5432 -h localhost --clean --if-exists yus | gzip > /home/ywesee/migration/yus.gz
  • Drop the concerned 8.3 database /usr/bin/psql -U postgres -p 5432 -h localhost --command="drop database yus"
  • Create the concerned 10.1 database /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost --command="create database yus;"
  • Load the concerned 10.1 database zcat /home/ywesee/migration/yus.gz | /usr/local/pg-10_1/bin/psql -U postgres -p 5433 -h localhost yus
  • Start the concerned service sudo svc -u /service/yus
  • Verify that the service works, eg. sudo tail -f /service/yus/log/main/current

But first we create the PostgreSQL database 10.1 and service on thinpower

su
cd /usr/local/src
./gen_postgres_101
cd /var/lib/postgresql/
export PATH=/usr/local/pg-10_1/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pg-10_1/lib:$LD_LIBRARY_PATH
sudo chown postgres:postgres /var/lib/postgresql/
sudo -u postgres /usr/local/pg-10_1/bin/initdb -D /var/lib/postgresql/10.1/data
# Changing port 5432 to 5433 in /var/lib/postgresql/10.1/data/postgresql.conf
# Adding /etc/init.d/postgresql-10.1. content see Attach:postgresql-10.1.txt
cd /etc/runlevels/default
ln -s /etc/init.d/postgresql-10.1 .

The /etc/init.d/postgresql-10.1. did not start on thinpower, as on oddb-ci a default value of 10 was given to the to variable. This lead to the error /etc/init.d/postgresql-10.1: Zeile 94: *1000: Syntax Fehler: Operator erwartet. (Fehlerverursachendes Zeichen ist \"*1000\").

Fix warnings when running migel

Currently we have many errors like

0.5.4/lib/dbd/pg/database.rb:82:in `rescue in initialize': uninitialized constant DBI::DBD::Pg::Database::PGError (NameError)