Sharing Docker volume w/ SQLite database file between Windows and Linux hosts

I have installed docker version of farmos with sqlite db with following environments
all the files are on my external hard drive, i use 2 computers to use farmos first is windows 10 and other is ubuntu 2204. previously i used to get the synched sqlite db (i was able to see the changes done on windows 10 pc in linux), one day i forgot to docker compose down and shut down the windows pc, since then my changes in windows 10 pc are not seen when i plug that external hard drive to ubuntu pc and run docker compose up,

can someone help me to figure out where is the sqlite db in my windows pc ( the default db in the sites folder is not updated on windows pc, but is updated on ubuntu pc)
my docker compose file reads :

version: ‘3’
services:
www:
# Update this to the latest stable version before deploying.
image: farmos/farmos:2.0.0-beta6
volumes:
- ‘./:/opt/drupal/web/sites’
ports:
- ‘80:80’
restart: always

my settings.php file’s db setting is :

$databases[‘default’][‘default’] = array (
‘database’ => ‘sites/default/files/.ht.sqlite’,
‘prefix’ => ‘’,
‘namespace’ => ‘Drupal\Core\Database\Driver\sqlite’,
‘driver’ => ‘sqlite’,
);

Alternately, I am open to migrate to postgresql too (if sqlite gives issues), need support for migration too :slight_smile:

Can’t say I like the idea of running containers on a removable drive used with 2 different PC’s.

I assume you are running the same docker-compose file from the same directory of the removable drive on both machines?
One would think the same SQLite file would work on both, have you physically found 2 versions of it?
Does Data persist for data created on the Win10 machine, if the container is restarted?

Why use it this way on 2 different machines? It’s a Web server, can’t you just use one machine to host?
I think Postgres would be favorable over SQLite and easier to manage in the long run. Migration should be straight forward enough but make sure to take a backup before trying anything.

maybe worth a read.

Can’t say I like the idea of running containers on a removable drive used with 2 different PC’s.

Agree with @Farmer-Ed this sounds very risky to me. If they were both Linux or both Windows maybe, but it feels like a good way to find an incompatibility issue and lose your data.

Can you set up one of the machines as an always-on server and connect to it from the other one?

Why do you need to move a drive between two?

1 Like

yes, data persisted previously,
the reason for not making one machine server is coz of IT policy of my organization which does not allow for intersubnet access and both machines are on different subnets, besides any one machine cannot be kept ON for 24 hrs as it is shared
i will give a try for data migration, but need support to configure my specific db, any help would be appreciated ( i went thru the stackoverflow post but it overflowed me!)

inorder to take the backup, on windows machine, i copied the whole folder to a different folder and ran the docker compose up, but it was showing readonly (same as linux).

the site from original location in windows machine also is not editable now
can anyone guide me to locate and migrate / backup - restore my self hosted farmos site?

I meant currently, as in is Windows loading the correct SQLite file but its read only?
It sounds like your issue is just permissions.

Have you checked the ownership and permissions of the file/(s)?
using ls -a -l in sites/default/files/ should show the permissions and ownership.

Personally I’d look for another solution as you will likely end up with permissions issues again with different files by using the 2 operating systems.

Why the need for 2 machines? is it one at work and one at home?

Assuming this is your project and not your organizations, here are solutions I think would be better:

  • A virtual machine image (still some risk on a removable drive but permissions would be simpler)
  • Hosted on Farmier (both machines would require Internet Access)
  • Self Host at home on always on low power machine like a Raspberry Pi 4 or NUC (needs internet access at both sites)
  • Get a laptop and bring with you (doesn’t need to be anything fancy)
  • Use a Pi 4 or NUC or Nettop but bring it with you and only borrow screen keyboard and mouse at work (maybe use a simple KVM switch).

chmod is 777

exactly

i prefer docker implementation as it is portable

1 Like

“portable” means the same Docker image can be run on multiple platforms… not the same Docker container or volumes. Maybe it’s possible in theory, but I just want to point out that this is not what is meant by portability, and this is probably not a recommended usage of Docker. So you are taking risks by doing this @perfectinfo - just want to make sure that you are aware and don’t blame us if something goes wrong. :wink:

All of the solutions @Farmer-Ed described above would be safer.

I’ve updated the title of this forum post to make it more explicitly about this unusual strategy.

I think Postgres would be favorable over SQLite and easier to manage in the long run. Migration should be straight forward enough but make sure to take a backup before trying anything.

