Sqlite to Postgresql migration

Added by john karippery 3 months ago

Redmine sqlite to postgresql

Install necessary packages

apt-get install sqlite3 ruby ruby-dev make libsqlite3-dev libpq-dev postgresql pgloader libpq-dev build-essential     

Go to the folder where your SQLite database resides and make a copy of it. If the Redmine server’s still running.

sqlite3 redmine.sqlite3
.backup /tmp/redmine_backup.sqlite3
.exit

Which will make sure there’s no corruption (i.e. no writes running in the background while you’re making the copy). If the server’s not running you don’t have to deal with this and just copy the file manually:

cp redmine.sqlite3 /tmp/redmine_backup.sqlite3
cp /var/lib/dbconfig-common/sqlite3/redmine/instances/default/redmine_default /tmp/redmine_backupdb.sqlite3

Now install various gems. The 1.5.2 version of the rack gem is breaking database conversion so stick with 1.4

gem install rack -v 1.4.5
gem install sqlite3 pg taps

Edit Gemfile

setup env variables http,https,ftp
go to App file where Gemfile locate

cd /usr/share/redmine/
gem update
#gem for migration database
group :production do
 gem "pg", "~> 0.19" 
 gem  "rake" 
gem "taps" 
end

Update Gemfile

change network setting to NAT

cd /usr/share/redmine 
bundle install

Now switch to the postgres user and enter the PostgreSQL command prompt:

cd 
su postgres
psql

Create a role and database for migrate

CREATE ROLE redmine LOGIN ENCRYPTED PASSWORD 'Qwe12345!' NOINHERIT VALID UNTIL 'infinity';
CREATE DATABASE redmine WITH ENCODING='UTF8' OWNER=redmine;
\q
exit

Authentication

nano /etc/postgresql/10/main/pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     trust
local   all             all                                     peer

Edit database.yml

nano /etc/redmine/default/database.yml
development:
  adapter: postgresql
  encoding: unicode
  database: redmine
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: redmine
  password: Qwe12345!

test:
  adapter: postgresql
  database: redmine_test
  username: redmine
  password: Qwe12345!
  host: localhost

production:
  adapter: postgresql
  encoding: unicode
  database: redmine
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: redmine
  password: Qwe12345!
  host: localhost

Migrate

cd /usr/share/redmine
rake db:migrate
rake db:setup

Alter table tokens

for easy step go to phppgadmin
token -> alter column created_on and updated_on -> *timestamp without time zone *

cd 
 pgloader --with "data only"  /tmp/redmine_backupdb.sqlite3  postgresql://redmine:"Qwe12345!"@localhost/redmine

please check again table tokens type

service apache2 restart

For confirmation

su postgres
psql
could not change directory to "/root": Permission denied
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# \c redmine
You are now connected to database "redmine" as user "postgres".
redmine=# select * from tokens
redmine-# ;
 id | user_id | action  |                  value                   |         created_on         |         updated_on
----+---------+---------+------------------------------------------+----------------------------+----------------------------
  3 |       1 | session | b73ac30b89ac9a895345b562592f71230dec33f0 | 2019-10-22 12:52:51.581302 | 2019-10-22 12:54:31.181062
  4 |       1 | feeds   | db3ae976e2567b590ba8e880d474a12bce8f9da1 | 2019-10-22 12:52:51.63742  | 2019-10-22 12:52:51.63742
(2 rows)

redmine=# \q

if you see any 00 end of the column created_on and updated_on that means not alter
exit

Re- login

go to redmine information page you can find

Database adapter               PostgreSQL

[[http://192.168.56.124/redmine/admin/info]]

---------------------------The End------------------------------------------

Sqlite queries

sqlite3 redmine.sqlite3
.tables # view tables 
select * from table_name; # view table data
.exit

Psql queries

su postgres
psql
\c database_name 
\dt  # view tables 
select * from table-name # view table data;
\l #list all database
ALTER TABLE table_name ADD column_name datatype;


exit

Replies (2)

RE: Sqlite to Postgresql migration - Added by Hofei - 3 months ago

Thank you very much for your manual!

Can you tell me if there are any special things to consider when switching from mysql / mariadb to Postgres?

RE: Sqlite to Postgresql migration - Added by john karippery 3 months ago

i think you should find a mariadb tool for migration like pgloader
and
edit database.yml file

(1-2/2)