Re: Highly academic: local etcd & Patroni Cluster for testing on a single host

On 2020/02/26 0:41, Paul Förster wrote:
> Hi,
>
> I have set up an etcd & Patroni cluster on a single machine for testing purposes as follows:
>
> /data/pg01a/db as data directory for the first "node"
> /data/pg01b/db as data directory for the second "node"
>
> I have set up Patroni to make each PostgreSQL database cluster archive to its own destination, so they won't interfere with each other. All is well and working fine so far. Failover/Switchover works, they are syncing properly, etc.
>
> Now:
>
> Inside /data/pg01a and /data/pg01b I also have a directory ts, i.e. /data/pg01a/ts and /data/pg01b/ts to simulate different filesystems.
>
> If I do a 'create tablespace' and specify its location, I must of course specify either /data/pg01a/ts or /data/pg01b/ts.
>
> Files with Tables get created as usual, but since it's a replication (sync, by the way), the other tablespace directory will not hold any files, but instead, the replica uses the same files as does the master.
>
> Is there any way around that, i.e. make the replica use its own files? I found a way to make each archive destination individual despite Patroni, but I can't seem to find a way to do something similar to the tablespaces. Even correcting the symlinks in ${PGDATA}/pg_tblspc manually (and then restart) does not work.
>
> The explanation is simple: the location is stored inside the database cluster information and is per definition the same across all nodes. So, if 'a' is master and I do "create tablespace test location '/data/pg01a/ts'" it creates files there with the replica 'b' using the *same* destination.
>
> Still, is there a way to make each node store that information individually? I know, this is highly academic but I only have a single host to do etcd & Patroni cluster experiments on.

Assuming the standby/replica is created using pg_basebackup, you can use the
-T/--tablespace-mapping option to remap the tablespace directories.

Regards

Ian Barwick

--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services