postgresql

• Overwrite docker-entrypoint.sh in https://github.com/docker-library/postgres/blob/master/docker-entrypoint.sh to enable multiple databases

Extensions:

• pgvector:

  postgres=# \c Langchain
  You are now connected to database "Langchain" as user "postgres".
  Langchain=# \dx
  List of installed extensions
  Name | Version | Default version | Schema | Description
  ---------+---------+-----------------+------------+------------------------------------------------------
  plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language
  vector | 0.8.3 | 0.8.3 | public | vector data type and ivfflat and hnsw access methods
  (2 rows)
  

• postgis:

  postgres=# \c postgres
  postgres=# \dx
  List of installed extensions
  Name | Version | Default version | Schema | Description
  ------------------------+---------+-----------------+------------+------------------------------------------------------------
  fuzzystrmatch | 1.2 | 1.2 | public | determine similarities and distance between strings
  plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language
  postgis | 3.6.4 | 3.6.4 | public | PostGIS geometry and geography spatial types and functions
  postgis_tiger_geocoder | 3.6.4 | 3.6.4 | tiger | PostGIS tiger geocoder and reverse geocoder
  postgis_topology | 3.6.4 | 3.6.4 | topology | PostGIS topology spatial types and functions
  (5 rows)
  

Customization Details:

• https://hub.docker.com/_/postgres "How to extend this image"

• docker_process_init_files:

  • Add the following 3 lines to each of file extensions found in /docker-entrypoint-initdb.d:

  file=${f##*/}
  database=${file%.*}
  export DBNAME=$database
  

• docker_process_sql:

  • Process the database files defined by docker_process_init_files with DB_NAME variable:

  if [ -n "$DBNAME" ]; then
  query_runner+=( --dbname "$DBNAME" )
  

• docker_setup_db:

  • Check and setup all the POSTGRES_DB_<foo>
  • Set the stage in first POSTGRES_DB_1 for the custom user required by all the subsequent DBs:

  CREATE USER :"user" WITH PASSWORD :'password' ;
  

  • In case a privilege escalation is needed,CREATE EXTENSION <foo>, for instance,

  ALTER USER :"user" WITH SUPERUSER ;
  

• docker_process_postgis:

  • Add PostGIS initialization script which would otherwise be overwritten by postgresql-initdb mounted volume.

Check Existing Databases

$ psql -U guest -h <svc-postgresql-nodeport> -l
Password for user guest:
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
------------------+----------+----------+-----------------+------------+------------+--------+-----------+-----------------------
AspNetCoreWebApi | guest | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
Langchain | guest | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
library | guest | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
school | guest | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(7 rows)

Dump the selected database

$ pg_dump -U guest -h <svc-postgresql-nodeport> -d <database> -f <database>.sql

Map the databases in postgresql.yml

 - name: POSTGRES_DB_1
value: AspNetCoreWebApi
- name: POSTGRES_DB_2
value: library
- name: POSTGRES_DB_3
value: school
- name: POSTGRES_DB_4
value: LangchainCheckpoint

PostGIS

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'postgis';
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.6.4 | | PostGIS geometry and geography spatial types and functions

PostgreSQL HA cluster

• https://github.com/bitnami/charts/tree/main/bitnami/postgresql-ha

Readings

• https://www.cncf.io/blog/2023/09/29/recommended-architectures-for-postgresql-in-kubernetes/