Agreed here as well. SQLite works but the performance is going to be much lower than PostgreSQL.

I have never done a migration from SQLite to PostgreSQL, so I can’t provide any support with this. It’s more of a general database question than a farmOS-specific one, so you could also try other forums.

I will say that I tried to help @Skipper migrate from PostgreSQL to MySQL on farmOS v1 and learned a lot about how difficult it is to go from one SQL database type to another. We ended up giving up. Maybe you’ll have better luck.

Confirmed with ls -al? at least one of your OS’s seams to disagree if its loading the database as read only.

I have, with your migration tool from SQLite on1.x to PostgresSQL on 2.x, It went without much issue, although admittedly there was not much in the database as I’d only just started using farmOS a short while when you’d announced that 2.x was now the preferred installation. It hardly works from 2.x to 2.x?

Otherwise pgloader mentioned in the stakoverflow link above. GitHub - dimitri/pgloader: Migrate to PostgreSQL in a single command!

For example, for a full migration from SQLite:

$ createdb newdb
$ pgloader ./test/sqlite/sqlite.db postgresql:///newdb
1 Like

Notably, this is not really a direct SQLite-to-PostgreSQL migration, but rather a Drupal 7 to Drupal 9 migration. Drupal provides a database abstraction layer, so the farmOS v1->v2 migration uses that to read records into PHP (from whatever db they are in), translate them to v2 records, then save them to the new db (whatever that is).

We don’t have any way to migrate v2 SQLite to v2 PostgreSQL that uses the Drupal database abstraction layer. So it will be a much lower-level process working directly with SQL queries.

1 Like

I figured that would more or less be the answer, I think pgloader will be the simplest solution.
But at @perfectinfo’s own risk and backups before trying anything.

1 Like

after creating pqsql user and db named farm, i tried, but getting following errors

farm@linux:~$ pgloader /media/admin/farmos/sqlite2pgsql/farmos/default/files/.ht.sqlite postgresql:///farm
FATAL: Failed to start the monitor thread.

Error opening #P"/tmp/pgloader/pgloader.log": Permission denied

permissions of /tmp are 777
farm@linux:~$ ls -la / | grep tmp
drwxrwxrwt 16 root root 4096 Oct 21 14:50 tmp

any idea how to tackle this?

Not much, you’ll probably need to seek help in a more SQL orientated forum on migrating from SQLite to Postgres. Maybe open an issue on pgloader github issues, or look at alternative migration tools.

I assume you ran it as a super user su / sudo ?
permission of /tmp does not necessarily mean the permissions of all files and subfolders are the same.
chmod -R 777 /tmp would apply the same permissions to every file and subfolder.
use of -R can be useful and dangerous consider what other files and sub folders would be effected

If the drive is flash/solid state I’ve seen disk failures which make files appear read-only.

i was able to fix permissions, but the pgloader could import with some errors.

