search

Vonng / pigsty

Battery-Included PostgreSQL Distro as a Free RDS Alternative
https://pigsty.io
GNU Affero General Public License v3.0
2.97k stars 241 forks source link

create database in non-default tablespace? #391

Closed SeanHoganSkyHawk closed 2 months ago

SeanHoganSkyHawk commented 2 months ago

Hi, I'm looking for some advice on how to create a database in a tablespace other than pg_default. I'm still in experimentation mode with Pigsty and definitely a newbie with Ansible, so this might just be an understanding gap, but I don't see a way to cause a tablespace to be created after the cluster and before the database. Thanks.

SeanHoganSkyHawk commented 2 months ago

I might have left that question a little too open-ended... I'll say more about the environment. I have a pair of AWS EC2 instances, each with a fairly small root volume and large additional one. It appears that Pigsty wants to create everything in the root volume, but that won't suffice in this case. This is version 2.6.0.

When I specify a database in pigsty.yml I can provide a tablespace to use, but I don't see how anything but pg_default could ever work, since the cluster is created and then immediately the database: there seems to be no opportunity to create the additional tablespace in between. I managed to hack around this by setting it up in pg_default first, then creating the tablespace and altering the database to use it; however, that seems at best very unclean. It also requires stopping and starting pg_exporter for the alter command since otherwise the database is "in use".

Related to this, after running for a while I see the available space on the root volume dropping as /data grows. That makes me wonder how big the root volume needs to be, or can I maybe shift /data onto the additional EBS volume with a symlink? Again it would be necessary to do that in between some of the existing playbooks, and I'm afraid my Ansible-fu is still quite weak.

Vonng commented 2 months ago

To use a dedicate disk, mount them to /data by default (node_data).

To specifiy tablespace for newly created database, add them to database definition. https://github.com/Vonng/pigsty/blob/master/pigsty.yml#L72

To customize tablespace, the proper location is the pg-init script. https://github.com/Vonng/pigsty/blob/master/roles/pgsql/templates/pg-init#L40

You can modify the roles/pgsql/templates/pg-init and overwrite a modified version on templates/pg-init

SeanHoganSkyHawk commented 2 months ago

That makes sense, thanks!