What is an OS collation often referred to when dealing with PostgreSQL servers?

We have seen the following error in on of our postgres databases:

WARNING: database "testdb" has a collation version mismatch DETAIL: 
The database was created using collation version 2.17, but the operating system provides version 2.34. 
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE testdb REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

So what exactly is a "Collation"?

An OS collation is the set of language‑dependent rules supplied by the operating system that define how strings are compared and sorted (alphabetical order, case rules, accents, etc.). On Linux, these rules come from glibc locales.

What the error means

PostgreSQL databases rely on the operating system’s locale / collation implementation (typically glibc on Linux) for:

string ordering (ORDER BY)

comparisons (<, >, =)

indexes on text, varchar, char using the default collation The database testdb was created when the OS provided collation version 2.17, but the server is now running glibc 2.34.

In other words:

• Database collation version: 2.17 
• OS collation version: 2.34

This usually happens after:

• an OS upgrade
• moving a data directory to a newer system
• restoring a dump created on an older system

Collation rules can change between glibc versions.

Implications for the database

• The sort order of strings may differ
• Existing indexes may no longer match the current collation rules
• Query results involving ordering or comparisons could be wrong
• Index corruption does not occur, but index semantics may be invalid

PostgreSQL cannot automatically fix this, so it warns you.

Only objects that use the default collation, typically:

• btree indexes on text / varchar
• constraints using string comparison
• materialized views

Objects using COLLATE "C" / POSIX or ICU collations are not affected.

Solution:

For each database, and every schema:

Rebuild all indexes and other dependet objects that rely on the default collation

Then refresh the collation version:

ALTER DATABASE prod REFRESH COLLATION VERSION;

The alternative to this is stated in the messages itself:

or build PostgreSQL with the right library version

which means downgrade glibc or run PostgreSQL on a system with glibc 2.17 — usually not desirable.

For references, how does this work on the oracle platform?

For Oracle

• Collation (NLS sorting/comparison rules) is implemented inside the database engine
• Controlled via NLS_SORT, NLS_COMP, character set, etc.
• Independent of the OS
• OS upgrades do not change string ordering semantics

For PostgreSQL (default setup)

• Delegates collation behavior to the operating system locale
• Uses glibc on Linux for ORDER BY, comparisons, and btree indexes on text
• If glibc changes, collation rules may change → PostgreSQL detects and warns

So PostgreSQL is not missing a feature; it is designed to rely on the OS by default. It is a design choice: PostgreSQL usually delegates collation to the OS; Oracle does not

Workaround for ORA-00942: table or view does not exist when executing GRANT on a table list

When executing dynamic SQL against a schema that has differenly named objects, you will run into the "ORA-00942: table or view does not exist" during execution.

For example in my schema, I had 32 tables and one of them were named "flyway_schema_history". This means oracle will preserve the lower case naming in the dictionary.

The work around is to use DBMS_ASSERT.ENQUOTE_NAME to quote only when required.

In the example below, I am granting all of SCOTT's object to JIM:

BEGIN
  FOR r IN (
    SELECT object_name
    FROM   dba_objects
    WHERE  owner = 'SCOTT'
    AND    object_type IN ('TABLE', 'VIEW')
    AND    status = 'VALID'
  ) LOOP
    EXECUTE IMMEDIATE
      'GRANT SELECT ON SCOTT.' ||
      DBMS_ASSERT.ENQUOTE_NAME(r.object_name, FALSE) ||
      ' TO JIM';
  END LOOP;
END;
/

Inter-database import and exports using Oracle data pump

To move an entire schema from one schema to another within the same Oracle database, there is no need to export data to disk and then reimport it into the other schema. It can all be done using data pump network mode.

Create a loopback database link, meaning a database link pointing to the same database:

CREATE DATABASE LINK loopback_dblink
CONNECT TO system IDENTIFIED BY secretpassword
USING 'proddb01';

Then, precreate the schema JIM and create a separate tablespace for the schema:

CREATE BIGFILE TABLESPACE JIM_TABSPC
DATAFILE 
  '/data/oradata/proddb01/jim_tabscp.dbf' SIZE 9G AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


CREATE USER JIM
  IDENTIFIED BY jimsseecretpassword 
  DEFAULT TABLESPACE JIM_TABSPC
  TEMPORARY TABLESPACE TEMP
  PROFILE APP_USER
  ACCOUNT UNLOCK;

GRANT CONNECT TO JIM;
GRANT RESOURCE TO JIM;
ALTER USER JIM DEFAULT ROLE ALL;

GRANT UNLIMITED TABLESPACE TO JIM;

ALTER USER JIM QUOTA UNLIMITED ON JIM_TABSPC;

Finally, execute the data pump script that will clone the data in schema SCOTT to schema JIM:

impdp system/secretpassword \
    schemas=SCOTT \
    network_link=loopback_dblink \
    remap_schema=SCOTT:JIM \
    remap_tablespace=SCOTT:JIM \
    directory=DATA_PUMP_DIR \
    logfile=scott_to_jim_clone.log \
    transform=OID:N \
    transform=SEGMENT_ATTRIBUTES:N

How to dump the entire schema's DDL into a file

pg_dump -d mydb \
  --schema-only \
  --no-owner \
  --no-privileges \
  -n myschema \
  -f create_schema.sql

