| BML-S | Help Login

Last entries

    20241016.1448

  1. postgres|postgresql|flyway > check|list status of advisory lock - [s]

    2. 20241016.1245

  2. postgres|postgresql > How to Resolve pg_wal Running Out of Disk Space - [s]

    $ pg_controldata -D /opt/homebrew/var/postgresql@14 pg_control version number: 1300 Catalog version number: 202107181 Database system identifier: 7156816885991412919 Database cluster state: in production pg_control last modified: Tue 15 Oct 18:01:00 2024 Latest checkpoint location: 97/5D384BB0 Latest checkpoint's REDO location: 97/5D384B78 Latest checkpoint's REDO WAL file: 00000001000000970000005D <<< Latest checkpoint's TimeLineID: 1 ... Time of latest checkpoint: Tue 15 Oct 18:01:00 2024 ... # Simulating/testing WAL file cleanup $ cd "/opt/homebrew/var/postgresql@14" $ ls -l ./pg_wal/00000001000000970000005D -rw------- 1 hosako admin 16777216 15 Oct 18:01 ./pg_wal/00000001000000970000005D $ pg_archivecleanup -n -d ./pg_wal/ 00000001000000970000005D pg_archivecleanup: keeping WAL file "./pg_wal//00000001000000970000005D" and later

    3. 20241016.1232

  3. The Power of JSONB in PostgreSQL. PostgreSQL is a powerful relational… | by Mohammad Roshandelpoor | Medium - [s]

    -> : Extracts a JSON element by key or array index. ->> : Extracts a JSON element as text. #> : Extracts a JSON sub-object at a specified path. #>> : Extracts a JSON sub-object as text. @> : Checks if a JSON document contains another JSON document. <@ : Checks if a JSON document is contained within another JSON document. jsonb_array_elements(): Expands a JSON array into a set of rows. jsonb_each(): Expands a JSON object into key-value pairs. ---- -- Access the first element in the 'ids' array within 'params' SELECT params->'ids'->0 FROM events; -- Check if the employee has a skill in "Sales" SELECT * FROM employees WHERE employee_data->'skills' @> '["Sales"]'; ---- -- Append JSON strings in the end of another JSON UPDATE promotion_banners SET items = items || '{"status": 1}'::jsonb WHERE id = 2;

    4. 20241016.1219

  4. Android Developers Blog: Quick introduction to Large Language Models for Android - [s]

    5. 20241002.1421

  5. postgres|postgresql > Administering a Patroni Managed PostgreSQL Cluster - [s]

    6. 20240926.1725

  6. postgres|postgresql > Determining the optimal value for shared_buffers using the pg_buffercache - [s]

    # Also see: https://www.postgresql.org/docs/current/pgbuffercache.html CREATE EXTENSION pg_buffercache; -- identify the top 10 relations residing in shared_buffers and percentage utilisation SELECT c.relname, pg_size_pretty(count(*)*8192) AS buffer_size, pg_size_pretty(pg_relation_size(c.oid)) as relation_size, Round(100.0 * Count(*) / (SELECT setting FROM pg_settings WHERE name = 'shared_buffers') :: INTEGER, 2) AS buffers_percent, ROUND(count(*)*8192*100/ pg_relation_size(c.oid)::numeric, 2 ) AS relation_percent, CASE WHEN c.relkind = 'r' THEN 'table' WHEN c.relkind = 'i' THEN 'index' WHEN c.relkind = 'S' THEN 'sequence' WHEN c.relkind = 't' THEN 'TOAST table' WHEN c.relkind = 'v' THEN 'view' WHEN c.relkind = 'm' THEN 'materialized view' WHEN c.relkind = 'c' THEN 'composite type' WHEN c.relkind = 'f' THEN 'foreign table' WHEN c.relkind = 'p' THEN 'partitioned table' WHEN c.relkind = 'I' THEN 'partitioned index' ELSE 'Unexpected relkind' END as relation_type FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON ( b.reldatabase = d.oid AND d.datname = Current_database() ) GROUP BY c.relname, c.oid ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;

    7. 20240923.0904

  7. Testing LDAP Connections With Java | Baeldung - [s]

    8. 20240920.0930

  8. postgres|postgresql > Copy|backup a table including index - [s]

    nxrm94950=# CREATE TABLE search_components_bak (LIKE search_components INCLUDING INDEXES); CREATE TABLE nxrm94950=# INSERT INTO search_components_bak SELECT * FROM search_components; # NOTE: as the names of indexes need to be unique, PostgreSQL seems to change the index names automatically. # Original Indexes: "pk_search_components" PRIMARY KEY, btree (repository_id, component_id, format) "idx_search_components_component_name" btree (search_component_name) "idx_search_components_entity_version" btree (entity_version) ... # Backup Indexes: "search_components_bak_pkey" PRIMARY KEY, btree (repository_id, component_id, format) "search_components_bak_search_component_name_idx" btree (search_component_name) "search_components_bak_entity_version_idx" btree (entity_version) ...

    9. 20240919.0705

  9. postgres|PostgreSQL > error:could not read block * in file "base/*/*": read only * of * bytes - [s]

[s] public (9)