Hi
On Fri, Jul 10, 2020 at 8:23 AM Kimball Johnson <kimball.john...@engagile.io>
wrote:
> Hi,
>
> I’m using PostgreSQL 10 on a mac pro running macOS Mojave. My version info
> for PGAdmin4 is provided below.
>
> I installed EDB standard PostgreSQL version 10 on the mac pro as I have
> done many times before. I’m going to be using this implementation to
> perform ETL, List Management, GIS operations using Tiger and OSM, so I’m
> isolating the operations for each of those processes using Tablespaces. And
> I’m performing the configuration of the tablespaces using the PGAdmin4 web
> tool in chrome.
>
> I added a larger group of tablespaces using the PGAdmin4 tool over a TCP
> connection to a Ubuntu server on my network with no problems similar to
> this. In that case, only the connected PostgreSQL configuration was
> affected. The database on the connected Ubuntu system is a separate
> installation of PostgreSQL, whose role is to support the Pentaho BA Server
> and its PostgreSQL DB repository. And the new tablespaces created in that
> connected system’s LVM disks seem to act in a reasonable manner and do not
> display this duplication.
>
> However, in addition to the connected Server on the Ubuntu machine, I have
> three local server instances showing in the PGAdmin interface on the mac
> pro. These are the main local PostgreSQL server instance, and two other
> local server instances (ETL and GIS) that isolate development from the
> Ubuntu system and the local Server. All three of these local server
> instances are looking at the same PostgreSQL installation and PGData folder
> on the Mac Pro.
>
> When I add tablespaces to any of these three local server instances, each
> of the local server instances gets a copy of each of the new tablespaces.
> These become visible in the PGAdmin4 Tablespace folder of the server
> instance. This outcome is puzzling because the PostgreSQL 10 docs state
> that Tablespaces must be unique across the Database Cluster.
>
Right, but you said "All three of these local server instances are looking
at the same PostgreSQL installation and PGData folder on the Mac Pro.". So
yes, changes made by one server process will affect the other processes
because they're pointing at the same data directory (aka database cluster).
That said, having multiple server instances sharing the same data directory
is going to lead to data corruption very, very quickly. Though I'll admit
I'm puzzled as to how you did it, as PostgreSQL uses various locking
techniques specifically to prevent you shooting yourself in the foot like
that.
> I have found no information indicating that Postgres might elect scope the
> Tablespace names by Server instance. But I’m not even sure whether the
> ‘Server’ concept is a PostgreSQL concept or a PGAdmin4 concept. So, at this
> point, I can’t determine whether this is a PostgreSQL misunderstanding or a
> PGAdmin4 issue. My reading of PostgreSQL docs indicates that a Cluster
> encompasses Databases, not Servers, but maybe I haven’t run across the
> distinguishing facts on that.
>
A "Server" in pgAdmin is metadata that allows it to connect to a Postgres
instance. You can have multiple servers pointing at the same Postgres
instance, for example if you need to use different roles to connect
regularly.
A Postgres instance is an invocation of the database server software which
*must* have a unique data directory. It also must have a unique port number
on which it listens (unless different instances are bound only to different
network adaptors and are using different directories for the unix domain
socket.
> Nonetheless, this duplication is alarming. Does any of this sound familiar?
>
Nope. And from what you've described, I don't believe the setup you have
("All three of these local server instances are looking at the same
PostgreSQL installation and PGData folder on the Mac Pro.") is even
possible. I suspect what you have is a single Postgres instance with a
single data directory, and multiple servers configured in pgAdmin that are
all pointing to that same Postgres instance - which would entirely explain
why tablespaces appear on all servers when created on one.
> Thanks,
>
> Kimball Johnson
>
> Product Manager at Engagile.io
>
>
>
> *Version*
>
> 3.1
>
Yikes! Please upgrade to something less prehistoric. There have been *29*
releases of pgAdmin since then, some of which include critical bug fixes.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EDB: http://www.enterprisedb.com