2022-10-21T11:38:36.667998Z LOG report summary reset
table name errors rows bytes total time


                              fetch          0          0                     0.000s
                    fetch meta data          0        770                     0.592s
                     Create Schemas          0          0                     0.020s
                   Create SQL Types          0          0                     0.012s
                      Create tables          0        324                     8.200s
                     Set Table OIDs          0        162                     0.028s

                             config          1          0                     0.164s
                          sequences          0          1     0.0 kB          0.508s
                          key_value          1          0                     0.444s
                           sessions          1          0                     0.248s
                              users          0          2     0.1 kB          0.332s
                         users_data          0          0                     0.336s
                        user__roles          0          0                     0.252s
                   users_field_data          0          2     0.2 kB          0.332s
                              batch          0          0                     0.336s
                              queue          0          0                     0.256s
                path_alias_revision          0          0                     0.336s
                         path_alias          0          0                     0.468s
                         file_usage          0          1     0.0 kB          0.140s
                             router          1          0                     0.340s
                           watchdog          1          0                     0.472s
                           consumer          0          2     0.1 kB          0.176s
                    consumer__roles          0          0                     0.336s
                       file_managed          0          1     0.2 kB          0.384s
               oauth2_token__scopes          0          0                     0.252s
                          menu_tree          1          0                     0.332s
        consumer__grant_user_access          0          2     0.0 kB          0.372s
                consumer_field_data          0          2     0.2 kB          0.260s
        consumer__limit_user_access          0          2     0.0 kB          0.332s
                       oauth2_token          0          0                     0.340s
                 taxonomy_term_data          0          0                     0.252s
                consumer__client_id          0          2     0.1 kB          0.340s
           taxonomy_term_field_data          0          0                     0.344s
   consumer__limit_requested_access          0          2     0.0 kB          0.268s
                     taxonomy_index          0          0                     0.336s
          consumer__allowed_origins          0          1     0.0 kB          0.344s
     taxonomy_term_revision__parent          0          0                     0.252s
             taxonomy_term_revision          0          0                     0.332s
                     asset_revision          0          4     0.1 kB          0.340s
       taxonomy_term_field_revision          0          0                     0.256s
               asset_field_revision          0          4     0.3 kB          0.336s
              taxonomy_term__parent          0          0                     0.464s
               asset_revision__flag          0          8     0.2 kB          0.152s
             asset_revision__id_tag          0          1     0.0 kB          0.344s
                              asset          0          3     0.2 kB          0.472s
                       log_revision          0          1     0.0 kB          0.136s
                 log_field_revision          0          1     0.1 kB          0.344s
                   asset_field_data          0          3     0.2 kB          0.344s
                 log_revision__flag          0          0                     0.256s
                        asset__flag          0          6     0.2 kB          0.340s
                log_revision__asset          0          1     0.0 kB          0.340s
                      asset__id_tag          0          1     0.0 kB          0.296s
 asset_revision__intrinsic_geometry          0          0                     0.340s
                                log          0          1     0.1 kB          0.528s
             log_revision__location          0          0                     0.124s
             log_revision__geometry          0          0                     0.336s
                     log_field_data          0          1     0.1 kB          0.544s
                  quantity_revision          0          0                     0.144s
             log_revision__quantity          0          0                     0.328s
                          log__flag          0          0                     0.332s
                log_revision__owner          0          1     0.0 kB          0.272s
                         log__asset          0          1     0.0 kB          0.376s
               asset_revision__file          0          0                     0.468s
          asset__intrinsic_geometry          0          0                     0.132s
                      log__location          0          0                     0.332s
              asset_revision__image          0          0                     0.332s
                      log__geometry          0          0                     0.248s
             asset_revision__parent          0          1     0.0 kB          0.376s
                           quantity          0          0                     0.336s
             log_revision__category          0          0                     0.256s
                      log__quantity          0          0                     0.336s
                 log_revision__file          0          0                     0.340s
                         log__owner          0          1     0.0 kB          0.288s
                log_revision__image          0          1     0.0 kB          0.336s
                        asset__file          0          0                     0.336s
          asset_revision__land_type          0          2     0.0 kB          0.260s
                       asset__image          0          0                     0.332s

