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"

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;

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

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);

PostgreSQL: how to tail the alert log of the day with filtering for long-running statements

I have set the parameter log_min_duration_statement to 2 seconds and reloaded the config:

postgres=# alter system set log_min_duration_statement='2s';
ALTER SYSTEM

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 2s
(1 row)

I expect every statement that lasts longer than 2 seconds to be logged in the postgreSQL server main log.

From a different session, I execute the following in any database in the cluster:

postgres=# SELECT pg_sleep(3);
 pg_sleep
----------

(1 row)

From another session, tail the log from today:

tail -F /var/log/postgres/postgresql-2025-07-11_000000.log | grep --line-buffered "duration:.*statement:"

Output

2025-07-11 09:45:42 CEST [173349]: [5-1] user=postgres,db=postgres,app=psql,client=[local] LOG:  duration: 3003.698 ms  statement: SELECT pg_sleep(3);

How to list all subdirectories and exclude certain subdirectories in a tree-like fashion

In this example I want to see all subdirectories that starts with "dir", but also to exclude the directory "dirdat":

cd $ORACLE_GG
tree -L 2 --noreport $(ls -d dir* | grep -v '^dirdat$')

ls -d dir*: lists all dir* entries (only directories if they match)

grep -v '^dirdat$': removes dirdat from the list

$(...): expands the filtered list as arguments to tree