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