Migrating from Sqlite to PostgresSQL

[Corrado Topi]

Dear all,

I am trying to migrate from Sqlite to PostgreSQL. I exported the Sqlite database in all possible forms and tried to re import it, but cannot make it work. Help! :)

Best

Corrado

[Mario Frasca]

Hi Corrado!

allow me first a silly joke: if it didn't work, you didn't yet try all
possible forms. ;-)

now seriously, help me understand what you did, what documentation you
used, what recipes you tried to apply.

anyhow, I suggest starting from two working ghini databases, that is:

- let first ghini.desktop create your target PostgreSQL database,

- back to your SQLite connection, export your data, straight from sqlite3:

  (`sqlite3 <your_dabase_file> -batch ".dump" > script.sql`),

- remove all `CREATE` instructions,

- replace the leading `PRAGMA foreign_keys=OFF;` with `set constraints
all deferred;`,

- now straight in your new database, try executing the new script.sql:

  (`\i script.sql`)

  which I still expect to give trouble,

- you possibly need to edit some fields (timestamps?),

- you possibly need to adapt some commands (sequences?),

please be very specific in your feedback!

would you mind opening a documentation issue in the site?

we could write a small python conversion script for this, if we don't
find an external working recipe.

ciao,

Mario

[Mario Frasca]

p.s.: did you consider this https://pgloader.io/ ?

[Corrado Topi]

Ciao Mario!

How are things?

I did the following: launched ghini with sqlite database, exported in JSON (or CSV or any other option), closed ghini, launched ghini again with a new PostgresSQL database, tried to import the JSON (or CSV or ....) file to populate the database .... but I always get different errors and it does not work.

I think you are suggesting to migrate from sqlite to postgresSQL outside of ghini, correct?

Best,

[Mario Frasca]

unfortunately undocumented and a bit hidden.  it's a two lines comment in a source file (plugins/imex/csv_.py#72).

# TODO: what happens when you export from one database type and try
# and import into a different database, e.g. postgres->sqlite

from time to time I've been converting batches of such lines into issues, but I've not been very consistent, and I never processed this particular file.

feel free to open an issue and since you have experimented, you can also add a description of what happens in the sqlite3->postgresql case.

thank you!

let me know if you did manage with the procedure I was describing in my previous answer.

ciao,

Mario

--
You received this message because you are subscribed to the Google Groups "ghini/bauble" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bauble+un...@googlegroups.com.
To post to this group, send email to bau...@googlegroups.com.
Visit this group at https://groups.google.com/group/bauble.
For more options, visit https://groups.google.com/d/optout.

[Corrado Topi]

Ciao Mario,

I was able to convert the sqlite database to postgres using pgloader. Unfortunately, ghini then does not work with the postgres connection.

This is the error:

Could not open connection.

(psycopg2.ProgrammingError) permission denied for relation bauble

[SQL: 'SELECT bauble.name AS bauble_name, bauble.value AS bauble_value, bauble.id AS bauble_id, bauble._created AS bauble__created, bauble._last_updated AS bauble__last_updated \nFROM bauble \nWHERE bauble.name = %(name_1)s \n LIMIT %(param_1)s'] [parameters: {'name_1': u'created', 'param_1': 1}]

Details:

Traceback (most recent call last):

 File "/home/user/.virtualenvs/ghide/local/lib/python2.7/site-packages/ghini.desktop-1.0.86-py2.7.egg/bauble/__init__.py", line 316, in main

    if db.open(uri, True, True):

 File "/home/user/.virtualenvs/ghide/local/lib/python2.7/site-packages/ghini.desktop-1.0.86-py2.7.egg/bauble/db.py", line 314, in open

    verify_connection(new_engine, show_error_dialogs)

 File "/home/user/.virtualenvs/ghide/local/lib/python2.7/site-packages/ghini.desktop-1.0.86-py2.7.egg/bauble/db.py", line 415, in verify_connection

    return verify_connection(engine, False)

 File "/home/user/.virtualenvs/ghide/local/lib/python2.7/site-packages/ghini.desktop-1.0.86-py2.7.egg/bauble/db.py", line 463, in verify_connection

    result = query(meta.BaubleMeta).filter_by(name=meta.CREATED_KEY).first()

 File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2445, in first

    ret = list(self[0:1])

 File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2281, in __getitem__

    return list(res)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2516, in __iter__

    return self._execute_and_instances(context)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2531, in _execute_and_instances

    result = conn.execute(querycontext.statement, self._params)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 914, in execute

    return meth(self, multiparams, params)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement

    compiled_sql, distilled_params

 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1146, in _execute_context

    context)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception

    exc_info

 File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 199, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1139, in _execute_context

    context)

 File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 450, in do_execute

    cursor.execute(statement, parameters)

