Tag: postgres

  • Postgres column type inference madness

    Long time no see.

    Anyways.

    This just cost me hours. And it’s “part I” because I just might add all the difficulties I had, which are … plenty.

    DISCLAIMER: In no way I want to bash either Postgres or SQLAlchemy here, which are incredible projects. This is just my personal experience in, well, “experiencing” the learning curve as someone who has basically no clue about all those things.

    Situation: Saving a database table, i get this error:

    Original exception was: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type double precision: "offset_key.251"
    E   LINE 1: ...:JSONB, ARRAY['scheduled-item']::VARCHAR[], NULL, 'offset_key.251',...
    E [SQL: INSERT INTO invoice_items (<<tons truncated>> offset_key, <<tons truncated>> ... offset_by_ref, cancelled_by_ref) SELEC ... 1158 characters truncated ... 7, p18, p19,
    E [parameters: {'offset_key__0': 'offset_key.251', <<again tons truncated>> ..., 'offset_key__1': nan, <<blablablablabla>>
    

    After a lot of debugging aorund I was absolutely sure that the value offset_key.251 was supposed to be written in the column offset_key. Which was perfectly fine, and which is both defined as a Mapped[String], as well as actually created as such during the test run. So the actual table column type was absolutely correct.

    After a lot of haggling with ChatGPT the solution was this (copy-pasted, because that stupid thing explains really well sometimes):

    Ah — now it’s crystal clear. Let’s unpack this carefully.

    (well, thanks and fuck you too)

    Sometimes, if the first row’s parameter is NULL, Postgres tries to infer a type. If SQLAlchemy passes ‘offset_key.251’ as a parameter in a position that Postgres already inferred as double precision (from another row, e.g., nan), you get this error.

    Databases are … weird. Shouldn’t it just use the actually defined column type?!? This seems really, really wrong (not the first time with the ChatGPTool …).

    But I digress.

    Notice: the second row is nan.

    Yes.

    In Python, float(‘nan’) is a FLOAT, so SQLAlchemy passes it as double precision.

    Postgres sees nan in row 1 → infers double precision for that column in the multi-row insert.

    Row 0 has ‘offset_key.251’ → cannot cast to float → error.

    ✅ This is a classic multi-row INSERT typing problem with Postgres.

    WTF.

    I was absolutely sure that could not be it. But I was completely out of options, so I went after the nan value – and it turns out, I read this from a Pandas DataFrame. Okay, that makes absolute sense, Pandas quite dislikes None, even in string columns.

    So I re-mapped this to None, ran the test, …

    … and it worked. Unbelievable.

    Again: HOURS.

  • pg_dump and pg_restore

    Since I can never remember … . Using pg_dump and pg_restore, tested with AWS RDS.

    First option, pg_dump can create SQL dumps, which can be read from psql.

    # save database; "user" can be admin user (tested) or db_user (untested)
    pg_dump    -p my-db.host   -U user      -d db   > my_backup.sql
    
    # restore database; maybe you can also use the admin user here (untested)
    psql       -p my-db.host   -U db_user   -d db   < my_backup.sql
    

    Second option, let’s now create a pg_dump “custom” dump file, and restore that with pg_restore.

    # save the db ... (add "-v" for more verbosity)
    pg_dump    -p my-db.host   -U user      -d db   -Fc -b -f   my_dump_file.pg_dump
    
    # and restore it. (add "-v" for more verbosity)
    pg_restore -p my-db.host   -U db_user   -d db   -1          my_dump_file.pg_dump
    
  • Postgres – restore database with different owner

    Situation:

    • You have a database
    • You want to “clone” this database into another database
    • That one should get a different owner

    Go.

    Step one, dump the database.

    pg_dump -h database.host            \
      -U database_user                  \
      -W                                \
      -O -Ft                            \
      database                          \
      > database.dump.tar
    

    Important here is -O, which means --no-owner, which means “do not include owner information.

    Step two, restore the database. The database in which the dump file is restored has to exist. If you don’t know how to create it, see here.

    pg_restore                          \
      -h database.host                  \
      -U different_database_user        \
      -W                                \
      -d different_database             \
      --no-owner                        \
      --role=different_database_user    \
       -Ft                              \
       database.dump.tar
    

    DISCLAIMER: I have no idea if there is a better way. That just worked.

  • Migrating Postgres databases in containers

    Run-through

    You should have two directories next to each other – one is the “old” postgres data dir, one is the new one. My setup was this:

    • /srv/postgres/data/10/data
    • /srv/postgres/data/14/data

    First, initialize the data dir of the “new” version:

    # note: mounting -v /.../14:/var/lib/postgresql will _NOT_ work.
    # no idea why. some volume issue.
    docker run --rm \
        -v /srv/postgres/data/14/data:/var/lib/postgresql/14/data \
        -e PGDATA=/var/lib/postgresql/14/data \
        -e POSTGRES_USER=postgres \
        -e POSTGRES_PASSWORD=postgres \
        postgres:14
    

    The migration afterwards is as simple as this:

    docker run --rm \
        -v /srv/postgres/data:/var/lib/postgresql \
        tianon/postgres-upgrade:10-to-14 --link
    

    The docker image tag (here: 10-to-14) describes the migration step.

    Source: tianon/docker-postgres-upgrade.

    Troubleshooting

    Unclean exit

    docker run -v $(pwd):/var/lib/postgresql --rm -u 999 -ti postgres:10 /bin/bash
    

    Source: Also somewhere in the websters.

    Adjust database collations

    $ docker exec -ti -u 999 silly_wescoff psql
    postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='postgres';
    postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template0';
    postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template1';
    

    Source: Stackoverflow.

    Re-set passwords

    $ docker exec -ti -u 999 postgres_container_name psql
    postgres=# \password nextcloud
    Enter new password:
    Enter it again:
    postgres=# _
    

    Source: Stackoverflow.

  • Create user and database in postgres

    Since I always google this. Here now a couple of methods.

    Method 1 – create and grant all privileges

    CREATE DATABASE yourdbname;
    CREATE USER youruser WITH PASSWORD 'yourpass';
    GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
    
    # for PG 15, you need to do more now (https://stackoverflow.com/a/75876944)
    \c yourdbname adminuser
    GRANT ALL ON SCHEMA public TO youruser;
    

    The owner of the database will not be “youruser”. This seems to be just fine in 99% of all cases. If you need database ownership for “myuser”, read on.

    Method 2 – create with a certain owner

    CREATE USER myuser WITH PASSWORD 'mypassword';
    GRANT myuser TO your_current_admin_user;
    CREATE DATABASE mydatabase WITH OWNER myuser ;
    

    DISCLAIMER: I have no idea what the 2nd line does and whether this is a good idea. It just worked.

    Note on encryption

    Note: Sometimes you read CREATE USER ... WITH ENCRYPTED .... Starting with Postgres 10 the ENCRYPTED keyword has no longer any significance:

    The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. (v10)

    Whereas the docs of Postgres 9.6 state:

    These key words control whether the password is stored encrypted in the system catalogs.

    UPDATES:

    • 2022-03-24: Added with owner creation
    • 2023-05-30: postgres 15 update
  • Install Zalando Postgres operator

    This was a bit painful, yet I managed. Used apparently v1.6.2.

    Add helm repos

    If you pull a little trick you can install the operator using helm, just add a helm repo pointing to the github repo:

    # found here: https://git.io/J3C7S
    export ZALANDO_BASE=https://raw.githubusercontent.com/zalando/postgres-operator
    helm repo add zalando-pg $ZALANDO_BASE/master/charts/postgres-operator/
    helm repo add zalando-ui $ZALANDO_BASE/master/charts/postgres-operator-ui/
    helm repo update
    

    Install postgres-operator

    To use CRD configuration (recommended) this must be present in the helm values.yaml file:

    # values.yaml - INCOMPLETE; DO NOT USE; use the one further down.
    configTarget: "OperatorConfigurationCRD"
    

    Unfortunately the helm chart is broken, and you have to re-create the default values.yaml file (see also below). Using this file you can now install the operator:

    helm upgrade -i \
    --create-namespace -n zalando-pg-operator \
    -f values.yaml \
    pg-operator \
    zalando-pg/postgres-operator
    

    Install operator-ui

    Just use default values, so no values.yaml needed.

    helm upgrade -i \
    --create-namespace -n zalando-pg-operator \
    pg-operator-ui \
    zalando-ui/postgres-operator-ui
    

    To access the service you have to do a port-forward, since there is no authentication that I know of:

    kubectl port-forward svc/pg-operator-ui-postgres-operator-ui 8081:80 -n zalando-pg
    curl localhost:8081
    

    Uninstallation

    Since it’s helm I’d assume a simple helm uninstall.

    Impressions

    • I dislike the helm chart quality.
    • I have a feeling the thing is pretty AWS specific, in the UI you can enable “ELB”s, which is AWS speak for “load balancers”
    • I really like the UI, and I like the way you see which objects are created.

    Resources

    values.yaml for helm

    The settings in the default values.yaml file do not have the correct data type and validation will fail once you try to install it. A working version can be found in this gist or below (identical contents). The file has the exact same values but in the correct data types (so a boolean is actually true and not "true", which is a string and not a bool value).

    ## INSTALL / UPGRADE:
    ##   helm upgrade -i --create-namespace -n zalando-pg pg-operator zalando/postgres-operator -f values.yaml
    
    # enable CRD configuration
    configTarget: OperatorConfigurationCRD
    
    # the following values ARE THE DEFAULT VALUES. unfortunately they are coded as
    # strings in the default values.yaml, and the verification (rightfully) complains
    # that either boolean or integer values are needed. so we have to re-include them
    # here but with the correct data type.
    # the only exceptions to this are the commented-out values, which contain integers,
    # but are needed as strings. what a mess.
    
    configGeneral:
      enable_crd_validation: true
      enable_lazy_spilo_upgrade: false
      enable_pgversion_env_var: true
      enable_shm_volume: true
      enable_spilo_wal_path_compat: false
      min_instances: -1
      max_instances: -1
      workers: 8
    
    configKubernetes:
      cluster_labels:
        application: spilo
      enable_init_containers: true
      enable_pod_antiaffinity: false
      enable_pod_disruption_budget: true
      enable_sidecars: true
      spilo_privileged: false
      spilo_allow_privilege_escalation: true
    
    #configPostgresPodResources:
    #  default_cpu_limit: 1
    
    configLoadBalancer:
      enable_master_load_balancer: false
      enable_replica_load_balancer: false
    
    configDebug:
      debug_logging: true
      enable_database_access: true
    
    configLoggingRestApi:
      api_port: 8080
      cluster_history_entries: 1000
      ring_log_lines: 100
    
    configAwsOrGcp:
      enable_ebs_gp3_migration: false
    
    configTeamsApi:
      enable_postgres_team_crd: false
      enable_teams_api: false
    
    configConnectionPooler:
      connection_pooler_max_db_connections: 60
      connection_pooler_number_of_instances: 2
      #connection_pooler_default_cpu_limit: 1
    
  • Install Crunchy Data Postgres operator

    It’s pretty simple, following the documentation, and tested with version 4.6.2 (commands super slightly adapted). Normally you might download the YAML file and adjust the installation settings in there, this is not done here (mainly because this is just a handling test).

    export CRUNCHY_BASE=https://raw.githubusercontent.com/CrunchyData/postgres-operator
    kubectl create namespace pgo
    kubectl apply -f $CRUNCHY_BASE/v4.6.2/installers/kubectl/postgres-operator.yml
    
    # install pgo binary - https://is.gd/U00wux
    curl $CRUNCHY_BASE/v4.6.2/installers/kubectl/client-setup.sh > client-setup.sh
    bash client-setup.sh
    rm client-setup.sh
    
    # THIS IS IMPORTANT
    kubectl delete job -n pgo pgo-deploy
    

    Using the pgo binary

    Every time you want to use the binary you have to port-forward to the operator container, and set PGO_APISERVER_URL:

    # keep running
    kubectl -n pgo port-forward svc/postgres-operator 8443:8443
    
    # second shell
    export PGO_APISERVER_URL="https://127.0.0.1:8443"
    pgo version
    

    Upgrading the operator (untested)

    Just apply the same manifest, but set the DEPLOY_ACTION in the YAML postgres-operator.yaml manifest to upgrade. You can do this by downloading and modifying, or just in a one-liner:

    export CRUNCHY_BASE=https://raw.githubusercontent.com/CrunchyData/postgres-operator
    export CRUNCHY_YAML=$CRUNCHY_BASE/v4.6.2/installers/kubectl/postgres-operator.yml
    curl $CRUNCHY_YAML | sed 's/value: install/value: upgrade/g' | kubectl apply -f -
    

    NOTE: If you get a huge error message you forgot to delete the job after the installation.

    Uninstalling the operator

    Like upgrade, but with the value uninstall.

    export CRUNCHY_BASE=https://raw.githubusercontent.com/CrunchyData/postgres-operator
    export CRUNCHY_YAML=$CRUNCHY_BASE/v4.6.2/installers/kubectl/postgres-operator.yml
    curl $CRUNCHY_YAML | sed 's/value: install/value: uninstall/g' | kubectl apply -f -
    

    NOTE: If you get a huge error message you forgot to delete the job after the installation.

    Afterwards clean up the remaining stuff which is created by $CRUNCHY_YAML defined above:

    kubectl delete job -n pgo pgo-deploy
    kubectl delete clusterrolebinding pgo-deployer-crb
    kubectl delete configmap pgo-deployer-cm
    kubectl delete clusterrole pgo-deployer-cr
    kubectl delete serviceaccount pgo-deployer-sa
    kubectl delete namespace pgo
    

    Impressions

    • Installing using the operator framework at operatorhub didn’t work. The framework failed to install.
    • I don’t like that the pgo binary is installed for every namespace you are using. This seems weird, it should somehow follow “standard” k8s RBAC, or the principle of kubectl contexts.
    • I also don’t like that the installation method is static and you cannot change the namespace without downloading and editing the files apparently.
    • I very much like the CLI approach, yet at the same time …
    • … it leaves the declarative “everything is a CRD / an API object” approach.

    So … mixed. Haven’t tried the databases – yet.