Postgresql
Fleetinginheritance
- Référence externe : https://www.postgresql.org/docs/current/ddl-inherit.html#DDL-INHERIT-CAVEATS
Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.
— https://www.postgresql.org/docs/current/ddl-inherit.html#DDL-INHERIT-CAVEATS
with bitnami
bitnami ne se configure pas bien
bitnami ne se configure pas bien sur k8s avec keycloak
Sur un cluster tout frais, le log de postgre montre
postgresql 13:15:22.82
postgresql 13:15:22.82 Welcome to the Bitnami postgresql container
postgresql 13:15:22.82 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-postgresql
postgresql 13:15:22.82 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-postgresql/issues
postgresql 13:15:22.83
postgresql 13:15:22.84 INFO ==> ** Starting PostgreSQL setup **
postgresql 13:15:22.88 INFO ==> Validating settings in POSTGRESQL_* env vars..
postgresql 13:15:22.89 INFO ==> Loading custom pre-init scripts...
postgresql 13:15:22.89 INFO ==> Initializing PostgreSQL database...
postgresql 13:15:22.92 INFO ==> pg_hba.conf file not detected. Generating it...
postgresql 13:15:22.92 INFO ==> Generating local authentication configuration
postgresql 13:15:24.53 INFO ==> Starting PostgreSQL in background...
postgresql 13:15:24.74 INFO ==> Changing password of postgres
postgresql 13:15:24.77 INFO ==> Creating user keycloak
postgresql 13:15:24.79 INFO ==> Granting access to "keycloak" to the database "keycloak"
postgresql 13:15:24.82 INFO ==> Setting ownership for the 'public' schema database "keycloak" to "keycloak"
postgresql 13:15:24.85 INFO ==> Configuring replication parameters
postgresql 13:15:24.88 INFO ==> Configuring synchronous_replication
postgresql 13:15:24.89 INFO ==> Configuring fsync
postgresql 13:15:24.93 INFO ==> Loading custom scripts...
postgresql 13:15:24.94 INFO ==> Enabling remote connections
postgresql 13:15:24.95 INFO ==> Stopping PostgreSQL...
waiting for server to shut down.... done
server stopped
postgresql 13:15:25.06 INFO ==> ** PostgreSQL setup finished! **
postgresql 13:15:25.09 INFO ==> ** Starting PostgreSQL **
2022-05-09 13:15:25.116 GMT [1] LOG: pgaudit extension initialized
Sur un autre cluster, le log de PostgreSQL donne
postgresql 12:23:01.42
postgresql 12:23:01.42 Welcome to the Bitnami postgresql container
postgresql 12:23:01.42 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-postgresql
postgresql 12:23:01.42 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-postgresql/issues
postgresql 12:23:01.43
postgresql 12:23:01.45 INFO ==> ** Starting PostgreSQL setup **
postgresql 12:23:01.48 INFO ==> Validating settings in POSTGRESQL_* env vars..
postgresql 12:23:01.49 INFO ==> Loading custom pre-init scripts...
postgresql 12:23:01.50 INFO ==> Initializing PostgreSQL database...
postgresql 12:23:01.53 INFO ==> pg_hba.conf file not detected. Generating it...
postgresql 12:23:01.53 INFO ==> Generating local authentication configuration
postgresql 12:23:01.54 INFO ==> Deploying PostgreSQL with persisted data...
postgresql 12:23:01.56 INFO ==> Configuring replication parameters
postgresql 12:23:01.59 INFO ==> Configuring fsync
postgresql 12:23:01.60 INFO ==> Configuring synchronous_replication
postgresql 12:23:01.64 INFO ==> Loading custom scripts...
postgresql 12:23:01.64 INFO ==> Enabling remote connections
postgresql 12:23:01.66 INFO ==> ** PostgreSQL setup finished! **
postgresql 12:23:01.69 INFO ==> ** Starting PostgreSQL **
2022-05-09 12:23:01.729 GMT [1] LOG: pgaudit extension initialized
2022-05-09 12:23:01.735 GMT [1] LOG: starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2022-05-09 12:23:01.737 GMT [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-05-09 12:23:01.737 GMT [1] LOG: listening on IPv6 address "::", port 5432
2022-05-09 12:23:01.743 GMT [1] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-09 12:23:01.751 GMT [92] LOG: database system was shut down at 2022-05-05 12:43:18 GMT
2022-05-09 12:23:01.764 GMT [1] LOG: database system is ready to accept connections
2022-05-09 12:23:18.228 GMT [107] FATAL: password authentication failed for user "keycloak"
2022-05-09 12:23:18.228 GMT [107] DETAIL: Connection matched pg_hba.conf line 1: "host all all 0.0.0.0/0 md5"
2022-05-09 12:23:18.388 GMT [109] FATAL: password authentication failed for user "keycloak"
2022-05-09 12:23:18.388 GMT [109] DETAIL: Connection matched pg_hba.conf line 1: "host all all 0.0.0.0/0 md5"
2022-05-09 12:23:18.863 GMT [111] FATAL: password authentication failed for user "keycloak"
2022-05-09 12:23:18.863 GMT [111] DETAIL: Connection matched pg_hba.conf line 1: "host all all 0.0.0.0/0 md5"
2022-05-09 12:23:19.156 GMT [112] FATAL: password authentication failed for user "keycloak"
Il semble qu’il ne se configure pas.
La différence réside sur
postgresql 13:15:24.53 INFO ==> Starting PostgreSQL in background...
postgresql 13:15:24.74 INFO ==> Changing password of postgres
postgresql 13:15:24.77 INFO ==> Creating user keycloak
postgresql 13:15:24.79 INFO ==> Granting access to "keycloak" to the database "keycloak"
postgresql 13:15:24.82 INFO ==> Setting ownership for the 'public' schema database "keycloak" to "keycloak"
...
postgresql 13:15:24.95 INFO ==> Stopping PostgreSQL...
waiting for server to shut down.... done
server stopped
contre
postgresql 12:23:01.54 INFO ==> Deploying PostgreSQL with persisted data...
On remarque que bitnami utilise un volume pour stocker les données PostgreSQL dans bitnami/postgresql alors que les données de configuration sont dans /opt/bitnami. Aussi,
Je pense que le point d’intérêt est dans cette zone.
if ! is_dir_empty "$POSTGRESQL_DATA_DIR"; then
info "Deploying PostgreSQL with persisted data..."
export POSTGRESQL_FIRST_BOOT="no"
is_boolean_yes "$create_pghba_file" && postgresql_restrict_pghba
is_boolean_yes "$create_conf_file" && postgresql_configure_replication_parameters
is_boolean_yes "$create_conf_file" && postgresql_configure_fsync
is_boolean_yes "$create_conf_file" && is_boolean_yes "$POSTGRESQL_ENABLE_TLS" && postgresql_configure_tls
[[ "$POSTGRESQL_REPLICATION_MODE" = "master" ]] && [[ -n "$POSTGRESQL_REPLICATION_USER" ]] && is_boolean_yes "$create_pghba_file" && postgresql_add_replication_to_pghba
[[ "$POSTGRESQL_REPLICATION_MODE" = "master" ]] && is_boolean_yes "$create_pghba_file" && postgresql_configure_synchronous_replication
[[ "$POSTGRESQL_REPLICATION_MODE" = "slave" ]] && postgresql_configure_recovery
else
if [[ "$POSTGRESQL_REPLICATION_MODE" = "master" ]]; then
postgresql_master_init_db
postgresql_start_bg "false"
[[ -n "${POSTGRESQL_DATABASE}" ]] && [[ "$POSTGRESQL_DATABASE" != "postgres" ]] && postgresql_create_custom_database
if [[ "$POSTGRESQL_USERNAME" = "postgres" ]]; then
postgresql_alter_postgres_user "$POSTGRESQL_PASSWORD"
else
if [[ -n "$POSTGRESQL_POSTGRES_PASSWORD" ]]; then
postgresql_alter_postgres_user "$POSTGRESQL_POSTGRES_PASSWORD"
fi
postgresql_create_admin_user
fi
is_boolean_yes "$create_pghba_file" && postgresql_restrict_pghba
[[ -n "$POSTGRESQL_REPLICATION_USER" ]] && postgresql_create_replication_user
is_boolean_yes "$create_conf_file" && postgresql_configure_replication_parameters
is_boolean_yes "$create_pghba_file" && postgresql_configure_synchronous_replication
is_boolean_yes "$create_conf_file" && postgresql_configure_fsync
is_boolean_yes "$create_conf_file" && is_boolean_yes "$POSTGRESQL_ENABLE_TLS" && postgresql_configure_tls
[[ -n "$POSTGRESQL_REPLICATION_USER" ]] && is_boolean_yes "$create_pghba_file" && postgresql_add_replication_to_pghba
else
postgresql_slave_init_db
is_boolean_yes "$create_pghba_file" && postgresql_restrict_pghba
is_boolean_yes "$create_conf_file" && postgresql_configure_replication_parameters
is_boolean_yes "$create_conf_file" && postgresql_configure_fsync
is_boolean_yes "$create_conf_file" && is_boolean_yes "$POSTGRESQL_ENABLE_TLS" && postgresql_configure_tls
postgresql_configure_recovery
fi
fi
Si les fichiers sont déjà là, on ne va pas appliquer les configurations issues des variables d’environnement $POSTGRESQL_PASSWORD et $POSTGRESQL_USERNAME.
Effectivement, sur le cluster à soucis
psql -h localhost -p 5432 -U keycloak
Password for user keycloak:
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "keycloak"
Sur le cluster OK
psql -h localhost -p 5432 -U keycloak
Password for user keycloak:
psql (14.2)
Type "help" for help.
keycloak=>
Il semble en fait que le mot de passe de keycloak ne soit pas le bon.
Résolution du problème
- Nous posons le mot de passe de l’utilisateur dans un secret k8s.
- Or le cluster s’est mis à ne plus fonctionner après un helm uninstall && helm install
- helm uninstall supprime le secret mais pas le pvc
- le mot de passe est généré dans le secret de façon aléatoire
- quand on réinstalle avec helm, la base de donnée continue à utiliser l’ancien mot de passe, alors qu’un nouveau secret, avec un nouveau mot de passe est créé
comment bitnami génère le mot de passe
comment bitnami
data:
{{- if .Values.auth.enablePostgresUser }}
postgres-password: {{ include "common.secrets.passwords.manage" (dict "secret" (include "common.names.fullname" .) "key" "postgres-password" "providedValues" (list "global.postgresql.auth.postgresPassword" "auth.postgresPassword") "context" $) }}
{{- end }}
{{- if not (empty (include "postgresql.username" .)) }}
password: {{ include "common.secrets.passwords.manage " (dict "secret" (include "common.names.fullname" .) "key" "password" "providedValues" (list "global.postgresql.auth.password" "auth.password") "context" $) }}
{{- end }}
{{- if eq .Values.architecture "replication" }}
replication-password: {{ include "common.secrets.passwords.manage" (dict "secret" (include "common.names.fullname" .) "key" "replication-password" "providedValues" (list "auth.replicationPassword") "context" $) }}
{{- end }}
# We don't auto-generate LDAP password when it's not provided as we do for other passwords
{{- if and .Values.ldap.enabled .Values.ldap.bind_password }}
ldap-password: {{ .Values.ldap.bind_password | b64enc | quote }}
{{- end }}
puis, dans la dépendance common de postgre de bitnami
{{- define "common.secrets.passwords.manage" -}}
{{- $password := "" }}
{{- $subchart := "" }}
{{- $chartName := default "" .chartName }}
{{- $passwordLength := default 10 .length }}
{{- $providedPasswordKey := include "common.utils.getKeyFromList" (dict "keys" .providedValues "context" $.context) }}
{{- $providedPasswordValue := include "common.utils.getValueFromKey" (dict "key" $providedPasswordKey "context" $.context) }}
{{- $secretData := (lookup "v1" "Secret" $.context.Release.Namespace .secret).data }}
{{- if $secretData }}
{{- if hasKey $secretData .key }}
{{- $password = index $secretData .key }}
{{- else }}
{{- printf "\nPASSWORDS ERROR: The secret \"%s\" does not contain the key \"%s\"\n" .secret .key | fail -}}
{{- end -}}
{{- else if $providedPasswordValue }}
{{- $password = $providedPasswordValue | toString | b64enc | quote }}
{{- else }}
{{- if .context.Values.enabled }}
{{- $subchart = $chartName }}
{{- end -}}
{{- $requiredPassword := dict "valueKey" $providedPasswordKey "secret" .secret "field" .key "subchart" $subchart "context" $.context -}}
{{- $requiredPasswordError := include "common.validations.values.single.empty" $requiredPassword -}}
{{- $passwordValidationErrors := list $requiredPasswordError -}}
{{- include "common.errors.upgrade.passwords.empty" (dict "validationErrors" $passwordValidationErrors "context" $.context) -}}
{{- if .strong }}
{{- $subStr := list (lower (randAlpha 1)) (randNumeric 1) (upper (randAlpha 1)) | join "_" }}
{{- $password = randAscii $passwordLength }}
{{- $password = regexReplaceAllLiteral "\\W" $password "@" | substr 5 $passwordLength }}
{{- $password = printf "%s%s" $subStr $password | toString | shuffle | b64enc | quote }}
{{- else }}
{{- $password = randAlphaNum $passwordLength | b64enc | quote }}
{{- end }}
{{- end -}}
{{- printf "%s" $password -}}
{{- end -}}
pg_dump
deduplicate rows
Provided there is no already existing id columns.
alter table mytable add column id serial
Then, partition other some commun attribute and find the row number.
delete from mytable where id in (
select id from (
select id, cid, row_number() over(partition by cid) from mytable -- group by the cid and show the row number
) as withrows where row_number > 1 -- find only the ones with more than 1 match
) -- remove them
Then, remove the temporary id column.
alter table mytable drop column id
Notes pointant ici
- how do I limit the resources that pg_dump uses?
- how do I store my files? (blog)
- how I organize my static files with IPFS and org-roam
- how to organise the inter subchart networkpolicies?
- postgresql stopped working on my raspberrypi