Request for Adding service Feature and to a more modular Postgres Installation

Hello,

I would like to suggest an improvement that I believe could benefit numerous users.

Postgres Installation as a Service (systemd): Currently, I do not see an option in the Ansible playbook to install Postgres as a service. This functionality would be useful for those of us who wish to use Postgres as a service without necessarily requiring high availability. Implementing a systemd unit service file for the Postgres installation would allow for easier management and monitoring of the Postgres service.

Modular Installation of Technical Components: I believe it would be beneficial to offer the option of installing the technical components gradually. Here is a proposed installation sequence:

Standalone Postgres, either as a service by default or via pg_ctl.
Pgbackrest.
Patroni + [etcd - consul] as a service by default.
PgBouncer.
HAProxy or VIP.

This modular approach would facilitate wider adoption of this collection by providing users with greater flexibility.

Specific Roles for Each Component: It would be more coherent to have a specific role for each component. For instance, an "Installer Role for Postgres" could be created to clarify responsibilities and facilitate contributions. Currently, it is somewhat difficult to understand where and how Postgres is installed.

User Interface for Installation: To improve the project's readability and accessibility, it might be worth considering a graphical user interface for the installation process. This would make it more accessible to users who are less familiar with command-line interfaces.

Thorough Testing: Prior to implementation, it would be crucial to conduct rigorous testing on the proposed enhancements to identify and resolve any potential issues or conflicts that may arise during installation.

To illustrate my point, I invite you to review Jeff Geerling's role on GitHub, which follows a similar approach: https://github.com/geerlingguy/ansible-role-postgresql/blob/master/defaults/main.yml#L20

I believe these improvements could make the project more user-friendly and easier to use. I would appreciate hearing your thoughts on these suggestions.

Thank you for your time.

Thank you for your detailed feedback and suggestions. We appreciate your keen interest in our project and your constructive insights.

Regarding the standalone installation of Postgres as a service, if that's your primary requirement without High Availability (HA), you might consider using roles tailored specifically for this purpose, such as the ansible-role-postgresql you mentioned.

This project primarily focuses on delivering an out-of-the-box solution for setting up HA clusters based on Patroni. We disable the systemd service for PostgreSQL since it is unnecessary in our case, with the PostgreSQL process being managed by the Patroni service instead. If you need a single Postgres server without HA, you can simply avoid defining replicas in the inventory file, resulting in a "cluster" consisting of a single server, a single instance of Postgres.

Regarding the modular installation of individual components, we view the postgresql_cluster project as a cohesive whole, even though we utilize roles to adhere to Ansible's code organization principles.

As for the User Interface (UI), we have already started exploring this aspect and plan to offer a Software-as-a-Service (SaaS) solution with a subscription model (UPD: postponed), providing a UI for deploying and possibly managing Postgres clusters. \ In essence, this service will cover not just the software installation and cluster configuration but also the creation of servers, disks, networking, etc. Users will have the option to choose from multiple cloud providers to deploy their clusters or even install on any servers in their own data centers or clouds. \ It will be similar to a managed database like RDS but without restrictions with full cluster control (including ssh access)

Concerning thorough testing, we make a concerted effort to ensure this. Molecule tests have already been implemented for each commit, along with daily automatic tests for deploying clusters. Here you can see a table with the results of these tests: https://github.com/vitabaks/postgresql_cluster#compatibility \ And we continue to work on improving them. I will be grateful for any help in providing reliable tests.

We value your suggestions and will consider them in our ongoing work to improve the project. Please feel free to reach out if you have any other ideas or feedback. Thank you once again for your thoughtful input.

@vitabaks Thank you for these clarifications.

I would like to focus on the standalone aspect.

I understand that if we only want to install PostgreSQL, we can rely on the role of Jeff Geerling, for example, but I'm not a fan of this approach.

Allow me to explain: If we invest time and energy in this project, it is not to use another project. This also applies to other users. If an individual, organization, company, etc., wants to invest time and energy in this project, I believe it would be good for the project to be able to meet all use cases and needs.

I fully understand that your goal is to provide a highly available project, and in this context, a simple installation of PostgreSQL without high availability does not align with your vision for this project.

However, I would like to propose, gradually, another vision, albeit very ambitious but achievable: to become the reference for PostgreSQL installation worldwide (I know it sounds very marketing 😆).

Would you at least agree to move in this direction? Of course, my intention is not to increase your workload, and I am even willing to take care of a large part of this aspect ! I just seek your approval.

There would be numerous benefits:

More contributions
Greater visibility
Increased popularity as well
More engagement (we could even seek companies to sponsor this project)
And many more.

At the same time, when you have time, could you explain in a little more detail if a service-based installation of PostgreSQL is compatible with a service-based setup of Patroni?

These are the two topics that interest me.

I don't want to be too intrusive, but my colleagues and I need some reassurance about the effort we are putting into this project.

If I'm not clear, please feel free to ask me to clarify 😇

There would be numerous benefits:

It sounds good, sponsors are really necessary for the project.

Although it is a little confusing why a regular PostgreSQL installation (without replication and HA) can add popularity to the project than everything that is already being offered, in terms of setting up a high availability cluster ready to work. \ But perhaps there is a scenario in which need a well-configured and managed database server (including CI/CD support) without high availability features.

Ok, let's make support for standalone Postgres installation without Patroni as a separate option, if you think it can be useful to users.

At the same time, when you have time, could you explain in a little more detail if a service-based installation of PostgreSQL is compatible with a service-based setup of Patroni?

No, they are not compatible with each other because when the database is running in cluster mode, the postgres systemd service is no longer used and the patroni service starts and stops postgres process.

To implement a Postgres installation with management via systemd, you just need to not perform the patroni role and related roles such as etcd, consul, vip-manager, haproxy, confd, keppelived.

Сreate a postgres role that will be independent and essentially exec initdb on the specified path in the postgresql_data_dir variable and also compatible with all postgresql_xx variables and roles such as postgresql_users, postgresql_databases, postgresql_schemas, postgresql_extensions.

also, it should be separate playbooks something like deploy_postgres.yml and config_postgres.yml.

Hi, I just wanted to weigh in here as I have been looking for a bit for a maintained and comprehensive ansible setup for postgresql.

I agree with Thomas that having a standalone postgresql as an option for this project would help a great deal and make this a go to project for postgresql.

For me, I want to start off with a stand alone setup and then move to cluster later on to scale. I understand this script can work on an existing postgres install, however using other people's script might have integration issues or other things for me to figure out.

A lot of other ansible projects I have seen don't seem to be as well maintained or with as comprehensive features as this one, such as setting up postgresql with timescaledb and also backup. O need to rely on multiple projects to get it.

It would be nice to just have a single source of reference for postgresql setup from a life cycle of standalone to full cluster setup.

This is a fantastic project and is very well written and maintained!

@snapshotleisure Thank you for your feedback!

This is just the beginning, there will be even more opportunities. Our team has extensive experience in PostgreSQL and we are gradually transferring it to this project, automating most of the routine tasks of managing database clusters. So this is already something more than a deployment tool. And I assure you, the project will continue to develop.

And if the project gets more sponsors, then I will be able to devote even more time to it.

vitabaks / postgresql_cluster

Request for Adding service Feature and to a more modular Postgres Installation #416