How to Set Up a Readonly PostgreSQL Database on Dropbox for Multiple Access

Discover how to manage a `readonly` PostgreSQL database on Dropbox, allowing access from multiple devices while adhering to permissions and configuration best practices.
---
This video is based on the question https://stackoverflow.com/q/72723949/ asked by the user 'whdaffer' ( https://stackoverflow.com/u/13000598/ ) and on the answer https://stackoverflow.com/a/72725164/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Readonly postgres database on Dropbox

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Set Up a Readonly PostgreSQL Database on Dropbox for Multiple Access

In today's interconnected world, accessing your data from different devices becomes essential. Whether you are working from a MacBook Pro or an iMac, having a central database that you can read from multiple machines is very convenient. However, setting up a readonly PostgreSQL database in a cloud storage like Dropbox can present unique challenges, especially concerning file permissions and compatibility across devices. This guide will walk you through the pitfalls and solutions to your problem of accessing a readonly PostgreSQL database on Dropbox from various Macs.

The Problem: Accessing the PostgreSQL Database on Multiple Machines

You initiated your PostgreSQL database under /Users/me/Dropbox/dbs on your MacBook Pro, intending to use Dropbox for easy accessibility across several Mac devices. However, you've encountered a lack of visibility of the database directory on other machines, leading to the realization that PostgreSQL's architecture complicates this straightforward use case.

To summarize the issues you're facing:

Your database contents are unavailable on different machines.

Attempting to change ownership to me:staff created accessibility problems for starting PostgreSQL.

The database requires specific user permissions that interfere with Dropbox usage.

Understanding PostgreSQL's Architecture

Before diving into the solution, it's vital to understand how PostgreSQL operates:

Client-Server Architecture: PostgreSQL runs as a server that manages the database while clients connect to it to query or manipulate the data.

Data Directory: This is where the database files reside, and it must be writable by the PostgreSQL server, even for readonly operations.

This means that simply storing your database files in Dropbox doesn't suffice. The server must have the necessary permissions to run effectively, which can disrupt your Dropbox synchronization across machines.

Solution: Setting Up a Readonly PostgreSQL Database

1. Utilize a Single PostgreSQL Server

You only need one instance of the PostgreSQL server running on one machine to manage the database. Instead of trying to access the database files from multiple devices directly, consider the following alternatives:

Run PostgreSQL on a Single Device: Create and maintain your database on one machine (e.g., your MacBook Pro). Use PostgreSQL's built-in functions to allow connection from other devices.

2. Share Database Access Remotely

Instead of trying to run the PostgreSQL server on a shared database directory in Dropbox, adopt one of these strategies:

Local Network Access: If the devices are on the same local network, you can configure PostgreSQL to accept connections from those IP addresses. You’ll need to edit the pg_hba.conf and postgresql.conf files to allow remote connections.

SSH Tunneling: If you prefer to access your database over the internet, set up an SSH tunnel to connect securely to your PostgreSQL server. This method provides robust security while maintaining the ability to access your readonly database.

3. Implementation Steps

Here’s a step-wise approach to implementing the local access solution:

Install and Configure PostgreSQL: Make sure PostgreSQL is properly installed and configured on the main machine where it will run.

Modify Configuration Files:

In postgresql.conf, enable the listening address to accept connections by changing listen_addresses = '*' or specifying the local IP address.

In pg_hba.conf, add an entry to allow local network access to your user role.

Connect from Other Machines: Use a PostgreSQL client tool like pgAdmin or a command line to connect to the database using the IP address of

Видео How to Set Up a Readonly PostgreSQL Database on Dropbox for Multiple Access канала vlogize