taxonomy_term_revision__transplant_days 0 0 0.456s
asset__parent 0 1 0.0 kB 0.128s
log__category 0 0 0.332s
taxonomy_term_revision__maturity_days 0 0 0.336s
log__file 0 0 0.252s
taxonomy_term_revision__image 0 0 0.332s
log__image 0 1 0.0 kB 0.340s
taxonomy_term_revision__crop_family 0 0 0.248s
asset__land_type 0 1 0.0 kB 0.336s
taxonomy_term_revision__companions 0 0 0.340s
taxonomy_term__transplant_days 0 0 0.256s
asset_revision__plant_type 0 0 0.340s
taxonomy_term__maturity_days 0 0 0.460s
asset_revision__season 0 0 0.128s
asset_revision__animal_type 0 0 0.332s
taxonomy_term__image 0 0 0.336s
asset_revision__birthdate 0 0 0.500s
taxonomy_term__crop_family 0 0 0.584s
asset_revision__is_castrated 0 0 0.340s
taxonomy_term__companions 0 0 0.260s
asset_revision__nickname 0 0 0.336s
asset__plant_type 0 0 0.340s
asset_revision__sex 0 0 0.256s
asset__season 0 0 0.332s
asset_revision__manufacturer 0 0 0.332s
asset__animal_type 0 0 0.252s
asset_revision__model 0 0 0.336s
asset__birthdate 0 0 0.464s
asset_revision__serial_number 0 0 0.124s
asset_revision__structure_type 0 2 0.1 kB 0.384s
asset__is_castrated 0 0 0.348s
log_revision__equipment 0 0 0.252s
asset__nickname 0 0 0.332s
log_revision__lot_number 0 0 0.344s
asset__sex 0 0 0.256s
log_revision__purchase_date 0 0 0.336s
asset__manufacturer 0 0 0.340s
log_revision__source 0 0 0.256s
asset__model 0 0 0.336s
log_revision__method 0 0 0.340s
asset__serial_number 0 0 0.268s
quantity_revision__material_type 0 0 0.344s
asset__structure_type 0 2 0.1 kB 0.344s
asset_revision__material_type 0 0 0.264s
log__equipment 0 0 0.340s
data_stream 0 0 0.348s
log__lot_number 0 0 0.272s
data_stream__asset 0 0 0.344s
log__purchase_date 0 0 0.344s
asset_revision__data_stream 0 0 0.260s
log__source 0 0 0.336s
asset_revision__private_key 0 0 0.476s
log__method 0 0 0.140s
quantity__material_type 0 0 0.340s
asset_revision__public 0 0 0.476s
asset__material_type 0 0 0.136s
data_stream_basic 0 0 0.340s
log_revision__group 0 0 0.352s
data_stream_data 0 0 0.268s
log_revision__lab 0 0 0.340s
asset__data_stream 0 0 0.348s
log_revision__lab_test_type 0 0 0.264s
asset__private_key 0 0 0.336s
log_revision__mother 0 0 0.344s
asset__public 0 0 0.268s
log_revision__vet 0 0 0.340s
log__group 0 0 0.484s
locales_source 0 54 2.1 kB 0.144s
locales_location 0 59 3.1 kB 0.440s
log__lab 0 0 0.344s
key_value_expire 1 0 0.272s
log__lab_test_type 0 0 0.344s
cachetags 0 40 0.9 kB 0.416s
log__mother 0 0 0.264s
cache_config 1 0 0.340s
log__vet 0 0 0.720s
cache_default 1 0 0.688s
cache_container 1 0 0.388s
cache_menu 1 0 0.480s
locales_target 0 0 0.144s
locale_file 0 0 0.336s
cache_entity 1 0 0.348s
cache_bootstrap 1 0 0.276s
cache_rest 0 0 0.344s
cache_discovery 1 0 0.672s
flood 0 3 0.2 kB 0.160s
cache_data 1 0 0.340s
semaphore 0 0 0.324s
cache_render 1 0 0.328s
cache_jsonapi_normalizations 0 0 0.440s
cache_toolbar 0 0 0.124s


            COPY Threads Completion          0          4                    26.868s
                     Create Indexes          8        600                  1m14.620s
             Index Build Completion          0        608                     0.124s
                    Reset Sequences          0         14                     0.116s
                       Primary Keys          0         54                     0.164s
                Create Foreign Keys          0          0                     0.000s
                    Create Triggers          0          0                     0.000s
                   Install Comments          0          0                     0.000s

                  Total import time         16        225     9.0 kB       1m41.892s

can someone let me know what am i doing wrong? am i supposed to create a dump of the file sqlite db and then import? ( i am importing the db directly as mentioned in the quote)

i am creating a new pgsql DB and punching all the data from scratch now as suggested by you all (running docker on only one PC, nothing portable)

BUT

am still skeptical for data loss as i am still not aware of the backup and restore process.

any step by step guide for data backup and restore would be appreciated ( at least i can take regular backups and will be sure that i can restore it as and when required !)

1 Like

This one popped up at the top of a “how to backup and restore postgresql database” internet search: How to Backup and Restore a PostgreSQL Database

Maybe there are others more relevant to your specific setup.

I use pg_dump to make dumps and and psql to import, FWIW. I also use continuous archiving on Farmier but that’s probably more than you need.

Thank you for the reply, but as I am deploying pgsql docker container, need a specific advice in accordance to farmos (the entire site SHOULD look like it was before backup)

could you pl let me know the specific commands with which I will get the exact same farmos site after restore?

This works for me:

Export DB
sudo docker exec -it farmos_www_1 /bin/bash
drush cr
drush sql-dump > farm.sql

Import DB
drush sql-drop
drush sqlc < farm.sql

Also Needed
Copy all folders which are bind mounts in your docker-compose file

eg.
- ‘./www/web/sites:/opt/drupal/web/sites’
- ‘./www/web/modules/custom:/opt/drupal/web/modules/custom’

All modules currently installed in your current site will need to be installed in a restored version before it will function correctly.