ProgrammingError: (psycopg2.ProgrammingError) permission denied for relation bauble

[SQL: 'SELECT bauble.name AS bauble_name, bauble.value AS bauble_value, bauble.id AS bauble_id, bauble._created AS bauble__created, bauble._last_updated AS bauble__last_updated \nFROM bauble \nWHERE bauble.name = %(name_1)s \n LIMIT %(param_1)s'] [parameters: {'name_1': u'created', 'param_1': 1}]

You received this message because you are subscribed to a topic in the Google Groups "ghini/bauble" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/bauble/60MHqR5dQk0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to bauble+un...@googlegroups.com.

[Mario Frasca]

buongiorno Corrado!

postgresql, unlike sqlite, has a complete user permissions management system.  you need to grant privileges to your users.  this is (superficially) described in the docs (check the 'initial configuration' chapter), and I believe there should be links to the postgresql manuals.

»When connecting to a database server [other than SQLite], you have to manually do the following: Create at least one user; Create your database; Give at least one user full permissions on your database; If you plan having more database users: Give one of your users the CREATEROLE privilege; Consider the user with the CREATEROLE privilege as a super-user, not meant to handle data directly; Keep your super-user credentials in a very safe place.«

we also have a 'scripts/create-role.sh' file, which tells you what it is that you need to do. 

the 'alter role' following the 'create role' is in case the role already exists and you want to fix it.

once you manage to connect, you will find some minimal user management tools in ghini.desktop, straight from the main menu.

let me know if this helps.

ciao,

Mario

[Corrado Topi]

Ciao Mario,

The superuser role is postgres, but to the new user all privileges have been granted.

In other words:

$sudo -i -u postgres

$psql

CREAT DATABASE ghinidb;

CREATE USER ghiniuser WITH PASSWORD ghinipassword;

GRANT ALL ON DATABASE ghinidb TO ghiniuser;

ALTER DATABASE ghinidb OWNER TO ghiniuser;

Is this what you meant?

Best,

[Mario Frasca]

I don't know if this that you suggest would work too, what I meant is what is written in the script I mentioned.

I'll copy it here for your Italian laziness ;-)

#!/bin/bash

USER=$1
PASSWD=$2
shift 2

cat <<EOF | psql bauble -U bauble $@
create role $USER with login password '$PASSWD';
alter role $USER with login password '$PASSWD';
grant all privileges on all tables in schema public to $USER;
grant all privileges on all sequences in schema public to $USER;
grant all privileges on all functions in schema public to $USER;
EOF

I know that this works.  if your version works, I don't know.  try it out and you tell me.

ciao,

Mario

[Mario Frasca]

On 07/30/2018 07:37 AM, Corrado Topi wrote:

> The superuser role is postgres, but to the new user all privileges
> have been granted.

yes, I know that `postgres` is the global super user for your database
server management.

I suggest a further level, a ghini super-user, for the management of a
single ghini database within the database server.

the lowest lever would be the user level, managed through the ghini
super-user, either with the mentioned script, or from the tools menu.

on your small home server you can skip the intermediate level, but on a
corporate server I am sure that no database administrator will surrender
the global super user credentials only because you ask.

please do open the docs.

http://ghini.readthedocs.io/en/ghini-1.0-dev/started.html

best,
Mario

[Corrado Topi]

Ciao Mario,

Yes, I looked at the doc. Those passages were in addition to be able to use pgloader to load the original full sqlite database into postgres. Or did I misunderstand your  answer?

[Mario Frasca]

passiamo all'italiano perché ora sono io a non capire.

1. hai creato/alterato i tuoi utenti usando lo script che ti ho indicato e copiato?

2. hai creato un utente intermedio per la gestione del tuo database ghini?

3. hai letto e seguito in particolare la sezione espandibile 'Some more hints if you need PostgreSQL'?

4. sei riuscito a connetterti alla tua base dati con la linea menzionata a conclusione di quella sezione?

