The file openshift-custom-postgresql.conf.template should be extended to allow remote connections for backups with the pg_basebackup tool.

Error Message:

pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)

With this additional configuration, the backup works:

max_wal_senders = 5
wal_level = hot_standby
archive_mode = on
archive_command = '/bin/true'

postgresql 9.5 have major changes please consider to build a cartridge

http://www.postgresql.org/about/featurematrix/

Error output:

waiting for server to start....LOG:  invalid value for parameter "shared_buffers": "128M"
HINT:  Valid units for this parameter are "kB", "MB", "GB", and "TB".
LOG:  invalid value for parameter "effective_cache_size": "256M"
HINT:  Valid units for this parameter are "kB", "MB", "GB", and "TB".
FATAL:  configuration file "/var/lib/pgsql/openshift-custom-postgresql.conf" contains errors
.... stopped waiting
pg_ctl: could not start server
Examine the log output.

Looks like computed settings based on memory aren't quite right: https://github.com/openshift/postgresql/blob/master/9.4/root/usr/share/container-scripts/postgresql/common.sh#L13-L15

Trying to share NFS mount from host machine, as data directory to postgresql 9.5 container

run command:

docker run -d  --name postgres -v /srv/db2/cccp/:/var/lib/pgsql/data -p 5432:5432 -e "POSTGRESQL_USER=cccp" -e "POSTGRESQL_PASSWORD=cccp" -e "POSTGRESQL_DATABASE=cccp" registry.centos.org/sclo/postgresql-95-centos7

here /srv/db2/cccp/ is NFS share mounted on the host system.

NFS server exports a directory with config
/nfsshare *(rw,sync,no_subtree_check,all_squash,anonuid=0,anongid=0)

the docs mentions using setfacl command to work around with postgresql container dir ownership behavior, however while running setfacl command as mentioned, it prompts Operation Not Permitted.

[root@client ~]# setfacl -m u:26:-wx /srv/db2/cccp/
setfacl: /srv/db2/cccp/: Operation not supported

Note: Changing NFS server config to have uid=26 works for postgresql container, however, how does one manage UID/GID consistency all over the place then?

Using the template from https://github.com/openshift/rails-ex, the postgresql db pod fails to start with the following error:

/usr/share/container-scripts/postgresql/common.sh: line 121: /var/lib/pgsql/passwd: Permission denied

I'm running atomic CLI from git master:

$ sudo ./atomic help registry.access.redhat.com/rhscl/postgresql-95-rhel7:latest
PostgreSQL(Docker)                                          PostgreSQL(Docker)



       This  repository contains Dockerfiles for PostgreSQL images for general
       usage and OpenShift.  Users can choose between RHEL  and  CentOS  based
       images.


Environment variables and volumes
       The  image  recognizes the following environment variables that you can
       set during initialization by passing -e VAR=VALUE  to  the  Docker  run
       command.

       allbox;  Variable  name  Description POSTGRESQL_USER          User name
       for PostgreSQL account to be created

       POSTGRESQL_PASSWORD      Password for the user account

       POSTGRESQL_DATABASE      Database name

       POSTGRESQL_ADMIN_PASSWORD          Password  for  the  postgres   admin
       account (optional)



       The following environment variables influence the PostgreSQL configura-
       tion file. They are all optional.

       allbox;  Variable  name   Description   Default  POSTGRESQL_MAX_CONNEC-
       TIONS         The     maximum     number    of    client    connections
       allowed      100

As you can see, the table is kind-of broken.

Why are there no instructions on how to use this image on OpenShift. It does not seem that I can just new-app this docker image.

