FWD » Core Development » Database

We need a tool to standardize the method by which a PostgreSQL cluster is created. It needs to be able to function both inside a Docker container, and in a more traditional install of PostgreSQL.

It should avoid pg_createcluster as that creates clusters files scattered across /etc/postgresql and /var/lib/postgresql/<version>/<name>, which the Docker image won’t recognize. Rather, it should just use initdb.

It should create the standard FWD roles of fwd_admin and fwd_user. Passwords should be passed on the command line, and default to admin and user, respectively.

• scram-sha-256 (default)
• md5
• password
• trust

The cluster's directory location is specified in a parameter, and defaults to /var/lib/postgresql/data

The locale is specified in a parameter, and defaults to en_US.UTF-8

The encoding is specified in a parameter, and defaults to UTF8.

The permissions and ownership (postgres:postgres) need to be set properly.

postgresql.conf and pg_hba.conf should be updated for both the requested authorization method, TCP/IP access, and other security items.

With respect to postgresql.conf, our current Docker implementation makes the below changes, which essentially uncomment the lines in the file, and make sure '*' is the listen_addresses value:

   sed -i -e 's/^\s*#\(\s*log_destination\)\( *=\)\(.*\)$/\1\2\3/g' \
          -e 's/^\s*#\(\s*logging_collector\)\( *=\)\(.*\)$/\1\2\3/g' \
          -e 's/^\s*#\(\s*log_directory\)\( *=\)\(.*\)$/\1\2\3/g' \
          -e 's/^\s*#\(\s*log_filename\)\( *=\)\(.*\)$/\1\2\3/g' \
          -e 's/^\s*#\(\s*log_file_mode\)\( *=\)\(.*\)$/\1\2\3/g' \
          -e 's/^\s*#\(\s*log_rotation_age\)\( *=\)\(.*\)$/\1\2\3/g' \
          -e 's/^\s*#\(\s*log_rotation_size\)\( *=\)\(.*\)$/\1\2\3/g' \
          ${PGDATA}/postgresql.conf
   sed -i -e 's/^\s*#\s*\(listen_addresses\s*=\s*\).*$/\1'\''*'\''/g' \
          ${PGDATA}/postgresql.conf

• the local all all trust line to local all all password
• the host all all 127.0.0.1/32 trust line to host all all 127.0.0.1/32 password
• the host all all ::1/128 trust line to host all all ::1/128 password
• the local replication all trust line to local replication all trust
• the host replication all 127.0.0.1/32 trust line to host replication all 127.0.0.1/32 md5
• the host replication all ::1/128 trust line to host replication all ::1/128 md5
• Add the host all all all $pgauth line to the end, with the given authentication method scram-sha-256 | md5 | password | trust.

It uses these sed scripts:

   sed -i -e 's/\(^local\s\+all\s\+all\s\+\)trust$/\1password/g' \
          -e 's/\(^host\s\+all\s\+all\s\+127\.0\.0\.1\/32\s\+\)trust$/\1password/g' \
          -e 's/\(^host\s\+all\s\+all\s\+\:\:1\/128\s\+\)trust$/\1password/g' \
          -e 's/\(^local\s\+replication\s\+all\s\+\)trust$/\1trust/g' \
          -e 's/\(^host\s\+replication\s\+all\s\+127\.0\.0\.1\/32\s\+\)trust$/\1md5/g' \
          -e 's/\(^host\s\+replication\s\+all\s\+\:\:1\/128\s\+\)trust$/\1md5/g' \
           ${PGDATA}/pg_hba.conf
   echo "host all all all $pgauth" >> ${PGDATA}/pg_hba.conf

Should I leave it like that, or allow each current line to have the specified authorization method, instead of the above "forced" values.

Any other input would be appreciated.

Added watchers.

Should I leave it like that, or allow each current line to have the specified authorization method, instead of the above "forced" values.

We need to know how our various customers configure, to understand if this is OK. Please do an inventory of each of the PostgreSQL customers and document the results.

This must be able to fully replace the cluster initialization in build_db.xml. In other words, we will eliminate that processing and call this standard script.

Also, this should be designed to be extended to MariaDB and SQLServer.

