Thursday, August 28, 2025

Spool out all parameters from a datafile to a file

psql -h postgres01.oric.no -U mysuperuser postgres -c "COPY (SELECT name || '='  || setting FROM pg_settings ORDER BY name) TO STDOUT WITH CSV HEADER"   > pg_config.csv

Wednesday, August 27, 2025

List a table, its partitions and the number of rows it contains

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.n_live_tup,
    s.last_analyze,
    s.last_autoanalyze
FROM
    pg_stat_all_tables s
JOIN
    pg_class c ON c.relname = s.relname
LEFT JOIN
    pg_inherits i ON i.inhrelid = c.oid OR i.inhparent = c.oid
WHERE
    s.schemaname = 'myschema'
    AND (
        s.relname = 'mytable'
        OR c.oid IN (
            SELECT inhrelid
            FROM pg_inherits
            WHERE inhparent = (
                SELECT oid FROM pg_class WHERE relname = 'mytable'
            )
        )
    )
ORDER BY
 s.n_live_tup DESC,s.last_analyze;

Example output

 schemaname      |  table_name     | n_live_tup | last_analyze   		| last_autoanalyze 
-----------------+-----------------+------------+-------------------------------+------------------------------
 myschema 	 | mytable_2015_02 |   95788115 | 2025-08-06 12:58:22.952592+00 | 
 myschema 	 | mytable_2015_03 |   78505350 | 2025-08-06 12:58:37.147433+00 | 
 myschema 	 | mytable_2015_04 |   71211253 | 2025-08-06 12:58:51.311452+00 | 
 myschema 	 | mytable_2015_01 |   68255510 | 2025-08-06 12:58:08.42708+00  | 
 myschema 	 | mytable_2015_05 |   62075043 | 2025-08-06 12:59:05.434118+00 | 
 myschema 	 | mytable_2015_06 |   60888876 | 2025-08-06 12:59:19.918657+00 | 2025-08-06 11:31:19.551345+00
 myschema 	 | mytable_2015_12 |   57485594 | 2025-08-06 13:00:43.112316+00 | 2025-08-27 03:13:11.394606+00
 myschema 	 | mytable_2016_01 |   57405307 | 2025-08-06 13:00:55.395113+00 | 2025-08-25 13:35:07.749773+00

Thursday, July 31, 2025

How to check if a PostgreSQL streaming replicate server is up-to-date with the primary

On the primary, run
SELECT pg_current_wal_lsn(); -- On the primary
In my case, the result was
 pg_current_wal_lsn
--------------------
 0/E015420
On the standby, run
SELECT pg_last_wal_replay_lsn();
In my case, the result was
pg_last_wal_replay_lsn
------------------------
 0/E015420

