Does anyone here know how to access root on instances in a Kubernetes cluster that have been created using Operator version 2.2?
I need to install custom dictionaries and have found the folder for dictionaries. It is: /usr/pgsql-15/share/tsearch_data
But… only root can write to this folder.
I have tried using the exec-as kubectl plugin and I have tried su - root, but I do not know the root password.
Thanks
Hey @christopher!
By default postgres user is not available for security reasons. You can enable it though: Application and system users - Percona Operator for PostgreSQL
Also if you want to connect to PostgreSQL with postgres through pgBouncer, please read this: Application and system users - Percona Operator for PostgreSQL
Hope it helps. Please let me know if you need any further assistance.
Thanks Sergey! I will try that.
I have also found a configuration setting in the Patroni docs that allows one to create ‘additionalVolumes’.
The docs say:
additionalVolumes List of additional volumes to mount in each container of the statefulset pod. Each item must contain a name, mountPath, and volumeSource which is a kubernetes volumeSource. It allows you to mount existing PersistentVolumeClaims, ConfigMaps and Secrets inside the StatefulSet. Also an emptyDir volume can be shared between initContainer and statefulSet. Additionaly, you can provide a SubPath for volume mount (a file in a configMap source volume, for example). You can also specify in which container the additional Volumes will be mounted with the targetContainers array option. If targetContainers is empty, additional volumes will be mounted only in the postgres container. If you set the all special item, it will be mounted in all containers (postgres + sidecars). Else you can set the list of target containers in which the additional volumes will be mounted (eg : postgres, telegraf)
See here for more info: Zalando Postgres Operator in Production: the way of Helm | by Zhangir Kapishov | Medium
So, I am also going to try to create a storage volume for /usr/pgsql-15/share/tsearch_data in the instances. I’ll likely loose the existing contents so I’ll first copy those files over, change the config, add the new volume and then copy everything back on the host.
Thanks Again.
looks like:
additionalVolumes:
- name: shared-data
mountPath: /usr/pgsql-15/share/tsearch_data/
volumeSource:
emptyDir: {}
does not seem to be supported by the percona operator… This is a significant limitation of this operator… there really needs to be a way to create additional volumes to allow things like dictionaries to be configured!
Interesting idea… yes… worrth a try… I’m going to try your first suggestion next… If I can get to root then I can just drop the dictionary files there… This approach will not survive a restart (which is why I think that additionalVolumes it the right way to go… if it were supported).
Unfortunatly, I am on an extreme deadline for this, I at this point I just need to get somehting working 
@Sergey_Pronin . When you said '“By default postgres user is not available for security reasons. You can enable it though: Application and system users - Percona Operator for PostgreSQL”
Did you mean create a ‘root’ user using something like
...
spec:
users:
- name: root
databases:
- root
So that the operator creates a password for root?
Or were you just trying to help me get pgbouncer to respond to the postgres user? If so… that’s ot really the issue. To install custom dictionaries for text search one needs to add files to the ‘/usr/pgsql-15/share/tsearch_data’ folder. Unfortunatly, it can only be accessed by the ‘root’ user on the pod… and I cannot find a password for the root user.
So the issue is being able to put files there… it’s not accessing postgres via pgbouncer.
Sidecars do not seem to work. Unless I am misunderstanding something about sidecars, it is my understanding that sidecars can only access a shared volume… not the internal files of the parent container.
In this situation I need to be able to add files to the : /usr/pgsql-15/share/tsearch_data/ folder, which is inside the main instance.
Another way might be to use the Percona image to create my own, but if I do that, I will only ever have access to dictionaries that I needed when I created the image.
The optimal way to do this would be to create a shared volume that remaps /usr/pgsql-15/share/tsearch_data/ .
It would be great if the Percona Operator provided that capability. Without this capability I cannot see how the Percona Operator could ever be used in situations where text search is required.
It’s a shame that hte location of tsearch_data is hard coded in Postgres. If it could be changed or reconfigured then I could move it to the data folder.
Strangely the default location for shared files is: /usr/share/pgsql/
but the image that is being used to create the postgres instances is: /usr/pgsql-15/share so that must have been redefined someplace. Perhaps Percona have created thier own postres image and defined at compile time? I do not see pgsql-15 noted anywhere in the operator project.
By default the “admin” user in PostgreSQL is postgres, not root.
Hence you need to create the postgres user as described here: Application and system users - Percona Operator for PostgreSQL
We can jump into a call to discuss your requirements around mounting volumes. I might not be following the idea behind it.
A call would be great. can I call you now?
Let me try to explain more methodically.
Our application uses postgres Full Text Search. See here: PostgreSQL: Documentation: 15: Chapter 12. Full Text Search
When you configure Postgres for ‘Full Text Search’. you very often need to create (and maintain) context specific dictionaries). See here: PostgreSQL: Documentation: 15: 12.6. Dictionaries
We use customized iSpell dictionaries.
To install a dictionary, you create it and the copy it to the $SHAREDIR/tsearch_data folder. The value of SHAREDIR is compiled into Postgres and you can get it using:
pg_config --sharedir
On Percona Operator instances SHAREDIR is : /usr/pgsql-15/share/
On a default install of Postgres (including Percona’s bare metal install) this is : /usr/share/postgresql/15/
Which suggests that Percona have compiled thier own Postgres and when doing so, changed this folder. But that is irrelevant.
What is relevant is that to add directories you need to copy the dictionaries that you need to : $SHAREDIR/tsearch_data
problem 1) that folder is owned by root with only read permissions for everyone else:
bash-4.4$ ls -l /usr/pgsql-15/share
total 1312
drwxr-xr-x 2 root root 4096 Jun 9 07:50 contrib
-rw-r--r-- 1 root root 33458 Mar 6 2023 errcodes.txt
drwxr-xr-x 2 root root 12288 Jun 9 07:50 extension
-rw-r--r-- 1 root root 115044 Mar 6 2023 information_schema.sql
drwxr-xr-x 5 root root 4096 Jun 9 07:50 man
-rw-r--r-- 1 root root 4703 Mar 6 2023 pg_hba.conf.sample
-rw-r--r-- 1 root root 1636 Mar 6 2023 pg_ident.conf.sample
-rw-r--r-- 1 root root 604 Mar 6 2023 pg_service.conf.sample
-rw-r--r-- 1 root root 929375 Mar 6 2023 postgres.bki
-rw-r--r-- 1 root root 19 Mar 6 2023 postgresql-15-libs.conf
-rw-r--r-- 1 root root 29383 Mar 6 2023 postgresql.conf.sample
-rw-r--r-- 1 root root 278 Mar 6 2023 psqlrc.sample
-rw-r--r-- 1 root root 44176 Mar 6 2023 snowball_create.sql
-rw-r--r-- 1 root root 35376 Mar 6 2023 sql_features.txt
-rw-r--r-- 1 root root 8816 Mar 6 2023 system_constraints.sql
-rw-r--r-- 1 root root 22914 Mar 6 2023 system_functions.sql
-rw-r--r-- 1 root root 49466 Mar 6 2023 system_views.sql
drwxr-xr-x 2 root root 4096 Jun 9 07:50 timezonesets
drwxr-xr-x 2 root root 4096 Jun 9 07:50 tsearch_data
and the files in it are similarly set:
bash-4.4$ ls -l /usr/pgsql-15/share/tsearch_data/
total 116
-rw-r--r-- 1 root root 424 Mar 6 2023 danish.stop
-rw-r--r-- 1 root root 453 Mar 6 2023 dutch.stop
-rw-r--r-- 1 root root 622 Mar 6 2023 english.stop
-rw-r--r-- 1 root root 1579 Mar 6 2023 finnish.stop
-rw-r--r-- 1 root root 805 Mar 6 2023 french.stop
-rw-r--r-- 1 root root 1349 Mar 6 2023 german.stop
-rw-r--r-- 1 root root 1227 Mar 6 2023 hungarian.stop
-rw-r--r-- 1 root root 243 Mar 6 2023 hunspell_sample.affix
-rw-r--r-- 1 root root 633 Mar 6 2023 hunspell_sample_long.affix
-rw-r--r-- 1 root root 98 Mar 6 2023 hunspell_sample_long.dict
-rw-r--r-- 1 root root 462 Mar 6 2023 hunspell_sample_num.affix
-rw-r--r-- 1 root root 129 Mar 6 2023 hunspell_sample_num.dict
-rw-r--r-- 1 root root 465 Mar 6 2023 ispell_sample.affix
-rw-r--r-- 1 root root 81 Mar 6 2023 ispell_sample.dict
-rw-r--r-- 1 root root 1654 Mar 6 2023 italian.stop
-rw-r--r-- 1 root root 4261 Mar 6 2023 nepali.stop
-rw-r--r-- 1 root root 851 Mar 6 2023 norwegian.stop
-rw-r--r-- 1 root root 1267 Mar 6 2023 portuguese.stop
-rw-r--r-- 1 root root 1235 Mar 6 2023 russian.stop
-rw-r--r-- 1 root root 2178 Mar 6 2023 spanish.stop
-rw-r--r-- 1 root root 559 Mar 6 2023 swedish.stop
-rw-r--r-- 1 root root 73 Mar 6 2023 synonym_sample.syn
-rw-r--r-- 1 root root 473 Mar 6 2023 thesaurus_sample.ths
-rw-r--r-- 1 root root 260 Mar 6 2023 turkish.stop
-rw-r--r-- 1 root root 9939 Mar 6 2023 unaccent.rules
-rw-r--r-- 1 root root 139 Mar 6 2023 xsyn_sample.rules
So… when you try to copy to the instance using ‘kubectl cp’ or when you log in to the instance using something like:
kubectl exec -it pg-cluster1-instance1-WHATEVER bash
you enter as user ‘postgres’ so you can not write to that folder. Which is somewhat expected because the contents of that folder need to match in ever instance and the contents will not be persisted.
So … that creates a real issue if you need to add dictionaries.
You suggested a sidecar… but as noted… a sidecar is a separate pod and has no access to files inside the postgres instance… only to share volumes.
So… what is really needed is a way to create a shared volume for folders like : $SHAREDIR/tsearch_data so that:
a) all instances can see the dictionaries and
b) db administrators can place dictionary files in one instance (or in a folder on the host server or storage system) and all instances can see the files
c) adding dictionaries DOES NOT require that postgres is restarted. if can be done while live.
The method employed by Zalando SU (the patromi team) in thier Postgres Operator is as follows… In the resource configuration file one can add the following:
additionalVolumes:
- name: shared-data
mountPath: /usr/pgsql-15/share/tsearch_data/
volumeSource:
emptyDir: {}
Which will create a storage volume (if necessary and with appropriate permissions) and mount it in every postgres instance… in the example above it would mount to $SHAREDIR/tsearch_data (/usr/pgsql-15/share/tsearch_data/ in the Percona container).
So… to get the files where they need to be, the options seem to be:
Does that help?
This is great description of the problem.
Thank you, now it is clear.
We discussed it internally and seems Option #2 is the easiest workaround for you right now. Meanwhile we will start thinking how to implement the proper long-term solution.
@Sergey_Pronin , Thank you for the opportunity to chat today. Was very nice to meet you.
With regard to option 2. Can you tell me more about how I would create my own instance image? Is there a guide for this somewhere in the docs?
I’ve looked at the operator deployment and it’s not clear what the operator does to instantiate the postgres instances.
Thanks Again
Hey @christopher - I will consult with the team how to do it best.
@Ege_Gunes - in case you want to chime in directly
Hi @Sergey_Pronin ,
Any luck with this or news on wheter the next version will allow us to remap additional folders onto a shared volume or how we might create our own version of hte Postgres image?
As noted in our recent chat… we would be very hapy to help beta test if that would help.
Many Thanks
Chris
Hello @christopher - I created the JIRA ticket for this here: [K8SPG-440] Mount additional volumes into PG container - Percona JIRA
We will work with the team to see if we can put it into Q4 (Oct-Dec) release. Will keep you posted.
PS. I still think it is something that can be done through sidecars. Will check.
Thanks @Sergey_Pronin ,
I may have completely misunderstood some of the characteristics of sidecar or init containers but it is my understating that the only way to access files between any kind of container is to have a shared volume:
So I do not think that it’s possible for any other container to access files that are not already shared through a volume outside the containers themselves.
I suppose that it might be possible to run a sidecar container that has access to a shared volume that in turn uses scp to copy files into the main container. but this seems like it might be more work that simply modifying the postgres container to allow access to the shared data folder.
I do not think that an init container could be used this way because the target container is not yet running when the init container is running. An init container would only work if there was already a shared volume for the files that need to be accessed but if we had the shared volume for these files then we would not need an init container or sidecar 
I believe that allowing direct access to another containers filesystem kind-a breaks the laws of container physics but I’m perfectly prepared to be wrong about this.
This post further expands on what I was saying:Sidecar Containers in Kubernetes Pods - Communication Between Containers
The containers within a single pod share the network namespace. Therefore, the sidecar container can communicate with the main application container through localhost. One downside is that the sidecar container and the main application container must not listen on the same port. This is because only a single process can listen to the same port on the same network namespace.
Another way for the sidecar container to interact with the main application container is by writing to the same volume. One way to share volume between containers in the same pod is by using the emptyDir volume. The emptyDir volume is created when a pod is created, and all the containers within the same pod can read from and write to that volume. However, the content of the emptyDir is ephemeral by nature and is erased when the pod is deleted.
For example, we can run a log shipper container alongside our main application container. The main application container will write the logs to the emptyDir volume and the log shipper container will tail the logs and ship them to a remote target.
So we have network comms, a shared emptyDir and whatever shared volumes might exist.
Hi Again Sergey,
How’s [K8SPG-440] Mount additional volumes into PG container - Percona JIRA coming for the Oct-Dec release?
Also… I still have not heard back on how we might build our own Postgres image. Do you have any more information on this?
Thanks
Chris
Unanswered | Unsolved | Solved
MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.