Обсуждение: Highly academic: local etcd & Patroni Cluster for testing on a singlehost

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
witheach 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
differentfilesystems. 

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
willnot 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
individualdespite Patroni, but I can't seem to find a way to do something similar to the tablespaces. Even correcting
thesymlinks 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
sameacross all nodes. So, if 'a' is master and I do "create tablespace test location '/data/pg01a/ts'" it creates files
therewith 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
havea single host to do etcd & Patroni cluster experiments on. 

Cheers,
Paul

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
interferewith 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
differentfilesystems.
 
> 
> 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
directorywill 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
individualdespite Patroni, but I can't seem to find a way to do something similar to the tablespaces. Even correcting
thesymlinks 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
sameacross all nodes. So, if 'a' is master and I do "create tablespace test location '/data/pg01a/ts'" it creates files
therewith 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
onlyhave 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

Hi Ian,

> On 26. Feb, 2020, at 01:38, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
>
> Assuming the standby/replica is created using pg_basebackup, you can use the
> -T/--tablespace-mapping option to remap the tablespace directories.

no, with Patroni, replicas are always initiated by Patroni. Patroni copies the whole PGDATA including everything
(postgresql.conf,etc.) in it to the replica site. When launching Patroni for the first time, all you need is its yaml
configurationfile and an empty PGDATA. It then will copy the whole master's PGDATA as is, launch the replica database
clusterand start replication. 

Even if Patroni uses pg_basebackup internally (which I assume it does), there is no way to pass parameters to it.

Then you can stop the Patroni process on the replica site which in turn takes the replica database cluster down, make
someconfiguration changes and launch it again. You can of course only make changes to things which don't get replicated
allover again or are managed by Patroni itself. This is, how I set up individual archive destinations for each
replicationmember because the initial archive destination of course is replicated, and thus identical, when Patroni
buildsthe replica database cluster. 

Tablespace mapping just creates the links to the directories in ${PGDATA}/pg_tblspc to a different location. And since
pg_basebackupisn't used, there is no way to do that. But I can do that by hand. That is not the problem. 

The problem is that PostgreSQL keeps the tablespace location inside the database and not in some config file. If the
latterwould be the case then I could just as well set it individually per node as I can with the archive destination. 

So, the tablespace location is always /data/pg01a/ts, even on the replica site where it should be /data/pg01b/ts.
Hence,on my local cluster, the replica site 'b' always uses the tablespace directory (and thus the files) of the master
'a'.

Cheers,
Paul

Hi,

On Wed, 26 Feb 2020 at 08:55, Paul Förster <paul.foerster@gmail.com> wrote:
> no, with Patroni, replicas are always initiated by Patroni. Patroni copies the whole PGDATA including everything
(postgresql.conf,etc.) in it to the replica site. When launching Patroni for the first time, all you need is its yaml
configurationfile and an empty PGDATA. It then will copy the whole master's PGDATA as is, launch the replica database
clusterand start replication. 

That's not correct, Patroni will happily pick up the existing data directory.


> Even if Patroni uses pg_basebackup internally (which I assume it does), there is no way to pass parameters to it.

This is also not correct. One can specify arbitrarily parameters for
pg_basebackup in the Patroni config file:
postgresql:
  basebackup:
    tablespace-mapping: /foo=/bar
    waldir: /my/waldir


Regards,
--
Alexander Kukushkin

On 2020/02/26 16:55, Paul Förster wrote:
> Hi Ian,
> 
>> On 26. Feb, 2020, at 01:38, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
>>
>> Assuming the standby/replica is created using pg_basebackup, you can use the
>> -T/--tablespace-mapping option to remap the tablespace directories.
> 
> no, with Patroni, replicas are always initiated by Patroni. Patroni copies the whole PGDATA including everything
(postgresql.conf,etc.) in it to the replica site. When launching Patroni for the first time, all you need is its yaml
configurationfile and an empty PGDATA. It then will copy the whole master's PGDATA as is, launch the replica database
clusterand start replication.
 
> 
> Even if Patroni uses pg_basebackup internally (which I assume it does), there is no way to pass parameters to it.
> 
> Then you can stop the Patroni process on the replica site which in turn takes the replica database cluster down, make
someconfiguration changes and launch it again. You can of course only make changes to things which don't get replicated
allover again or are managed by Patroni itself. This is, how I set up individual archive destinations for each
replicationmember because the initial archive destination of course is replicated, and thus identical, when Patroni
buildsthe replica database cluster.
 
> 
> Tablespace mapping just creates the links to the directories in ${PGDATA}/pg_tblspc to a different location. And
sincepg_basebackup isn't used, there is no way to do that. But I can do that by hand. That is not the problem.
 
> 
> The problem is that PostgreSQL keeps the tablespace location inside the database and not in some config file. 

It doesn't - it takes the tablespace location directly from the symlink in the "pg_tblspc"
directory (since PostgreSQL 9.2), so you can manipulate those manually, provided the server
isn't running of course.

Not sure how that would fit in with the Patroni side of things.


Regards


Ian Barwick


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

Hi Alexander,

> On 26. Feb, 2020, at 09:19, Alexander Kukushkin <cyberdemn@gmail.com> wrote:
> That's not correct, Patroni will happily pick up the existing data directory.

maybe I didn't express myself correctly. Of course it does. Otherwise replication wouldn't make sense. I meant,
startinga Patroni replica for the first time will copy everything over from the master as it is. 

> This is also not correct. One can specify arbitrarily parameters for
> pg_basebackup in the Patroni config file:
> postgresql:
>  basebackup:
>    tablespace-mapping: /foo=/bar
>    waldir: /my/waldir

I didn't know that. Thanks very much. :-) I'll give it a try.

So, this is a Patroni question and not a PostgreSQL question after all and actually doesn't belong here anyway. Still,
thanksfor the answer. 

Cheers,
Paul

Hi Ian,

> On 26. Feb, 2020, at 09:27, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
> It doesn't - it takes the tablespace location directly from the symlink in the "pg_tblspc"
> directory (since PostgreSQL 9.2), so you can manipulate those manually, provided the server
> isn't running of course.
>
> Not sure how that would fit in with the Patroni side of things.

yes, I know, but with Patroni, instantiating the initial replica is a different thing. Also, when I do the "create
tablespace",I have to manually intervene. And on the replica the \db command shows the paths of the master... 

Cheers,
Paul