This means that

  • All WAL records have been received
  • All WAL recores have been replayed (not just flushed) on the standby
  • There is no lag between primary and standby
  • Basic postgres connect syntax

    psql -h hostname -U username -d database
    
    Options:
    Option	Description
    -h <host>	Hostname or IP address of the PostgreSQL server
    -p <port>	Port number (default is 5432)
    -U <username>	Username to connect as
    -d <dbname>	Database name
    -W Prompt for password (useful if password auth is required)
    -f <filename>	Execute commands from a SQL script file
    -c "<command>"	Execute a single SQL command and exit
    -v var=value	Set psql variable (used inside scripts)
    --set=var=value	Same as -v, more explicit
    --dbname=<connstr>	Full connection string, e.g.: postgresql://user:pass@host:port/dbname
    
    Example
    psql -h pg01.oric.no -p 5432 -U myuser -d mydb
    
    Alternatively, use an URL, which is more scripting friendly:
    psql "postgresql://replicate@pg01.oric.no:5432/mydb"
    

    Wednesday, July 30, 2025

    How to find the columns in a specific postgres data dictionary table

    For example, to check which columns belongs to the view "pg_stat_wal_receiver"
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = 'pg_stat_wal_receiver';
    
    Output
         column_name
    -----------------------
     pid
     last_msg_receipt_time
     latest_end_lsn
     latest_end_time
     sender_port
     receive_start_lsn
     receive_start_tli
     written_lsn
     flushed_lsn
     received_tli
     last_msg_send_time
     status
     slot_name
     sender_host
     conninfo
    (15 rows)
    
    Or use the psql metacommand "\d+":
    echo '\d+ pg_stat_wal_receiver' |psql
    
    Output
                                      View "pg_catalog.pg_stat_wal_receiver"
            Column         |           Type           | Collation | Nullable | Default | Storage  | Description
    -----------------------+--------------------------+-----------+----------+---------+----------+-------------
     pid                   | integer                  |           |          |         | plain    |
     status                | text                     |           |          |         | extended |
     receive_start_lsn     | pg_lsn                   |           |          |         | plain    |
     receive_start_tli     | integer                  |           |          |         | plain    |
     written_lsn           | pg_lsn                   |           |          |         | plain    |
     flushed_lsn           | pg_lsn                   |           |          |         | plain    |
     received_tli          | integer                  |           |          |         | plain    |
     last_msg_send_time    | timestamp with time zone |           |          |         | plain    |
     last_msg_receipt_time | timestamp with time zone |           |          |         | plain    |
     latest_end_lsn        | pg_lsn                   |           |          |         | plain    |
     latest_end_time       | timestamp with time zone |           |          |         | plain    |
     slot_name             | text                     |           |          |         | extended |
     sender_host           | text                     |           |          |         | extended |
     sender_port           | integer                  |           |          |         | plain    |
     conninfo              | text                     |           |          |         | extended |
    View definition:
     SELECT pid,
        status,
        receive_start_lsn,
        receive_start_tli,
        written_lsn,
        flushed_lsn,
        received_tli,
        last_msg_send_time,
        last_msg_receipt_time,
        latest_end_lsn,
        latest_end_time,
        slot_name,
        sender_host,
        sender_port,
        conninfo
       FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port, conninfo)
      WHERE pid IS NOT NULL;
    

    Monday, July 28, 2025

    How to list the postgres processes running on your server and what their jobs are

    A neet little trick I learned from the series "Mirage of memory, part 2: PSS"
    ps -fu postgres | grep $(pgrep -f postmaster)
    postgres    1913       1  0 Jul17 ?        00:02:51 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
    postgres    1982    1913  0 Jul17 ?        00:00:24 postgres: logger
    postgres    2063    1913  0 Jul17 ?        00:00:18 postgres: checkpointer
    postgres    2064    1913  0 Jul17 ?        00:00:03 postgres: background writer
    postgres    2119    1913  0 Jul17 ?        00:00:07 postgres: walwriter
    postgres    2120    1913  0 Jul17 ?        00:00:14 postgres: autovacuum launcher
    postgres    2121    1913  0 Jul17 ?        00:00:01 postgres: archiver
    postgres    2122    1913  0 Jul17 ?        00:00:00 postgres: logical replication launcher
    postgres    2657    1913  0 Jul17 ?        00:02:05 postgres: postgres_exporter postgres localhost(48674) idle
    
    The command lists all processes by user "postgres" and and greps for the output of the command "pgrep -f postmaster", which returns a list of process IDs

    You could also count the number of processes by user "postgres" by simply typing
    pgrep -c postmaster
    9
    

    Monday, July 14, 2025

    How to find when a PostgreSQL table was last vacuumed

    SELECT
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
    FROM pg_stat_user_tables
    WHERE relname = 'documents';
       schemaname   | relname  | last_vacuum |        last_autovacuum        |         last_analyze          |       last_autoanalyze
    ----------------+----------+-------------+-------------------------------+-------------------------------+-------------------------------
     scott          | documents|             | 2025-06-25 02:05:20.692869+02 | 2025-07-12 22:04:05.119051+02 | 2025-06-17 08:41:12.567351+02
    (1 row)
    
    Explanation of columns:

  • last_vacuum: Last manual vacuum.
  • last_autovacuum: Last automatic vacuum.
  • last_analyze: Last manual analyze.
  • last_autoanalyze: Last automatic analyze.

    What does ANALYZE do in PostgreSQL?

    ANALYZE collects statistics about the contents of a table (or all tables), such as:
  • Number of rows
  • Distribution of values
  • Most common values
  • Histogram of value ranges
  • Null fractions These statistics are stored in pg_statistic and are used by the query planner to make decisions (e.g., which index to use, join strategies, etc.).

    Syntax to run ANALYZE

    Analyze the entire database (all tables in all schemas):
    ANALYZE;
    
    Analyze a specific table:
    ANALYZE my_table;
    
    Analyze specific columns only:
    ANALYZE my_table (column1, column2);