Sharing PostgreSQL database on one dual-boot machine without duplication (cross-platform)

Question

I have PostgreSQL 9.4 installed on a dual-boot machine (both on Win 7 and Ubuntu 14.10). The two systems share a drive (NTFS-formated) which includes a PSQL database directory which was created under Windows. Obviously the PSQL installations are system-specific, and they also happen to be on different partitions. The drive is automatically mounted under Linux and given full read/write/execute permissions.

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa.

Is this possible, and if so, how would one set this up from Linux assuming all the data was originally generated under Windows?

EDIT: I tried running:

sudo pg_dropcluster --stop 9.4 main

followed by

sudo pg_createcluster -d /db_from_windows_path 9.4 main

which appeared to work, but then when starting up the server again, I get:

sudo /etc/init.d/postgresql start 
 * Starting PostgreSQL 9.4 database server
 * Error: could not exec /usr/lib/postgresql/9.4/bin/pg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl start -D /db_from_windows_path -s -o  -c config_file="/etc/postgresql/9.4/main/postgresql.conf" :
 [fail]

It seems extremely cryptic, especially since /var/log/postgresql/postgresql-9.4-main.log is completely empty, and so is the rest of the /var/log/postgresql/ directory

EDIT 2: After some looking about, I noticed that the chown hadn't taken because NTFS systems can apparently only be set at mount time. After fixing the /etc/fstab to give ownership to postgres, I get a different error: The PostgreSQL server failed to start. Please check the log output. Which, again, resulted in no log, so I ran:

/usr/lib/postgresql/9.4//bin/postgres -d 5 -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf

which gave this:

LOG:  invalid value for parameter "dynamic_shared_memory_type": "windows"
HINT:  Available values: posix, sysv, mmap, none.
2015-04-17 12:38:43 BST [3653-3] LOG:  invalid value for parameter "lc_messages": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-4] LOG:  invalid value for parameter "lc_monetary": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-5] LOG:  invalid value for parameter "lc_numeric": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-6] LOG:  invalid value for parameter "lc_time": "English_United Kingdom.1252"
2015-04-17 12:38:43 BST [3653-7] FATAL:  configuration file "/etc/postgresql/9.4/main/postgresql.conf" contains errors
2015-04-17 12:38:43 BST [3653-8] DEBUG:  shmem_exit(1): 0 before_shmem_exit callbacks to make
2015-04-17 12:38:43 BST [3653-9] DEBUG:  shmem_exit(1): 0 on_shmem_exit callbacks to make
2015-04-17 12:38:43 BST [3653-10] DEBUG:  proc_exit(1): 0 callbacks to make
2015-04-17 12:38:43 BST [3653-11] DEBUG:  exit(1)

This seems like a different problem altogether, perhaps POSIX-related?

Answer 1

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa.

You can't. Just not supported, not possible. No can do. PostgreSQL does not attempt to make the data directory portable across architectures, operating systems, etc.

The only way you can do this is to run a Windows virtual machine inside Ubuntu when you want to access a Windows data directory, or a Linux virtual machine inside Windows when you want to access a Linux data directory.

You might want to run a VM image that's shared between both platforms, and have it contain the database - but be careful, Linux's NTFS driver isn't great for performance, and might not have all the wrinkles ironed out when it comes to concurrent I/O so I'd be a little leery running something like a VM image off it. FAT32 and exFAT are not crash-safe. So there aren't really any good choices for a file system both systems can share to run "real work" off.

So personally... I'd just have a small separate machine. Or I'd keep a dump of the DB and reload it when I rebooted.

(Additionally, if running PostgreSQL under Linux on Linux's NTFS file system driver works, that's a little surprising, and I certainly wouldn't rely on it for anything I cared about).