per aiutarti con la 4 ho bisogno che tu risponda "SI" alle prime tre.  ;-)

fammi sapere,

Mario

You received this message because you are subscribed to the Google Groups "ghini/bauble" group.

To unsubscribe from this group and stop receiving emails from it, send an email to bauble+un...@googlegroups.com.

[Corrado Topi]

Si a tutti e quattro! Posgres funziona senza problema ....

E` solo dopo aver poi migrato il database sqlite a quello postgres usando pgloader che cominciano i problemi che ti ho elencato :)

[Mario Frasca]

che utente hai usato per pgloader? se hai creato un utente intermedio
per la gestione del database ghini, dovresti aver utilizzato quello.

dopo aver usato pgloader, hai rifatto tutti i passi 1, 2, 3?

dopo aver usato pgloader, riesci ancora a connetterti alla base dati
migrata, con la linea di comando 4?

[Corrado Topi]

Ciao Mario,

Cominciamo dalla prima, perche` credo il problema sia proprio in questa: pgloader non mi permette di definire un utente, almeno sulla mia macchina .... quindi posso solo lanciarlo come postgres, non con il mio superuser ghini (ghinisuperuser), che sarebbe il mio utente privilegiato intermedio.

Inoltre non posso configurare psql per accedere al database con opzione no password, perche` questa e` una macchina di lavoro (questioni di sicurezza).

A risentirci

[Mario Frasca]

d'accordo, niente --no-password,

ma per il resto, riesci, o non riesci a connetterti?

voglio dire: hai deciso di voler configurare ghini.desktop in modo che si connetta utilizzando un utente specifico.

puoi o non puoi ora utilizzare questo stesso utente per connetterti da linea di comando? 

non ho capito se hai (modificato ed) eseguito lo script che ti ho indicato, per alterare o per creare l'utente che hai deciso di utilizzare.

fammi capire,

Mario

You received this message because you are subscribed to the Google Groups "ghini/bauble" group.

To unsubscribe from this group and stop receiving emails from it, send an email to bauble+un...@googlegroups.com.

[Mario Frasca]

On 07/30/2018 08:53 AM, Corrado Topi wrote:

> Cominciamo dalla prima, perche` credo il problema sia proprio in
> questa: pgloader non mi permette di definire un utente, almeno sulla
> mia macchina .... quindi posso solo lanciarlo come postgres, non con
> il mio superuser ghini (ghinisuperuser), che sarebbe il mio utente
> privilegiato intermedio.

io non ho esperienze con pgloader, te lo indicavo come shortcut.
l'azione preferita che ti suggerivo era di creare la base dati da
ghini.desktop, e di eseguire lo script (il dump) dopo averlo alterato
leggermente.
ci hai provato?  se ci hai provato ed hai avuto problemi che ti hanno
sconsigliato di procedere, quali sono stati?
M

[Corrado Topi]

Si certo, ho provato anche con lo script che mi hai mandato, ma la situazione non cambia: posso connettermi ed usare il database postres da ghini senza problemi, prima di lanciare pgloader. Pero` se lancio pgloader, non posso piu` usarlo. mmmm .... ho provato anche con altri approcci  .... ma non fa differenza. Fammi pensare un attimo .... potrebbe essere dovuto alla configurazione di sicurezza della macchina specifica. Faccio un po di prove poi ti faccio sapere ....

[Corrado Topi]

Ciao Mario,

Ho provato anche usando sql dump da sqlite secondo le tue istruzioni, ma non funziona. Quando provi  a fare insert ritorna centinaia di errori e purtroppo non ho piu` tempo di fare tutte le prove, mi spiace .... vedo nei prossimi giorni!

[Mario Frasca]

Hi Corrado!

I can confirm that the procedure I was describing, even if it's a bit of work on your side, would just work.

so you let ghini.desktop create a fresh database in your database server, using the credentials you prefer.  part of what ghini does you need to undo, namely you need to remove everything from the genus_synonym, genus, family_synonym, family, in this order.  and you need to remove everything from the bauble table.  just to be safe, remove everything, but take care of the order in which you do this.

the extra work you need to execute is:

- remove all create statements.

- replace them with the corresponding delete from <table>;

- put these delete from statements in the correct order (as described above),