Description copied from original bug report (https://bugzilla.redhat.com/show_bug.cgi?id=1474683)

Templates have the following livenessProbe included:

          livenessProbe:
            initialDelaySeconds: 30
            tcpSocket:
              port: 5432
            timeoutSeconds: 1

which lead to the following in the postgress log within the container every 30 sec:

LOG:  incomplete startup packet
LOG:  incomplete startup packet
LOG:  incomplete startup packet

Steps to Reproduce:

oc process -f /usr/share/ansible/openshift-ansible/roles/openshift_examples/files/examples/v1.5/db-templates/postgresql-ephemeral-template.json  -v POSTGRESQL_USER=jim -v POSTGRESQL_PASSWORD=redhat -v POSTGRESQL_DATABASE=testdb | oc create -f -
secret "postgresql" created
service "postgresql" created
deploymentconfig "postgresql" created
[root@inf150 ~]# oc get all
NAME            REVISION   DESIRED   CURRENT   TRIGGERED BY
dc/postgresql   1          1         1         config,image(postgresql:9.5)

NAME              DESIRED   CURRENT   READY     AGE
rc/postgresql-1   1         1         1         1m

NAME             CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
svc/postgresql   172.30.83.86   <none>        5432/TCP   1m

NAME                    READY     STATUS    RESTARTS   AGE
po/postgresql-1-2j6sl   1/1       Running   0          57s
[root@inf150 ~]# 
[root@inf150 ~]# oc rsh postgresql-1-2j6sl
sh-4.2$ tail -f /var/lib/pgsql/data/userdata/pg_log/postgresql-Tue.log 
LOG:  autovacuum launcher started
LOG:  incomplete startup packet
LOG:  incomplete startup packet
LOG:  incomplete startup packet
LOG:  incomplete startup packet
LOG:  incomplete startup packet
LOG:  incomplete startup packet
LOG:  incomplete startup packet
LOG:  incomplete startup packet

Actual results:
"LOG: incomplete startup packet" every 30 seconds in /var/lib/pgsql/data/userdata/pg_log/postgresql-Tue.log

Expected results:
Our template checks should not cause more logs

Additional info:
Having a look at

• http://www.postgresql-archive.org/Incomplete-startup-packet-help-needed-td5199030.html

is stating that doing just a tcp connect without sending data at all leads to exactly this message.
The check in our templates is:

          livenessProbe:
            initialDelaySeconds: 30
            tcpSocket:
              port: 5432
            timeoutSeconds: 1

which is exactly what is decribed by just doing tcp connect without sending/requesting further input.

A check like the following could prevent the issue:

       livenessProbe:
          exec:
            command:
            - /bin/sh
            - -i
            - -c
            - pg_isready -h 127.0.0.1 -p 5432
          failureThreshold: 3
          initialDelaySeconds: 30
          periodSeconds: 10
          successThreshold: 1
          timeoutSeconds: 1

We could employ ShellCheck or some other static analysis tools (or even plain sed calls...)

When running a shell to pick up the SCL environment I always get these two log lines in my pods:

sh: cannot set terminal process group (-1): Inappropriate ioctl for
device sh: no job control in this shell

This looks bad but is as far as I can tell benign. In any case this should be at least not shown to the user if it's not an error they should care about.

Mentioned in #172.

for runing openshift tests and customize for this containers' use-case.

Example:
sclorg/nginx-container#35

And its diff for easier application:
https://patch-diff.githubusercontent.com/raw/sclorg/nginx-container/pull/35.diff

I recently set 'umask 0077' on my box. This causes that 'git clone' results in lot of files
and directories with at most rwx------ permissions, which is desired (for me, as I
consider this to be rather safe default on multi-user box while I also need to have 'o+x'
on my home directory).

When the image is then build on such box, Dockerfile instruction ADD root / causes
that (for example) /usr directory has drwx------ root root permissions and, for all
users except for root, we are unable to execve() any file within /usr/bin/... That
results in magic error messages for any command executed after USER 26 instruction.

This shouldn't be an immediate issue, but ATM the permissions under root are rather
not precisely defined. And I'm thinking about writing pull request for hack/build.sh
or fix-permissions explicitly ensuring that (some important) files/directories under root
git directories have explicit permissions, others have 755 (directories) and 644 (regular
files without execute bit held by git) and 755 (for executable files).

Also, it could be considered good policy to re-install filesystem package right before
the yum clean all call, that re-sets the permissions for some important system directories
according to defaults.

WDYT?

It would be great to create roles and set the password for many accounts when starting the pod.

For many databases you have fine-grained permissions. It would be great to manage credentials in one place (one secret per database role), instead of having to set it once in the database, and once in the application.

I propose, that instead of hardcoding /etc/credentials/pgmaster, /etc/credentials/pguser, /etc/credentials/pgadmin, we use every directory within /etc/credentials, create the role if it does not exist yet, and set the password.

If/when a solution to downward-api for secrets (kubernetes/kubernetes#18372) lands, we could/should set role passwords automatically. That still leaves the question on what to do with additions. That can only be fixed properly when one can mount new volumes (or at least secrets) into running kubernetes pods, I suppose.

To the best of my understanding the images do not support it currently.

This is wrong design -- each configuration option should have its own
variable. FWIW, turning prepared transaction off by default is suggested by
PostgreSQL upstream:
http://www.postgresql.org/docs/9.4/static/runtime-config-resource.html

We had a short discussion about this with @mnagy today, the problem I can see is that we are going to use the same directory for 'data' for all versions of postgresql/mongo/mysql.

What happen when we will have multiple versions of these where all databases will write into same directory. Where this is not a problem if you don't want to use your existing volume, it might be problem if you do. In that case, your database might be corrupted by the new version...

I propose to use something like:

VOLUME /var/lib/pgsql-92/data
VOLUME /var/lib/pgsql-95/data
etc...

Alternatively, we can store the version of postgresql in VOLUME and check if the version matches with the current version:

echo $POSTGRESQL_VERSION > /var/lib/pgsql/data/.version

If timezone is not set, postgresql will use environment variable TZ, which seems, to me, a much better way to alter the timezone from the outside.

Should be in a reasonable bin directory and should enable docker exec -ti psql work out of box.

We were using this, but finding it difficult to enable ssl=on, and injecting the crt and key files in postgres user permissions.

See the stackoverflow entry i created for this, for more details: -https://stackoverflow.com/questions/45353038/how-to-mount-secret-in-openshift-with-uidgid-set-correctly

The current s2i proposal in #208 suffers from one ache, it is that even if user provides the initial database state in the "sql" dump to be restored after "initdb", it takes more than several seconds to get the database initialized.

I'm curious whether we could run initdb also during the run of assemble script, and than copy the data directory somewhere within the image -- IOW whether we could have the binary data directory baked into the built s2i image. Then, we could skip the initdb and just copy the backed directory under $PGDATA, and save a lot of time. WDYT?

CI hangs sometimes on:

09:17:45  Fingerprint: 43a6 e49c 4a38 f4be 9abf 2a53 4568 9c88 2fa6 58e0
09:17:45  Package    : redhat-release-server-7.4-18.el7.x86_64 (@anaconda/7.4)
09:17:45  From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
09:17:45 �[0mRunning transaction check
09:17:45 Running transaction test
09:17:45 Transaction test succeeded
09:17:45 Running transaction
09:17:46   Updating   : yum-utils-1.1.31-45.el7.noarch                               1/2 

https://coreservices-jenkins.rhev-ci-vms.eng.rdu2.redhat.com/job/rhscl-images-postgresql-rh-openshift/41/console

We should debug what's going on there.

By $Subject I mean directories of PostgreSQL versions within postgresql-container repo, because we already have Makefile, it is trivial to write everything once. The all-time C&P'ing and pointing someone to C&P something really feeds my OCD .. :) and also we don't have to cheat the number-of-{added,removed-lines} statistics on github, right?

I suggest moving from multiple source trees into (a) one source tree and (b) multiple build trees (excluded by .gitignore). I wouldn't enforce this rule, if somebody likes C&P, but at least we should allow that.

There are other issues in actual approach:

• We can not share common library paths ATM. There was some attempt to make container debugging easier in sclorg/mysql-container#119, and most of the stuff could be reused, but c&p it out from there and apply here is crazy.
• For example #136, which effectively causes that that /usr/bin has non-default permissions. Yes, we could fix fix-permissions script somehow but we can also fix permissions before we run docker build -- but we can not do it now, because you simply can not set 0555 mode on 9.2/root/usr/bin, because then git is refused to add/remove files within that directory.
• C&P'ing wastes contributor's concentration, demotivates contributors that want to fix one issues in one particular version of say mysql container, but (to accept his fix) we make him to fix all other versions.

There were worries about our contributors, and that such change will make it harder to contribute to containers' sources, but I really doubt this is truth -- taking into account that almost any project (honestly non-dockerfile related, yes) in the wild uses similar approach.

There were worries about templating/runtime issues, but I wouldn't force everybody to use exactly the same approach, I don't want to discuss templating in this issue either. What I would like to do is simply and slowly not-C&P those parts of our code-base that doesn't have to differ at all across $(VERSION)s.

Currently, we have a problem that a restarting slave occasionally doesn't start up properly. The quick fix was rm -rf'ing userdata, so a started PostgreSQL process will replicate it anew from the master. It seems to me we could do better (and @praiskup didn't like our current solution). I asked our storage team, and they are of the opinion that making OpenShift/Kubernetes do the EmptyDir cleanup would not make much sense, since EmptyDirs are pod-specific.

Viable options that come to mind:

• Don't use a volume for the slave data, just a plain container directory. This might be problematic, since we might not be able to do quota space accounting later. It would be a very quick and easy solution, though, without risk of deleting user's data (unless he really wants to)
• Be intelligent about the deletion. Include some sort of "state" file that would make guarantees about the last known state of the data. This would be harder to implement and do right, but would provide additional benefit of reusing the data and saving some bandwidth.

cc @bparees @hhorak @mfojtik @rhcarvalho

The postGIS extension is available for use with the build of postgresql in Openshift Online:

CREATE EXTENSION postgis;

It would be nice to have the same default set of extensions available for v3 users as well.

(this issue copied from openshift/origin#6745)

@dhodovsk can you have a look?

     yum-config-manager --enable rhel-7-server-rpms && \
     yum-config-manager --enable rhel-7-server-optional-rpms && \
-    INSTALL_PKGS="rsync tar gettext bind-utils nss_wrapper rh-postgresql95 rh-postgresql95-postgresql-contrib rh-postgresql94-postgresql-server" && \
+
+    INSTALL_PKGS="rsync tar gettext bind-utils nss_wrapper rh-postgresql96 rh-postgresql96-postgresql-contrib rh-postgresql95-postgresql-server" && \
     yum -y --setopt=tsflags=nodocs install $INSTALL_PKGS && \

Updated the resource limits for a postgresql-persistent 9.5 deployment

pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
 done
server started
ERROR:  tuple already updated by self

It seems the first pod did not shutdown cleanly and left the PID in /var/lib/pgsql/data/userdata/postmaster.pid volume thus preventing the container from starting up automatically without manual intervention

Perhaps an edge case as this is the first time seeing this with many other postgresql deployments

Based on #208 (comment).

Currently, PostgreSQL DB redirect logs to $pg_log path (/var/lib/pgsql/data/userdata/pg_log/*), and there is no variable that allows the user to change this value.

This behaviour would be very helpful in use cases (i.e.) where the DB logs want to be collected by the EFK stack within the default paths.

This is fairly easy and the code doesn't take up much space in the image. I can fork and pull request if you want this.

If I use this image as a base image and I install more packages that also add more system users, the system users will not be visible for postgresql because they are not in the passwd.template we provide.

Instead of having the passwd.template in this repo, we should be able to take /etc/passwd, parse it and substitute the UID/GID we are replacing:

cat /etc/passwd | sed -i "s/26/`id -u`/" > new_passwd

When validation of POSTGRESQL_USER fail, because user set it to 'foo-bar' you get this:

For general container run, you must either specify the following environment
variables:
  POSTGRESQL_USER (regex: '^[a-zA-Z_][a-zA-Z0-9_]*$')
  POSTGRESQL_PASSWORD (regex: '^[a-zA-Z0-9_~!@#$%^&*()-=<>,.?;:|]+$')
  POSTGRESQL_DATABASE (regex: '^[a-zA-Z_][a-zA-Z0-9_]*$')
Or the following environment variable:
  POSTGRESQL_ADMIN_PASSWORD (regex: '^[a-zA-Z0-9_~!@#$%^&*()-=<>,.?;:|]+$')
Or both.

Instead you should get something like Invalid POSTGRESQL_USER value: foo-bar (regexp)

assert_login_access always succeed.

That means that assertions like assert_login_access $USER "${PASS}_foo" false never fail.

seems like build.sh should pass the list of selected test cases to test/run

As discussed in sclorg/mysql-container/pull/94 and mysql/pull/92#issuecomment-139270899.

Fwiw "Reorder Dockerfile for better use of cache" commit message did not help me
to understand the reasoning for the chmod a+rwx .. change...

When running the CentOS 7 based PostgreSQL container I was hitting:

$ sudo docker logs eec18d2f88fa
mkdir: cannot create directory '/var/lib/pgsql/data/userdata': Permission denied

For instance, when using uuid-ossp, the supporting packages need to be installed using yum as they are not included in the image normally (maybe they should be? UUID generation is pretty common). The postgres install looks under some /opt/rh/... path instead of the normal /usr/share path for these files, so layering an image on top of openshift/postgresql required a USER reset, the installation, and then some symbolic linking. This seems fairly convoluted and was frustrating to work through.

It would be great if some functionality at the begining of run-postgresql were moved as new pre-init hooks to lighten run-postgresql script. Something which mariadb has https://github.com/sclorg/mariadb-container/tree/master/root-common/usr/share/container-scripts/mysql/pre-init .

See PR proposed - #11

PostgreSQL needs a change similar to sclorg/mysql-container#135

It would be nice to have fedora dockerfile here, too. Just to not loose the track of Fedora's image.

Fedora's image is basically the same what we have here in sclorg, but due to incompatible packaging techniques (scl/non-scl) we can not simply sync the scripts into Fedora's dist-git (otherwise for example the pg_upgrade feature won't work).

Currently it is either $HOME/data/userdata or $HOME/data. This gives
unpredictable behavior depending on how the data directory is mounted.

I am pretty sure, that's only possible using a separate docker image and "RUN pgxn install ...". If you think that ought to be documented, I can supply an example and write some documentation.

With postgres 9.6 being available, I have containers that are going from supporting 9.2 to 9.6. It would be great if there were also a 9.6 container we could use in openshift.

On a Bitnami RHEL 7 VM with 1CPU, 2GB RAM I see the following when trying to run this container:

waiting for server to start....FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5432001, size=45793280, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 45793280 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
    The PostgreSQL documentation contains more information about shared memory configuration.
pg_ctl: could not start server
Examine the log output.
.... stopped waiting
$ ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073741824
min seg size (bytes) = 1

The request size is smaller than the maximum and larger than the minimum, so I think the invalid argument claim for the shmget system call may be the real issue. I attempted to run with --privileged but it didn't seem that the permissions were the issue, either. Thoughts on whether this is a postgres config issue with the container or not?

When creating the user, we shouldn't open the postgres port.

I tried deploying sclo/postgresql-94-centos7 on OpenShift , and I see these mkdir permission denied errors:

$ oc logs db-1-rrlv8
mkdir: cannot create directory '/var/lib/pgsql/data/userdata': Permission denied

I created a pv with the following config:

apiVersion: "v1"
kind: "PersistentVolume"
metadata:
  name: "pv0001"
spec:
  capacity:
    storage: "5Gi"
  accessModes:
    - "ReadWriteOnce"
  persistentVolumeReclaimPolicy: Recycle
  hostPath:
    path: /tmp/pv0001

Here is the docker-compose that I am using:

version: '2'

services:
  db:
    image: registry.centos.org/sclo/postgresql-95-centos7:latest
    ports:
      - "5432:5432"
    environment:
      POSTGRESQL_ADMIN_PASSWORD: mysecretpassword
    volumes:
      - /var/lib/pgsql/data
  
  core:
    image: almightycore/almighty-core:latest
    environment:
      ALMIGHTY_POSTGRES_HOST: db
    ports:
      - "8080:8080"

  ui:
    image: surajd/almighty-ui-deploy:demonov8
    ports:
      - "8088:8080"
    environment:
      API_URL: core:8080/api

So we can selectively run particular tests.