The flag "--no-owner" tells pg_dump not to include OWNER TO ... statements in the dump. When you restore the file in another database, objects will automatically be owned by the user running psql, not by the owner of the the schema in the mydb database.

The flag "--no-privileges" tells pg_dump not to include GRANT/REVOKE statements. This avoids restoring production permissions into test and lets you manage privileges separately.

Just paste it into your terminal as the user owning the postgres software, and the file "create_schema.sql" will be created in your current directory.

Some examples of how to use the function pg_partition_tree

From PostgreSQL 11, the fuction pg_partition_tree has been available

Usage, in its simplest form:

SELECT *
FROM pg_partition_tree('ldksf.entitet');

select * from pg_partition_tree('ldksf.entitet');
       relid        | parentrelid | isleaf | level
--------------------+-------------+--------+-------
 entitet            |             | f      |     0
 entitet_default    | entitet     | t      |     1
 entitet_p0         | entitet     | t      |     1
 entitet_p120000000 | entitet     | t      |     1
 entitet_p150000000 | entitet     | t      |     1

Make it a bit more informativ, together with other tables in the data dictionary. Put the following into a file called pg_tree_info.sql:

\echo myschema = :myschema
\echo mytable  = :mytable

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.n_live_tup,
    s.last_analyze,
    s.last_autoanalyze
FROM pg_partition_tree(format('%I.%I', :'myschema', :'mytable')::regclass) pt
JOIN pg_class c
  ON c.oid = pt.relid
JOIN pg_namespace n
  ON n.oid = c.relnamespace
JOIN pg_stat_all_tables s
  ON s.schemaname = n.nspname
 AND s.relname = c.relname
ORDER BY s.n_live_tup DESC, s.last_analyze;

SELECT
    pt.level,
    pt.isleaf,
    n.nspname,
    c.relname
FROM pg_partition_tree(
        format('%I.%I', :'myschema', :'mytable')::regclass
     ) pt
JOIN pg_class c ON c.oid = pt.relid
JOIN pg_namespace n ON n.oid = c.relnamespace
ORDER BY pt.level, c.relname;

Example output:

myschema = scott
mytable = entitet
 schemaname |     table_name     | n_live_tup |         last_analyze          |       last_autoanalyze
------------+--------------------+------------+-------------------------------+-------------------------------
 ldksf      | entitet_p30000000  |   14706380 | 2026-03-10 22:15:50.390363+01 | 2026-03-10 16:29:36.398134+01
 ldksf      | entitet_p0         |   12193064 | 2026-03-10 22:15:50.749426+01 | 2026-03-10 16:27:35.272815+01
 ldksf      | entitet_p60000000  |    5481387 | 2026-03-10 22:15:51.069335+01 | 2026-03-10 16:31:35.842357+01
 ldksf      | entitet_default    |          0 | 2026-03-10 22:15:53.688216+01 |
 ldksf      | entitet_p180000000 |          0 | 2026-03-10 22:15:53.68893+01  |
 
 
 level | isleaf | nspname  |      relname
-------+--------+----------+--------------------
     0 | f      |   ldksf  | entitet
     1 | t      |   ldksf  | entitet_default
     1 | t      |   ldksf  | entitet_p0
     1 | t      |   ldksf  | entitet_p120000000
     1 | t      |   ldksf  | entitet_p150000000

Excute it like this:

psql -h prod1.pgsql01.oric.no -d mydb -U scott -v myschema=ldksf -v mytable=entitet -f pg_tree_info.sql

Find active queries in PostgreSQL

SELECT pid, state, now() - query_start AS duration, left(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND usename = 'myuser'
ORDER BY query_start;

Example output:

   pid   | state  |    duration     |                                    query
---------+--------+-----------------+------------------------------------------------------------------------------
 1648516 | active | 01:29:28.979693 |                                                                             +
         |        |                 | UPDATE myschema.mytable1 eeu                                   +
         |        |                 | SET aggregated_value = (SELECT e.aggregated_value FROM

This is the query I am looking for, started in another session. The full query text is

UPDATE myschema.mytable1 eeu
SET aggregated_value = (SELECT e.aggregated_value FROM myschema.mytable2 e WHERE e.systemid = eeu.entitet AND e.instansid = eeu.instansid);

Identity columns vs free-standing sequences in PostgreSQL

Are the columns typically used in primary key columns, defined as IDENTITY columns, or as standalone columns with a default value generated from a free-standing sequence?

SELECT table_name, column_name, is_identity, column_default
FROM information_schema.columns
WHERE table_schema = 'myschema'
AND column_name IN ('sekvnr', 'id')
AND table_name IN ('table1','table2','table3','table4',
                   'table5','table6',
                   'table7','table8')
ORDER BY table_name, column_name;

Result:

        table_name          |  column_name  | is_identity | column_default
----------------------------+---------------+-------------+----------------
 table1                     | id            | YES         |
 table2                     | id            | YES         |
 table3                     | sekvnr        | NO          |
 table4                     | id            | YES         |
 table5                     | id            | YES         |
 table6                     | id            | YES         |
 table7                     | id            | YES         |
 table8                     | id            | YES         |
(8 rows)

It's clear from the query that the only table that is still supported by a free-standing sequence to generate its primary key values, is table3!