- correct the boolean values.  this is the nasty/boring part.  you need to replace all zeroes and ones that really mean 'f' and 't' (false and true).  you need to do this by hand, or you can write a script, or you can trust the search and replace of your text editor of choice.  the fastest for you (and possibly also for me) would be if you would send me the dump and let me edit it.  by the way: donations are always welcome. ;-)

anyhow, I've done the above for a smallish database and it just worked.

so, try again, or let me know.

ciao,

Mario

[Mario Frasca]

sembrano centinaia di errori, ma è il primo che ti dice che cosa è successo, mentre gli altri sono semplicemente "ho incontrato un primo errore, e non posso proseguire".

leggi l'altro messaggio che ho appena inviato alla lista, in inglese, che ti descrive la procedura che ho seguito e che mi ha prodotto un database utilizzabile.

prima o poi potrei scrivere uno script che automatizza i passi.  non sono per nulla complicati, se fatti in automatico, l'unico minimo problema è il parsing dello SQL.

ciao,

Mario

You received this message because you are subscribed to the Google Groups "ghini/bauble" group.

To unsubscribe from this group and stop receiving emails from it, send an email to bauble+un...@googlegroups.com.

[Mario Frasca]

On 07/30/2018 10:25 AM, Mario Frasca wrote:
> - correct the boolean values.  this is the nasty/boring part.  you
> need to replace all zeroes and ones that really mean 'f' and 't'
> (false and true).

it's the 4th field of the "species" table,
the 10th field of the "accession" table,
the 3rd field of the "plant" table.

[Corrado Topi]

Ciao Mario,

Ho provato la strada che mi hai proposto .... pero` zero successo per ora. Ovviamente, gli errori sono dovuti a quello che dici, ma non solo: ci sono parecchi errori, ho provato a fare parecchi degli INSERT individualmente (a mano!) e gli errori sono differenti.

Ho anche provato una strada alternativa (usando di nuovo pgloader  ma in forma differente), che mi ha portato a riuscire ad usare il database  postgres, pero` continuo ad aver un errore  con psycopg2: ogni volta che faccio un operazione in ghini (con il database postgres), my appare un errore che dice connessione persa. Pero` l'operazione viene completata .... mah!

Mi spiace non riuscire a fare piu` tests, ma ho pochissimo tempo questa settimana e la prossima! Per ora continuiamo ad usare il database sqlite3, e poi appena ho qualche ora da dedicare provo di nuovo ....

[Mario Frasca]

Corrado, ti prego, sii specifico, come faccio ad aiutarti …

On 07/30/2018 12:51 PM, Corrado Topi wrote:
> Ciao Mario,
>

> Ho provato la strada che mi hai proposto .... pero` zero successo per
> ora.

cioè incontri un errore. quale? dove?

mi sorprende, perché, come t'ho detto, ho fatto una migrazione ed ha
funzionato tranquillamente.

come detto: mandami il tuo dump così posso vedere cosa succede.

> Ovviamente, gli errori sono dovuti a quello che dici, ma non solo:

quale messaggio di errore ti porta a pensare che non sia dovuto solo a
quello che dico?

> ci sono parecchi errori, ho provato a fare parecchi degli INSERT
> individualmente (a mano!) e gli errori sono differenti.

ci sono migliaia di errori differenti, ciascuno con più di una soluzione.

> Ho anche provato una strada alternativa (usando di nuovo pgloader  ma
> in forma differente),

quale?

> che mi ha portato a riuscire ad usare il database  postgres, pero`
> continuo ad aver un errore  con psycopg2: ogni volta che faccio un
> operazione in ghini (con il database postgres), my appare un errore
> che dice connessione persa. Pero` l'operazione viene completata .... mah!

magari il log serve.

>
> Mi spiace non riuscire a fare piu` tests, ma ho pochissimo tempo
> questa settimana e la prossima! Per ora continuiamo ad usare il
> database sqlite3, e poi appena ho qualche ora da dedicare provo di
> nuovo ....

io ti posso aiutare, ma senza dati precisi, dovendo andare ad indovinare
cosa stai facendo, mi risulta molto ma molto dispendioso in termini di
tempo.

mi costerebbe meno tempo se mi passassi il dump.

ciao,

Mario

[Mario Frasca]

oh, and then we still need to update all sequences.

it's only sequences for the automatic object id, so all you need to do
is to iterate on all tables and to store the maximum id into the sequence.

On 07/30/2018 10:25 AM, Mario Frasca wrote:
>

[Mario Frasca]