Greg,
The build_db.xml might not be the best place for configuring the cluster. This is a one-time operation (only if it's idempotent so it can be applied multiple times). Or with a very specific ant task?

I am not sure SQLServer instances (pretty similar to clusters) can be administered from command line. They have the user interface. But I might be wrong.

I am not suggesting that we expand use of build_db.xml. The opposite, I want everything in there to be using tools that are built into FWD itself. build_db.xml can be a lightweight helper to execute things but it should not have any real processing that is needed for managing a FWD install.

I thought we had automated the cluster initialization. The fact that we haven't is a problem for me. We don't want every customer having to figure out how to automate critical steps in our installation/setup process. The fact that ithas been a manual thing to this day is not good. Yes, it happens only once on a given system. It still is not something that should be a manual process.

We will take Roger's docker approach and generalize it for non-docker cases.

Greg Shah wrote:

I thought we had automated the cluster initialization. The fact that we haven't is a problem for me. We don't want every customer having to figure out how to automate critical steps in our installation/setup process. The fact that ithas been a manual thing to this day is not good. Yes, it happens only once on a given system. It still is not something that should be a manual process.

We will take Roger's docker approach and generalize it for non-docker cases.

It's difficult to tell how any other projects create their clusters, since it isn't part of any current scripting. It is quite possible that the main cluster that is created by a default installation of PostGreSQL is all that is ever used for basic installation. If there are special language requirements, that's when a new cluster would be in order. The Database Server Setup for PostgreSQL on Linux and Database Server Setup for PostgreSQL on Windows wikis might be considered the current "bibles". From this, I gather that md5 was the preferred authorization method at the time of writing. At present, choices can be: scram-sha-256, md5, password, and trust. From what I gather, the extent of the configuration choices we care about are the location of the cluster, the language, the locale, and the authorization method. There are tons of other configuration values, but do we give them simply a few choices, and they can manipulate configuration files as they wish?

Should the tool have a PowerShell equivalent for native Windows use? I am not sure a WSL solution would work if you truly wanted your DB to be native Windows. On that note, SQL Server's concept of an "instance" is very different from a cluster. How do we address this? I have heard of a Docker instance of SQL Server. Do we include that, as well?

It's difficult to tell how any other projects create their clusters, since it isn't part of any current scripting. It is quite possible that the main cluster that is created by a default installation of PostGreSQL is all that is ever used for basic installation.

No, I don't think this is likely. Other than those environments using UTF-8, setting up the custom locale is very important. Otherwise the database sorting is borked.

If there are special language requirements, that's when a new cluster would be in order.

No, it is not just about languages. It is about the codepage itself.

The Database Server Setup for PostgreSQL on Linux and Database Server Setup for PostgreSQL on Windows wikis might be considered the current "bibles".

I worry that these are out of date.

From this, I gather that md5 was the preferred authorization method at the time of writing. At present, choices can be: scram-sha-256, md5, password, and trust. From what I gather, the extent of the configuration choices we care about are the location of the cluster, the language, the locale, and the authorization method. There are tons of other configuration values, but do we give them simply a few choices, and they can manipulate configuration files as they wish?

We need Ovidiu, Eric or Alex to answer this.

Should the tool have a PowerShell equivalent for native Windows use?

Yes

On that note, SQL Server's concept of an "instance" is very different from a cluster. How do we address this?

Discuss this with Ovidiu and Eric.

I have heard of a Docker instance of SQL Server. Do we include that, as well?

Maybe at some point but let's not blow up the scope of this task right now.

Greg Shah wrote:

From this, I gather that md5 was the preferred authorization method at the time of writing. At present, choices can be: scram-sha-256, md5, password, and trust. From what I gather, the extent of the configuration choices we care about are the location of the cluster, the language, the locale, and the authorization method. There are tons of other configuration values, but do we give them simply a few choices, and they can manipulate configuration files as they wish?

We need Ovidiu, Eric or Alex to answer this.

• trust: I do not think we can recommend this, From what I understand, this allows users to connect without a password;
• password is to be avoided since it sends the plain test over the 'wire' and can be potentially sniffed.
• md5 and scram-sha-256 are pretty much similar: instead the plain password, the hash is used. Evidently, the latter is much more secure the the former at the expense of a few milliseconds at the moment a new connection is created.

On that note, SQL Server's concept of an "instance" is very different from a cluster. How do we address this?

Discuss this with Ovidiu and Eric.

• https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt

But I never tried.

Revision 85 of the container project contains updates in preparation for PowerShell support. I broke a portion of the SQL code out into its own file, so it could be used by both bash and PS. The current usage is:

Usage: ./base_ubuntu/repo/fwd_create_pg_cluster.sh [options]

Creates a PostgreSQL cluster with upstream initdb, compatible with the official
postgres Docker image. Safe to run on Ubuntu (host) or in an Ubuntu-based container.

Options:
  --pgdata DIR                 Data directory (default: /var/lib/postgresql/data)
  --pgport PORT                Conection port (default: 5342)
  --locale LOCALE              Cluster locale (default: en_US.UTF-8)
  --encoding ENC               Cluster encoding (default: UTF8)
  --auth METHOD                pg_hba auth method: scram-sha-256 | md5 | password | trust
                               (default: scram-sha-256)

  --postgres-password PASS     Initial password for postgres superuser
  --admin-user NAME            Admin role name (default: fwd_admin)
  --admin-pass PASS            Admin role password
  --user-user NAME             Standard role name (default: fwd_user)
  --user-pass PASS             Standard role password

  --temp-port PORT             Temporary port for bootstrap (default: 55432)
  --socket-dir DIR             Unix socket dir for bootstrap (default: /tmp)
  --rolefile-path FILE         Path to create_roles.sql file (default: .)

Examples:
  ./base_ubuntu/repo/fwd_create_pg_cluster.sh --pgdata /data/pg17 --locale en_US.UTF-8 --encoding UTF8 \
     --pgport 5433
     --auth scram-sha-256 \
     --postgres-password adminpw \
     --admin-user app_admin --admin-pass S3cret! \
     --user-user app_user --user-pass userpw

Notes:
- Uses initdb (NOT pg_createcluster), so the layout matches postgres Docker images.
- Ensures correct ownership and permissions on PGDATA.

Ovidiu, the default authentication is scram-sha-256.

Roger Borrello wrote:

I added functionality so that the cluster can be controlled properly with systemctl.

TBD: In order to control multiple clusters, I will need to update the /usr/lib/systemd/system/postgresql.service file, as it is a meta unit for controlling the clusters. The [Unit] section needs to have:
[...]
Where each <cluster name> is unique.

In a docker container, we don't have systemctl available to us. However, I can start each cluster with:

/usr/lib/postgresql/17/bin/pg_ctl -D /opt/db/<cluster name> -w start

I will make fwd_create_pg_cluster.sh aware of whether it is in a docker container or not. Also, the starting of the cluster before finishing will be an option, like --start_cluster so after it is created, it is started.

Roger Borrello wrote:

I added functionality so that the cluster can be controlled properly with systemctl.

TBD: In order to control multiple clusters, I will need to update the /usr/lib/systemd/system/postgresql.service file, as it is a meta unit for controlling the clusters. The [Unit] section needs to have:
[...]
Where each <cluster name> is unique.

I opted not to use services. It is much easier just to perform pg_ctl -D....

I have a proposed change so that max_connections can be updated from the default of 100. It would involve adding --max-connections VALUE option. The change would be to update the postgresql.conf file:

run_as_postgres sed -i -e 's/^\s*#\(\s*log_destination\)\( *=\)\(.*\)$/\1\2\3/g' \
                       -e 's/^\s*#\(\s*logging_collector\)\( *=\)\(.*\)$/\1\2\3/g' \
                       -e 's/^\s*#\(\s*log_directory\)\( *=\)\(.*\)$/\1\2\3/g' \
                       -e 's/^\s*#\(\s*log_filename\)\( *=\)\(.*\)$/\1\2\3/g' \
                       -e 's/^\s*#\(\s*log_file_mode\)\( *=\)\(.*\)$/\1\2\3/g' \
                       -e 's/^\s*#\(\s*log_rotation_age\)\( *=\)\(.*\)$/\1\2\3/g' \
                       -e 's/^\s*#\(\s*log_rotation_size\)\( *=\)\(.*\)$/\1\2\3/g' \
                       -e "s/^\s*#\(\s*max_connections\)\( *=\)\(.*\)$/\1\2 ${MAX_CONNECTIONS}/g" \
                       -e "s/^\s*#\(\s*port\)\( *=\)\(.*\)$/\1\2 ${PGPORT}/g" \
                "$POSTGRESQL_CONF" 

Ovidiu, I have been seeing the below after the server connects to the DB. Do you know where this function comes from?

cact_db       | 2026-03-05 17:38:40.956 EST [220] ERROR:  function getfwdversion_1() does not exist at character 8
cact_db       | 2026-03-05 17:38:40.956 EST [220] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
cact_db       | 2026-03-05 17:38:40.956 EST [220] STATEMENT:  SELECT getFWDVersion_1()

Roger,
I remember seeing that warning. I did not investigate what is the exact cause, but my guess is that it happens at the time the secondary databases (dirty/meta) are initialized. If this is problem, I can investigate its cause.

Greg,
I've written a script for creating PSQL clusters (and copy the database), but that assumes a lot of parameters to be a priori chosen. This script is at a higher level. I started the review, but it will take a bit for me to fully understand it. It is an opportunity for me to learn from the expert :).

Ovidiu Maxiniuc wrote:

Roger,
I remember seeing that warning. I did not investigate what is the exact cause, but my guess is that it happens at the time the secondary databases (dirty/meta) are initialized. If this is problem, I can investigate its cause.

I don't really know that it's a problem. It just doesn't look good to me. I haven't seen any functional failures.

Greg,
I've written a script for creating PSQL clusters (and copy the database), but that assumes a lot of parameters to be a priori chosen. This script is at a higher level. I started the review, but it will take a bit for me to fully understand it. It is an opportunity for me to learn from the expert :).

Well this tool is now in a new branch 10491a for review. I wouldn't consider this anything of an expert design :-) What I would say is I've done a lot of examination of the scripts that PostGres used in their Docker image for creation of the cluster, and thought about how we use PG from our documentation, and had enough info pounded into me by Greg to come up with this tool.

The main approach was to create a tool that would work well through our Docker development image entrypoint, and then work from a standard environment, as well. The lack of control the user has to the base Docker image creates some of the limitations and assumptions we had to make.

Ovidiu Maxiniuc wrote:

[...]
...but my guess is that it happens at the time the secondary databases (dirty/meta) are initialized.

That seems like a reasonable assumption.

At least, the function naming convention used by the query suggests an H2 database. The name ends with _1, which implies we are using the explicit multiplexing technique to implement function overloading.

PostgreSQL natively supports function overloading, so the function name would not end in _1.

H2 does not support native overloading, so we create multiple functions with sequentially numbered suffixes and determine which one to call at runtime, based on the types of parameters passed to the function.

Updated in revision 16473 when I realized the external tool create_roles.sql was not included. This version creates the file via heredoc, then removes it when done.

Roger Borrello wrote:

Ovidiu, I have been seeing the below after the server connects to the DB. Do you know where this function comes from?
[...]

Roger, see #11150#note-105.

Ovidiu Maxiniuc wrote:

Roger Borrello wrote:

Ovidiu, I have been seeing the below after the server connects to the DB. Do you know where this function comes from?
[...]

Roger, see #11150#note-105.

I am thankful you are on the case!

I rebased 10491a and verified fwd_create_pg_cluster.sh matches up with the lastest from the container project. It would be great for a final review, and get this merged so the tool is available.

• the dependency on startup_pg.sh. This (along sibling shutdown_pg.sh) is part of gcd_systems project and deployed with fwd_create_pg_cluster.sh starting with revision 161 (from about a month ago) of this repository. But, are these helper scripts available on customer machines?
• the PG_VERSION is not defined as a default value. I am not sure if pg should automatically declare it at OS level or the customer. Regardless, it was not defined on my system. I think at least be sure a default value is used if none is inherited.

Then I also used the script to create a test cluster. What I noticed was that, opposed to the script I wrote for creation of multiple tenants (it was using pg_createcluster), this script (which uses initdb) will put the configuration file in the same location with the data. This is not a bad thing, as it groups together the whole cluster, but the pg_lsclusters tool is not able to list it (because it only scans the /etc/postgresql/<N> directory). An additional parameter for this would fix the issue but will add complexity to the script usage.