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!

How to find the current value for a PostgreSQL sequence

SELECT last_value FROM myschema.mysequence;

or

SELECT nextval('myschema.mysequence');

Note: nextval() advances the sequence — use last_value if you just want to inspect without side effects.