On 07/30/2018 02:25 PM, Mario Frasca wrote:
> oh, and then we still need to update all sequences.
>
> it's only sequences for the automatic object id, so all you need to do
> is to iterate on all tables and to store the maximum id into the sequence.

like this:

select setval('accession_id_seq', (select max(id) from accession));
select setval('accession_note_id_seq', (select max(id) from
accession_note));
select setval('bauble_id_seq', (select max(id) from bauble));
select setval('collection_id_seq', (select max(id) from collection));
select setval('color_id_seq', (select max(id) from color));
select setval('default_vernacular_name_id_seq', (select max(id) from
default_vernacular_name));
select setval('family_id_seq', (select max(id) from family));
select setval('family_note_id_seq', (select max(id) from family_note));
select setval('family_synonym_id_seq', (select max(id) from
family_synonym));
select setval('genus_id_seq', (select max(id) from genus));
select setval('genus_note_id_seq', (select max(id) from genus_note));
select setval('genus_synonym_id_seq', (select max(id) from genus_synonym));
select setval('geography_id_seq', (select max(id) from geography));
select setval('habit_id_seq', (select max(id) from habit));
select setval('history_id_seq', (select max(id) from history));
select setval('location_id_seq', (select max(id) from location));
select setval('plant_change_id_seq', (select max(id) from plant_change));
select setval('plant_id_seq', (select max(id) from plant));
select setval('plant_note_id_seq', (select max(id) from plant_note));
select setval('plant_prop_id_seq', (select max(id) from plant_prop));
select setval('plant_status_id_seq', (select max(id) from plant_status));
select setval('plugin_id_seq', (select max(id) from plugin));
select setval('prop_cutting_id_seq', (select max(id) from prop_cutting));
select setval('prop_cutting_rooted_id_seq', (select max(id) from
prop_cutting_rooted));
select setval('prop_seed_id_seq', (select max(id) from prop_seed));
select setval('propagation_id_seq', (select max(id) from propagation));
select setval('source_detail_id_seq', (select max(id) from source_detail));
select setval('source_id_seq', (select max(id) from source));
select setval('species_distribution_id_seq', (select max(id) from
species_distribution));
select setval('species_id_seq', (select max(id) from species));
select setval('species_note_id_seq', (select max(id) from species_note));
select setval('species_synonym_id_seq', (select max(id) from
species_synonym));
select setval('tag_id_seq', (select max(id) from tag));
select setval('tagged_obj_id_seq', (select max(id) from tagged_obj));
select setval('verification_id_seq', (select max(id) from verification));
select setval('vernacular_name_id_seq', (select max(id) from
vernacular_name));
select setval('voucher_id_seq', (select max(id) from voucher));

did casual testing, no more than adding objects to the new migrated
database, and it works.  possibly still something missing, maybe not. 
I'm all ears.

[Mario Frasca]

I opened the corresponding issue.  this helps me make things visible
where I expect to find them, that is where I look for them.

https://github.com/Ghini/ghini.desktop/issues/390

[Mario Frasca]

On 07/30/2018 08:27 AM, Corrado Topi wrote:
> E` solo dopo aver poi migrato il database sqlite a quello postgres
> usando pgloader che cominciano i problemi che ti ho elencato :)

e dopo, hai ovviamente nuovamente eseguito con successo lo script di
aggiornamento utenti (1), e continua a non funzionare ¿?

troppo strano, non so che dire, dovrei vederlo, c'è qualcosa che sfugge.

M

[Corrado Topi]

Aspetta Mario, sto preparando un documento completo su google doc con tutti i dati! Devo aver tempo di fare delle prove e per le prossime due settimane sono un po' impedito .... stiamo lavorando 12 ore al giorno.

--
You received this message because you are subscribed to a topic in the Google Groups "ghini/bauble" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/bauble/60MHqR5dQk0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to bauble+un...@googlegroups.com.

[Mario Frasca]

On Saturday, 28 July 2018 10:38:43 UTC-5, Corrado Topi wrote:

I am trying to migrate from Sqlite to PostgreSQL. I exported the Sqlite database in all possible forms and tried to re import it, but cannot make it work. Help! :)

looks like our user from Australia also had the same trouble, didn't ask for help and solved the issue himself.  he's now sharing experience and solutions.

https://github.com/Ghini/ghini.desktop/issues/390#issuecomment-412001908

Mario