Tablespaces support

[sgotti]

Now stolon doesn't works with tablespaces since it can't know where the tablespaces files will be placed and cannot remove them during resync.

Some possibilities:

• Add a way to define a list of tablespaces dirs in the cluster data
• A preresync script to be executed before pg_basebackup where user can add some cleanup commands.

[smkingsoft]

Maybe preresync script can give user more customization.

[smkingsoft]

@sgotti

Can use simple string array define User TableSpaces dir root eg: stolonctl init '{ "initMode": "new", "pgParameters": {"log_min_duration_statement": "1s" }, "pgUserTbSps": ["/UserData1","UserData2"]}'

The User table space path is /UserData1/UserTb1, /UserData1/UserTb2, /UserData2/UserTb3, /UserData2/UserTb4

When stolon is initialized, the directory '/UserData1' ,'/UserData2' is created

[sgotti]

@smkingsoft Defining a static list of directories is the faster way. The keeper will delete and recreate them when doing a full resync. The admin must update this list before adding new tablespaces in additional directories.

An improvement could be to automatically retrieve them (the keeper will get them and publish them to the cluster data like already done with other information like pg parameters) so the admin shouldn't need to set them manually.

But I think there are some unfixable corner cases caused by the pg tablespaces nature: they can be defined everywhere outside the pg data dir and so stolon won't be able to fully control them.

For example imagine a standby that has a tablespace directory that doesn't exists, has wrong permissions or is filled with other data while on the master it's all ok, if we create a new tablespace on the master it will succeed while the standby will probably (haven't tested it) break since it cannot create the data files.

[smkingsoft]

@sgotti In an online system, it is possible to create a tablespace directory outside the pg data dir. the DBA will set the directory and permissions in advance, the directory will fill only pg data it is his work. My idea is to set the root directory of the tablespace directory, the tabelspace dir is subdir in the root, as long as the root directory does not change, the administrator does not care the tablespace add or del.

[sgotti]

@smkingsoft I think your proposal will add another concept not existing in postgres: tablespaces root, the unique usefulness of it is to avoid specifing all the tablespaces dirs but it'll also put some conditions on how users should define their tablespaces structures.

But,as I proposed above, we could also make stolon automatically determine and report all tablespaces dir and automate this process without adding any tablespaces knowledge in the cluster spec.

But all of this won't help solving the real problem: a user can create a tablespace everywhere and this requires a perfect simmetry on all the nodes, if this doesn't happen (also manual error), the replication will break or in the worst case the data will be lost on a master change.

So, until someone can prove that we can do something in stolon to avoid these problems, I'll happily review PR that could add initial tablespaces support to stolon (preferring the automated tablespace discovery way) but putting a big warning in the documentation saying that tablespaces, if not correctly managed, will break the cluster in bad ways.

[smkingsoft]

@sgotti I also want make stolon automatically determine and report all tablespaces dir. that is great!

[smkingsoft]

hi @sgotti i pull a request auto query tablespaces dir from master, then remove and recreate these dir.

[superboum]

Just a note that Stolon still does not support tablespace. I found this issue after wanting to report it as a bug, because I did not expect that I would break my cluster by adding a tablespace. It might be interesting to inform users very early in the doc that some feature are not supported like tablespace.

If you created a tablespace, you will have the following error when resyncing:

pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING:  skipping special file "./postgresql.auto.conf"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 14BA/1000098 on timeline 4
pg_basebackup: error: directory "/mnt/slow" exists but is not empty
pg_basebackup: removing data directory "/mnt/persist/postgres"
2022-03-09T14:12:27.069Z	ERROR	cmd/keeper.go:1365	failed to resync from followed instance	{"error": "sync error: exit status 1"}
2022-03-09T14:12:32.075Z	ERROR	cmd/keeper.go:1110	db failed to initialize or resync
2022-03-09T14:12:32.080Z	INFO	cmd/keeper.go:1141	current db UID different than cluster data db UID	{"db": "", "cdDB": "c62a96b3"}
2022-03-09T14:12:32.080Z	INFO	cmd/keeper.go:1296	resyncing the database cluster
2022-03-09T14:12:32.103Z	INFO	cmd/keeper.go:1321	database cluster not initialized
2022-03-09T14:12:32.114Z	INFO	cmd/keeper.go:925	syncing from followed db	{"followedDB": "97df6f8f", "keeper": "2f396e97"}
2022-03-09T14:12:32.115Z	INFO	postgresql/postgresql.go:964	running pg_basebackup

A workaround to this issue is to first manually empty your tablespace folder (/mnt/slow), then the resync can happen.

You can deactivate the tablespace from psql too:

postgres=#  \db+
                                   List of tablespaces
    Name    |  Owner   | Location  | Access privileges | Options |  Size   | Description
------------+----------+-----------+-------------------+---------+---------+-------------
 pg_default | postgres |           |                   |         | 8717 MB |
 pg_global  | postgres |           |                   |         | 591 kB  |
 slow       | postgres | /mnt/slow |                   |         | 0 bytes |

postgres=# drop tablespace slow;
DROP TABLESPACE